Re: allow LIMIT in UPDATE and DELETE

On Fri, May 19, 2006 at 05:19:37PM +0200, Csaba Nagy wrote:
> > Since Postgresql's MVCC system doesn't require rollback segments I guess the performance question is the more important of the two. If there could be a performance gain in Postgresql with "delete from X where Y limit Z" vs "delete from X where Y in (some subselect limit Z)" I'd think Csaba suggestion has some merit.
>
> But postgres has in turn the problem of not functional vacuum when you
> have long running transactions. That is a problem for heavily recycled
> tables like queue tables.

I'd rather see time invested in coming up with a way to seperate long
running and short running transactions from an MVCC standpoint than
relying on the ability to shorten transactions lengths. But, there is
another advantage to doing deletes or even updates in chunks; it means
fewer dead rows in the table being modified, since autovacuum can go to
work any time after the first long-running update/delete finishes.

> Now recently I have solved the vacuum problem by regularly CLUSTER-ing
> our most heavily used queue table, so long running transactions are not
> anymore such a huge problem for us, but we still have the case of some
> user triggered operations which time out on the web before finishing on
> the DB. Some of those would make perfect sense to be done partially and
> the user then can restart the operation from where it left... now we
> often have 5 minutes of updates rolled back and leaving nothing useful
> but only dead rows.

BTW, there's a bug/issue with CLUSTER that makes it not entirely
transaction safe.

> And yes, not everything we do must be perfectly transactional...
> sometimes the partial work worth more than partial work rolled back.

Yes, something that some people in the community have a hard time
accepting. A great example is storing session data for websites. It's
convenient to do it in the database, but it can cause big performance
problems with MVCC. But it's session data; if you have to wipe the table
after a database crash, who cares?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461