From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Duncan Sands <duncan.sands@deepbluecap.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
Date: Thu, 12 Sep 2024 16:47:07 -0400
Message-ID: <4156698.1726174027@sss.pgh.pa.us> (raw)
In-Reply-To: <78a13f951a03bf325027f8dcfb32b1b5089cdb14.camel@cybertec.at>
References: <d374b6fc-4217-48a9-8639-2c9b4d3a92d0@deepbluecap.com>
<78a13f951a03bf325027f8dcfb32b1b5089cdb14.camel@cybertec.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, duncan.sands@deepbluecap.com, pgsql-bugs@lists.postgresql.org
Subject: Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
In-Reply-To: <4156698.1726174027@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox