Oliver Kohll wrote:
>> In the example you show, some connections are "idle in transaction".
>> Such connections can hold locks that block your CREATE INDEX.
>> Could you look at pg_locks if there is a lock that is not granted?
>> There could also be prepared transactions holding locks, if
>> you use that feature.
>
> Yes there was indeed an un-granted lock on the table. By logging all
SQL I think I've found the client
> code which is causing the problem, a line was added recently that
basically causes
>
> DROP INDEX IF EXISTS
> followed by
> CREATE INDEX
>
> to be run twice in quick succession. These are all in the same thread
and transaction so I wouldn't
> have thought there'd be a locking problem but it's the most likely
candidate at the moment.
You should always include the list in your replies -
other people might be interested in the solution.
There must be at least two transactions involved
to create a locking problem like you describe.
But since CREATE INDEX takes strong locks, it can
easily get locked by other "harmless" things.
Yours,
Laurenz Albe