transaction toggling

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

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/

--- 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

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