Re: Struggling with EXCLUDE USING gist - Mailing list pgsql-general

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@gmail.com> wrote:
>>
>>> On Sat, Jun 5, 2021 at 12:48 AM Laura Smith
>>> n5d9xq3ti233xiyif2vp@protonmail.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.klaver@aklaver.com