Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query crashes/hangs server

Query crashes/hangs server

From
Bruce Momjian
Date:
I recieved this report of a failing set of queries:
BEGIN;CREATE TABLE a (i INT);INSERT INTO a VALUES(1);DECLARE acur CURSOR FOR SELECT * FROM a;FETCH acur;\q

It certainly looks like a simple set of queries.

If this is done in 8.0.X the server shows:FATAL:  block 0 of 1663/17230/58190 is still referenced (private 2,global
1)LOG: server process (PID 14655) exited with exit code 1LOG:  terminating any other active server processesLOG:  all
serverprocesses terminated; reinitializingLOG:  database system was interrupted at 2005-03-17 23:20:52 EST
 

In CVS HEAD this seems to hang the server session in a way I have not
determined, but shutting down the server is impossible unless you use
pg_ctl -m immediate stop.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: Query crashes/hangs server

From
"Qingqing Zhou"
Date:
"Bruce Momjian" <pgman@candle.pha.pa.us>
> I recieved this report of a failing set of queries:
>
> BEGIN;
> CREATE TABLE a (i INT);
> INSERT INTO a VALUES(1);
> DECLARE acur CURSOR FOR SELECT * FROM a;
> FETCH acur;
> \q
>
> It certainly looks like a simple set of queries.
>
> If this is done in 8.0.X the server shows:
>
> FATAL:  block 0 of 1663/17230/58190 is still referenced (private 2,
> global 1)
> LOG:  server process (PID 14655) exited with exit code 1
> LOG:  terminating any other active server processes
> LOG:  all server processes terminated; reinitializing
> LOG:  database system was interrupted at 2005-03-17 23:20:52 EST

Confirmed.

Seems that's the problem of implicite end transactions. If you have a
COMMIT/ABORT  after FETCH, the server is ok.

Regards,
Qingqing

Re: Query crashes/hangs server

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I recieved this report of a failing set of queries:

Fixed.  ShutdownPostgres needs to be sure we've released buffer pins
before it tries to drop newly-created files.  This has actually been
wrong all along, but it is masked pre-8.0 because DropRelFileNodeBuffers
was willing to arbitrarily throw away a pin on a victim buffer.
I thought that was a bad idea and took it out, expecting that we'd find
any bad consequences a bit quicker than this ...
        regards, tom lane

Re: Query crashes/hangs server

From
Tatsuo Ishii
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I recieved this report of a failing set of queries:
> 
> Fixed.  ShutdownPostgres needs to be sure we've released buffer pins
> before it tries to drop newly-created files.  This has actually been
> wrong all along, but it is masked pre-8.0 because DropRelFileNodeBuffers
> was willing to arbitrarily throw away a pin on a victim buffer.
> I thought that was a bad idea and took it out, expecting that we'd find
> any bad consequences a bit quicker than this ...

It seems your patches do not fix the case when the table is a
temporary table...
--
Tatsuo Ishii

Re: Query crashes/hangs server

From
Tom Lane
Date:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> It seems your patches do not fix the case when the table is a
> temporary table...

Ah, should've thought to try that case too.  Thanks, Tatsuo.
        regards, tom lane