From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Duncan Sands <duncan.sands@deepbluecap.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
In-reply-to: <78a13f951a03bf325027f8dcfb32b1b5089cdb14.camel@cybertec.at>
References: <d374b6fc-4217-48a9-8639-2c9b4d3a92d0@deepbluecap.com>
 <78a13f951a03bf325027f8dcfb32b1b5089cdb14.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Thu, 12 Sep 2024 22:02:01 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4156697.1726174027.1@sss.pgh.pa.us>
Date: Thu, 12 Sep 2024 16:47:07 -0400
Message-ID: <4156698.1726174027@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4156698.1726174027%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Thu, 2024-09-12 at 10:27 +0200, Duncan Sands wrote:
>> pg_restore --data-only --disable-triggers --dbname duncan dump.custom
>> ^ Observe that "Triggers firing always" has disappeared.

> This looks like a user error to me.
> If you restore with --data-only, the table and constraint definitions
> are not restored at all.  So the table "test_table" in database "duncan"
> must already have existed before the restore, and the trigger was already
> defined like that.

The given recipe seems incomplete, 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.

			regards, tom lane