I have a question regarding true serializability and predicate locking. There's some context on the wiki page:
http://wiki.postgresql.org/wiki/Serializable under the heading "Predicate Locking". If you have the following DDL: create table mytable(mycircle circle); create index mytable_mycircle_idx on mytable using gist (mycircle); and two transactions: T1: BEGIN; SELECT * FROM mytable WHERE mycircle && '<(0, 0), 10>'; -- if any rows are returned, ROLLBACK INSERT INTO mytable(mycircle) VALUES('<(0, 0), 10>'); COMMIT; T2: BEGIN; SELECT * FROM mytable WHERE mycircle && '<(5, 5), 5>'; -- if any rows are returned, ROLLBACK INSERT INTO mytable(mycircle) VALUES('<(5, 5), 5>'); COMMIT; Clearly one of those transactions should abort, because that will happen in either serialized order. But I don't see where any lock is stored, nor how the conflict is detected. There has been a lot of theoretical discussion on this matter, but I'd like to know how it will work in this specific case. You can't merely lock a few index pages, because the INSERT might put the tuple in another page. I'm still trying to catch up on this discussion as well as relevant papers, but this question has been on my mind. One approach that might work for GiST is to get some kind of lock (SIREAD?) on the predicates for the pages that the search does not match. That way, the conflict can be detected if an INSERT tries to update the predicate of a page to something that the search may have matched. If the index was GIN instead of GiST, I think the fastupdate feature would cause a problem, though (as Greg brought up). Fastupdate may need to be disabled when using truly serializable transactions. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
