On Fri, Jul 28, 2006 at 03:08:08AM +0300, Hannu Krosing wrote: > > The other POV is that we don't really care about long-running > > transaction in other databases unless they are lazy vacuum, a case which > > is appropiately covered by the patch as it currently stands. This seems > > to be the POV that Hannu takes: the only long-running transactions he > > cares about are lazy vacuums. > > Yes. The original target audience of this patch are users running 24/7 > OLTP databases with big slow changing tables and small fast-changing > tables which need to stay small even at the time when the big ones are > vacuumed. > > The other possible transactions which _could_ possibly be ignored while > VACUUMING are those from ANALYSE and non-lazy VACUUMs.
There are other transactions to consider: user transactions that will run a long time, but only hit a limited number of relations. These are as big a problem in an OLTP environment as vacuum is. Rather than coming up with machinery that will special-case vacuum or pg_dump, etc., I'd suggest thinking about a generic framework that would work for any long-runnnig transaction. One possibility: Transaction flags itself as 'long-running' and provides a list of exactly what relations it will be touching. That list is stored someplace a future vacuum can get at. The transaction runs, with additional checks that ensure it will not touch any relations that aren't in the list it provided. Any vacuums that start will take into account these lists of relations from long-running transactions and build a list of XIDs that have provided a list, and the minimum XID for every relation that was listed. If vacuum wants to vacuum a relation that has been listed as part of a long-running transaction, it will use the oldest XID in the database/cluster or the oldest XID listed for that relation, whichever is older. If it wants to vacuum a relation that is not listed, it will use the oldest XID in the database/cluster, excluding those XIDs that have listed exactly what relations they will be looking at. That scheme won't help pg_dump... in order to do so, you'd need to allow transactions to drop relations from their list. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
