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

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(at)anarazel(dot)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(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

--
Kind Regards / Med Vennlig Hilsen

Olav Grønås Gjerde

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