DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy

strk <strk@keybit.net> writes:

Do you have an idea on how to further debug this ?

That usually goes with providing a self-contained test case… that is a
minimum script that creates the function(s) and calls them.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

I've handled to produce a small testcase:

http://strk.keybit.net/tmp/could_not_open_relation.sql

It still requires postgis (svn), but if anyone has that it might help.
Will try to go on with the reduction.

--strk;

On Mon, Feb 07, 2011 at 12:38:08PM +0100, strk wrote:

Hi all,
I'm trying to debug an ugly error triggered from a "DROP SCHEMA xxx CASCADE"
call inside a function.

The call is the last step of the stored pl/pgsql procedure.

I've verified that removing the "DROP SCHEMA" command from _inside_
the function body and performing it _outside_ it (right after return)
everything works fine.

Note that the schema that the function is trying to drop was created
by a function called by the function attempting to drop it.
Both function (the one which creates the schema and the one which
attempts to drop it) are defined as VOLATILE.

Also, I can see traces of the DROP SCHEMA CASCADE being executed, till
the ERROR comes out (lots of traces for cascading objects).

This is :
PostgreSQL 8.4.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit

Do you have an idea on how to further debug this ?
TIA.

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

--

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

I've uploaded also the script output ( CASCADE traces ) :

http://strk.keybit.net/tmp/could_not_open_relation.sql
http://strk.keybit.net/tmp/could_not_open_relation.log

And realized that the relation oid is the one first
requested for deletion. Ie:

DROP TABLE XXX CASCADE;
..
ERROR: could not open relation with OID XXX:regclass::oid

I've found two ways to avoid the error:

1. Perform the DROP TABLE outside the transaction triggering its creation
2. Avoiding population of the table being dropped (the AddEdge call)

Note that the 'edge_data' table has a foreign key to itself, and the
constraint is initially deferred (may have a role here, I guess ?)

"next_left_edge_exists" FOREIGN KEY (abs_next_left_edge)
REFERENCES.edge_data(edge_id)
DEFERRABLE
INITIALLY DEFERRED,
"next_right_edge_exists" FOREIGN KEY (abs_next_right_edge)
REFERENCES edge_data(edge_id)
DEFERRABLE
INITIALLY DEFERRED

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

On Mon, Feb 07, 2011 at 02:31:49PM +0100, Dimitri Fontaine wrote:

strk <strk@keybit.net> writes:

Do you have an idea on how to further debug this ?

That usually goes with providing a self-contained test case… that is a
minimum script that creates the function(s) and calls them.

I've finally completed the debugging phase and have
a minimal self-contained testcase showing the problem.

It has to do with INITIALLY DEFERRED constraints.

The testcase is attached.

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html

strk <strk@keybit.net> writes:

I've finally completed the debugging phase and have
a minimal self-contained testcase showing the problem.
It has to do with INITIALLY DEFERRED constraints.

I looked into this and find that the issue is you're trying to drop a
table that has unfired AFTER TRIGGER events pending. When they finally
fire, they can't find the table anymore.

I'm inclined to think that we should disallow that; or even more to the
point, that it'd be a good thing to apply CheckTableNotInUse() when
about to drop a table. If we disallow such cases for ALTER TABLE, then
a fortiori we should do so for DROP TABLE.

Aside from disallowing unfired trigger events, CheckTableNotInUse would
disallow the table being actively relation_open'd by any operation.
This seems like a real good thing anyway (imagine, eg, DROP TABLE
executed from a trigger for that table).

It's possible that we could handle the unfired-trigger problem by
marking the relevant events AFTER_TRIGGER_DONE, but I'm unconvinced that
it's worth spending effort on. The relation_open part of it seems
essential even so; you could likely crash the backend with that.

Comments?

regards, tom lane

On Thu, Feb 10, 2011 at 12:03:49AM -0500, Tom Lane wrote:

strk <strk@keybit.net> writes:

I've finally completed the debugging phase and have
a minimal self-contained testcase showing the problem.
It has to do with INITIALLY DEFERRED constraints.

I looked into this and find that the issue is you're trying to drop a
table that has unfired AFTER TRIGGER events pending. When they finally
fire, they can't find the table anymore.

I'm inclined to think that we should disallow that; or even more to the
point, that it'd be a good thing to apply CheckTableNotInUse() when
about to drop a table. If we disallow such cases for ALTER TABLE, then
a fortiori we should do so for DROP TABLE.

Aside from disallowing unfired trigger events, CheckTableNotInUse would
disallow the table being actively relation_open'd by any operation.
This seems like a real good thing anyway (imagine, eg, DROP TABLE
executed from a trigger for that table).

+1. We even do it for TRUNCATE, so surely it's proper for DROP.

It's possible that we could handle the unfired-trigger problem by
marking the relevant events AFTER_TRIGGER_DONE, but I'm unconvinced that
it's worth spending effort on.

Seems rare enough not to worry much about, particularly considering the SET
CONSTRAINTS escape hatch.

On Thu, Feb 10, 2011 at 12:03:49AM -0500, Tom Lane wrote:

strk <strk@keybit.net> writes:

I've finally completed the debugging phase and have
a minimal self-contained testcase showing the problem.
It has to do with INITIALLY DEFERRED constraints.

I looked into this and find that the issue is you're trying to drop a
table that has unfired AFTER TRIGGER events pending. When they finally
fire, they can't find the table anymore.

I'm inclined to think that we should disallow that; or even more to the
point, that it'd be a good thing to apply CheckTableNotInUse() when
about to drop a table. If we disallow such cases for ALTER TABLE, then
a fortiori we should do so for DROP TABLE.

Makes sense to me disallowing drop.
An intuitive error message is all I was looking for.

--strk;

() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html