Date: Tue, 20 Oct 2020 14:44:12 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers@postgresql.org, Amit Langote <amitlangote09@gmail.com>
Subject: Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for
 inherited row triggers
Message-ID: <20201020194412.GF9241@telsasoft.com>
References: <20200930223450.GA14848@telsasoft.com>
 <20201020190420.GA18579@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20201020190420.GA18579@alvherre.pgsql>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

On Tue, Oct 20, 2020 at 04:04:20PM -0300, Alvaro Herrera wrote:
> On 2020-Sep-30, Justin Pryzby wrote:
> 
> > CREATE TABLE t(i int) PARTITION BY RANGE(i);
> > CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (10);
> > CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ begin raise exception 'except'; end $$;
> > CREATE TRIGGER tg AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION tgf();
> > ALTER TABLE t1 DISABLE TRIGGER tg;
> > INSERT INTO t VALUES(1); -- inserts when trigger is disabled: good
> > ALTER TABLE t DISABLE TRIGGER tg;
> > CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);
> > 
> > postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass::text IN ('t1','t2');
> >  tgrelid | tgenabled 
> > ---------+-----------
> >  t1      | D
> >  t2      | O
> > (2 rows)
> > 
> > I consider this a bug,but CreateTrigStmt doesn't have any "enabled" member
> > (since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where
> > the fix should be.
> 
> 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.

+0.8 to backpatch.  To v13 if not further.

We don't normally disable triggers, otherwise I would say +1.

For context, I ran into this issue while migrating a customer to a new server
using pg_restore and a custom backup script which loops around pg_dump, and
handles partitioned tables differently depending if they're recent or historic.

Our backup job works well, but is technically a bit of a hack.  It doesn't do
the right thing (causes sql errors and pg_restore warnings) for inherited
indexes and, apparently, triggers.  Disabling the trigger was my 4th attempt to
handle an error restoring a specific table (mismatched column type between
parent dump and child dumped several days earlier).  I eventually (5th
or 6th attempt) dropped the parent trigger, created the child tables using
--section=pre-data, ALTERed a column to match, and then ran post-data and
attached it.

-- 
Justin