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

Sorry, it is not a phantom read, but a non-repeatable read. Sorry for
the confusion.

On Tue, Jul 24, 2018 at 9:14 PM, PG Bug reporting form
<noreply(at)postgresql(dot)org> 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?
>

--
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde

BackupBay Gjerde
Asalvegen 19
4051 SOLA
Norway
Phone: +47 918 000 59