On 6/4/21 11:21 AM, Adrian Klaver wrote:
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')
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 ?
What I got to work:
create table ts_range(
id integer,
tsrange_fld tstzrange default tstzrange('-infinity', 'infinity'),
EXCLUDE USING gist (id WITH=, tsrange_fld WITH &&) );
test_(aklaver)(5432)=> insert into ts_range values (1);
INSERT 0 1
test_(aklaver)(5432)=> select * from ts_range ;
id | tsrange_fld
----+----------------------
1 | [-infinity,infinity)
update ts_range set tsrange_fld = tstzrange('-infinity', 'now') where id
= 1;
UPDATE 1
test_(aklaver)(5432)=> select * from ts_range ;
id | tsrange_fld
----+---------------------------------------------
1 | [-infinity,"2021-06-04 11:19:39.861045-07")
(1 row)
insert into ts_range values (1, tstzrange('now', 'infinity'));
INSERT 0 1
test_(aklaver)(5432)=> select * from ts_range ;
id | tsrange_fld
----+---------------------------------------------
1 | [-infinity,"2021-06-04 11:19:39.861045-07")
1 | ["2021-06-04 11:19:53.672274-07",infinity)
(2 rows)
Did not think this all the way through. If you are doing these
statements within a transaction you would need use something like:
tstzrange('-infinity', clock_timestamp())
as 'now'/now() captures the timestamp at the start of the transaction
and does not change with subsequent calls in the transaction.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
