Skip site navigation (1) Skip section navigation (2)

Re: understanding the interaction with delete/select/vacuum

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: stange(at)rentec(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: understanding the interaction with delete/select/vacuum
Date: 2005-08-29 19:52:13
Message-ID: 12520.1125345133@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Alan Stange <stange(at)rentec(dot)com> writes:
> I have a long running process which does a 'SELECT ID FROM T'.  The 
> results are being streamed to the client using a fetch size limit.  This 
> process with take 26 hours to run.    It turns out that all the "C" and 
> "P" are going to be deleted when the SELECT gets to them.

> Several hours into this process, after the "C" rows have been deleted in 
> a separate transaction but we haven't yet gotten to the "P" rows, a 
> vacuum is begun on table T.

> What happens?

VACUUM can't remove any rows that are still potentially visible to any
open transaction ... so those rows will stay.  It's best to avoid having
single transactions that take 26 hours to run --- there are a lot of
other inefficiencies that will show up in such a situation.  Can you
break the long-running process into shorter transactions?

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Alan StangeDate: 2005-08-29 20:09:51
Subject: Re: understanding the interaction with delete/select/vacuum
Previous:From: Oren MazorDate: 2005-08-29 19:18:48
Subject: Re: understanding the interaction with delete/select/vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group