Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)
Date: 2018-06-27 21:01:13
Message-ID: 20180627210113.kjch2c3klieum6mu@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Gmail users: this comes from https://postgr.es/m/20180627191819.6g73wu7ck23fdhv6@alvherre.pgsql

On 2018-Jun-27, Alvaro Herrera wrote:

> On 2018-Jun-19, Amit Langote wrote:
>
> > In CreateTrigger(), 86f575948c7 did this.
> >
> > - values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal);
> > + values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal ||
> > in_partition);
> >
> > I'm not sure why it had to be done, but undoing this change doesn't seem
> > to break any regression tests (neither those added by 86f575948c7 nor of
> > the partitioned table foreign key commit). Did we really intend to change
> > the meaning of tginternal like this here?
>
> I'm pretty sure pg_dump breaks if you turn that flag off for triggers in
> partitions, because pg_dump is going to emit commands to create those
> triggers, and those fail.

After idlying some more on this, I think one possibility is to add
another column to pg_trigger to differentiate triggers that should not
be shown by psql nor dumped, from triggers that should be shown by psql
but not dumped.

Two possibilities:
a) a boolean flag, "trgpartition" or something like that, indicating
that this is a trigger in a partition. pg_dump does not dump such
triggers, but psql shows them.

b) an OID, pointing to the parent trigger. This could theoretically
help a future implementation of BEFORE ROW triggers, as discussed
before (if trigger trgparent has already run for this row, don't run the
current trigger). However, it's easy to revamp pg_trigger definition in
pg12 to add this column and remove the boolean one, so unless there is
some other immediate use for trgparent then there's no point.

Another angle is that we're already in beta3 and there may be concerns
about altering catalog definition this late in the cycle. Anybody?
Maybe psql can just match tgisinternal triggers by name, and if one
match occurs then we assume it's a clone that should be listed as a
partition trigger.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Adrien Nayrat 2018-06-27 21:13:34 Re: New GUC to sample log queries
Previous Message Andres Freund 2018-06-27 20:37:33 Re: ALTER TABLE on system catalogs