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.marlowe@xxxxxxxxx> 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.fr@xxxxxxxxx> 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@xxxxxxxxxxxx> wrote: >>> Alexandre de Arruda Paes <adaldeia@xxxxxxxxx> wrote: >>>> 2010/8/18 Tom Lane <tgl@xxxxxxxxxxxxx> >>> >>>>> 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@xxxxxxxxxxxxxx) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-performance >>> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > > > -- > To understand recursion, one must first understand recursion. > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
