Re: 7.4b4 undetected deadlock?

When grilled further on (Sun, 05 Oct 2003 23:22:45 -0400),
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. Unfortunately, I lost my connection before I got your e-mail, so I cannot
check.

>
> which is not waiting for any lock but is just sitting on its duff while
> holding quite a pile of locks. Ergo, no deadlock, just badly written
> client software.

Ok. I'll believe that. I jumped a little early as I did run into a real
problem a couple of months ago on 7.4b1 that you fixed, which was strikingly
similar to what I just saw.

>
> BTW, the 29335 process is holding rather a large pile of locks itself,
> way more than I'd expect for a REINDEX alone. It looks like you must
> be running the REINDEX inside a transaction that has already touched
> a number of other tables. 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. The indexes are required
for reasonable performance. The data is spatially consistent, so I VACUUM on
the first data set, then REINDEX all subsequent sets. I'm not enough of a dB
wiz to figure out a better way of doing this, as none of the data can go in if
all of it doesn't. And all the tables being hit are FK linked in some fashion.

I guess I could reverse the process which would hit the 3k record sets first,
and then take the join...

Thanks,
Rob

--
21:33:12 up 65 days, 14:02, 4 users, load average: 2.18, 2.79, 2.53