pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore with --disable-triggers discards ENABLE ALWAYS
6+ messages / 3 participants
[nested] [flat]
* pg_restore with --disable-triggers discards ENABLE ALWAYS
@ 2024-09-12 08:27 Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-12 20:02 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Duncan Sands @ 2024-09-12 08:27 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
2024-09-12 08:27 pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
@ 2024-09-12 20:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-09-12 20:47 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Tom Lane <tgl@sss.pgh.pa.us>
2024-09-13 08:58 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2024-09-12 20:02 UTC (permalink / raw)
To: Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
2024-09-12 08:27 pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-12 20:02 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-09-12 20:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-13 09:04 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-13 09:08 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 2024-09-12 20:47 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
2024-09-12 08:27 pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-12 20:02 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Laurenz Albe <laurenz.albe@cybertec.at>
2024-09-12 20:47 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-09-13 09:04 ` Duncan Sands <duncan.sands@deepbluecap.com>
1 sibling, 0 replies; 6+ messages in thread
From: Duncan Sands @ 2024-09-13 09:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
2024-09-12 08:27 pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-12 20:02 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Laurenz Albe <laurenz.albe@cybertec.at>
2024-09-12 20:47 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-09-13 09:08 ` Duncan Sands <duncan.sands@deepbluecap.com>
1 sibling, 0 replies; 6+ messages in thread
From: Duncan Sands @ 2024-09-13 09:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_restore with --disable-triggers discards ENABLE ALWAYS
2024-09-12 08:27 pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-12 20:02 ` Re: pg_restore with --disable-triggers discards ENABLE ALWAYS Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-09-13 08:58 ` Duncan Sands <duncan.sands@deepbluecap.com>
1 sibling, 0 replies; 6+ messages in thread
From: Duncan Sands @ 2024-09-13 08:58 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-09-13 09:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-12 08:27 pg_restore with --disable-triggers discards ENABLE ALWAYS Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-12 20:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-09-12 20:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-13 09:04 ` Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-13 09:08 ` Duncan Sands <duncan.sands@deepbluecap.com>
2024-09-13 08:58 ` Duncan Sands <duncan.sands@deepbluecap.com>
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