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

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

strk <strk(at)keybit(dot)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(at)keybit(dot)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(at)keybit(dot)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(at)keybit(dot)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(at)keybit(dot)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