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