From: Antonin Houska <ah@cybertec.at>
To: Corey Huinker <corey.huinker@gmail.com>
cc: emre@hasegeli.com, Adam Brusselback <adambrusselback@gmail.com>,
    Pavel Stehule <pavel.stehule@gmail.com>,
    Alvaro Herrera <alvherre@2ndquadrant.com>,
    PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Referential Integrity Checks with Statement-level Triggers
In-reply-to: 
 <CADkLM=dqP5HPpDi5S4xWazjTm=mcOWG2zbo1Rzf3qYkKm=fxLQ@mail.gmail.com>
References: 
 <CAFj8pRCQe9=tX5GmC-S_Dmms9tdU4OWGD2xaHZFm-yaMS1zmoQ@mail.gmail.com>
 <20181217172729.mjfkflaelii2boaj@alvherre.pgsql>
 <CAFj8pRBKcgMb1onbDz7abPL_aK6A6uUfbS5hpaQ=Og9nAskU7Q@mail.gmail.com>
 <CAMjNa7fPEUonQJDE2bY4KvCR+Ai7qUTT1XdUhgHYXJ8DMpx+tg@mail.gmail.com>
 <CAE2gYzxGYagVfHvG9bMAiz7vjGK7GQwVaY0YLddPditD1o6kcQ@mail.gmail.com>
 <CADkLM=diwD7cQW-kvuEEVLKW-P7EN0gqDYBdUm2vGR3zd+zz-A@mail.gmail.com>
 <17100.1550662686@localhost>
 <CADkLM=dqP5HPpDi5S4xWazjTm=mcOWG2zbo1Rzf3qYkKm=fxLQ@mail.gmail.com>
Comments: In-reply-to Corey Huinker <corey.huinker@gmail.com>
   message dated "Fri, 22 Feb 2019 12:22:06 -0500."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4901.1551085231.1@localhost>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 25 Feb 2019 10:00:31 +0100
Message-ID: <4902.1551085231@localhost>
Precedence: bulk

Corey Huinker <corey.huinker@gmail.com> wrote:

>  Have you considered bulk processing of individual rows by row-level tri=
gger?
>  For IMMEDIATE constraints we'd have to ensure that the trigger is notif=
ied
>  that the current row is the last one from the current query, but that m=
ight
>  not be difficult.
> =

> I'm not sure I understand what you're suggesting, but if it keeps the
> overhead of one trigger firing per row deleted, then it doesn't seem lik=
e
> much of a win.

I thought of a trigger that still fires for each row, but most of the time=
 it
only stores the row deleted into a tuplestore of the appropriate lifespan.=
 The
queries that you proposed would only be executed if the tuplestore contain=
s
given amount of tuples or if the last row of the current statement has bee=
n
stored.

> Given that this patch has been punted to v13, I'd like to instead look a=
t
> how we might go about building up the transition tuplestores for the
> specific purpose of doing the RI checks, not just deletes, and executing
> those at the appropriate time, rather than trying to make our needs fit =
into
> trigger form.

Constraint-specific tuplestore can make some things easier, but if table h=
as
both constraints and (non-constraint) triggers which use the transition
tables, then the tuples will have to be stored in both tuplestores. On the
other hand, using the same tuplestore for both constraint and non-constrai=
nt
triggers is difficult because deferred constraint triggers need to see row=
s
added by all statements while the non-constraint triggers should only see =
rows
of the current statement.

In order to avoid per-row calls of the constraint trigger functions, we co=
uld
try to "aggregate" the constraint-specific events somehow, but I think a
separate queue would be needed for the constraint-specific events.

In general, the (after) triggers and constraints have too much in common, =
so
separation of these w/o seeing code changes is beyond my imagination.

-- =

Antonin Houska
https://www.cybertec-postgresql.com