Isolation of schema renames

From: Ben Leslie <benno(at)benno(dot)id(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Isolation of schema renames
Date: 2017-08-10 01:44:31
Message-ID: CABZ0LtDfEOq-nqpU+yczkViMt5HsyF=_7u5dUK2kCQQwcTCZEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm wondering if I can/should expect schema renames to be isolated.

For example, I have two schemas "test" and "test_new". Each with a "test"
table (with same columns, but different data).

In one transaction I'm renaming the schemas test => test_old, test_new =>
test. I.e.:

BEGIN;
ALTER SCHEMA test RENAME TO test_old;
ALTER SCHEMA test_new RENAME TO test;
COMMIT;

In another transaction I do:

BEGIN;
SELECT * FROM test.test;
<first transaction occurs here on a different session>
SELECT * FROM test.test;
COMMIT;

My expectation is that both "SELECT" would return the same data (or that
the transaction performing the rename is blocked until the select
transaction is complete).

In testing this on 9.5.7 the behaviour I see is that the select "SELECT"
returns different data. (Regardless of isolation level chosen).

Is it possible to perform this rename in any way?

If not (which I suspect is the case) is this documented somewhere, I
couldn't find it (but that is probably me not looking hard enough / in the
right place).

Thanks,

Ben

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-08-10 02:14:47 Re: Isolation of schema renames
Previous Message Alvaro Herrera 2017-08-09 22:39:42 Re: Multixact members limit exceeded