Re: Referential Integrity Checks with Statement-level Triggers

On Mon, Dec 17, 2018 at 11:27 AM Alvaro Herrera
<alvherre(at)2ndquadrant(dot)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/