Re: foreign key constraint lock behavour in postgresql

On Thu, Feb 4, 2010 at 12:40 AM, <david(at)lang(dot)hm> wrote:
> I could be wrong in this (if so I know I'll be corrected :-)
>
> but Postgres doesn't need to lock anything for what you are describing.
>
> instead there will be multiple versions of the 'b1' row, one version will be
> deleted, one version that will be kept around until the first transaction
> ends, after which a vaccum pass will remove the data.

Just for kicks I tried this out and the behavior is as the OP
describes: after a little poking around, it sees that the INSERT grabs
a share-lock on the referenced row so that a concurrent update can't
modify the referenced column.

It's not really clear how to get around this. If it were possible to
lock individual columns within a tuple, then the particular update
above could be allowed since only the name is being changed. Does
anyone know what happens in Oracle if the update targets the id column
rather than the name column?

Another possibility is that instead of locking the row, you could
recheck that the foreign key constraint still holds at commit time.
But that seems like it could potentially be quite expensive.

...Robert