Re: 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: Re: Scaling up deferred unique checks and the after trigger queue
Date: 2009-10-19 16:48:46
Message-ID: 8e2dbb700910190948x735b93ebnd55a449784d8c80c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

This is a WIP patch to replace the after-trigger queues with TID bitmaps
to prevent them from using excessive amounts of memory. Each round of
trigger executions is a modified bitmap heap scan.

Part of the motivation for this is to scale up the deferrable unique
constraints support. I've not done anything to optimise that case
directly yet, but this patch will make it easier to switch over to doing a
bulk check in that case, and even though it is still executing the trigger
for each potential violation, performance for large updates is already
greatly improved because of the reduction in the queue's memory footprint.

It passes all the regression tests, except for the copy2 test, which
sometimes fails, with rows being ordered differently. I believe that the
reason for this failure is that during the bitmap heap scan, pages are
being pruned, and so the UPDATEs inside later trigger executions start
re-using space at the start of the page, whereas the old code moves onto
the next page. This could be fixed by changing the test, grouping the
COPYs inside a single transaction, to prevent page pruning.

- Dean

Attachment Content-Type Size
after_triggers_queue.patch text/x-patch 87.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2009-10-19 16:49:19 Re: Controlling changes in plpgsql variable resolution
Previous Message David E. Wheeler 2009-10-19 16:42:02 Re: Controlling changes in plpgsql variable resolution