Re: Partial index locks

On 22 March 2014 00:59, Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> wrote:
> On 03/22/2014 01:43 AM, Thom Brown wrote:
>> Hi,
>>
>> I've created a table with 1000 partial indexes. Each one matches
>> exactly one row based on the predicate WHERE id = <value>.
>>
>> However, when I perform an UPDATE of a single row in a transaction,
>> I've noticed that all those partial indexes show up in pg_locks with
>> RowExclusiveLock.
>>
>> Only 2 of those indexes have a reference to the row: the primary key
>> and a single partial index.
>>
>> Is it necessary for a partial index that doesn't include the row to be
>> involved in locking?
>
> What if the update puts the row into one of the other indexes?

Well here's where I'm confused. The entries in pg_locks show than a
RowExclusiveLock is being held on the index for which there is no
matching row. What does that translate as? There is also a
RowExclusiveLock on the table itself too, which is what I expect to
see.

Also, a delete results in all the locks being taken too. That can't
possibly result in a new entry being put into any of those indexes.

As those indexes don't contain references to the row, what is it locking?

--
Thom