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

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/exclusionconstraints 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.
ButI'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)

-- 
Adrian Klaver
adrian.klaver@aklaver.com