Re: Connection Idle in transaction

David Wall wrote:
>>Not block sorry, but the vacuum is not able to free all the row updated
>>by other connections.
>
>
> But each connection shouldn't have any updated rows that haven't been
> committed or rolled back. Now, if you are running some statements against a
> connection and then never calling commit/rollback, that's another thing
> since you are actually tying up resources then. But since the driver just
> does a 'begin' after any given commit/rollback, there shouldn't be anything
> locked by that connection.

It's not a lock issue as I understand it. It's that the presence of an
open "old" transaction means that for rows that have been updated since
that transaction started, VACUUM cannot delete the "old" version of the row.

For example:

> test=> select * from t;
> i | j
> ---+---
> 1 | 1
> 2 | 2
> 3 | 3
> (3 rows)
>
> test=> vacuum full verbose t;
> INFO: vacuuming "public.t"
> INFO: "t": found 3 removable, 3 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet. [...]

> test=> begin;
> BEGIN

On a separate connection:

> test=> update t set j=5 where i=3;
> UPDATE 1
> test=> vacuum full verbose t;
> INFO: vacuuming "public.t"
> INFO: "t": found 0 removable, 4 nonremovable row versions in 1 pages
> DETAIL: 1 dead row versions cannot be removed yet. [...]

Back on the original connection:

> test=> commit;
> COMMIT
> test=> vacuum full verbose t;
> INFO: vacuuming "public.t"
> INFO: "t": found 1 removable, 3 nonremovable row versions in 1 pages
> DETAIL: 0 dead row versions cannot be removed yet. [...]

So the open transaction prevents the old version of the row (where i=3
and j=3) from being removed.

I have a feeling this is an implementation artifact more than anything
-- as it appears that the snapshot to use for a (serializable)
transaction is not actually "taken" until the first query in a
transaction is executed, so the unremovable row in the above example is
never actually needed. But I'm not familiar with how the backend code
works so this is mostly guesswork :)

-O