Postgres Pro
Downloads
Home   >   mailing lists

Thread: Server v7.3RC2 Dies

Server v7.3RC2 Dies

From
"CN"
Date:
Hi!

My v7.3RC server died. Any idea?

Please note that more message following "key referenced from t40 not f3"
is truncated by telnet program.

Regards,

CN
-------------------------
database1=# alter table table40 add CONSTRAINT fkt40c2 FOREIGN KEY (c2)
REFERENCES table13 (c1) ON UPDATE CASCADE ON delete cascade;
NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR:  fkt40c2 referential integrity violation - key referenced from t40
not f3
database1=# copy table40 to '/tmp/table40';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
--
http://fastmail.fm - mmm... fastmail...

Re: Server v7.3RC2 Dies

From
"CN"
Date:
This is to follow up my previous post.
I notice it is a problem coming from COPY command. I can select the table
but both COPY and pg_dump abort with similar messages.
================
database1=# select * from table2;
 c1 |          c2
----+-----------------------
  3 | English
  1 | Chinese
  2 | French
(3 rows)

server:/tmp$ pg_dump database1>db1
pg_dump: SQL command to dump the contents of table "table2" failed
pg_dump: Error message from server: server closed the connection
unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_dump: The command was: COPY public.table2 (c1, c2) TO stdout;
================
Regards,

CN
--
http://fastmail.fm - The professional email service

Re: Server v7.3RC2 Dies

From
Tom Lane
Date:
"CN" <cnliou9@fastmail.fm> writes:
> This is to follow up my previous post.
> I notice it is a problem coming from COPY command. I can select the table
> but both COPY and pg_dump abort with similar messages.

Bizarre.  What exactly is the schema of the table (pg_dump -s -t table2
would be useful to see)?  What shows up in the postmaster log?  Can you
get a stack backtrace from the core dump?

            regards, tom lane

Re: Server v7.3RC2 Dies

From
Neil Conway
Date:
On Wed, 2002-11-27 at 07:54, CN wrote:
> database1=# alter table table40 add CONSTRAINT fkt40c2 FOREIGN KEY (c2)
> REFERENCES table13 (c1) ON UPDATE CASCADE ON delete cascade;
> NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR:  fkt40c2 referential integrity violation - key referenced from t40
> not f3
> database1=# copy table40 to '/tmp/table40';
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

Can you get a backtrace from the core file? The core file should be
located in $PGDATA/base/$oid_of_database ($oid_of_database being the OID
of the database the backend was connected to). If you could recompile
PostgreSQL with debugging symbols (./configure --enable-debug), that
would be more useful.

Also, can you get a reproducible example of the crash? I tried to
reproduce it based on your other message (table2, columns c1 & c2,
failed attempt to add an FK), but couldn't produce any problems...

Cheers,

Neil

Re: Server v7.3RC2 Dies

From
"CN"
Date:
I have done
reindex table <the tables>
but still can not COPY. I have tried COPYing 4 or 5 different tables
without success. I guess my database is corrupted if no one else
encounters this same problem. If so, please ignore this problem as I can
rebuild the database though it takes much time.

> > This is to follow up my previous post.
> > I notice it is a problem coming from COPY command. I can select the table
> > but both COPY and pg_dump abort with similar messages.
>
> Bizarre.  What exactly is the schema of the table (pg_dump -s -t table2
> would be useful to see)?

--
-- PostgreSQL database dump
--

\connect - dba

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 17891)
-- Name: t1; Type: TABLE; Schema: public; Owner: dba
--

CREATE TABLE t1 (
    f1 integer DEFAULT nextval('"t1_f1_seq"'::text) NOT NULL
);


--
-- TOC entry 3 (OID 17894)
-- Name: t1_pkey; Type: CONSTRAINT; Schema: public; Owner: dba
--
ALTER TABLE ONLY t1
    ADD CONSTRAINT t1_pkey PRIMARY KEY (f1);

--
-- TOC entry 4 (OID 73547)
-- Name: RI_ConstraintTrigger_73547; Type: TRIGGER; Schema: public;
Owner: dba
--

CREATE CONSTRAINT TRIGGER fkd1f1
    AFTER DELETE ON t1
    FROM d1
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_cascade_del" ('fkd1f1', 'd1', 't1',
    'UNSPECIFIED', 'f1', 'f1');
-- TOC entry 5 (OID 73548)
-- Name: RI_ConstraintTrigger_73548; Type: TRIGGER; Schema: public;
Owner: dba
--

CREATE CONSTRAINT TRIGGER fkd1f1
    AFTER UPDATE ON t1
    FROM d1
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('fkd1f1', 'd1', 't1',
    'UNSPECIFIED'
, 'f1', 'f1');

--
-- TOC entry 6 (OID 73556)
-- Name: RI_ConstraintTrigger_73556; Type: TRIGGER; Schema: public;
Owner: dba
--
CREATE CONSTRAINT TRIGGER fkt5f2
    AFTER DELETE ON t1
    FROM t5
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_noaction_del" ('fkt5f2', 't5', 't1',
    'UNSPECIFIED
', 'f2', 'f1');

--
-- TOC entry 7 (OID 73557)
-- Name: RI_ConstraintTrigger_73557; Type: TRIGGER; Schema: public;
Owner: dba
--

CREATE CONSTRAINT TRIGGER fkt5f2
    AFTER UPDATE ON t1
    FROM t5
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('fkt5f2', 't5', 't1',
    'UNSPECIFIED', 'f2','f1');

> What shows up in the postmaster log?

How magic postgreSQL is! The log looks like postmaster is able to be
reborn by itself.

LOG:  database system was shut down at 2002-11-28 21:32:46 CST
LOG:  checkpoint record is at 0/20CBE0C
LOG:  redo record is at 0/20CBE0C; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 5406; next oid: 98763
LOG:  database system is ready
LOG:  server process (pid 1625) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing shared memory and
semaphores
LOG:  database system was interrupted at 2002-11-28 21:33:04 CST
LOG:  checkpoint record is at 0/20CBE0C
LOG:  redo record is at 0/20CBE0C; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 5406; next oid: 98763
LOG:  database system was not properly shut down; automatic recovery in
progress
FATAL:  The database system is starting up
LOG:  ReadRecord: record with zero length at 0/20CBE4C
LOG:  redo is not required
LOG:  database system is ready

> Can you get a stack backtrace from the core dump?
>

I don't know how to do that! ^.^

Best regards,

CN
--
http://fastmail.fm - Same, same, but different...

Re: Server v7.3RC2 Dies

From
Tom Lane
Date:
"CN" <cnliou9@fastmail.fm> writes:
> LOG:  server process (pid 1625) was terminated by signal 11

Definitely a backend crash ...

>> Can you get a stack backtrace from the core dump?

> I don't know how to do that! ^.^

See our mail list archives for previous explanations, but basically

1. find the "core" file at $PGDATA/base/yourdbnum/core (if not there,
restart postmaster with "ulimit -c unlimited" context and cause problem
again).

2. gdb /path/to/postgres /path/to/core
   bt
   quit

            regards, tom lane

Re: Server v7.3RC2 Dies

From
"CN"
Date:
> 1. find the "core" file at $PGDATA/base/yourdbnum/core (if not there,
> restart postmaster with "ulimit -c unlimited" context and cause problem
> again).
>
> 2. gdb /path/to/postgres /path/to/core
>    bt
>    quit
>

Thank you a lot! Tom,
I am not sure if this helps diagnose.

Regards,

CN
==============================
Attaching to process 16985
ptrace: No such process.
Core was generated by `postgres: d'.
Program terminated with signal 11, Segmentation fault.
#0  0x0817c540 in ?? ()
(gdb) bt
#0  0x0817c540 in ?? ()
#1  0x080badfb in ?? ()
#2  0x080ba8f1 in ?? ()
#3  0x081285c9 in ?? ()
#4  0x081263e4 in ?? ()
#5  0x081275b5 in ?? ()
#6  0x0810e70f in ?? ()
#7  0x0810df15 in ?? ()
#8  0x0810d07a in ?? ()
#9  0x0810cbcf in ?? ()
#10 0x080e9257 in ?? ()
#11 0x400df5c6 in ?? ()
(gdb) quit
--
http://fastmail.fm - Accessible with your email software
                      or over the web

Re: Server v7.3RC2 Dies

From
Tom Lane
Date:
"CN" <cnliou9@fastmail.fm> writes:
> I am not sure if this helps diagnose.

> Program terminated with signal 11, Segmentation fault.
> #0  0x0817c540 in ?? ()
> (gdb) bt
> #0  0x0817c540 in ?? ()
> #1  0x080badfb in ?? ()
> #2  0x080ba8f1 in ?? ()
> #3  0x081285c9 in ?? ()
> #4  0x081263e4 in ?? ()
> #5  0x081275b5 in ?? ()
> #6  0x0810e70f in ?? ()
> #7  0x0810df15 in ?? ()
> #8  0x0810d07a in ?? ()
> #9  0x0810cbcf in ?? ()
> #10 0x080e9257 in ?? ()
> #11 0x400df5c6 in ?? ()
> (gdb) quit

Um, no :-(.  You seem to be using a postgres executable that's been
stripped of even minimal debugging info.  We need some routine names
in that list, at the very least.

Can you rebuild Postgres with --enable-debug in the configure options?

            regards, tom lane

Re: Server v7.3RC2 Dies

From
"CN"
Date:
Attaching to program: /opt/pgsql/bin/postgres, process 16985
ptrace: No such process.

warning: core file may not match specified executable file.
Core was generated by `postgres: dba d'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /usr/lib/libz.so.1...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /usr/lib/libreadline.so.3...done.
Loaded symbols for /usr/lib/libreadline.so.3
Reading symbols from /lib/libcrypt.so.1...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libresolv.so.2...done.
Loaded symbols for /lib/libresolv.so.2
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/libm.so.6...done.
Loaded symbols for /lib/libm.so.6
Reading symbols from /lib/libc.so.6...done.
Loaded symbols for /lib/libc.so.6
Reading symbols from /lib/libtermcap.so.2...done.
Loaded symbols for /lib/libtermcap.so.2
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_compat.so.2...done.
Loaded symbols for /lib/libnss_compat.so.2
#0  0x0817c870 in pfree (pointer=0x82a9e18) at mcxt.c:480
480             (*header->context->methods->free_p) (header->context,
pointer);
--
http://fastmail.fm - A fast, anti-spam email service.