Skip site navigation (1) Skip section navigation (2)

Scaling up deferred unique checks and the after trigger queue

From: Dean Rasheed <dean(dot)a(dot)rasheed(at)googlemail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Scaling up deferred unique checks and the after trigger queue
Date: 2009-10-07 17:14:13
Message-ID: 8e2dbb700910071014h13987769k9e001985148c7a05@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
I've started looking at the following TODO item:

  "Improve deferrable unique constraints for cases with many conflicts"

and Tom's suggestion that the rows to be checked can be stored in a
bitmap, which would become lossy when the number of rows becomes large
enough. There is also another TODO item:

  "Add deferred trigger queue file"

to prevent the trigger queue from exhausting backend memory.

I've got some prototype code which attempts to replace all the
after-triggers-queue stuff with TID bitmaps (not just for deferred
constraint triggers). This would solve the memory-usage problem without
resorting to file storage, and makes it easier to then optimise constraint
checks by doing a bulk check if the number of rows is large enough.

The initial results are encouraging, but I'm still pretty new to a lot of
this code, so I wanted to check that this is a sane thing to try to do.
For UPDATEs, I'm storing the old tid in the bitmap and relying on its ctid
pointer to retrieve the new tuple for the trigger function. AFAICS
heap_update() always links the old and new tuples in this way.

I'm aware that the "triggers on columns" patch is going to be a problem
for this. I haven't looked at it in any detail, but I suspect that it won't
work with a lossy queue, because the information about exactly which
rows to trigger on is only known at update time. So maybe I could fall
back on a tuplestore, spilling to disk in that case?

Thoughts?

 - Dean

Responses

pgsql-hackers by date

Next:From: David E. WheelerDate: 2009-10-07 17:28:08
Subject: Re: Concurrency testing
Previous:From: Alvaro HerreraDate: 2009-10-07 17:07:35
Subject: Re: Concurrency testing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group