On exclusion constraints and validity dates

[Justin Giacobbi <jgiacobbi@xxxxxxxxxxxxxxxxx>]

Hello,

 

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_ran

gecol_excl do update rangecol = concat('[', lower(rangetest.rangecol),',', lower(excluded.ran

gecol),')')::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.

 

Thank you