From: | Brian Hirt <bhirt(at)mobygames(dot)com> |
---|---|
To: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
Cc: | Medi Montaseri <medi(dot)montaseri(at)intransa(dot)com>, Neil Conway <neilc(at)samurai(dot)com>, Francisco Reyes <lists(at)natserv(dot)com>, pgsql General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Closing inactive connections OR user connections |
Date: | 2002-11-20 23:16:59 |
Message-ID: | 1037834219.5828.17.camel@tex.mobygames.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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
--
Brian Hirt <bhirt(at)mobygames(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2002-11-20 23:20:38 | Re: [GENERAL] Bug with sequence |
Previous Message | scott.marlowe | 2002-11-20 23:12:56 | Re: Closing inactive connections OR user connections limits |
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2002-11-20 23:20:38 | Re: [GENERAL] Bug with sequence |
Previous Message | scott.marlowe | 2002-11-20 23:12:56 | Re: Closing inactive connections OR user connections limits |