Thread: BUG #15294: Phantom read in serializable transaction when you renameschema.

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?

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

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@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?

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

Thank you for your reply. I understand that this is difficult to get
to work right, could this be improved with adding a warning message
for the user?

On Tue, Jul 24, 2018 at 9:30 PM, Andres Freund <andres@anarazel.de> wrote:
> 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@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?
>
> 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



--
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde

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