Thread: transaction toggling

transaction toggling

From
Matthew Peter
Date:
Is there a way to disable transactions on certian
queries? I read a while back that this wasn't
possible, just wondering if it is in 8.1 or planned in
the future?

Does it even make a dent in the performance if a query
is wrapped in a transaction instead of out? Such as a
view counter, like...

update views = views + 1 where x = 1;?

Thanks
MP



__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

Re: transaction toggling

From
Martijn van Oosterhout
Date:
On Wed, Oct 05, 2005 at 12:49:33AM -0700, Matthew Peter wrote:
> Does it even make a dent in the performance if a query
> is wrapped in a transaction instead of out? Such as a
> view counter, like...
>
> update views = views + 1 where x = 1;?

All statements are within a transaction. The question is if it's one
started by you or one created just for that statement. So, no
difference.

Regular vacuum, that's the solution.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Attachment

Re: transaction toggling

From
Richard Huxton
Date:
Matthew Peter wrote:
> Is there a way to disable transactions on certian
> queries? I read a while back that this wasn't
> possible, just wondering if it is in 8.1 or planned in
> the future?
>
> Does it even make a dent in the performance if a query
> is wrapped in a transaction instead of out? Such as a
> view counter, like...
>
> update views = views + 1 where x = 1;?

It's not in general possible to have part of your database under
transactional control and part not.

For example, what happens in your update if there are two processes
incrementing the view counter - A is in a transaction, B is not.
   A begins transaction
   A increments view count
   B increments view count
   A rolls back transaction

What should happen here?
--
   Richard Huxton
   Archonet Ltd

Re: transaction toggling

From
Matthew Peter
Date:
--- Richard Huxton <dev@archonet.com> wrote:

> Matthew Peter wrote:
> > Is there a way to disable transactions on certian
> > queries? I read a while back that this wasn't
> > possible, just wondering if it is in 8.1 or
> planned in
> > the future?
> >
> > Does it even make a dent in the performance if a
> query
> > is wrapped in a transaction instead of out? Such
> as a
> > view counter, like...
> >
> > update views = views + 1 where x = 1;?
>
> It's not in general possible to have part of your
> database under
> transactional control and part not.
>
> For example, what happens in your update if there
> are two processes
> incrementing the view counter - A is in a
> transaction, B is not.
>    A begins transaction
>    A increments view count
>    B increments view count
>    A rolls back transaction
>
> What should happen here?


I'm not worried about that. I know what transactions
are suppose to 'prevent', but for a hit counter I
don't need transaction support to rollback.

Having a busy site I don't see the benefits to
automatically wrap every transaction with BEGIN; ...
COMMIT; w/o option to IGNORE; ... DONE;

I don't want to consume more resources than necessary,
generating unnecessary overhead and junk for VACUUM to
clean up if possible...

MP



__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

Re: transaction toggling

From
"Jim C. Nasby"
Date:
On Wed, Oct 05, 2005 at 12:49:33AM -0700, Matthew Peter wrote:
> Is there a way to disable transactions on certian
> queries? I read a while back that this wasn't
> possible, just wondering if it is in 8.1 or planned in
> the future?
>
> Does it even make a dent in the performance if a query
> is wrapped in a transaction instead of out? Such as a
> view counter, like...
>
> update views = views + 1 where x = 1;?

There was just a thread along these lines in -performance. *If*
performance becomes a concern, you can replace that update with:

insert into queue_table

Then periodically delete from that table using a plpgsql function that
can catch a row count, and use that row count to update your views
table.

But remember the first rule of performance tuning: don't.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461