Date: Wed, 20 Nov 2002 16:12:56 -0700 (MST)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Medi Montaseri <medi.montaseri@intransa.com>
Cc: Neil Conway <neilc@samurai.com>, Francisco Reyes <lists@natserv.com>,
	pgsql General List <pgsql-general@postgresql.org>
Subject: Re: Closing inactive connections OR user connections limits
In-Reply-To: <3DDC110B.3030502@intransa.com>
Message-ID: <Pine.LNX.4.33.0211201609270.21609-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 20 Nov 2002, Medi Montaseri wrote:

> Its my understanding that vacuum actually removes tuples that have been 
> updated or deleted.
> Sort of like emptying your trash .... whence a tuple has been removed, 
> no rollback can set the
> state back. If you have logically removed a tuple (not vacuumed yet), 
> then one can rollback,
> but if you vacuum then you can not rollback.
> 
> Now suppose transaction A decides to delete some tuples, a vacuum job 
> comes along and
> deletes things (in parallel), trans A decides to rollback....engines who 
> support parallel
> vacuum-ing and transactions such as PG 7.2 better have a way of 
> protecting themselves
> against this....
> 
> Correct me if ...

Yes, you are wrong.  Postgresql's vacuuming does NOT free tuples that are 
still in a transaction, hence a full vacuum will hand waiting for the 
transaction to complete or roll back.  A normal 7.2 vacuum will simply 
skip the in transaction tuples.

For proof, try this:  (Note A> and B> are used to represent two different 
sessions)

A> create table test (a text, id int);
A> insert into test (a,id) values ('abc',123);
A> begin;
A> delete from test where id=123;
B> vacuum;
A> rollback;
A> select * from test;
a  |  id
--------
abc| 123

Still there.


> 
> Neil Conway wrote:
> 
> >Medi Montaseri <medi.montaseri@intransa.com> writes:
> >  
> >
> >>I think from the data integrity point of view, vacuum is more
> >>important than vacuum full.
> >>    
> >>
> >
> >Why would VACUUM have any effect on data integrity, either positive or
> >negative?
> >
> >Cheers,
> >
> >Neil
> >
> >  
> >
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>