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

Re: understanding the interaction with delete/select/vacuum

From: "Oren Mazor" <oren(dot)mazor(at)gmail(dot)com>
To: stange(at)rentec(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: understanding the interaction with delete/select/vacuum
Date: 2005-08-29 19:18:48
Message-ID: op.swaalmshv14azh@oren-mazors-computer.local (view raw or flat)
Thread:
Lists: pgsql-novice
my understanding of how postgres works may be flawed, but the way I do  
understand it is that each process gets its own 'copy' of the database to  
work with...

which is why, for example, you wont be able to run a vaccuum if you've got  
an ongoing connection to the dbase.

I think.

On Mon, 29 Aug 2005 15:11:14 -0400, Alan Stange <stange(at)rentec(dot)com> wrote:

> Hello all,
>
> say for example I have a larger table T with 26 millions rows, one  
> million associated with each letter of the alphabet.
>
> 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?
>
> Will the 1 million "C" rows be freed and made available for reuse or  
> will their visibility with the initial SELECT statement cause the vacuum  
> to skip over them?
>
> Thanks!
>
> -- Alan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings



-- 
Oren Mazor // Developer, Sysadmin, Explorer
GPG Key: http://www.grepthemonkey.org/secure
"Ut sementem feceris, ita metes"

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2005-08-29 19:52:13
Subject: Re: understanding the interaction with delete/select/vacuum
Previous:From: Alan StangeDate: 2005-08-29 19:11:14
Subject: understanding the interaction with delete/select/vacuum

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