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.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com