Re: BUG #15294: Phantom read in serializable transaction when yourename 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@postgresql.org> wrote:
> 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?
>



--
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde

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