On 8/5/10 12:33 PM, Kevin Grittner wrote: > I don't know whether this is the right time to discuss those 9 > different uses, but just so everyone knows, the SIRead locks needed > for the SSI implementation in the current serializable patch have > some characteristics which may be exactly what you want (if you want > cache invalidation or some such) or may render them totally useless > from some purposes.
Yeah, I haven't wrapped my head around your stuff enough yet. I would say that having such locks available only for serializable transactions limits some of the uses I'm thinking of. Anyway, here's some of the uses I'm thinking of: (1) Pre-insert lock: you know that you're going to insert a record with PK="X" later in a long-running SP, so you want to lock out other inserts of PK="X" at the beginning of the procedure. (2) FK Locking: you plan to modify or delete a parent FK record in this transaction, so you want to prevent any updates or inserts on its related child records. (in my experience, FK-releated sharelocks are the #1 cause of deadlocking). (3) No-duplicate queueing: you want to create a queue table which doesn't accept duplicate events, but you don't want it to be a source of deadlocks. This is a variant of (1), but a common case. (4) Blackouts: records of type "x" aren't supposed to be created during period "y to y1" or while procedure "z" is running. Predicate locking can be used to prevent this more easily than adding and removing a trigger. (5) Debugging: (variant of 4) records of type "x" keep getting inserted in the table, and you don't know where they're coming from. You can predicate lock to force an error and debug it. ... that's off the top of my head. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers