Horton

Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org, Amit Langote <amitlangote09@gmail.com>
Date: 2020-10-21T17:02:37Z
Lists: pgsql-hackers 
On 2020-Oct-21, Justin Pryzby wrote:

> I came up with this, which probably needs more than a little finesse.

Hmm, there are two important changes needed on this: 1) it must not emit
CREATE lines for the child triggers; only the ALTER TABLE ONLY
<partition> lines to set disable state on the partition are needed.  2)
tgparentid does not exist prior to pg13, so you need some additional
trick to cover that case.

Also, I think the multipartition case is broken: if grandparent has
trigger enabled, parent has trigger disabled and child trigger set to
always, is that dumped correctly?  I think the right way to do this is
change only the partitions that differ from the topmost partitioned
table -- not their immediate parents; and use ONLY to ensure they don't
affect downstream children.

Change 1 also means that the test with the "this shouldn't ever get
emitted" comment remains unchanged.

I'm not sure how to tackle change 2.  You need to search pg_depend for
entries with classid=pg_trigger and refclass=pg_trigger ... (commit
1fa846f1c9af might give some clue)

Commits

  1. Fix pg_dump for disabled triggers on partitioned tables

  2. Preserve firing-on state when cloning row triggers to partitions

  3. Fix ALTER TABLE .. ENABLE/DISABLE TRIGGER recursion

  4. psql \d: Display table where trigger is defined, if inherited

  5. Fix cloning of row triggers to sub-partitions

  6. Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise)