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

Hi,

On 2018-07-24 19:14:52 +0000, PG Bug reporting form wrote:
> 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?

Transactions are supported, but DDL basically is processed as READ
COMMITTED. There's not really a good way around that - you have to use
the newer table definition etc, otherwise you might e.g. insert rows
that violate newly added constraints and such.

Btw, although that's unrelated in this case, you really can only rely on
serializable if all the participating transactions use serializable.

Greetings,

Andres Freund