Thread: pg_restore with --disable-triggers discards ENABLE ALWAYS
This is with postgresql version 16.4. You can reproduce as follows:
CREATE TABLE test_table(x int);
CREATE FUNCTION test_function() RETURNS trigger AS $$ BEGIN RETURN NULL; END; $$
LANGUAGE plpgsql;
CREATE TRIGGER test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE
FUNCTION test_function();
ALTER TABLE test_table ENABLE ALWAYS TRIGGER test_trigger;
Checking the table:
duncan=> \d test_table
Table "public.test_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
Triggers firing always:
test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION
test_function()
^ Observe "Triggers firing always".
Now for the dump + restore:
pg_dump -f dump.custom -Fc --table test_table
pg_restore --data-only --disable-triggers --dbname duncan dump.custom
Checking the table:
duncan=> \d test_table
Table "public.test_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
Triggers:
test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION
test_function()
^ Observe that "Triggers firing always" has disappeared.
Best wishes, Duncan.
On Thu, 2024-09-12 at 10:27 +0200, Duncan Sands wrote: > CREATE TABLE test_table(x int); > CREATE FUNCTION test_function() RETURNS trigger AS $$ BEGIN RETURN NULL; END; $$ > LANGUAGE plpgsql; > CREATE TRIGGER test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE > FUNCTION test_function(); > ALTER TABLE test_table ENABLE ALWAYS TRIGGER test_trigger; > > Checking the table: > > duncan=> \d test_table > Table "public.test_table" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > x | integer | | | > Triggers firing always: > test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION > test_function() > > ^ Observe "Triggers firing always". > > > Now for the dump + restore: > > pg_dump -f dump.custom -Fc --table test_table > pg_restore --data-only --disable-triggers --dbname duncan dump.custom > > Checking the table: > > duncan=> \d test_table > Table "public.test_table" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+--------- > x | integer | | | > Triggers: > test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION > test_function() > > ^ 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. Yours, Laurenz Albe
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
Hi Laurenz, On 12/09/2024 22:02, Laurenz Albe wrote: > On Thu, 2024-09-12 at 10:27 +0200, Duncan Sands wrote: >> CREATE TABLE test_table(x int); >> CREATE FUNCTION test_function() RETURNS trigger AS $$ BEGIN RETURN NULL; END; $$ >> LANGUAGE plpgsql; >> CREATE TRIGGER test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE >> FUNCTION test_function(); >> ALTER TABLE test_table ENABLE ALWAYS TRIGGER test_trigger; >> >> Checking the table: >> >> duncan=> \d test_table >> Table "public.test_table" >> Column | Type | Collation | Nullable | Default >> --------+---------+-----------+----------+--------- >> x | integer | | | >> Triggers firing always: >> test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION >> test_function() >> >> ^ Observe "Triggers firing always". >> >> >> Now for the dump + restore: >> >> pg_dump -f dump.custom -Fc --table test_table >> pg_restore --data-only --disable-triggers --dbname duncan dump.custom >> >> Checking the table: >> >> duncan=> \d test_table >> Table "public.test_table" >> Column | Type | Collation | Nullable | Default >> --------+---------+-----------+----------+--------- >> x | integer | | | >> Triggers: >> test_trigger AFTER UPDATE ON test_table FOR EACH ROW EXECUTE FUNCTION >> test_function() >> >> ^ 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. No, the trigger was ENABLE ALWAYS when dumped, and restoring zapped that. To confirm that it was ENABLE ALWAYS when dumped: $ pg_restore -f - dump.custom | grep 'ENABLE ALWAYS' ALTER TABLE public.test_table ENABLE ALWAYS TRIGGER test_trigger; To summarize: the table existed before the restore, and the trigger was ENABLE ALWAYS at the moment it was dumped as shown in the steps to reproduce above: I create the table, make an ENABLE ALWAYS trigger, then dump the table, then restore the dump with --data-only and --disable-triggers. At which point (in spite of --data-only) the table has been modified: the trigger is no longer an ENABLE ALWAYS trigger. Best wishes, Duncan.
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.
Hi Tom, I accidentally sent my previous reply without completing it. Here's the rest: Your suggestion as to the root cause certainly sounds plausible. If no-one has the energy to fix it, maybe it's best to at least add a note about this in the pg_restore --disable-triggers documentation. Best wishes, Duncan.