Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

Great! - it's what I expected until now :-)
but discussion in this thread put my mind in trouble :-))

So, the advice for Alexandre here is just to check the age of the
oldest running transaction and the last time when the table in
question was modified.. - if modification time is older than the
oldest transaction = we have a problem in PG.. Otherwise it works as
expected to match MVCC.

Rgds,
-Dimitri

On 8/21/10, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> 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(dot)fr(at)gmail(dot)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(dot)Grittner(at)wicourts(dot)gov> wrote:
>>> Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> wrote:
>>>> 2010/8/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
> --
> To understand recursion, one must first understand recursion.
>