Skip site navigation (1) Skip section navigation (2)

Re: error on drop table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joseph Shraibman <jks(at)selectacast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: error on drop table
Date: 2001-11-29 05:23:48
Message-ID: 15014.1007011428@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> To reproduce:
> create table table1 ( list text, ukey int);
> insert into table1 values( '1', 1);
> insert into table1 values( '2', 2);
> insert into table1 values( '3', 3);
> insert into table1 values( '4', 4);
> insert into table1 values( '5', 5);

>   SELECT list,ukey INTO temporary tqt  FROM table1;
> BEGIN;
>   DECLARE cname CURSOR FOR SELECT list, ukey  FROM tqt ;
> FETCH 10 from cname;
> DROP TABLE tqt;
> END;

I don't see an error if I do it that way, but I do see an error if the 
cursor hasn't yet fetched all the rows:

regression=# SELECT list,ukey INTO temporary tqt  FROM table1;
SELECT
regression=# BEGIN;
BEGIN
regression=# DECLARE cname CURSOR FOR SELECT list, ukey  FROM tqt ;
SELECT
regression=# FETCH 1 from cname;
 list | ukey
------+------
 1    |    1
(1 row)

regression=# DROP TABLE tqt;
NOTICE:  FlushRelationBuffers(tqt, 0): block 0 is referenced (private 2, global 1)
ERROR:  heap_drop_with_catalog: FlushRelationBuffers returned -2
regression=#

The error message is evidently arising because the cursor's seqscan
still has a pin on the scan's current page.

Ideally we should refuse to execute the DROP because of the open cursor.
Offhand I don't see an easy way to do that, however.  Bruce, would you
put something about it in TODO?

Note that there's no problem if the cursor is open in a different
backend from the one trying to DROP: the DROP will wait, trying to get
an exclusive lock on the table, until the cursor-containing transaction
is closed.  But the lock doesn't solve the problem here because a
transaction's own locks never self-conflict: we can get exclusive lock
even though we already have a read lock.

AFAICS we'd have to actually grovel through the backend's own open
cursors to see if there's any reference to what we plan to drop.
This is probably better tackled as part of a generalized reference-
tracking mechanism than as a one-purpose fix.

Anyway my concern is considerably eased now that I understand the
behavior.  I'd rank it as an annoyance (unhelpful error message)
more than a serious problem.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2001-11-29 05:49:04
Subject: Re: ident authorization (was backup: pg_dumpall and full backups in general)
Previous:From: qradiusDate: 2001-11-29 05:11:27
Subject: time calculation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group