Re: 7.4b4 undetected deadlock?

Robert Creager <Robert_Creager(at)LogicalChaos(dot)org> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> confessed:
>> so indeed everyone is waiting for this process:
>>
>> postgres 29304 20209 0 17:37 pts/4 00:00:00 postgres: robert tassiv
>> 192.168.0.250 idle in transaction

> Hmmm... Not sure what that was doing. I'm only running one client.
> I had just aborted an import, and may have left the client suspended,
> rather than killing it.

Well, the problem may just reduce to that then.

>> ... I'd have to counsel rethinking that ---
>> even though there's no deadlock in this case, you are surely risking
>> deadlocks. Since REINDEX needs an exclusive lock, you should try to
>> minimize the number of other locks you have when you start the REINDEX.

> Well, I'm inserting single records into 4 tables, updating another
> using a temp table, importing ~3k records into two tables (each), then
> joining those last two tables (hence the REINDEX) to get matching
> entries, then selecting the results into the final table, all in one
> transaction.

This is perfectly okay as long as you're not expecting concurrent
transactions on those same tables. If you are then there may be a risk.
The temp table is obviously not a problem, but without detailed analysis
of access patterns to the permanent tables, it's hard to say whether
this is problematic or not.

regards, tom lane