On Mon, Dec 17, 2018 at 11:27 AM Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > On 2018-Dec-17, Pavel Stehule wrote: > >> ROW trigger call RI check too often, and statement trigger too less. I >> think so ideal design can be call RI check every 10K rows. I think so can >> be unfriendly if somebody does very long import and it fails on the end. I >> don't think so there should not be any performance difference, if RI check >> is called per 1000 or more rows. > > This is a good point, but I'm not sure if it's possible to implement > using statement-level triggers. I think the way transition tables work > is that you get the full results at the end of the command; there's no > way to pass control to the RI stuff at arbitrary points during the > execution of the command. > > Is there any guidance on the SQL standard about this? I don't think the > timing indicators in the standard (IMMEDIATE, DEFERRED) have any say on > this. Or do they?
Yes, they do. *ALL* AFTER triggers fire after the statement completes, it's a question of whether a particular trigger fires once for the whole statement or once for each row. Observe: test=# CREATE TABLE t1 (t1id int PRIMARY KEY, t1desc text); CREATE TABLE test=# CREATE TABLE t2 (t2id int PRIMARY KEY, t1id int NOT NULL, t2desc text, test(# FOREIGN KEY (t1id) REFERENCES t1); CREATE TABLE test=# CREATE FUNCTION t2_insert_func() test-# RETURNS TRIGGER test-# LANGUAGE plpgsql test-# AS $$ test$# BEGIN test$# RAISE NOTICE '%', new; test$# RETURN new; test$# END; test$# $$; CREATE FUNCTION test=# CREATE TRIGGER t2_insert_trig test-# BEFORE INSERT ON t2 test-# FOR EACH ROW test-# EXECUTE FUNCTION t2_insert_func(); CREATE TRIGGER test=# INSERT INTO t1 VALUES (1), (2), (3); INSERT 0 3 test=# INSERT INTO t2 VALUES (10, 1), (20, 2), (30, 3), (40, 4), (50, 5); NOTICE: (10,1,) NOTICE: (20,2,) NOTICE: (30,3,) NOTICE: (40,4,) NOTICE: (50,5,) ERROR: insert or update on table "t2" violates foreign key constraint "t2_t1id_fkey" DETAIL: Key (t1id)=(4) is not present in table "t1". All inserts occur before the statement fails, per standard. Kevin Grittner VMware vCenter Server https://www.vmware.com/
