Обсуждение: 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@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.sqlhttp://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
INITIALLYDEFERRED, "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