Re: locks in CREATE TRIGGER, ADD FK

Neil Conway wrote:
> ... except that when we rebuild the relation's indexes, we acquire an
> AccessExclusiveLock on the index. This would introduce the risk of
> deadlock. It seems necessary to acquire an AccessExclusiveLock when
> rebuilding shared indexes, since we do the index build in-place, but I
> think we can get by with an ExclusiveLock in the non-shared case, for
> similar reasons as above: we build the new index and then swap
> relfilenodes.

From looking at the code, it should be quite possible to do this.

Further points from discussion on IRC:

- TRUNCATE suffers from the same behavior (it acquires an
AccessExclusiveLock where really an ExclusiveLock or similar should be
good enough)

- if we make these changes, we will need some way to delete a
no-longer-visible relfilenode. It should be sufficient to delete a
relfilenode when the expired pg_class row that refers to it is no longer
visible to any transactions -- but this isn't necessarily going to be
true when the transaction that executed the REINDEX/CLUSTER/TRUNCATE
commits. We could perform this check in some kind of periodic process,
perhaps -- like the bgwriter, at checkpoint time.

-Neil