pg_dump with disable trigger

On Tue, Apr 10, 2018 at 4:17 PM, Soni M <diptatapa@gmail.com> wrote:

Hello All,

I think I found bug on pg_dump 9.6.8.
The bug arise when using pg_dump with --disable-trigger option.
The command just :
pg_dump -f /tmp/test_disable_trigger.sql -a -b --disable-triggers -t
some_table some_database
On the resulted script, it simply did

ALTER TABLE public.some_table DISABLE TRIGGER ALL;
COPY command
ALTER TABLE public.some_table ENABLE TRIGGER ALL;

This become a problem, when the destination table already has Disabled
user triggers, it gets enabled by the dump script.

I would classify this as a limitation of the concept as opposed to a bug.​
I was going to say a bit more but I'm not that fluent with disabled
triggers mode in dump/restore setups. I would advise using pg_restore and
leaving the disable triggers option of the pg_dump command. It lacks
sufficient information to write an appropriate script for an unknown target
server.

If you have issues with pg_restore those would be more easily be deemed as
bugs.

FWIW we have a "SET LOCAL" command that results in GUC variables being
restored to their state post-transaction; we'd need a similar command for
pg_dump to work properly.

It also altered another kind of trigger like Triggers firing on replica

only, get altered to ordinary triggers.

​This seems like a bug - though I'm not going to be confirming the behavior
and you haven't provided evidence.
​
David J.

On Wed, Apr 11, 2018 at 6:46 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tue, Apr 10, 2018 at 4:17 PM, Soni M <diptatapa@gmail.com> wrote:

Hello All,

I think I found bug on pg_dump 9.6.8.
The bug arise when using pg_dump with --disable-trigger option.
The command just :
pg_dump -f /tmp/test_disable_trigger.sql -a -b --disable-triggers -t
some_table some_database
On the resulted script, it simply did

ALTER TABLE public.some_table DISABLE TRIGGER ALL;
COPY command
ALTER TABLE public.some_table ENABLE TRIGGER ALL;

This become a problem, when the destination table already has Disabled
user triggers, it gets enabled by the dump script.

I would classify this as a limitation of the concept as opposed to a
bug.​ I was going to say a bit more but I'm not that fluent with disabled
triggers mode in dump/restore setups. I would advise using pg_restore and
leaving the disable triggers option of the pg_dump command. It lacks
sufficient information to write an appropriate script for an unknown target
server.

If you have issues with pg_restore those would be more easily be deemed as
bugs.

FWIW we have a "SET LOCAL" command that results in GUC variables being
restored to their state post-transaction; we'd need a similar command for
pg_dump to work properly.

It also altered another kind of trigger like Triggers firing on replica

only, get altered to ordinary triggers.

​This seems like a bug - though I'm not going to be confirming the
behavior and you haven't provided evidence.
​

This happen when copying slony log table.

Before restore :

Table
"_consprod_replication.sl_log_1"
Indexes:
"PartInd_consprod_replication_sl_log_1-node-2" btree (log_txid) WHERE
log_origin = 2
"sl_log_1_idx1" btree (log_origin, log_txid, log_actionseq)
Triggers firing on replica only:
apply_trigger BEFORE INSERT ON _consprod_replication.sl_log_1 FOR EACH
ROW EXECUTE PROCEDURE
_consprod_replication.logapply('_consprod_replication')

And after restore :
Table
"_consprod_replication.sl_log_1"
Indexes:
"PartInd_consprod_replication_sl_log_1-node-2" btree (log_txid) WHERE
log_origin = 2
"sl_log_1_idx1" btree (log_origin, log_txid, log_actionseq)
Triggers:
apply_trigger BEFORE INSERT ON _consprod_replication.sl_log_1 FOR EACH
ROW EXECUTE PROCEDURE
_consprod_replication.logapply('_consprod_replication')

David J.

--
Regards,

Soni Maula Harriz