Re: Closing inactive connections OR user connections limits

Reread the original message I was replying to. He was worried that if I
was to delete rows then roll back, that a vacuum running while I had
deleted the tuples would prevent the rollback.

I.e. I was proving that you COULD roll back a transaction that had a
vacuum occur during it.

On 20 Nov 2002, Brian Hirt wrote:

> Scott,
>
> You did a rollback on the transaction, so the delete was never commited.
> I hope you aren't expecting vacuum to delete uncommited rows from the
> database, are you?
>
> --brian
>
> On Wed, 2002-11-20 at 16:12, 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....
> > >
> > > 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(dot)montaseri(at)intransa(dot)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(at)postgresql(dot)org)
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>