On 03/20/2018 07:56 AM, Durumdara wrote:
Dear Adrian!
2018-03-20 15:47 GMT+01:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>:
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@xxxxxxxxxxx
