Re: Lock for table renaming

Why does renaming a table take out an access exclusive lock on the target
table? Isn't this just an UPDATE on a few system catalog rows.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Hello Peter,

Peter Eisentraut wrote:
> Why does renaming a table take out an access exclusive lock on the target
> table? Isn't this just an UPDATE on a few system catalog rows.

I guess because system catalog updates are visible immediately? Try the
following:

markus=# CREATE TABLE test (a INT);
CREATE TABLE
markus=# BEGIN;
BEGIN
markus=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
markus=# INSERT INTO TEST (a) VALUES (1);
INSERT 0 1

Then switch to another terminal and rename the table by hand (to
circumvent the lock):

markus=# UPDATE pg_class SET relname='gone' WHERE relname = 'test';
UPDATE 1

Go back to the first transaction and try to read from the table again:

markus=# SELECT * FROM test;
ERROR: relation "test" does not exist

What works instead, is:

postgres=# SELECT * FROM gone;
a
---
1
(1 row)

AFAICT, that applies to both, READ COMMITTED as well as SERIALIZABLE.
Please correct me if I'm wrong here.

Regards

Markus

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Why does renaming a table take out an access exclusive lock on the target
> table?

To prevent other transactions failing because the table "disappears"
underneath them.

regards, tom lane