Re: error on drop table

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