Simon Riggs wrote:
On Sat, 2007-02-03 at 09:43 -0800, Stephan Szabo wrote:
On Sat, 3 Feb 2007, Simon Riggs wrote:
On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
No, I don't. I think knowledge of which columns are in a PK is quite a
few levels away from the semantics of row locking. To point out just
one problem, what happens when you add or drop a PK? Or drop and
replace with a different column set? Yes, I know dropping one requires
exclusive lock on the table, but the transaction doing it could hold row
locks within the table, and now it's very unclear what they mean.
There are issues, yes. Dropping PKs is a very irregular occurrence nor
is it likely to be part of a complex transaction. It wouldn't bother me
to say that if a transaction already holds a RowExclusiveLock or a
RowShareLock it cannot upgrade to an AccessExclusiveLock.
The lock check seems like a strange constraint, given that it's not
necessarily going to be anything that conflicts with the row locks. I'm
not sure there'd be a better idea given this sort of scheme, but it still
seems strange.
The TODO I was requesting you consider was this:
"Develop non-conflicting locking scheme to allow RI checks to co-exist
peacefully with non-PK UPDATEs on the referenced table".
That is, IMHO, a general statement of an important unresolved issue with
our Referential Integrity implementation. That is in no way intended as
any form of negative commentary on the excellent detailed work that has
got us so far already.
Well, if we really want to solve that completely then we really need
column locking, or at least locking at the level of arbitrary (possibly
overlapping) unique constraints, not just the PK because foreign keys
don't necessarily reference the primary key. But the PK case is certainly
the most common and it'd certainly be nice to cover that case.
IMHO generic column level locking would hardly ever be used. Locking for
RI seems to be 99% of the use case, which means we'd be OK if we found a
way of only locking an arbitary number of unique col groups. By
definition, each of these column groups is covered by a unique index.
Not saying this'll gain us anything but...
It has ocurred to me that the lock could be reduced in another way. If
we had an "immutable" constraint that could be applied to pkey-columns
then we wouldn't have to worry about updates at all. If a pkey value was
there before an update, it would be there after too. The only thing
you'd need to prevent would be deletes.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
