Message-ID: <bde300cd-c8d5-4d80-843e-0c852ca5d8ac@illuminatedcomputing.com>
Date: Mon, 26 Aug 2024 12:22:50 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: On exclusion constraints and validity dates
To: pgsql-general@lists.postgresql.org,
 Justin Giacobbi <jgiacobbi@optionmetrics.com>
References: <BYAPR01MB4374D78F63C66F0ED4178696A18F2@BYAPR01MB4374.prod.exchangelabs.com>
Content-Language: en-US
From: Paul Jungwirth <pj@illuminatedcomputing.com>
In-Reply-To: <BYAPR01MB4374D78F63C66F0ED4178696A18F2@BYAPR01MB4374.prod.exchangelabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/bde300cd-c8d5-4d80-843e-0c852ca5d8ac%40illuminatedcomputing.com>
Precedence: bulk

On 8/22/24 11:13, Justin Giacobbi wrote:

> I have an issue that on the surface seems orthogonal to existing functionality. I’m trying to 
> dynamically update validity ranges as new <thing>s replace old <thing>s.
> 
> In a nutshell the problem looks like this:
> 
> psqlprompt=# select * from rangetest;
> id |                      rangecol
> ----+-----------------------------------------------------
>    0 | empty
>    0 | ["2024-05-05 00:00:00+00","2024-05-06 00:00:00+00")
>    0 | ["2024-05-06 00:00:00+00","9999-03-31 00:00:00+00")
>    1 | ["2024-05-06 00:00:00+00",)
> 
> psqlprompt=# insert into rangetest values (1, '["2024-06-07 00:00:00+0",)') on conflict on 
> constraint rangetest_id_rangecol_excl do update rangecol = concat('[', lower(rangetest.rangecol),',', lower(excluded.rangecol),')')::tstzrange;
> 
> ERROR:  ON CONFLICT DO UPDATE not supported with exclusion constraints
> 
> So I’m not sure if I’m after a feature request, a workaround or contribution advice. Maybe someone 
> can point me in the right direction.
> 
>  1. A ‘currently valid’ item that becomes invalid and is replaced by a new ‘currently valid’ item
>     seems like such a real-world use case that there should be explicit support for it.
>      1. Unfortunately, the temporal tables extensions seem too immature for my needs currently.
>  2. Barring that an exclusion constraint arbiter would be a lovely solution.
>  3. Barring either of those at least a ‘select all conflicts’ type feature that at least makes it
>     easy to pair the offending rows.
> 
> Currently I’m looking at working around this in the application or in a stored procedure/insert 
> trigger that is essentially the same logic. Whichever seems easier to maintain.
> 
> Advice on how to submit a feature request, or maybe a better workaround that I haven’t discovered 
> would be most welcome. What would be even more welcome is someone with insight into these pieces of 
> the program that can tell me if I’d be biting off more than I can chew (or violating a principle) 
> trying to submit one of the three options above as a feature.

Your example looks a bit like UPDATE FOR PORTION OF (from SQL:2011). That would give you this result:

     1 | ["2024-05-06 00:00:00+00","2024-06-07 00:00:00+00")
     1 | ["2024-06-07 00:00:00+00",)

We update the range you targeted, and we preserve untargeted range(s) before/after that.

I have submitted a patch for that,[0] but if you need it today you might be able to use the periods 
extension (although that is built on start/end columns, not ranges).[1]

An example that better fits ON CONFLICT DO UPDATE would be inserting `(1, '["2024-01-01 
00:00:00+0",)')`. That range is *wider* than what you already have in your table. Probably you want 
get this:

     1 | ["2024-01-01 00:00:00+00","2024-05-06 00:00:00+00")
     1 | ["2024-05-06 00:00:00+00",)

In other words: fill in the empty gaps and update what is already there.

This is similar to what Tom Johnston calls "temporal merge" in *Bitemporal Data: Theory and 
Practice* (building on his ideas for "whenever insert" and "whenever update"), pages 179-184.

I'm not sure we have enough information to do the right thing for an arbitrary exclusion constraint, 
but once we have primary keys and unique constraints with WITHOUT OVERLAPS (also in my submitted 
patches), the semantics should be specific enough that Postgres could implement it. It's on my list 
of things to do once we finish supporting SQL:2011. Maybe it would use the ON CONFLICT DO UPDATE 
syntax, or perhaps MERGE---I'm not sure yet.

[0] https://commitfest.postgresql.org/49/4308/
[1] https://github.com/xocolatl/periods

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com