Обсуждение: transaction toggling
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
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.
Вложения
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
--- 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