Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 43D79475843 for ; Wed, 20 Nov 2002 18:52:32 -0500 (EST) Received: from medi.engr.intransa.com (unknown [12.146.157.2]) by postgresql.org (Postfix) with ESMTP id B0D6E474E61 for ; Wed, 20 Nov 2002 18:52:31 -0500 (EST) Received: from intransa.com (localhost.localdomain [127.0.0.1]) by medi.engr.intransa.com (8.12.5/8.12.5) with ESMTP id gAKNr6NB024671; Wed, 20 Nov 2002 15:53:07 -0800 Message-ID: <3DDC2062.7000109@intransa.com> Date: Wed, 20 Nov 2002 15:53:06 -0800 From: Medi Montaseri User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020830 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "scott.marlowe" Cc: Neil Conway , Francisco Reyes , pgsql General List Subject: Re: Closing inactive connections OR user connections limits References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/879 X-Sequence-Number: 33300 scott.marlowe wrote: >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.... >> >> So I think you have shown that 7.2 achieves this by skiping current transactions.... Thank you >>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 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) >> >> >> > > >