Restarted the PG service before reading your email, so I can't verify - but your idea about uncomitted prepared transactions may be the culprit.
I am using the ruby PG gem's exec method and then closing the connection. When I get some time I will fire off a few tests and see if I get the same unexpected locking.
Thank you folks for taking the time to reply.
2012/4/10 Tom Lane
<tgl@sss.pgh.pa.us>Steve Horn <steve@stevehorn.cc> writes:
> Here is the result of this select:
> select datname, procpid, current_query from pg_stat_activity;
> As you can see, there appears to be no connection to the other tables with
> the AccessShareLocks.
Well, it's hard to tell what other locks those transactions have already
got. You really ought to be looking in pg_locks not just
pg_stat_activity.
BTW, another thing that occasionally trips people up is unintended use
of a prepared transaction (that they then forget to commit or roll back).
Does the pg_prepared_xacts view show anything?
regards, tom lane
--
Steve Horn