Thread: Restore - disable triggers - when they fired?
Dear Members!
I saw in PGAdmin 3/4 that pg_restore have an option "disable triggers".
Because we need to move some databases in the near future I have to know about the meaning of this option.
I wrote a table with an BEFORE UPDATE trigger:
create table tr_test
(
id integer not null primary key,
value1 varchar(100),
value2 varchar(100)
);
insert into tr_test values(1, 'a', 'a');
insert into tr_test values(2, 'b', 'b');
CREATE OR REPLACE FUNCTION tfbu_tr_test()
RETURNS trigger AS
$BODY$
begin
new.value2 = cast(current_timestamp as varchar(30));
RETURN NEW;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER tbi_tr_test
BEFORE INSERT
ON tr_test
FOR EACH ROW
EXECUTE PROCEDURE public.tfbu_tr_test();
insert into tr_test values(3, 'c', 'c');
select * from tr_test;
and I tried to dump and restore in PGAdmin IV.
The dumped data is same as I read after restore.
The pg_restore log shows me that triggers and indexes created after data copy.
At this point I confused in "disable triggers" option.
At this point I confused in "disable triggers" option.
When it would be useful?
Firstly I supposed that data copy somehow could start the triggers - but how?
Which triggers? Or how they fired with this order?
Or they remains as disabled AFTER the backup for next, by hand manipulations?
So please light my mind a little!
Thank you!
Regards
dd
On 03/20/2018 07:31 AM, Durumdara wrote: > Dear Members! > > I saw in PGAdmin 3/4 that pg_restore have an option "disable triggers". > > Because we need to move some databases in the near future I have to know > about the meaning of this option. > > I wrote a table with an BEFORE UPDATE trigger: > > create table tr_test > ( > id integer not null primary key, > value1 varchar(100), > value2 varchar(100) > ); > > insert into tr_test values(1, 'a', 'a'); > insert into tr_test values(2, 'b', 'b'); > > CREATE OR REPLACE FUNCTION tfbu_tr_test() > RETURNS trigger AS > $BODY$ > begin > new.value2 = cast(current_timestamp as varchar(30)); > RETURN NEW; > end; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > > > CREATE TRIGGER tbi_tr_test > BEFORE INSERT > ON tr_test > FOR EACH ROW > EXECUTE PROCEDURE public.tfbu_tr_test(); > > insert into tr_test values(3, 'c', 'c'); > select * from tr_test; > > > and I tried to dump and restore in PGAdmin IV. > > The dumped data is same as I read after restore. > > The pg_restore log shows me that triggers and indexes created after data > copy. > > At this point I confused in "disable triggers" option. > > When it would be useful? 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? > > Or they remains as disabled AFTER the backup for next, by hand > manipulations? > > So please light my mind a little! > > Thank you! > > Regards > dd -- Adrian Klaver adrian.klaver@aklaver.com
Dear Adrian!
2018-03-20 15:47 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
https://www.postgresql.org/doc
When it would be useful?s/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?
Thanks!
dd
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
Dear Adrian!
2018-03-20 16:33 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/20/2018 07:56 AM, Durumdara wrote: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()
...
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
Thank you! Now I got it!
So the difference is:
1.) FULL DB restore into empty database creates the extra objects at the end, so I do not need to worry about triggers.
2.) Partial data restore into an existing database to existing tables with triggers: this point I need to set "disable triggers" option.
Very-very thank you!
dd
On 03/21/2018 02:41 AM, Durumdara wrote: > Dear Adrian! > > 2018-03-20 16:33 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > On 03/20/2018 07:56 AM, Durumdara wrote: > > > 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() > ... > 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 > > > Thank you! Now I got it! > So the difference is: > > 1.) FULL DB restore into empty database creates the extra objects at the > end, so I do not need to worry about triggers. This also holds if you do a single(or multiple) table restore of both the table schema and data at the same time. > > 2.) Partial data restore into an existing database to existing tables > with triggers: this point I need to set "disable triggers" option. Well partial or complete data restore, anything that will trip the triggers on an existing table. > Very-very thank you! Glad it helped. > > dd -- Adrian Klaver adrian.klaver@aklaver.com