Re: "Idle in Transaction" revisited.

"John R Pierce" <pierce(at)hogranch(dot)com> writes:
> We've been running into a *lot* of problems with "Idle in Transaction"
> threads causing long term blockage of VACUUM's ability to free up dead rows.

Yeah, that's from prematurely issuing BEGIN. I believe there's a
solution for this in the most recent JDBC driver versions --- but
I dunno if it's in any official releases yet.

> One case in point, as a SQL form of 'mutex', one of these developers was
> used to doing a 'UPDATE' on a particular row of a table without ever
> committing to leave a row lock in place so that other threads won't attempt
> to 'subscribe' to the same 'topic' on a corporate intranet messaging system.
> We can't do this in Postgres (works great in Oracle) due to the 'idle in
> transaction' issue...

Check. It seems like what you might want here is cooperative locking
a la the contrib/userlock module. We were just having a discussion in
another list about whether that code had any real future ... but if we
see people actually using it in the field, it's much more likely to get
moved into the mainline than dropped.

> What about a resource locking table like I described above, where we've got
> a few dozen arbitrarily named resources we need to gain a mutex on? When I
> suggested 'select for update' to see if the resource was available, then
> 'update' to mark it in use, and commit, the developer said that leaves the
> resource locked if the app crashes or the box is rebooted or whatever, while
> his row lock would be cleared automatically.

A userlock will be dropped on session exit or database restart, so I
think that base is covered. The main objection I can think of is that
it is voluntary, since the backend has no idea what other DB resources
a userlock is meant to protect. Depending on your coding practices that
might be fine, or it might be a fatal drawback.

If you migrate to using userlocks or some variant, let us know --- real
applications using it will definitely move that code way up in priority.

regards, tom lane