Re: SIREAD lock versus ACCESS EXCLUSIVE lock

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:

> I haven't dug into ALTER INDEX enough to know whether it can ever
> cause an index to be rebuilt.  If so, we need to treat it like
> DROP INDEX and REINDEX -- which should change all predicate locks
> of any granularity on the index into relation locks on the
> associated table.
>
> CLUSTER or an ALTER TABLE which causes a rewrite should change all
> predicate locks on the table and all indexes into relation locks
> on the associated table.  (Obviously, an existing relation lock on
> the table doesn't require any action.)
>
> TRUNCATE TABLE and DROP TABLE should generate a rw-conflict *in*
> to the enclosing transaction (if it is serializable) from all
> transactions holding predicate locks on the table or its indexes.
> Note that this could cause a transactions which is running one of
> these statements to roll back with a serialization error. This
> seems correct to me, since these operations essentially delete all
> rows.  If you don't want the potential rollback, these operations
> should be run at another isolation level.  The difference between
> these two statements is that I think that TRUNCATE TABLE should
> also move the existing predicate locks to relation locks on the
> table while DROP TABLE (for obvious reasons) should just delete
> the predicate locks.
>
> DROP DATABASE should quietly clean up any predicate locks from
> committed transactions which haven't yet hit their cleanup point
> because of overlapping transactions in other databases.

I missed VACUUM FULL when pulling together the above, but I haven't
found any other omissions.  (Still happy to hear about any that
anyone can spot.)

I notice that most of these need to shift transfer locks to relation
locks on the heap, either for a single index or for the heap and all
indexes.  I wrote a function to do this and called it from one place
to be able to test it.  Consider this a WIP patch on which I would
appreciate review while I work on finding the other places to call
it and the miscellaneous other fixes needed.

Note that I had to expose one previously-static function from
index.c to find the heap OID from the index OID.  Also, I ran
pgindent against predicate.c, as I generally like to do when I
modify much code, and it found four comment blocks in predicate.c
touched since the recent global pgindent run which it re-wrapped.
I can manually exclude those from the final patch if people would
prefer that; but if people can ignore those whitespace tweaks, it
might not be all bad to get standard formatting onto them at this
point.

-Kevin