Обсуждение: BUG #5669: server process was terminated by exception 0xC0000005
The following bug has been logged online: Bug reference: 5669 Logged by: Andrew Geery Email address: andrew.geery@gmail.com PostgreSQL version: 9.0 Operating system: Windows Vista Description: server process was terminated by exception 0xC0000005 Details: My PG 9.0 DB (EnterpriseDB edition 32-bit) on Windows Vista SP1 crashes pretty predictably. I executed some SQL and the server crashes. Actually, the SQL completes successfully and then the server crashes. I've tried rebooting my workstation which is running the db server but that doesn't fix things. Searching through the mail archives, the usual advice is to disable virus protection and firewalls. Let me know what other information I can supply! Thanks Andrew 2010-09-21 14:23:03 EDT LOG: connection authorized: user=postgres database=beast 2010-09-21 14:23:04 EDT LOG: connection received: host=127.0.0.1 port=50101 2010-09-21 14:23:04 EDT LOG: connection authorized: user=postgres database=beast 2010-09-21 14:23:05 EDT LOG: disconnection: session time: 0:00:01.412 user=postgres database=beast host=127.0.0.1 port=50101 2010-09-21 14:23:05 EDT LOG: disconnection: session time: 0:00:02.640 user=postgres database=beast host=127.0.0.1 port=50100 2010-09-21 14:24:08 EDT LOG: server process (PID 1004) was terminated by exception 0xC0000005 2010-09-21 14:24:08 EDT HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. 2010-09-21 14:24:08 EDT LOG: terminating any other active server processes 2010-09-21 14:24:08 EDT WARNING: terminating connection because of crash of another server process 2010-09-21 14:24:08 EDT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-09-21 14:24:08 EDT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-09-21 14:24:08 EDT WARNING: terminating connection because of crash of another server process 2010-09-21 14:24:08 EDT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-09-21 14:24:08 EDT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-09-21 14:24:08 EDT WARNING: terminating connection because of crash of another server process 2010-09-21 14:24:08 EDT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-09-21 14:24:08 EDT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-09-21 14:24:08 EDT WARNING: terminating connection because of crash of another server process 2010-09-21 14:24:08 EDT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-09-21 14:24:08 EDT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-09-21 14:24:08 EDT LOG: all server processes terminated; reinitializing 2010-09-21 14:24:18 EDT FATAL: pre-existing shared memory block is still in use 2010-09-21 14:24:18 EDT HINT: Check if there are any old server processes still running, and terminate them.
On 21/09/10 21:57, Andrew Geery wrote: > > The following bug has been logged online: > > Bug reference: 5669 > Logged by: Andrew Geery > Email address: andrew.geery@gmail.com > PostgreSQL version: 9.0 > Operating system: Windows Vista > Description: server process was terminated by exception 0xC0000005 > Details: > > My PG 9.0 DB (EnterpriseDB edition 32-bit) on Windows Vista SP1 crashes > pretty predictably. > > I executed some SQL and the server crashes. > > Actually, the SQL completes successfully and then the server crashes. What's the query that causes the crash? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
I'm just doing insert statements via JDBC. Here's the table definition:
CREATE TABLE collection
(
id serial NOT NULL,
app_uid character(36) NOT NULL DEFAULT uuid_generate_v4(),
lock_rev integer NOT NULL DEFAULT 0,
collection_type_code character(3) NOT NULL,
asset_type_code character(3) NOT NULL,
code character varying(10),
"name" character varying(128) NOT NULL,
title_prefix character varying(128),
item_count integer,
is_fixed_count boolean NOT NULL DEFAULT false,
is_active boolean NOT NULL DEFAULT true,
CONSTRAINT pk_collection PRIMARY KEY (id),
CONSTRAINT collection_collection_type_code_fkey FOREIGN KEY
(collection_type_code)
REFERENCES collection_type (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT collection_type_code_fkey FOREIGN KEY (asset_type_code)
REFERENCES asset_type (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT collection_app_uid_key UNIQUE (app_uid)
)
WITH (
OIDS=3DFALSE
);
Thanks
Andrew
On Tue, Sep 21, 2010 at 3:00 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 21/09/10 21:57, Andrew Geery wrote:
>>
>> The following bug has been logged online:
>>
>> Bug reference: =A0 =A0 =A05669
>> Logged by: =A0 =A0 =A0 =A0 =A0Andrew Geery
>> Email address: =A0 =A0 =A0andrew.geery@gmail.com
>> PostgreSQL version: 9.0
>> Operating system: =A0 Windows Vista
>> Description: =A0 =A0 =A0 =A0server process was terminated by exception 0=
xC0000005
>> Details:
>>
>> My PG 9.0 DB (EnterpriseDB edition 32-bit) on Windows Vista SP1 crashes
>> pretty predictably.
>>
>> I executed some SQL and the server crashes.
>>
>> Actually, the SQL completes successfully and then the server crashes.
>
> What's the query that causes the crash?
>
> --
> =A0Heikki Linnakangas
> =A0EnterpriseDB =A0 http://www.enterprisedb.com
>
Running the server in debug mode, I see the following before the server crashes -- it looks like something goes wrong with autovac_balance_cost when trying to analyze the collection table (that was the table the inserts were being done into). Thanks Andrew 2010-09-21 16:27:40 EDTDEBUG: collection: vac: 0 (threshold 50), anl: 76 (threshold 50) 2010-09-21 16:27:40 EDTDEBUG: autovac_balance_cost(pid=3D5896 db=3D4329981, rel=3D4331344, cost_limit=3D200, cost_delay=3D20) 2010-09-21 16:27:40 EDTDEBUG: CommitTransaction 2010-09-21 16:27:40 EDTDEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-09-21 16:27:40 EDTDEBUG: StartTransaction 2010-09-21 16:27:40 EDTDEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-09-21 16:27:40 EDTDEBUG: analyzing "public.collection" 2010-09-21 16:27:41 EDTDEBUG: reaping dead processes 2010-09-21 16:27:41 EDTDEBUG: server process (PID 5896) was terminated by exception 0xC0000005 2010-09-21 16:27:41 EDTHINT: See C include file "ntstatus.h" for a description of the hexadecimal value. 2010-09-21 16:27:41 EDTLOG: server process (PID 5896) was terminated by exception 0xC0000005 2010-09-21 16:27:41 EDTHINT: See C include file "ntstatus.h" for a description of the hexadecimal value. 2010-09-21 16:27:41 EDTLOG: terminating any other active server processes On Tue, Sep 21, 2010 at 3:12 PM, Andrew Geery <andrew.geery@gmail.com> wrot= e: > I'm just doing insert statements via JDBC. =A0Here's the table definition: > > CREATE TABLE collection > ( > =A0id serial NOT NULL, > =A0app_uid character(36) NOT NULL DEFAULT uuid_generate_v4(), > =A0lock_rev integer NOT NULL DEFAULT 0, > =A0collection_type_code character(3) NOT NULL, > =A0asset_type_code character(3) NOT NULL, > =A0code character varying(10), > =A0"name" character varying(128) NOT NULL, > =A0title_prefix character varying(128), > =A0item_count integer, > =A0is_fixed_count boolean NOT NULL DEFAULT false, > =A0is_active boolean NOT NULL DEFAULT true, > =A0CONSTRAINT pk_collection PRIMARY KEY (id), > =A0CONSTRAINT collection_collection_type_code_fkey FOREIGN KEY > (collection_type_code) > =A0 =A0 =A0REFERENCES collection_type (code) MATCH SIMPLE > =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION, > =A0CONSTRAINT collection_type_code_fkey FOREIGN KEY (asset_type_code) > =A0 =A0 =A0REFERENCES asset_type (code) MATCH SIMPLE > =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION, > =A0CONSTRAINT collection_app_uid_key UNIQUE (app_uid) > ) > WITH ( > =A0OIDS=3DFALSE > ); > > Thanks > Andrew > > On Tue, Sep 21, 2010 at 3:00 PM, Heikki Linnakangas > <heikki.linnakangas@enterprisedb.com> wrote: >> On 21/09/10 21:57, Andrew Geery wrote: >>> >>> The following bug has been logged online: >>> >>> Bug reference: =A0 =A0 =A05669 >>> Logged by: =A0 =A0 =A0 =A0 =A0Andrew Geery >>> Email address: =A0 =A0 =A0andrew.geery@gmail.com >>> PostgreSQL version: 9.0 >>> Operating system: =A0 Windows Vista >>> Description: =A0 =A0 =A0 =A0server process was terminated by exception = 0xC0000005 >>> Details: >>> >>> My PG 9.0 DB (EnterpriseDB edition 32-bit) on Windows Vista SP1 crashes >>> pretty predictably. >>> >>> I executed some SQL and the server crashes. >>> >>> Actually, the SQL completes successfully and then the server crashes. >> >> What's the query that causes the crash? >> >> -- >> =A0Heikki Linnakangas >> =A0EnterpriseDB =A0 http://www.enterprisedb.com >> >
Excerpts from Andrew Geery's message of mar sep 21 16:37:20 -0400 2010: > Running the server in debug mode, I see the following before the > server crashes -- it looks like something goes wrong with > autovac_balance_cost when trying to analyze the collection table (that > was the table the inserts were being done into). > > Thanks Hmm, can you get a stack trace? Please see http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows Probably the easiest way to catch a dying autovacuum process is to set a very high value of vacuum_cost_delay, so that it sleeps for long enough that you can attach to it with the debugger before it dies. Thanks -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I think I've tracked down the problem, although I haven't been able to
get a stack trace yet. I'm afraid that it's my own stupidity, but I
still don't understand why this crashes the entire engine.
On the collection table, I have a FTS index. I probably should have
defined the index this way:
create index ix_collection_name_fts on collection using
gin(to_tsvector('english', name));
but instead I defined it this way:
create index ix_collection_name_fts on collection using
gin(to_tsvector(get_fts_config_name(), name));
where the definition of the function is:
create or replace function get_fts_config_name() returns regconfig as $$
select setting::regconfig from pg_settings
where name =3D 'default_text_search_config';
$$ language sql immutable;
Once I remove the bad FTS index and replaced it with the good index,
the server no longer crashes.
Reading about immutable functions here
[http://developer.postgresql.org/pgdocs/postgres/xfunc-volatility.html],
made it sound like the only problem with making a volatile function
immutable is that the query planner might do the wrong thing. I
didn't realize it could have such drastic results... Is it still
worth getting a stack trace or is this just a don't-ever-do-that
thing?
Thanks
Andrew
On Tue, Sep 21, 2010 at 5:50 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Andrew Geery's message of mar sep 21 16:37:20 -0400 2010:
>> Running the server in debug mode, I see the following before the
>> server crashes -- it looks like something goes wrong with
>> autovac_balance_cost when trying to analyze the collection table (that
>> was the table the inserts were being done into).
>>
>> Thanks
>
> Hmm, can you get a stack trace? =A0Please see
> http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_Postgr=
eSQL_backend_on_Windows
>
> Probably the easiest way to catch a dying autovacuum process is to set a
> very high value of vacuum_cost_delay, so that it sleeps for long enough
> that you can attach to it with the debugger before it dies.
>
> Thanks
>
> --
> =C1lvaro Herrera <alvherre@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
Andrew Geery <andrew.geery@gmail.com> writes:
> I think I've tracked down the problem, although I haven't been able to
> get a stack trace yet. I'm afraid that it's my own stupidity, but I
> still don't understand why this crashes the entire engine.
Me either. Can you narrow it down to a self-contained test case so
others can reproduce the problem?
regards, tom lane
The schema below crashes pretty reliably for me. There are three
objects: an immutable function, a table called a and a gin index on
table a using the immutable function. To reproduce the crash, do
enough inserts into table a to kick off the auto-vacuum process; the
insert statement below with the generate_series should do this. I
tested this on Windows Vista using PG 9.0 and also on Linux (RHES4)
using 8.4.4 (both packaged by EnterpriseDB) and it crashed both
servers.
Thanks
Andrew
create or replace function get_fts_config_name() returns regconfig as $$
select setting::regconfig from pg_settings
where name =3D 'default_text_search_config';
$$ language sql immutable;
CREATE TABLE a
(
id serial primary key,
my_text varchar(128) not null
);
create index ix_a_my_text on a using
gin(to_tsvector(get_fts_config_name(), my_text));
insert into a (my_text) select 'Test' from generate_series(1,10000);
On Wed, Sep 22, 2010 at 11:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew Geery <andrew.geery@gmail.com> writes:
>> I think I've tracked down the problem, although I haven't been able to
>> get a stack trace yet. =A0I'm afraid that it's my own stupidity, but I
>> still don't understand why this crashes the entire engine.
>
> Me either. =A0Can you narrow it down to a self-contained test case so
> others can reproduce the problem?
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>
Andrew Geery <andrew.geery@gmail.com> writes:
> The schema below crashes pretty reliably for me.
OK ... so the problem is that the function uses the pg_settings view,
which results in calling show_all_settings(), and in particular it
runs show_session_authorization(), which then dumps core because
session_authorization_string is NULL in an autovacuum process.
As far as a fix for the crash goes, I'm not sure if it'd be better to
try to make show_session_authorization() return some sort of default
value in this scenario, or to try to ensure that the variable has been
set to something valid before we start running user-supplied code.
In either case the problem is potentially wider than this one function
and variable. Thoughts anyone?
As far as the function itself goes, ISTM you ought to rethink it,
because this looks like the very opposite of immutable to me:
> create or replace function get_fts_config_name() returns regconfig as $$
> select setting::regconfig from pg_settings
> where name = 'default_text_search_config';
> $$ language sql immutable;
That's no excuse for the backend crashing, of course, but I would not
be surprised at all if you end up with corrupt indexes due to the
allegedly-immutable value changing.
regards, tom lane
Excerpts from Tom Lane's message of mié sep 22 12:39:24 -0400 2010: > OK ... so the problem is that the function uses the pg_settings view, > which results in calling show_all_settings(), and in particular it > runs show_session_authorization(), which then dumps core because > session_authorization_string is NULL in an autovacuum process. Ugh. > As far as a fix for the crash goes, I'm not sure if it'd be better to > try to make show_session_authorization() return some sort of default > value in this scenario, or to try to ensure that the variable has been > set to something valid before we start running user-supplied code. > In either case the problem is potentially wider than this one function > and variable. Thoughts anyone? My first thought is that the weird encoding of session_authorization_string should better be contained in as few places as possible, so we shouldn't try to initialize it to something valid-looking. Seems easier to have show_session_authorization() cope with a NULL value. But what would this default value be? -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Tom Lane's message of mié sep 22 12:39:24 -0400 2010:
>> As far as a fix for the crash goes, I'm not sure if it'd be better to
>> try to make show_session_authorization() return some sort of default
>> value in this scenario, or to try to ensure that the variable has been
>> set to something valid before we start running user-supplied code.
>> In either case the problem is potentially wider than this one function
>> and variable. Thoughts anyone?
> My first thought is that the weird encoding of
> session_authorization_string should better be contained in as few places
> as possible, so we shouldn't try to initialize it to something
> valid-looking. Seems easier to have show_session_authorization() cope
> with a NULL value.
Yeah, coping with a NULL seems like the best thing to me too after
further reflection: the other way couldn't possibly cope with scenarios
like "show_session_authorization gets called before we got around to
initializing the variable".
> But what would this default value be?
Wouldn't an empty string be acceptable? SQL doesn't allow zero-length
identifiers, so this couldn't be confused with any really-valid value.
regards, tom lane
Excerpts from Tom Lane's message of mié sep 22 13:03:06 -0400 2010: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > But what would this default value be? > > Wouldn't an empty string be acceptable? SQL doesn't allow zero-length > identifiers, so this couldn't be confused with any really-valid value. I dunno really -- what is this value used for, anyway? If it's just to be able to generate the SHOW table which is then parsed to obtain the fulltext configuration, seems OK. -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Tom Lane's message of mié sep 22 13:03:06 -0400 2010:
>> Wouldn't an empty string be acceptable? SQL doesn't allow zero-length
>> identifiers, so this couldn't be confused with any really-valid value.
> I dunno really -- what is this value used for, anyway? If it's just to be
> able to generate the SHOW table which is then parsed to obtain the
> fulltext configuration, seems OK.
Well, the more general point is what should "SHOW session_authorization"
show in an autovacuum process? The fact that Andrew wasn't
intentionally doing that doesn't mean that someone else might not try
it. I think we can either decide it should be an obviously-illegal
value, or try to make it return the name of the cluster-creating
superuser. The latter seems like more work than it's worth though.
regards, tom lane
Excerpts from Tom Lane's message of mié sep 22 13:36:18 -0400 2010: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Excerpts from Tom Lane's message of mié sep 22 13:03:06 -0400 2010: > >> Wouldn't an empty string be acceptable? SQL doesn't allow zero-length > >> identifiers, so this couldn't be confused with any really-valid value. > > > I dunno really -- what is this value used for, anyway? If it's just to be > > able to generate the SHOW table which is then parsed to obtain the > > fulltext configuration, seems OK. > > Well, the more general point is what should "SHOW session_authorization" > show in an autovacuum process? The fact that Andrew wasn't > intentionally doing that doesn't mean that someone else might not try > it. I think we can either decide it should be an obviously-illegal > value, or try to make it return the name of the cluster-creating > superuser. The latter seems like more work than it's worth though. +1 for the illegal value. -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Tom Lane's message of mié sep 22 13:36:18 -0400 2010:
>> Well, the more general point is what should "SHOW session_authorization"
>> show in an autovacuum process? The fact that Andrew wasn't
>> intentionally doing that doesn't mean that someone else might not try
>> it. I think we can either decide it should be an obviously-illegal
>> value, or try to make it return the name of the cluster-creating
>> superuser. The latter seems like more work than it's worth though.
> +1 for the illegal value.
OK. I just dug through all the other show-hook functions and confirmed
that show_session_authorization is the only one that will dump core if
its variable is NULL, so it looks like we have just one case to fix.
Will get on it once I get my repo back together ...
regards, tom lane
On Wed, Sep 22, 2010 at 1:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> Excerpts from Tom Lane's message of mi=E9 sep 22 13:36:18 -0400 2010: >>> Well, the more general point is what should "SHOW session_authorization" >>> show in an autovacuum process? =A0The fact that Andrew wasn't >>> intentionally doing that doesn't mean that someone else might not try >>> it. =A0I think we can either decide it should be an obviously-illegal >>> value, or try to make it return the name of the cluster-creating >>> superuser. =A0The latter seems like more work than it's worth though. > >> +1 for the illegal value. > > OK. =A0I just dug through all the other show-hook functions and confirmed > that show_session_authorization is the only one that will dump core if > its variable is NULL, so it looks like we have just one case to fix. > Will get on it once I get my repo back together ... Can SHOW return a NULL value, rather than the empty string? --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes:
> Can SHOW return a NULL value, rather than the empty string?
I think that would take some work in guc.c, and likely a redefinition
of the API for show-hook functions. I'm not excited about doing it,
particularly not in a bug fix that needs to be back-patched.
regards, tom lane
On Wed, Sep 22, 2010 at 9:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> Can SHOW return a NULL value, rather than the empty string? > > I think that would take some work in guc.c, and likely a redefinition > of the API for show-hook functions. =A0I'm not excited about doing it, > particularly not in a bug fix that needs to be back-patched. OK. Yeah, that doesn't seem worth it. It was just a thought. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company