Re: Documenting serializable vs snapshot isolation levels

>>> "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
>> Not sure about "most". Referential integrity is a pretty common
use
>> case, and it is not covered without explicit locking. Many other
>> common use cases are not, either. I agree many are, and that the
rest
>> can be worked around easily enough that I wouldn't want to see
>> blocking introduced to the degree that non-MVCC databases use for
>> serializable access.
>
> What do you mean by referential integrity? I don't believe you can
> construct a foreign key problem at any transaction isolation level.

I mean that if someone attempts to maintain referential integrity with
SQL code, without using explicit locks, it is not reliable.
Presumably the implementation of foreign keys in PostgreSQL takes this
into account and blocks the kind of behavior shown below. This
behavior would not occur with true serializable transactions.

-- setup
create table parent (parid int not null primary key);
create table child (chiid int not null primary key, parid int);
insert into parent values (1);

-- connection 1 (start of T0)
start transaction isolation level serializable;
select * from parent where parid = 1;
-- parent row exists; OK to insert child.
insert into child values (100, 1);

-- connection 2 (T1)
start transaction isolation level serializable;
select * from child where parid = 1;
-- child row doesn't exist; OK to delete parent
delete from parent where parid = 1;
commit;

-- connection 1 (end of T0)
commit transaction;

-- database now lacks referential integrity
select * from parent;
parid
-------
(0 rows)

select * from child;
chiid | parid
-------+-------
100 | 1
(1 row)

-Kevin