Re: Restore - disable triggers - when they fired?

On 03/20/2018 07:56 AM, Durumdara wrote:
> Dear Adrian!
>
>
> 2018-03-20 15:47 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>
>
>
> When it would be useful?
>
>
> https://www.postgresql.org/docs/10/static/app-pgrestore.html
> <https://www.postgresql.org/docs/10/static/app-pgrestore.html>
> "--disable-triggers
>
>     This option is relevant only when performing a data-only
> restore. It instructs pg_restore to execute commands to temporarily
> disable triggers on the target tables while the data is reloaded.
> Use this if you have referential integrity checks or other triggers
> on the tables that you do not want to invoke during data reload.
>
>     Presently, the commands emitted for --disable-triggers must be
> done as superuser. So you should also specify a superuser name with
> -S or, preferably, run pg_restore as a PostgreSQL superuser.
>
>
>
>
> Firstly I supposed that data copy somehow could start the
> triggers - but how?
>
> Which triggers? Or how they fired with this order?
>
>
>
> I have read it, but I don't understand it.
>
> Do you have a good example?

create table disable_trigger_test(id int PRIMARY KEY, fld_1 text);

insert into disable_trigger_test values (1, 'dog'), (2, 'cat');

test=> select * from disable_trigger_test ;
id | fld_1
----+-------
1 | dog
2 | cat

pg_dump --disable-triggers -d test -U aklaver -t disable_trigger_test -a
-f disable_trigger_test_data.sql

CREATE OR REPLACE FUNCTION public.trigger_test()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
new.fld_1 := new.fld_1 || 'test';
RETURN NEW;
end;
$function$;

CREATE TRIGGER tr_test
BEFORE INSERT
ON disable_trigger_test
FOR EACH ROW
EXECUTE PROCEDURE public.trigger_test();

truncate disable_trigger_test ;

#Note I do this as a superuser.
psql -d test -U postgres -f disable_trigger_test_data.sql

test=> select * from disable_trigger_test ;
id | fld_1
----+-------
1 | dog
2 | cat

test=> insert into disable_trigger_test values (3, 'fish');
INSERT 0 1
test=> select * from disable_trigger_test ;
id | fld_1
----+----------
1 | dog
2 | cat
3 | fishtest
(3 rows)

>
> Thanks!
> dd

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com