Re: Struggling with EXCLUDE USING gist

On 6/4/21 11:21 AM, Adrian Klaver wrote:
> On 6/4/21 10:37 AM, Laura Smith wrote:
>>
>>
>>
>> Sent with ProtonMail Secure Email.
>>
>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>> On Friday, 4 June 2021 18:11, Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>>
>>> On Sat, Jun 5, 2021 at 12:48 AM Laura Smith
>>> n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch 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"))."
>>>> [...]
>>>> CREATE FUNCTION create_or_update_test(p_val text) RETURNS bool AS $$
>>>> DECLARE
>>>> v_version text;
>>>> v_range tstzrange;
>>>> BEGIN
>>>> -- N.B. Have coded it this way round (not insert first) because "ON
>>>> CONFLICT does not support deferrable unique constraints/exclusion
>>>> constraints as arbiters"
>>>> SELECT t_version,t_range into v_version,v_range from test_v where
>>>> t_val='abc';
>>>> IF NOT FOUND THEN
>>>> INSERT INTO test(t_val) values(p_val)
>>>> END IF;
>>>> -- If range conflict, adjust old and set new
>>>> UPDATE test set t_range=tstzrange(lower(v_range),now(),'[)') where
>>>> t_version=v_version;
>>>> INSERT INTO test(t_val) values(p_val);
>>>> RETURN FOUND;
>>>> END;
>>>> $$ language plpgsql;
>>>
>>> You need to provide more information. I suspect that what's happening
>>> is a concurrency issue where the create_or_update_test() is called
>>> multiple time and both initially see and empty table so try to insert
>>> an -infinity/infinity range before updating it, so the 2nd call will
>>> fail once the 1st one commits.
>>
>>
>> Happy to provide more information although not quite sure how much
>> more I can provide ?  Perhaps my use case ?
>>
>> My use-case is version tracking for items.
>>
>> My implementation concept :
>> Default insert is tstzrange('-infinity','infinity')
>> When a "new" version of the item comes along:
>> (a) the "old" item becomes archived (i.e. valid until 'infinity' =>
>> valid until 'now()' )
>> (b) the "new" item becomes current (i.e. valid until 'infinity')
>>
>> If tstzrange and EXCLUDE USING is the wrong way to do this sort of
>> thing, then I'm all ears to other suggestions.  But I've seen so many
>> examples out on the web that suggest this is exactly the sort of thing
>> that tstzrange and EXCLUDE using *is* very good for ?
>>
>>
>
> What I got to work:
>
> create table ts_range(
> id integer,
> tsrange_fld tstzrange default tstzrange('-infinity', 'infinity'),
> EXCLUDE USING gist (id WITH=, tsrange_fld WITH &&) );
>
> test_(aklaver)(5432)=> insert into ts_range values (1);
>
> INSERT 0 1
> test_(aklaver)(5432)=> select * from ts_range ;
>  id |     tsrange_fld
> ----+----------------------
>   1 | [-infinity,infinity)
>
> update ts_range set tsrange_fld = tstzrange('-infinity', 'now') where id
> = 1;
> UPDATE 1
> test_(aklaver)(5432)=> select * from ts_range ;
>  id |                 tsrange_fld
> ----+---------------------------------------------
>   1 | [-infinity,"2021-06-04 11:19:39.861045-07")
> (1 row)
>
> insert into ts_range values (1, tstzrange('now', 'infinity'));
> INSERT 0 1
> test_(aklaver)(5432)=> select * from ts_range ;
>  id |                 tsrange_fld
> ----+---------------------------------------------
>   1 | [-infinity,"2021-06-04 11:19:39.861045-07")
>   1 | ["2021-06-04 11:19:53.672274-07",infinity)
> (2 rows)
>

Did not think this all the way through. If you are doing these
statements within a transaction you would need use something like:

tstzrange('-infinity', clock_timestamp())

as 'now'/now() captures the timestamp at the start of the transaction
and does not change with subsequent calls in the transaction.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com