public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Giacobbi <[email protected]>
To: [email protected] <[email protected]>
Subject: On exclusion constraints and validity dates
Date: Thu, 22 Aug 2024 18:13:19 +0000
Message-ID: <BYAPR01MB4374D78F63C66F0ED4178696A18F2@BYAPR01MB4374.prod.exchangelabs.com> (raw)
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.
* 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
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: On exclusion constraints and validity dates
In-Reply-To: <BYAPR01MB4374D78F63C66F0ED4178696A18F2@BYAPR01MB4374.prod.exchangelabs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox