Обсуждение: error on drop table
From my postgres log: DEBUG: query: SELECT mytext,myint INTO TEMP tqt FROM ...; . . . DEBUG: query: DROP TABLE tqt; DEBUG: ProcessUtility: DROP TABLE tqt; NOTICE: FlushRelationBuffers(tqt, 0): block 0 is referenced (private 1, global 1) ERROR: heap_drop_with_catalog: FlushRelationBuffers returned -2 My version is: PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph Shraibman <jks@selectacast.net> writes:
> NOTICE: FlushRelationBuffers(tqt, 0): block 0 is referenced (private 1, global 1)
> ERROR: heap_drop_with_catalog: FlushRelationBuffers returned -2
Hmm, can you provide a sequence to reproduce this?
regards, tom lane
I don't have a test script for you yet, but it appears the problem is that I declared a cursor on the temp table and dropped it before I did the END on the block that I declared the cursor in. I'm working on the test script. Tom Lane wrote: > Joseph Shraibman <jks@selectacast.net> writes: > >>NOTICE: FlushRelationBuffers(tqt, 0): block 0 is referenced (private 1, global 1) >>ERROR: heap_drop_with_catalog: FlushRelationBuffers returned -2 >> > > Hmm, can you provide a sequence to reproduce this? > > regards, tom lane > -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
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; Tom Lane wrote: > Joseph Shraibman <jks@selectacast.net> writes: > >>NOTICE: FlushRelationBuffers(tqt, 0): block 0 is referenced (private 1, global 1) >>ERROR: heap_drop_with_catalog: FlushRelationBuffers returned -2 >> > > Hmm, can you provide a sequence to reproduce this? > > regards, tom lane > -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph Shraibman <jks@selectacast.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
> 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?
Seems it fails with temp and regular tables. Added to TODO:
o Prevent DROP of table being referenced by our own open cursor
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Tom Lane wrote:
> Joseph Shraibman <jks@selectacast.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:
>
What version are you using? I get the error using exactly what I showed you on 7.1.3.
playpen=# create table table1 ( list text, ukey int);
CREATE
playpen=# insert into table1 values( '1', 1);
INSERT 571129 1
playpen=# insert into table1 values( '2', 2);
INSERT 571130 1
playpen=# insert into table1 values( '3', 3);
INSERT 571131 1
playpen=# insert into table1 values( '4', 4);
INSERT 571132 1
playpen=# insert into table1 values( '5', 5);
INSERT 571133 1
playpen=# SELECT list,ukey INTO temporary tqt FROM table1;
SELECT
playpen=# BEGIN;
BEGIN
playpen=# DECLARE cname CURSOR FOR SELECT list, ukey FROM tqt ;
SELECT
playpen=# FETCH 10 from cname;
list | ukey
------+------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
(5 rows)
playpen=# DROP TABLE tqt;
NOTICE: FlushRelationBuffers(tqt, 0): block 0 is referenced (private 1, global 1)
ERROR: heap_drop_with_catalog: FlushRelationBuffers returned -2
playpen=# END;
COMMIT
playpen=#
playpen=# drop table table1;
DROP
playpen=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
Joseph Shraibman <jks@selectacast.net> writes:
> Tom Lane wrote:
>> 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:
> What version are you using? I get the error using exactly what I showed you on 7.1.3.
Hmmm ... I was using current sources. I do replicate your result on
7.1.3, however. It looks like 7.1 holds onto a buffer pin for a
completed scan a little longer than 7.2 does. I believe this is related
to my changes of 2001-06-09 14:16, but don't feel like running it to
ground right now. The bottom line is the same in any case: the open
cursor represents a reference to the relation, which should prevent
us from dropping the relation.
regards, tom lane