Horton

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

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers@postgresql.org, Amit Langote <amitlangote09@gmail.com>
Date: 2020-10-20T22:01:16Z
Lists: pgsql-hackers 
On Tue, Oct 20, 2020 at 03:56:30PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Hmm, next question: should we backpatch a fix for this?  (This applies
> > all the way back to 11.)  If we do, then we would change behavior of
> > partition creation.  It's hard to see that the current behavior is
> > desirable ... and I think anybody who would have come across this, would
> > wish it behaved the other way.  But still -- it would definitely be a
> > behavior change.
> 
> The behavior change seems like it'd be an improvement in a vacuum,
> but I wonder how it would interact with catalog contents left behind
> by the old misbehavior.  Also, would we expect pg_dump to try to do
> anything to clean up the mess?  If so, allowing a back branch to have
> had more than one behavior would complicate that greatly.

I don't think there's a problem with catalog content ?
I think it's fine if there's an enabled child trigger inheriting from a
disabled parent?  This changes the initial tgenabled for new partitions.

The old catalog state is still possible - it's what you'd get if you did
CREATE TABLE child PARTITION OF..; ALTER TABLE child DISABLE TRIGGER.

I don't think pg_dump needs to do anyting special, since the restore will now
do what's wanted without added commands.  Note that pg_dump switched from
"PARTITION OF" to "ATTACH PARTITION" at commit 33a53130a.  This would handles
both on the server side.

However...it looks like pg_dump should ALTER the child trigger state if it
differ from its parent.  Or maybe it needs to CREATE child triggers with the
proper state before attaching the child table ?

-- 
Justin

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)