Re: Struggling with EXCLUDE USING gist

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.


> 
> I'm on PostgresSQL 12.5 if it makes any difference.
> 
> 
> It is my understanding that:
> (a) Postgres functions are one big transaction and so what I'm trying to do in my function code should work (i.e.
updatetstzrange before updating something that would normally conflict).
 
> (b) That infinity takes precedence over a defined point in time. The error I'm receiving - shown above - seems to
counterthat perception though ?
 
> 
> 
> Simplified example:
> 
> CREATE TABLE test (
> t_val text not null,
> t_version text unique not null default gen_random_uuid() ,
> t_range tstzrange not null default tstzrange('-infinity','infinity'),
> EXCLUDE USING gist (t_val WITH=, t_range WITH &&) DEFERRABLE INITIALLY DEFERRED
> );
> 
> CREATE VIEW test_v AS select * from test where t_range @> now();
> 
> INSERT INTO test(t_val) values('abc');
> 
> 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;
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com