SEO

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

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, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"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

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster