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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
