Thread: BUG #1220: "alter table rename to" inside a transaction violates ACID ordering

The following bug has been logged online:

Bug reference:      1220
Logged by:          adam sah

Email address:      asah@midgard.net

PostgreSQL version: 7.4.3

Operating system:   linux (suse 9.1 professional)

Description:        "alter table rename to" inside a transaction violates
ACID ordering

Details:

to repro, open two psql windows and execute cmds in this order:

in window 1, "create table test (foo integer);"
in window 2, "select count(*) from foo;"
in window 1, "begin transaction;"
in window 2, "select count(*) from foo;"
in window 1, "alter table foo rename to bar;"
in window 2, "select count(*) from foo;"
==> this blocks, like it should
in window 1, "commit;"
==> yikes!  in window 2, it should have errored.
instead it succeeded with 0 records (I'm guessing
because the catalog lookup had already occurred?)

anyway, if you try the same thing, but with "drop
table" instead of rename you do get an error in the
query window.

btw, you guys rock!  8.0 looks awesome.

hope this helps,
adam

(former UC Berkeley grad student in stonebraker's
   database group)

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> ==> yikes!  in window 2, it should have errored.

Why are you of the opinion that this is a bug?  The catalog lookup has
to happen before the second xact can hope to obtain a lock on the table,
no?

            regards, tom lane

Adam Sah <asah@speakeasy.net> writes:
> well, that's fine I suppose -- but then why does it work the other way
>     for DROP TABLE?

It doesn't work the other way for DROP.  The error you get in the DROP
case is a low-level error that really should never be seen by users.
But I see that as a problem with DROP, not with RENAME.

In my view, once the code has determined which relation (identified by
OID) is meant, it should be OK to take the OID as identifying the
relation henceforth.  This is not significantly different from the fact
that

    create table a (...);
    create view v as select * from a;
    alter table a rename to b;

will leave view v referencing b.  Would you claim that v should break?

            regards, tom lane

well, that's fine I suppose -- but then why does it work the other way
    for DROP TABLE?

i.e. feels like we're confusing "implementation" and "desired semantics"

adam


Tom Lane wrote:

> "PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
>
>>==> yikes!  in window 2, it should have errored.
>
>
> Why are you of the opinion that this is a bug?  The catalog lookup has
> to happen before the second xact can hope to obtain a lock on the table,
> no?
>
>             regards, tom lane