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. update tstzrange 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 counter that 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/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;
>
>
>

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