2010/8/21 Dimitri <dimitrik...@gmail.com> > 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 > > Unfortunately, the customer can't wait for the solution and the programmer eliminated the use of this table by using a in-memory array.
I understood that all transactions, touching this table or not, can affect the ability of the vacuum to recover the dead tuples. In my scenario, it's too bad because I have long transactions and I really not know when I will recover this tuples. And, like I sad, the table will become more slow every time. Only for discussion: the CLUSTER command, in my little knowledge, is a intrusive command that's cannot recover the dead tuples too. Only TRUNCATE can do this job, but obviously is not applicable all the time. Best regards, Alexandre > On 8/21/10, Scott Marlowe <scott.marl...@gmail.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...@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.gritt...@wicourts.gov> wrote: > >>> Alexandre de Arruda Paes <adald...@gmail.com> wrote: > >>>> 2010/8/18 Tom Lane <t...@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. > > >
