Re: Question about Idle in TX

On 04/08/10 03:17, John R Pierce wrote:
> On 08/03/10 12:13 PM, David Kerr wrote:
>> I know that Idle in Transactions are a problem, however I'm trying to
>> assess how much of a problem.
>>
>> for example: If a java program connects to the DB and does "begin;"
>> and then internally does a "sleep 6 days"
>>
>> Does that cauz any issues other than eating a connection to the database?
>>
>> (note, nothing i have does this, i'm just trying to understand)
>>
>> I know that "Idle in TXs" can interfere with Vaccums for example, but
>> I'm not sure if that's due to them usually having some form of lock on a
>> table.
>
> no dead tuples created after the oldest active transaction (including
> said <Idle in Transaction>) can be vacuumed, from anywhere in the database.

Is that still true for READ COMMITTED transactions? Because it need not be.

I seem to remember a previous discussion in which it emerged that as of
8.3 or 8.4 Pg is smart enough to realize that an open READ COMMITTED
transaction can't ever refer to tuples from snapshots older than the
currently running statement (if any), so it shouldn't impede vacuum. I
can't seem to find any references for that, though.

For that matter, a SERIALIZABLE transaction only acquires its snapshot
on the first _real_ command (SELECT, etc) so it shouldn't impede VACUUM
if it's just issued a BEGIN and a few SETs. However, I'm not totally
sure it *doesn't* impede vacuum, it just doesn't have to.

--
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.com/