Re: Referential Integrity Checks with Statement-level Triggers

po 17. 12. 2018 v 18:27 odesílatel Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
napsal:

> 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.
>

It was just a idea. When I think about it, I am not sure, if RI check from
statement trigger is not worse when statement related changes are too big.
On second hand, it is premature optimization maybe. We can check
performance on prototype.

> 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
>