BUG #15294: Phantom read in serializable transaction when you rename schema.

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: olav(at)backupbay(dot)com
Subject: BUG #15294: Phantom read in serializable transaction when you rename schema.
Date: 2018-07-24 19:14:52
Message-ID: 153245969228.1404.11293353392171366243@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15294
Logged by: Olav Gjerde
Email address: olav(at)backupbay(dot)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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Olav Gjerde 2018-07-24 19:26:05 Re: BUG #15294: Phantom read in serializable transaction when you rename schema.
Previous Message Sergei Kornilov 2018-07-24 18:22:18 Re: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events