Обсуждение: BUG #4289: drop owned by report ERROR: cache lookup failed for relation 16390

The following bug has been logged online:

Bug reference:      4289
Logged by:          crystal
Email address:      crystal_lium@163.com
PostgreSQL version: 8.3.3
Operating system:   linux
Description:        drop owned by report ERROR:  cache lookup failed for
relation 16390
Details:

$ ./psql TEST
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

TEST=# create user u1;
CREATE ROLE
TEST=# create user u2;
CREATE ROLE
TEST=# set session authorization u1;
SET
TEST=> create table t1 (a int);
CREATE TABLE
TEST=> set session authorization u2;
SET
TEST=> create view v2 as select * from t1;
CREATE VIEW
TEST=> reset session authorization;
SET
TEST=# drop owned by u1, u2 cascade;
NOTICE:  drop cascades to view v2
ERROR:  cache lookup failed for relation 16390
TEST=# \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | u1
 public | v2   | view  | u2
(2 rows)

crystal wrote:

> TEST=# drop owned by u1, u2 cascade;
> NOTICE:  drop cascades to view v2
> ERROR:  cache lookup failed for relation 16390

Rats.  I guess this is due to the two-pass nature of some algorithm.
(Dropping owned by u1 cascade drops the view too, and then when dropping
owned by u2, it tries to find the view and the cache lookup fails).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> crystal wrote:
>> TEST=# drop owned by u1, u2 cascade;
>> NOTICE:  drop cascades to view v2
>> ERROR:  cache lookup failed for relation 16390

> Rats.  I guess this is due to the two-pass nature of some algorithm.

Works okay in CVS HEAD ... not sure if a small back-port is possible,
though.

            regards, tom lane

Alvaro Herrera wrote:
> crystal wrote:
>
> > TEST=# drop owned by u1, u2 cascade;
> > NOTICE:  drop cascades to view v2
> > ERROR:  cache lookup failed for relation 16390
>
> Rats.  I guess this is due to the two-pass nature of some algorithm.
> (Dropping owned by u1 cascade drops the view too, and then when dropping
> owned by u2, it tries to find the view and the cache lookup fails).

This patch fixes it, but I'm not sure if it breaks some other case (not
awake enough yet I think).

The problem is that we're accumulating objects for all users in a single
pass, and then calling performMultipleDeletion with all of them.  As far
as I understand the comments in performMultipleDeletion, this should
work ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Rats.  I guess this is due to the two-pass nature of some algorithm.
> (Dropping owned by u1 cascade drops the view too, and then when dropping
> owned by u2, it tries to find the view and the cache lookup fails).

Actually, on looking closer, the problem is that recursiveDeletion fails
to pass alreadyDeleted down to (and through) deleteDependentObjects.
So the cascaded delete doesn't get reported back to
performMultipleDeletions, and it doesn't know to skip the view when it
gets to it.  So this is clearly a bug, and seemingly not so hard to fix.

            regards, tom lane

Alvaro Herrera <alvherre@commandprompt.com> writes:
> This patch fixes it, but I'm not sure if it breaks some other case (not
> awake enough yet I think).

AFAICS this patch destroys the entire reason for having the
performMultipleDeletion mechanism in the first place, which was to not
fail in the RESTRICT case when there were dependencies between objects
owned by different users.

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Rats.  I guess this is due to the two-pass nature of some algorithm.
> > (Dropping owned by u1 cascade drops the view too, and then when dropping
> > owned by u2, it tries to find the view and the cache lookup fails).
>
> Actually, on looking closer, the problem is that recursiveDeletion fails
> to pass alreadyDeleted down to (and through) deleteDependentObjects.
> So the cascaded delete doesn't get reported back to
> performMultipleDeletions, and it doesn't know to skip the view when it
> gets to it.  So this is clearly a bug, and seemingly not so hard to fix.

Hmm, in this case I wonder if this could show up in other cases too,
like DROP SCHEMA.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> Actually, on looking closer, the problem is that recursiveDeletion fails
>> to pass alreadyDeleted down to (and through) deleteDependentObjects.

> Hmm, in this case I wonder if this could show up in other cases too,
> like DROP SCHEMA.

No, because alreadyDeleted is only used by performMultipleDeletions
which (in 8.3) is only used by DROP OWNED.

            regards, tom lane