>>> "Robert Haas" <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
