Re: SQL:2011 application time

Just sharing my progress here since it's been a week:

On 11/6/24 17:03, Paul Jungwirth wrote:
> On 11/4/24 13:16, Sam Gabrielsson wrote:
>> Foreign key violation errors are incorrectly raised in a few cases for a temporal foreign key with
>> default ON UPDATE NO ACTION. Test is based on the commited v39 patches (used a snapshot version of
>> PG18 devel available from PGDG).
>
> Thank you for the report! I confirmed that this is a problem. In ri_restrict we fail if any fk
> records still match the being-changed pk, but for temporal if you're merely shrinking the pk range,
> fk references could still wind up being valid (if you're only shrinking it a little). So we need to
> do more work.

I'm nearly done with a patch for this. I'll try to wrap it up today and get it sent this evening.

> IIRC for RESTRICT it is *correct* to reject the change, so we would want to keep the old SQL there,
> and only update it for NOACTION.

I realized there are problems with the RESTRICT case also. I've got a fix written for that too, but
it needs some tidying up. I'll submit both patches together.

The RESTRICT case needs to find the *lost* time span(s) (because it might not be the whole thing)
and check for references to those. To do that, it calls our without_portion support function. That
function was intended to support FOR PORTION OF, but it happens to be exactly what we need here. So
I'm reordering the patches a bit and adjusting the documentation there.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com