pg_dump with disable trigger

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.
It also altered another kind of trigger like Triggers firing on replica
only, get altered to ordinary triggers.

Thanks

--
Regards,

Soni Maula Harriz

On Tue, Apr 10, 2018 at 4:17 PM, Soni M <diptatapa(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, Apr 10, 2018 at 4:17 PM, Soni M <diptatapa(at)gmail(dot)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