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 4: Don't 'kill -9' the postmaster
