From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables
Date: Tue, 21 Apr 2020 19:03:30 -0400
Message-ID: <20200421230330.GA23831@alvherre.pgsql> (raw)
In-Reply-To: <20200420195740.GE3890@telsasoft.com>
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
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, pryzby@telsasoft.com, amitlangote09@gmail.com, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables
In-Reply-To: <20200421230330.GA23831@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