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"))."
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;
