Re: Referential Integrity Checks with Statement-level Triggers

From: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
To: emre(at)hasegeli(dot)com
Cc: Adam Brusselback <adambrusselback(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Referential Integrity Checks with Statement-level Triggers
Date: 2019-01-23 17:16:14
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

Attached is a patch that refactors DELETE triggers to fire at the statement

I chose delete triggers partly out of simplicity, and partly because there
some before/after row linkage in the ON UPDATE CASCADE cases where
statement level triggers might not be feasible as we have currently
implemented them.

After having done the work, I think INSERT triggers would be similarly
straightforward, but wanted to limit scope.

Also, after having stripped the delete cases out of the update-or-delete
functions, it became obvious that the on-update-set-null and
on-update-set-default cases differed by only 3-4 lines, so those functions
were combined.

On a vagrant VM running on my desktop machine, I'm seeing a speed-up of
about 25% in the benchmark provided. I think that figure is cloudy and
below my expectations. Perhaps we'd get a much better picture of whether or
not this is worth it on a bare metal machine, or at least a VM better
suited to benchmarking.

Currently 4 make-check tests are failing. Two of which appear to false
positives (the test makes assumptions about triggers that are no longer
true), and the other two are outside the scope of this benchmark so I'll
revisit them if we go forward.

ri-set-logic.sql is an edited benchmark script adapted from Kevin
Grittner's benchmark that he ran against hand-rolled triggers and posted on
ri_test.out is a copy paste of two runs of the benchmark script.

Many thanks to everyone who helped, often despite their own objections to
the overall reasoning behind the endeavor. I'm aware that a large
contingent of highly experienced people would very much like to replace our
entire trigger architecture, or at least divorce RI checks from triggers.
Maybe this patch spurs on that change. Even if nothing comes of it, it's
been a great learning experience.

On Sat, Dec 22, 2018 at 11:28 AM Emre Hasegeli <emre(at)hasegeli(dot)com> wrote:

> > It is far from a premature optimization IMO, it is super useful and
> something I was hoping would happen ever since I heard about transition
> tables being worked on.
> Me too. Never-ending DELETEs are a common pain point especially for
> people migrated from MySQL which creates indexes for foreign keys
> automatically.

Attachment Content-Type Size
0001-Change-Delete-RI-triggers-to-Statement-Level-Trigger.patch text/x-patch 36.3 KB
ri-set-logic.sql application/sql 1.4 KB
ri_test.out application/octet-stream 1.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Chapman Flack 2019-01-23 17:22:23 Re: ArchiveEntry optional arguments refactoring
Previous Message Andres Freund 2019-01-23 17:10:12 Re: ArchiveEntry optional arguments refactoring