The following bug has been logged on the website:
Bug reference: 15294
Logged by: Olav Gjerde
Email address: olav@backupbay.com
PostgreSQL version: 10.4
Operating system: Linux olav-system 4.15.0-29-generic #31-Ubuntu SMP
Description:
We are developing a process where we want to rename schema when deploying a
data model to "prod".
When using serializable you will not get any phantom reads if you use UPDATE
statements. But if you rename the schema name you will.
You can do the following to reproduce this error
CREATE SCHEMA prod;
CREATE SCHEMA staging;
CREATE SCHEMA history;
CREATE TABLE prod.link(id serial, text text);
CREATE TABLE staging.link(id serial, text text);
INSERT INTO prod.link (text) VALUES ('prod link text');
INSERT INTO staging.link (text) VALUES ('staging link text');
Now start the first read transaction:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select * from prod.link;
id | text
----+----------------
1 | prod link text
(1 row)
Then open another session and start new transaction where you rename schema
names and commit
BEGIN TRANSACTION;
DROP SCHEMA history CASCADE;
ALTER SCHEMA prod RENAME TO history;
ALTER SCHEMA staging RENAME TO prod;
COMMIT;
Now go back to the first session and continue within the same transaction:
select * from prod.link;
id | text
----+-------------------
1 | staging link text
(1 row)
Is this an error? Or have I misunderstood something? Are transactions not
supported for schema renames?
