Referential Integrity and SHARE locks

I'm reading the SQL Standard and I can't find anywhere that says that we
need to place SHARE locks on rows in the referenced table.
RI_FKey_check() clearly does that.

What I do see is this:
"4. For each row of the referenced table, its matching rows, unique
matching rows, and non-unique matching rows are determined immediately
prior to the execution of any <SQL procedure statement>. No new
matching rows are added during the execution of that <SQL procedure
statement>.

The association between a referenced row and a non-unique matching row
is dropped during the execution of that SQL-statement if the referenced
row is either marked for deletion or updated to a distinct value on
any referenced column that corresponds to a non-null referencing column.
This occurs immediately after such a mark for deletion or update of the
referenced row. Unique matching rows and non-unique matching rows for a
referenced row are evaluated immediately after dropping the association
between that referenced row and a non-unique matching row."

under General Rules for <referential constraint definition>

That explicitly says "are determined immediately prior to the
execution". To me, that implies that a Read Committed snapshot is
sufficient to read referenced rows and that no lock is required.

Why do we need a SHARE lock at all, on the **referenc(ed)** table?

It sounds like if we don't put a SHARE lock on the referenced table then
we can end the transaction in an inconsistent state if the referenced
table has concurrent UPDATEs or DELETEs. BUT those operations do impose
locking rules back onto the referencing tables that would not be granted
until after any changes to the referencing table complete, whereupon
they would restrict or cascade. So an inconsistent state doesn't seem
possible to me.

What am I missing?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

On Fri, 2007-02-02 at 10:51, Simon Riggs wrote:
[snip]
> Why do we need a SHARE lock at all, on the **referenc(ed)** table?
>
> It sounds like if we don't put a SHARE lock on the referenced table then
> we can end the transaction in an inconsistent state if the referenced
> table has concurrent UPDATEs or DELETEs. BUT those operations do impose
> locking rules back onto the referencing tables that would not be granted
> until after any changes to the referencing table complete, whereupon
> they would restrict or cascade. So an inconsistent state doesn't seem
> possible to me.
>
> What am I missing?

Well, here we do have a patch (deployed on production servers) which
does not put the shared lock on the referenced table, and it lets in
occasionally rows in the referencing tables which do not have parent
rows in the referenced table. I'm not sure what is the mechanism, but it
does happen, I can assure you. It happens rare enough that is not
disturbing for us, compared to the deadlocks which happen without the
patch - that's another matter...

In our application we never update any key ids, so only deletes/inserts
come in play, and I guess it happens when a referenced row is deleted
just between a newly inserted child row checks that the parent row
exists and the row is really inserted. Or something like that...

Cheers,
Csaba.

Csaba Nagy wrote:
> On Fri, 2007-02-02 at 10:51, Simon Riggs wrote:
> [snip]
>> Why do we need a SHARE lock at all, on the **referenc(ed)** table?

> Well, here we do have a patch (deployed on production servers) which
> does not put the shared lock on the referenced table, and it lets in
> occasionally rows in the referencing tables which do not have parent
> rows in the referenced table. I'm not sure what is the mechanism, but it
> does happen, I can assure you. It happens rare enough that is not
> disturbing for us, compared to the deadlocks which happen without the
> patch - that's another matter...

You say below the cut that you're not updating keys, so presumably it's
other columns. Which leads me to something I've wondered for a while -
why do we lock the whole row? Is it just a matter of "not optimised that
yet" or is there a good reason why locking just some columns isn't
practical.

--
Richard Huxton
Archonet Ltd

> You say below the cut that you're not updating keys, so presumably it's
> other columns. Which leads me to something I've wondered for a while -
> why do we lock the whole row? Is it just a matter of "not optimised that
> yet" or is there a good reason why locking just some columns isn't
> practical.

For the conditions of generating the deadlock, see:

http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php

The reason of the occasional orphan rows is not completely clear to me,
but it must be some kind of race condition while
inserting/deleting/?updating concurrently the parent/child tables.

Cheers,
Csaba.

Csaba Nagy wrote:
> The reason of the occasional orphan rows is not completely clear to me,
> but it must be some kind of race condition while
> inserting/deleting/?updating concurrently the parent/child tables.

I guess the following sequence would generate a orphaned row.
A: executes "insert into table_child parent_id=1"
B: executes "delete from table_parent where id=1"
A: RI trigger checks for matching row in table_parent
B: The row with id=1 is marked as deleted in table_parent
A: The new row with parent_id=1 is inserted into table_child
B: The delete is commited
A: The insert is comitted.

Any ordering that marks the row as deleted between the execution
of the ri-trigger and the insertion of the new row would lead
to the same result..

greetings, Florian Pflug

On Fri, 2 Feb 2007, Simon Riggs wrote:

> It sounds like if we don't put a SHARE lock on the referenced table then
> we can end the transaction in an inconsistent state if the referenced
> table has concurrent UPDATEs or DELETEs. BUT those operations do impose
> locking rules back onto the referencing tables that would not be granted
> until after any changes to the referencing table complete, whereupon
> they would restrict or cascade. So an inconsistent state doesn't seem
> possible to me.

What locking back to the referencing table are you thinking about? The row
locks are insufficient because that doesn't prevent an insert of a
new row that matches the criteria previously locked against AFAIK.

On Fri, 2007-02-02 at 12:01 +0100, Csaba Nagy wrote:
> > You say below the cut that you're not updating keys, so presumably it's
> > other columns. Which leads me to something I've wondered for a while -
> > why do we lock the whole row? Is it just a matter of "not optimised that
> > yet" or is there a good reason why locking just some columns isn't
> > practical.
>
> For the conditions of generating the deadlock, see:
>
> http://archives.postgresql.org/pgsql-general/2006-12/msg00029.php
>
> The reason of the occasional orphan rows is not completely clear to me,
> but it must be some kind of race condition while
> inserting/deleting/?updating concurrently the parent/child tables.

Thanks very much to Csaba, Richard and Florian for insight on this.

As you might have guessed across my recent posts, I'm coping with a
locking problem that is occurring on RI checks. Similarly to Csaba's
example, this is a port from Oracle.

My earlier thinking was that Oracle appears to be able to avoid locking
and my thought was that this was simply a rather dodgy interpretation of
the SQL Standard. Anyway, I'm not happy with simply forgetting the SHARE
lock; that clearly leads to invalid states in some cases, even if I need
to have a strong cup of coffee in the morning before I see them.

Using SELECT ... FOR SHARE in RI checks is better than using FOR UPDATE,
but its still too heavy a lock for many scenarios.

In particular, an INSERT on the referencing table can be blocked because
of an RI check against the referenced table, when there is a concurrent
UPDATE (on referenced table). So RI works well when the referenced
tables are mostly read-only, but we see lots of problems when we perform
regular updates against both referencing and referenced tables.

When we perform an INSERT into the referencing table, we want to be
certain that the FK value we are inserting still exists within the
referenced table. A DELETE on the referenced table should prevent the
INSERT, as should an UPDATE which changes the Primary Key. However, an
UPDATE which simply UPDATEs a non-PK column on the referenced table
should neither block nor prevent the INSERT on the referencing table.

We might think of using a SELECT ... FOR SHARE NOWAIT but that will
return an ERROR. Even if we wrap the request in a subtransaction the
query will still fail even when a permissible non-PK UPDATE is taking
place, so that alone is not good enough.

Various other thoughts about new lock modes yield nothing useful either,
after close analysis. So changing the lock *strength* is not the right
thing to do, but changing the lock footprint does seem worthwhile.

My initial proposal is to change the way write locking works, so as to
implement simplified column-level locking. Rather than locking each
column individually, we can lock the columns in one of two groups, plus
the full row. Thus we have three types of write lock:

1. full row write lock

as well as two mutually exclusive groups of columns:

2.a) PK cols
2.b) all columns apart from the PK cols

So type (1) conflicts with either (2a) or (2b). (2a) and (2b) do not
conflict with one another. Shared and Write locks conflict as before at
the various levels.

INSERT, DELETE - full row write lock

UPDATE - will place a write lock on either: full row or all-non-PK-cols,
depending upon whether the SET clause touches the PK columns. (So you
cannot UPDATE the PK while the non-PK cols are being UPDATEd...) If no
FK references this table, we will always take a full row write lock.

SELECT FOR UPDATE - full row write lock

SELECT FOR SHARE - will place full row lock by default, but in cases
where the SELECT doesn't reference anything apart from the PK columns,
we would place the lock only on the PK-cols. (Might be easier to do this
only for RI check queries.)

With this model, an INSERT or FK UPDATE on the referencing table that
generates a SELECT FOR SHARE onto the referenced table will conflict
with a DELETE or a PK UPDATE, but won't conflict at all with a non-PK
UPDATE.

This would be possible by using 2 additional tuple info bits to flag
that the lock held was either HEAP_LOCKED_PK_ONLY or
HEAP_LOCKED_NON_PK_COLS. When both lock types are held simultaneously we
will replace xmax with a multitransaction id, where we hold only two
transactionids at most - the first Xid is the holder of the PK cols
lock, the second Xid is the holder of the non-PK cols lock.

As a non-PK UPDATE is carried out, the details of any share locks on the
PK cols are carried forward onto the new row version.

So all of the machinery we need is already in place, we just need to
extend it somewhat.

Clearly an 8.4+ item, but seems worth getting onto the TODO list if we
agree to it:

TODO:

"Develop non-conflicting locking scheme to allow RI checks to co-exist
peacefully with non-PK UPDATEs on the referenced table".

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

On Fri, 2007-02-02 at 10:35 -0800, Stephan Szabo wrote:
> On Fri, 2 Feb 2007, Simon Riggs wrote:
>
> > It sounds like if we don't put a SHARE lock on the referenced table then
> > we can end the transaction in an inconsistent state if the referenced
> > table has concurrent UPDATEs or DELETEs. BUT those operations do impose
> > locking rules back onto the referencing tables that would not be granted
> > until after any changes to the referencing table complete, whereupon
> > they would restrict or cascade. So an inconsistent state doesn't seem
> > possible to me.
>
> What locking back to the referencing table are you thinking about? The row
> locks are insufficient because that doesn't prevent an insert of a
> new row that matches the criteria previously locked against AFAIK.

Probably best to read the later posts; this one was at the beginning of
my thought train, so is slightly off track, as later posters remind me.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> Thus we have three types of write lock:
> 1. full row write lock
> as well as two mutually exclusive groups of columns:
> 2.a) PK cols
> 2.b) all columns apart from the PK cols

This appears to require that we add enough fields to row headers to
represent *three* locking transactions instead of the current one.
Given the amount of griping about row header overhead that normally
flows across this list, I can't see such a proposal getting accepted.

> This would be possible by using 2 additional tuple info bits to flag
> that the lock held was either HEAP_LOCKED_PK_ONLY or
> HEAP_LOCKED_NON_PK_COLS. When both lock types are held simultaneously we
> will replace xmax with a multitransaction id, where we hold only two
> transactionids at most - the first Xid is the holder of the PK cols
> lock, the second Xid is the holder of the non-PK cols lock.

You haven't thought that through: it fails to distinguish who holds
which lock (mxact membership is not ordered), and it doesn't scale to
more than two holders, and I don't think it works for combinations of
share and exclusive lock. Also, what happened to the third type of lock?

Implementation details aside, I'm a tad concerned about introducing
deadlock failures that did not happen before, in scenarios where
transactions touch the row multiple times and end up needing to
acquire one of these locks while already holding another.

regards, tom lane

On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > Thus we have three types of write lock:
> > 1. full row write lock
> > as well as two mutually exclusive groups of columns:
> > 2.a) PK cols
> > 2.b) all columns apart from the PK cols
>
> This appears to require that we add enough fields to row headers to
> represent *three* locking transactions instead of the current one.
> Given the amount of griping about row header overhead that normally
> flows across this list, I can't see such a proposal getting accepted.

Yeh, I said "ouch" myself.

> > This would be possible by using 2 additional tuple info bits to flag
> > that the lock held was either HEAP_LOCKED_PK_ONLY or
> > HEAP_LOCKED_NON_PK_COLS. When both lock types are held simultaneously we
> > will replace xmax with a multitransaction id, where we hold only two
> > transactionids at most - the first Xid is the holder of the PK cols
> > lock, the second Xid is the holder of the non-PK cols lock.
>
> You haven't thought that through: it fails to distinguish who holds
> which lock (mxact membership is not ordered)

OK, sorry, I thought there was some ordering possible.

> , and it doesn't scale to
> more than two holders, and I don't think it works for combinations of
> share and exclusive lock. Also, what happened to the third type of lock?

Well, we just need to record the maximum two lock holders (given the
semantics described). The third lock type is both locks at once.

> Implementation details aside, I'm a tad concerned about introducing
> deadlock failures that did not happen before, in scenarios where
> transactions touch the row multiple times and end up needing to
> acquire one of these locks while already holding another.

Well, right now we have deadlocks and lots of locking. Updating PKs
isn't a regular occurence, so I'd rather swap a common deadlock for an
uncommon one, any day.

Anyway, implementation aside, I wanted to agree the overall TODO, so we
can think through the best way over a long period, if you agree in
general.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
>> , and it doesn't scale to
>> more than two holders, and I don't think it works for combinations of
>> share and exclusive lock. Also, what happened to the third type of lock?

> Well, we just need to record the maximum two lock holders (given the
> semantics described). The third lock type is both locks at once.

You're not going to support shared locks? That will be a big step
backwards ...

> Anyway, implementation aside, I wanted to agree the overall TODO, so we
> can think through the best way over a long period, if you agree in
> general.

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.

regards, tom lane

Simon Riggs wrote:
> My earlier thinking was that Oracle appears to be able to avoid locking
> and my thought was that this was simply a rather dodgy interpretation of
> the SQL Standard. Anyway, I'm not happy with simply forgetting the SHARE
> lock; that clearly leads to invalid states in some cases, even if I need
> to have a strong cup of coffee in the morning before I see them.
I think oracle is in a completly different situation here - Oracle imposes
limits on the maximum "size" of a transaction dues to various reasons
I believe - one being the size of the rollback segment. AFAIK, postgres
doesn't impose any such limits (apart from problems with long-running
transactions an vacuums, and possibly if you do "set constraints all deferred")
- which is why row locks have to be stored on-disk in the tuple header,
and not in some shared-memory segment.

Now, _if_ you're already imposing limits on transaction size, than it becomes
quite feasable IMHO to also limit the number of row-locks a transaction can take -
and to just store them in memory. This again makes column-level locking much
easier I'd think.

> Using SELECT ... FOR SHARE in RI checks is better than using FOR UPDATE,
> but its still too heavy a lock for many scenarios.
I think it's not too heavy, but it's actually the wrong kind of lock for ri
checks. Both "SHARE" and "EXCLUSIVE" row locks are, well, _row_ locks - the
lock a specific tuple. This is fine, if you want to guarantee that
a certain tuple stays as it is as long as still need it. But it's not really
what a RI constraints wants to ensure. An RI constraint actually wants to
force a specific _condition_ (namely, the existence of a row with a certain
value in a certain column) to be true, not prevent a specific physical tuple
from being modifier.

Now, a generic mechanism for "condition locking" is probably impossible to
implement with large performance sacrifices - but AFAICS the cases needed for
RI checks are always of the form "Is there a row where (field1, field2, ...) =
(a, b, c, ...)". And - at least for RI-Checks done when updating the _referencing_
table, postgres already forces an index to exist on (field1, field2, ...) I think.

The condition "There is a row where (field1, field2, ...) = (a,b,c,...)" is
the same as saying "There as an index entry for (a,b,c,....) that points to a live row".
_If_ is was possible to somehow take a lock on a index key (not a certain index entry,
but rather _all_ entries with a given key), than that could maybe be used for more
efficient RI locks. I guess this would need some sort of tuple-visibility-in-index entries,
but it seems that there a few people interested in making this happen.

greetings, Florian Pflug

On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
> >> , and it doesn't scale to
> >> more than two holders, and I don't think it works for combinations of
> >> share and exclusive lock. Also, what happened to the third type of lock?
>
> > Well, we just need to record the maximum two lock holders (given the
> > semantics described). The third lock type is both locks at once.
>
> You're not going to support shared locks? That will be a big step
> backwards ...

I did say that Shared locks were supported also. The lack of ordering of
multitransactions is a hole in my suggestion, so I need to reconsider.

> > Anyway, implementation aside, I wanted to agree the overall TODO, so we
> > can think through the best way over a long period, if you agree in
> > general.
>
> 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.

For me, PKs are intimately tied to the rows they uniquely identify; we
should be mentally linking them and seeing them as a replacement for
Oids, which we still see as intimately linked at low level. We'll be
missing many optimizations if we don't, we've discussed others this week
already.

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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.

On 2/2/2007 4:51 AM, Simon Riggs wrote:
> It sounds like if we don't put a SHARE lock on the referenced table then
> we can end the transaction in an inconsistent state if the referenced
> table has concurrent UPDATEs or DELETEs. BUT those operations do impose
> locking rules back onto the referencing tables that would not be granted
> until after any changes to the referencing table complete, whereupon
> they would restrict or cascade. So an inconsistent state doesn't seem
> possible to me.
>
> What am I missing?
>

You're missing MVCC. The newly inserted reference only becomes visible
when it is committed. If the order of actions is insert and check for
PK, other transaction deletes PK and commits, inserted FK commits ...
the other transaction didn't see "it coming".

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

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.

It occurs to me that if we had visibility in unique indexes, this would
allow the index rows to be separately lockable to the main row. That's
exactly what we need here.

It also occurs to me that putting visibility in indexes doesn't prevent
us from optimizing away index inserts for UPDATEs. There is no
particular reason why the xmin and xmax of a unique index exactly
matches the xmin and xmax of the main row. [I said the opposite to Jim
Nasby a few days ago, regrettably]. The indexes would record the xmin
and xmax of the row, while the main heap would have the xmin and xmax of
the individual row versions.

If we did both HOT + visibility in unique indexes then we would be able
to eliminate the contention between INSERTs and UPDATEs with RI.

As Tom pointed out this would complicate deadlock detection, but then so
would any column-level locking scheme, so that isn't an argument against
any one in particular.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

On Sat, 3 Feb 2007, Simon Riggs wrote:

> 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.

Actually, since rearranging PKs is such a drastic change I would expect
them only to be part of a large complex transaction. I know for apps I
work on it would be part of a single transaction script that updated
whole chunks of data and schema.

Kris Jurka

On Sun, 2007-02-04 at 09:38 +0000, Simon Riggs wrote:
> > > 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.

...

> It occurs to me that if we had visibility in unique indexes, this would
> allow the index rows to be separately lockable to the main row. That's
> exactly what we need here.

I've implemented a work-around using this principle, utilising RULEs and
a duplicated PK column-only table. This still allows FK checks to work
correctly, yet doesn't require the backend hack Csaba mentioned.

My feeling is that more work in this area is required, even if we can't
yet agree a TODO item.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Simon Riggs wrote:
> > It occurs to me that if we had visibility in unique indexes, this would
> > allow the index rows to be separately lockable to the main row. That's
> > exactly what we need here.
>
> I've implemented a work-around using this principle, utilising RULEs and
> a duplicated PK column-only table. This still allows FK checks to work
> correctly, yet doesn't require the backend hack Csaba mentioned.
>
> My feeling is that more work in this area is required, even if we can't
> yet agree a TODO item.

OK, please propose some wording so at least we can get agreement on
that.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Mon, 5 Feb 2007, Simon Riggs wrote:

> On Sun, 2007-02-04 at 09:38 +0000, Simon Riggs wrote:
> > > > 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.
>
> ...
>
> > It occurs to me that if we had visibility in unique indexes, this would
> > allow the index rows to be separately lockable to the main row. That's
> > exactly what we need here.
>
> I've implemented a work-around using this principle, utilising RULEs and
> a duplicated PK column-only table. This still allows FK checks to work
> correctly, yet doesn't require the backend hack Csaba mentioned.
>
> My feeling is that more work in this area is required, even if we can't
> yet agree a TODO item.

I actually like the general idea your TODO item had, although I would say
non-referenced column update rather than non-PK update. Even if we put it
far out due to questions about what would be acceptable implementation.

"Bruce Momjian" <bruce(at)momjian(dot)us> writes:

> OK, please propose some wording so at least we can get agreement on
> that.

How about something open-ended like "arrange for updates that do not update
columns referenced by foreign keys from other tables to avoid being blocked by
locks from concurrent RI checks"

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

"Gregory Stark" <stark(at)enterprisedb(dot)com> writes:

> "Bruce Momjian" <bruce(at)momjian(dot)us> writes:
>
>> OK, please propose some wording so at least we can get agreement on
>> that.
>
> How about something open-ended like "arrange for updates that do not update
> columns referenced by foreign keys from other tables to avoid being blocked by
> locks from concurrent RI checks"

Hum. Reading back in the thread it seems what I wrote is basically equivalent
to the wording Simon originally proposed.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Mon, 2007-02-05 at 23:25 +0000, Gregory Stark wrote:
> "Gregory Stark" <stark(at)enterprisedb(dot)com> writes:
>
> > "Bruce Momjian" <bruce(at)momjian(dot)us> writes:
> >
> >> OK, please propose some wording so at least we can get agreement on
> >> that.
> >
> > How about something open-ended like "arrange for updates that do not update
> > columns referenced by foreign keys from other tables to avoid being blocked by
> > locks from concurrent RI checks"
>
> Hum. Reading back in the thread it seems what I wrote is basically equivalent
> to the wording Simon originally proposed.

I like your wording. It's clearer and includes Stephan's clarification.
Some minor mods...

TODO

"avoid blocking of updates because of concurrent RI checks when those
updates do not alter columns referenced by foreign keys from other
tables"

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

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