No, it means it can't clean rows that are younger than the oldest
transaction currently in progress. if you started a transaction 5
hours ago, then all the dead tuples created in the last 5 hours are
not recoverable. Dead tuples created before that transaction are
recoverable. If you run transactions for days or weeks, then you're
gonna have issues.
On Sat, Aug 21, 2010 at 2:25 AM, Dimitri <dimitrik.fr@gmail.com> wrote:
> So, does it mean that VACUUM will never clean dead rows if you have a
> non-stop transactional activity in your PG database???... (24/7 OLTP
> for ex.)
>
> Rgds,
> -Dimitri
>
>
> On 8/19/10, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
>>> 2010/8/18 Tom Lane <tgl@sss.pgh.pa.us>
>>
>>>> There's an open transaction somewhere that VACUUM is preserving
>>>> the tuples for. This transaction need not ever have touched the
>>>> table, or ever intend to touch the table --- but VACUUM cannot
>>>> know that, so it saves any tuples that the transaction might be
>>>> entitled to see if it looked.
>>>>
>>>> > carmen=# select * from vlocks where relname='tp93t'; select *
>>>> > from pg_stat_activity where usename='webpa';
>>>>
>>>> You keep on showing us only subsets of pg_stat_activity :-(
>>
>>> select * from pg_stat_activity where usename='webpa';
>>
>> You keep on showing us only subsets of pg_stat_activity :-(
>>
>> *ANY* open transaction, including "idle in transaction" including
>> transactions by other users in other databases will prevent vacuum
>> from cleaning up rows, for the reasons Tom already gave you.
>>
>> What do you get from?:
>>
>> select * from pg_stat_activity where current_query <> '<IDLE>'
>> order by xact_start limit 10;
>>
>> -Kevin
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
To understand recursion, one must first understand recursion.