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

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 (view raw or flat)
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: after_triggers_queue.patch
Description: text/x-patch (87.3 KB)

In response to

Responses

pgsql-hackers by date

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

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