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@xxxxxxxxx> wrote:
On Sat, Jun 5, 2021 at 12:48 AM Laura Smith
n5d9xq3ti233xiyif2vp@xxxxxxxxxxxxx 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.
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')
The problem is your default of tstzrange('-infinity','infinity') for a
new item is always going to contain your updated value of
tstzrange('-infinity','now').
If tstzrange and EXCLUDE USING is the wrong way to do this sort of thing, then I'm all ears to other suggestions. But I'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 ?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
