From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Ashutosh Bapat <ashutosh.bapat@2ndquadrant.com>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: BEFORE ROW triggers for partitioned tables
Date: Wed, 18 Mar 2020 18:02:13 -0300
Message-ID: <20200318210213.GA9781@alvherre.pgsql> (raw)
In-Reply-To: <CAG-ACPUA4jWQ5dAB30U5ox8JeRumEh447vM6HM3rnnmgjhz0jQ@mail.gmail.com>
On 2020-Mar-17, Ashutosh Bapat wrote:
> On Fri, 13 Mar 2020 at 21:55, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
> > Note that in this implementation I no longer know which column is the
> > problematic one, but I suppose users have clue enough. Wording
> > suggestions welcome.
>
> When we have expression as a partition key, there may not be one particular
> column which causes the row to move out of partition. So, this should be
> fine.
True.
> A slight wording suggestion below.
>
> - * Complain if we find an unexpected trigger type.
> - */
> - if (!TRIGGER_FOR_AFTER(trigForm->tgtype))
> - elog(ERROR, "unexpected trigger \"%s\" found",
> - NameStr(trigForm->tgname));
>
> !AFTER means INSTEAD OF and BEFORE. Do you intend to allow INSTEAD OF
> triggers as well?
Hmm, yeah, this should check both types; I'll put it back. Note that
this is just a cross-check that the catalogs we're going to copy don't
contain bogus info; the real backstop for that at the user level is in
the other one you complain about:
> - */
> - if (stmt->timing != TRIGGER_TYPE_AFTER)
>
> Same comment as the above?
Note that in this one we have a check for INSTEAD before we enter the
FOR EACH ROW block, so this case is already covered -- AFAICS the code
is correct.
> + /*
> + * After a tuple in a partition goes through a trigger, the user
> + * could have changed the partition key enough that the tuple
> + * no longer fits the partition. Verify that.
> + */
> + if (trigger->tgisclone &&
>
> Why do we want to restrict this check only for triggers which are
> cloned from the ancestors?
Because it's not our business in the other case. When the trigger is
defined directly in the partition, it's the user's problem if something
going amiss.
> + !ExecPartitionCheck(relinfo, slot, estate, false))
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("moving row to another partition during a BEFORE trigger is not
> supported"),
> + errdetail("Before trigger \"%s\", row was to be in partition \"%s.%s\"",
>
> In the error message you removed above, we are mentioning BEFORE FOR EACH
> ROW trigger. Should we continue to use the same terminology?
Sounds good, I'll change that.
I also changed the errdetail slightly:
errdetail("Before executing trigger \"%s\", the row was to be in partition \"%s.%s\"",
> I was wondering whether it would be good to check the partition
> constraint only once i.e. after all before row triggers have been
> executed. This would avoid throwing an error in case multiple triggers
> together worked to keep the tuple in the same partition when
> individual trigger/s caused it to move out of that partition. But then
> we would loose the opportunity to point out the before row trigger
> which actually caused the row to move out of the partition. Anyway,
> wanted to bring that for the discussion here.
Yeah, I too thought about a combination of triggers that move the tuple
elsewhere and back. Frankly, I don't think we need to support that. It
sounds devious and likely we'll miss some odd corner case -- anything
involving the weird cross-partition UPDATE mechanism sounds easy to get
wrong.
> +-- Before triggers and partitions
>
> The test looks good. Should we add a test for partitioned table with
> partition
> key as expression?
Will do.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, ashutosh.bapat@2ndquadrant.com, ashutosh.bapat.oss@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: BEFORE ROW triggers for partitioned tables
In-Reply-To: <20200318210213.GA9781@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox