|From:||Corey Huinker <corey(dot)huinker(at)gmail(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|
|Views:||Raw Message | Whole Thread | Download mbox|
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
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
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
|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|