On Sat, Jun 5, 2021 at 12:48 AM Laura Smith <n5d9xq3ti233xiyif...@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.
