Обсуждение: Long running transactions

I have a quite heavily accessed database that is vacuumed nightly.
Attached to that database are processes, that at times, due to the way
they are coded, stay "idle in transaction" for long periods of time
(client lib implementation issues a BEGIN).  I've been reading up on
XIDs and the like and I'm trying to determine if this is going to cause
problems.

The reason I ask is one of these processes blocked a slony replication
set from doing it's initial copy with a warning about an old transaction
id.  It made me wonder if long running transactions could some how hurt
things in other areas.  Obviously the slony copy is not an issue for the
postgres list -- I am more interested in the effects of long running
transactions in general.

Wayne

Wayne Schroeder <raz@positivenetworks.net> writes:
> I have a quite heavily accessed database that is vacuumed nightly.
> Attached to that database are processes, that at times, due to the way
> they are coded, stay "idle in transaction" for long periods of time
> (client lib implementation issues a BEGIN).  I've been reading up on
> XIDs and the like and I'm trying to determine if this is going to cause
> problems.

Yup, it is; you should try to fix those apps to not send BEGIN until
they are ready to do some work.  Idle-in-transaction processes will tend
to prevent VACUUM from removing dead rows, for instance, because it
can't be sure that those processes shouldn't be able to see recently-dead
rows.

            regards, tom lane