Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables

On 2020-Apr-20, Justin Pryzby wrote:

> On Mon, Apr 20, 2020 at 06:35:44PM +0900, Amit Langote wrote:

> > Also, how about, for consistency, making the parent table labeling of
> > the trigger look similar to that for the foreign constraint, so
> > Triggers:
> > TABLE "f1" TRIGGER "trig" BEFORE INSERT ON f11 FOR EACH ROW EXECUTE FUNCTION trigfunc()
>
> I'll leave that for committer to decide.

Pushed. Many thanks for this!

Changes: I thought that printing the "ON TABLE" bit when it's defined in
the same table is pointless and ugly, so I added a NULLIF to prevent it
in that case (it's not every day that you can put NULLIF to work). I
also changed the empty string to NULL for the case with older servers,
so that it doesn't print a lame "ON TABLE " clause for them. Lastly,
added pg_catalog qualifications everywhere needed.

Contrary to what I had said, I decided to leave the output as submitted;
the constraint lines are not really precedent against it:

55432 13devel 24286=# \d lev3
Partitioned table "public.lev3"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ not null │
Partition of: lev2 FOR VALUES IN (3)
Partition key: LIST (a)
Indexes:
"lev3_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
TABLE "lev1" CONSTRAINT "lev1_a_fkey" FOREIGN KEY (a) REFERENCES lev1(a)
Referenced by:
TABLE "lev1" CONSTRAINT "lev1_a_fkey" FOREIGN KEY (a) REFERENCES lev1(a)
Triggers:
tt AFTER UPDATE ON lev3 FOR EACH ROW EXECUTE FUNCTION trigger_nothing(), ON TABLE lev2
Number of partitions: 1 (Use \d+ to list them.)

In the "FK constraints" and "referenced by" entries, it looks natural
since the constraint refers to a table. Not so in the trigger case.

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