Re: Struggling with EXCLUDE USING gist

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 18:07, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> On 6/4/21 9:47 AM, Laura Smith wrote:
>
> > All the examples I've seen around the internet make this sound so easy.
> > But I seem to be missing some important step because all I'm getting are messages such as "DETAIL: Key (t_val, t_version)=(def, [-infinity,infinity)) conflicts with existing key (t_val, t_version)=(def, [-infinity,"2021-06-04 16:56:08.008122+01"))."
>
> That would be correct:
>
> select '[-infinity,infinity)'::tstzrange && '[-infinity,"2021-06-04
> 16:56:08.008122+01")'::tstzrange;
> ?column?
>
> -----------------------------------------------------------------------------------------------------------------------------------------
>
> t
>
> The ranges overlap so they fail the exclusion constraint.
>

So it seems we are agreed (me via error message, you via example) that a transaction (function script) that updates the "old" row to fixed timestamp before inserting a "new" row will not have the desired result.

What is the solution then ? I need to keep historical versions but at the same time I need a "current" version. If I am not able to use "infinity" as bounds for "current" version then clearly I'm wasting my time trying to use EXCLUDE AS for version tracking because clearly using fixed timestamps instead of "infinity" for tstzrange would be a hacky fix that will be fragile and prone to breakage.