Обсуждение: An statement causes postmaster to die

Sean Kelly (lists@shortestpath.org) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
An statement causes postmaster to die

Long Description
I am having a problem trying to run an update on my database. The SQL that causes the problem is listed below, as is
somedata from the tables used. 

The message I see is one of the following.  It's either:

Server process (pid 22322) exited with status 11 at Sun Apr 22 16:14:15 2001
Terminating any active server processes...
Server processes were terminated at Sun Apr 22 16:14:15 2001
Reinitializing shared memory and semaphores
DEBUG:  Data Base System is starting up at Sun Apr 22 16:14:15 2001
DEBUG:  Data Base System was interrupted being in production at Sun Apr 22 16:14:06 2001
DEBUG:  Data Base System is in production state at Sun Apr 22 16:14:15 2001

or

Server process (pid 22408) exited with status 11 at Sun Apr 22 16:18:05 2001
Terminating any active server processes...
NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
Server processes were terminated at Sun Apr 22 16:18:05 2001
Reinitializing shared memory and semaphores
DEBUG:  Data Base System is starting up at Sun Apr 22 16:18:05 2001
DEBUG:  Data Base System was interrupted being in production at Sun Apr 22 16:14:15 2001
DEBUG:  Data Base System is in production state at Sun Apr 22 16:18:05 2001

I am running PostgreSQL 7.0.2 on Linux 2.2.16 using an Intel Celeron 333 and 128Mb RAM.

Sample Code
isp=> \d domain_tbl
         Table "domain_tbl"
 Attribute |    Type     | Modifier
-----------+-------------+----------
 domain    | varchar(80) | not null
 ident     | varchar(5)  | not null
 max_email | integer     | not null
 username  | varchar(10) | not null
 added     | timestamp   |
Indices: domain_tbl_ident_key,
         domain_tbl_pkey

isp=> SELECT * from domain_tbl where domain='test.domain';
   domain    | ident | max_email | username |         added
-------------+-------+-----------+----------+------------------------
 test.domain | test  |        10 | sean     | 2001-04-22 16:17:22+01
(1 row)

isp=> update domain_tbl set max_email='15' where domain='test.domain';
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

No file was uploaded with this report

Sean Kelly (lists@shortestpath.org) writes:
> isp=> update domain_tbl set max_email='15' where domain='test.domain';
> pqReadData() -- backend closed the channel unexpectedly.

Can't tell much of anything from this.  Can you provide a gdb backtrace
from the corefile that the failed backend presumably left?  It should be
in $PGDATA/base/isp/core.

If you don't see a corefile there, you may need to restart the
postmaster with "ulimit -c unlimited" to allow core dumping.

            regards, tom lane

On Sun, 22 Apr 2001, tgl@sss.pgh.pa.us wrote:

> Can't tell much of anything from this.  Can you provide a gdb backtrace
> from the corefile that the failed backend presumably left?  It should be
> in $PGDATA/base/isp/core.

    I have the core file but cannot remember how to make a backtrace.
Can you give me the steps please?

    Thanks,

--
Sean

On Sun, 22 Apr 2001, tgl@sss.pgh.pa.us wrote:

> Can't tell much of anything from this.  Can you provide a gdb backtrace
> from the corefile that the failed backend presumably left?  It should be
> in $PGDATA/base/isp/core.

    Please ignore my last mail, I've found some documentation.
Starting gdb like:

        gdb ~/bin/postgres core

as the PostgreSQL user and getting a backtrace looks like:

(gdb) bt
#0  0x8115eb2 in ri_BuildQueryKeyFull ()
#1  0x8115dc2 in RI_FKey_keyequal_upd ()
#2  0x8096d7c in DeferredTriggerSaveEvent ()
#3  0x8096016 in ExecARUpdateTriggers ()
#4  0x809c617 in ExecReplace ()
#5  0x809c256 in ExecutePlan ()
#6  0x809b8f3 in ExecutorRun ()
#7  0x80eb46a in ProcessQueryDesc ()
#8  0x80eb4d0 in ProcessQuery ()
#9  0x80ea153 in pg_exec_query_dest ()
#10 0x80ea033 in pg_exec_query ()
#11 0x80eaeec in PostgresMain ()
#12 0x80d565a in DoBackend ()
#13 0x80d523a in BackendStartup ()
#14 0x80d45ee in ServerLoop ()
#15 0x80d407c in PostmasterMain ()
#16 0x80ab115 in main ()
#17 0x400fa577 in __libc_start_main () from /lib/libc.so.6
(gdb)

    Thanks,

--
Sean

Sean Kelly <lists@shortestpath.org> writes:
> (gdb) bt
> #0  0x8115eb2 in ri_BuildQueryKeyFull ()
> #1  0x8115dc2 in RI_FKey_keyequal_upd ()

Ah, you're probably running into the 7.0 bug about not coping gracefully
when the referenced table for a foreign key constraint has been dropped
or renamed.  This is repaired in 7.0.3, at least to the extent that you
get an error message instead of a crash.

            regards, tom lane