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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
