Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Kevin Grittner <kgrittn@ymail.com> writes:
>> we acquire locks on all indexes even for a HOT UPDATE which uses
>> a seqscan, and hold those until end of transaction. Is there a
>> reason for that?
>
> Sounds dubious to me; although in the HOT code it might be that
> there's no convenient place to release the index locks.
Further testing shows that any UPDATE or DELETE statement acquires
a RowExclusiveLock on every index on the table and holds it until
end of transaction, whether or not any rows are affected and
regardless of whether an index scan or a seqscan is used. In fact,
just an EXPLAIN of an UPDATE or DELETE does so. It is only INSERT
which releases the locks at the end of the statement.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company