Re: foreign key introduces unnecessary locking ?

> > though constraint triggers should use SnapshotDirty instead of
> > SELECT FOR UPDATE anyway.
> >
> > Did you consider this, Jan?
>
>     Whenever the checks are done, the transaction inserting a new
>     reference to the key must ensure that  this  key  cannot  get
>     deleted until it is done and it's newly inserted reference is
>     visible  to  others.    Otherwise   a   referential   action,
>     preventing referenced key deletion (or other action) wouldn't
>     see those and it would be possible to violate the constraint.
>
>     I  don't  see  any  other way doing it than obtaining a lock.
>     Using SnapshotDirty would mean, that  one  transaction  could
>     DELETE  a  reference,  then  another  transaction removes the
>     primary key  (because  using  Dirty  the  DELETE  is  already
>     visible),  but  now the first transaction rolls back.  Voila,
>     constraint violated.

Using Dirty transaction removing/updating PK could see that concurrent
xaction attempts to update/insert FK and so would wait for its commit/abort.
Just like now same row writers wait for each other.

Having this, we could insert FK without holding locks over PK. At the moment
of constarint check we would see and wait concurrent PK deletion.

If two xactons will wait for each other then one of them will be aborted.

This behaviour is more natural for MVCC system postulated that
only same-row-writers wait for each other. Why two same FK inserters
should wait for each other if we can avoid this?

Also, is there any way to get deferrable PK/UK constraints? I wonder
why unique index is used for them.

Vadim