Re: Referential Integrity Checks with Statement-level Triggers

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?

Maybe there is a solution for this.  I think it's worth thinking about,
even if it's just to say that we won't do it.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services