agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Antonin Houska <[email protected]>
To: Corey Huinker <[email protected]>
Cc: [email protected]
Cc: Adam Brusselback <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Referential Integrity Checks with Statement-level Triggers
Date: Mon, 25 Feb 2019 10:00:31 +0100
Message-ID: <4902.1551085231@localhost> (raw)
In-Reply-To: <CADkLM=dqP5HPpDi5S4xWazjTm=mcOWG2zbo1Rzf3qYkKm=fxLQ@mail.gmail.com>
References: <CAFj8pRCQe9=tX5GmC-S_Dmms9tdU4OWGD2xaHZFm-yaMS1zmoQ@mail.gmail.com>
<[email protected]>
<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>
Corey Huinker <[email protected]> wrote:
> Have you considered bulk processing of individual rows by row-level trigger?
> For IMMEDIATE constraints we'd have to ensure that the trigger is notified
> that the current row is the last one from the current query, but that might
> 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 like
> 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 contains
given amount of tuples or if the last row of the current statement has been
stored.
> Given that this patch has been punted to v13, I'd like to instead look at
> 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 has
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-constraint
triggers is difficult because deferred constraint triggers need to see rows
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 could
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
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Referential Integrity Checks with Statement-level Triggers
In-Reply-To: <4902.1551085231@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox