Обсуждение: PG crash on simple query

Hi,
I have consistent PG crash on some query,
so I was able to isolate a simple query that crash postgres...

SELECT p.docid FROM prod.t_documents AS p
INNER JOIN t_tempdocs AS t
    ON p.docid = t.docid
        LEFT OUTER JOIN prod.t_refs AS ct
            ON ct.docid = p.docid;

It seems like any reference to prod.t_refs lead to that.

We run PG 7.3 on SunOS

Is there any particular steps to follow in order to resolve this.
Thank you.

"Maksim Likharev" <mlikharev@aurigin.com> writes:
> I have consistent PG crash on some query,
> so I was able to isolate a simple query that crash postgres...

> SELECT p.docid FROM prod.t_documents AS p
> INNER JOIN t_tempdocs AS t
>     ON p.docid = t.docid
>         LEFT OUTER JOIN prod.t_refs AS ct
>             ON ct.docid = p.docid;

It's difficult to provide any advice when you haven't told us exactly
what the failure is nor shown the table definitions ...

> We run PG 7.3 on SunOS

I'd recommend an update to 7.3.3 on general principles, but it's
impossible to guess whether that would fix the problem for you.

            regards, tom lane

Is there any way to see what really happened?
I mean more exhausted debug info.
It seems like other server running same PG 7.3 and more or less
identical hardware is not affected by that.

Info starts here:

--main table
CREATE TABLE prod.t_documents (
  documentid int4 NOT NULL,
  docid char(16) NOT NULL,
  documentnum char(12) NOT NULL,
  issue date,
  file date,
  sections int4,
  section1 varchar(20),
  language char(3),
  filter char(3),
  CONSTRAINT pk_t_documentd PRIMARY KEY (documentid)
) WITH OIDS;

number of rows: 20000000

--ref table
CREATE TABLE prod.t_refs (
  documentid int4       NOT NULL,
  docid      char(16))  NOT NULL
) WITH OIDS;

number of rows: 45000000

t_tempdocs (char(16))

Log:

LOG:  server process (pid 2004) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  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.
FATAL:  The database system is in recovery mode
LOG:  all server processes terminated; reinitializing shared memory and
semaphores
LOG:  database system was interrupted at 2003-06-29 12:41:26 EDT
LOG:  checkpoint record is at D/F899DBE0
LOG:  redo record is at D/F899DBE0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 16135; next oid: 345209319
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  ReadRecord: record with zero length at D/F899DC20
LOG:  redo is not required
LOG:  database system is ready

"Maksim Likharev" <mlikharev@aurigin.com> writes:
> LOG:  server process (pid 2004) was terminated by signal 11

> Is there any way to see what really happened?

I would like to see a stack backtrace (get a core dump, use gdb
on it.  See the archives for descriptions of the standard procedure...)

            regards, tom lane

Ok,
seems that story came to the end,
corrupted index or table,
after full table reload and index rebuild ( that is by itself very
painful
procedure, especially if number of row ~50M )
works, may be problem resolved.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sunday, June 29, 2003 3:25 PM
To: Maksim Likharev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PG crash on simple query


"Maksim Likharev" <mlikharev@aurigin.com> writes:
> LOG:  server process (pid 2004) was terminated by signal 11

> Is there any way to see what really happened?

I would like to see a stack backtrace (get a core dump, use gdb
on it.  See the archives for descriptions of the standard procedure...)

            regards, tom lane