namespace dilemma - Mailing list pgsql-general

From Jeffrey R. Greco
Subject namespace dilemma
Date
Msg-id 20040403052720.GA24968@eGreco.net
Whole thread Raw
List pgsql-general
I came across an interesting feature regarding namespace name changes.
To illustrate suppose you have two connections open whose commands occur in
the following sequence:

 Time | Session A                       | Session B
------+---------------------------------+----------------------------
  1   | CREATE SCHEMA my_schema;        |
  2   | CREATE TABLE my_schema.my_table |
      |     (my_column int);            |
  3   | BEGIN;                          |
  4   | INSERT INTO my_schema.my_table  |
      |     VALUES (1);                 |
  5   |                                 | BEGIN;
  6   |                                 | ALTER SCHEMA my_schema
      |                                 |     RENAME TO your_schema;
  7   |                                 | COMMIT;
  8   | SELECT my_column                |
      |     FROM my_schema.my_table;    |

If this is attempted, then session A results in the following error
after the command issued at time "8":
    ERROR:  schema "my_schema" does not exist

This feature occurs when the isolation level is either READ COMMITED or
SERIALIZABLE.  If you instead were to attempt a table rename in session
B, then session B would appropriately hang waiting for an ACCESS EXCLUSIVE
lock.

My humble opinion (as a non-PostgreSQL developer) is that renaming the
schema in an implied rename of the table from my_schema.my_table to
your_schema.my_table.  Therefore it should also obtain a lock of some
type.

As a result, all of my server side functions begin with something along
the lines of:
    SELECT oid FROM pg_catalog.pg_namespaces
    WHERE nspname = 'my_schema' FOR UPDATE;
I do this for every schema which the function consults through the SPI
manager.  Also, AFAIK, to be very careful (paranoid) would require this
tedious approach for every transaction.

I attempted to get around this issue by adding various entries to
pg_rewrite to try to force a select statement on pg_namespace to be
rewritten as a SELECT ... FOR UPDATE.  This failed.  I have not tried to
patch the source, though I imagine it would not be difficult.

Any opinions on approaches to this issue or the correctness of the
current behavior of PostgreSQL are greatly appreciated.

Thanks,
Jeff Greco

pgsql-general by date:

Previous
From: Manfred Koizar
Date:
Subject: Re: Casting int to bool with join...
Next
From: "Rajat Katyal"
Date:
Subject: Problem in calling prepare statement from STORED PROCEDURE