Message-ID: <efe94b68-24f6-49c6-8283-8deb2f8b05d8@deepbluecap.com>
Date: Fri, 13 Sep 2024 11:04:07 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
To: Tom Lane <tgl@sss.pgh.pa.us>, Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
References: <d374b6fc-4217-48a9-8639-2c9b4d3a92d0@deepbluecap.com>
 <78a13f951a03bf325027f8dcfb32b1b5089cdb14.camel@cybertec.at>
 <4156698.1726174027@sss.pgh.pa.us>
Content-Language: en-US
From: Duncan Sands <duncan.sands@deepbluecap.com>
In-Reply-To: <4156698.1726174027@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/efe94b68-24f6-49c6-8283-8deb2f8b05d8%40deepbluecap.com>
Precedence: bulk

Hi Tom, thanks for taking a look.

 > The given recipe seems incomplete,

I'm not sure what is missing in the recipe.  I just dropped and recreated the 
database, and performed exactly the steps listed in my original email, and get 
exactly the same result.

 > but I think Duncan has put his
> finger on a shortcoming.  pg_restore with --disable-triggers will
> issue
> 
> ALTER TABLE foo DISABLE TRIGGER ALL;
> 
> and later
> 
> ALTER TABLE foo ENABLE TRIGGER ALL;
> 
> and if you read the fine print in the ALTER TABLE man page,
> you'll discover that ENABLE TRIGGER sets the triggers' replication
> mode to the default (origin only).  I'm not sure why somebody
> thought that replication mode shouldn't be stored separately
> from enable/disable, but it isn't: there's just one four-valued
> state field.
> 
> We could probably add code to make pg_dump individually re-enable
> the table's triggers with the appropriate state(s), but I can't
> muster too much enthusiasm for writing that myself.