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.klaver@aklaver.com

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

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 18:07, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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.

So it seems we are agreed (me via error message, you via example) that a transaction (function script) that updates the "old" row to fixed timestamp before inserting a "new" row will not have the desired result.

What is the solution then ? I need to keep historical versions but at the same time I need a "current" version. If I am not able to use "infinity" as bounds for "current" version then clearly I'm wasting my time trying to use EXCLUDE AS for version tracking because clearly using fixed timestamps instead of "infinity" for tstzrange would be a hacky fix that will be fragile and prone to breakage.

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 18:11, Julien Rouhaud <rjuju123@gmail.com> wrote:

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

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 ?

On Jun 4, 2021, at 9:47 AM, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:

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

<snip>

INSERT INTO test(t_val) values(p_val);

This will insert a t_range of ('-infinity','infinity'), won't it? Wouldn't you want to instead insert with t_range starting around now() rather than starting at -infinity?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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@gmail.com> wrote:

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

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@aklaver.com

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@gmail.com> wrote:

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

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)

--
Adrian Klaver
adrian.klaver@aklaver.com

On 6/4/21 1:32 PM, Laura Smith wrote:

What is the solution then ? I need to keep historical versions but
at the same time I need a "current" version. If I am not able to use
"infinity" as bounds for "current" version then clearly I'm wasting
my time trying to use EXCLUDE AS for version tracking because clearly
using fixed timestamps instead of "infinity" for tstzrange would be a
hacky fix that will be fragile and prone to breakage.

This is not exactly the same thing you are trying to do (I think), but
maybe you can get some useful ideas from this:

https://www.joeconway.com/presentations/RLS_TimeTravel-FOSDEM2019.pdf

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 18:45, Mark Dilger <mark.dilger@enterprisedb.com> wrote:

On Jun 4, 2021, at 9:47 AM, Laura Smith n5d9xq3ti233xiyif2vp@protonmail.ch wrote:
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
);

<snip>

INSERT INTO test(t_val) values(p_val);

This will insert a t_range of ('-infinity','infinity'), won't it? Wouldn't you want to instead insert with t_range starting around now() rather than starting at -infinity?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Interesting. I will go test. It hadn't occurred to me the start time might be what was causing all the errors.

Thanks for the suggestion Mark. I will report back.

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 4 June 2021 18:45, Mark Dilger <mark.dilger@enterprisedb.com> wrote:

On Jun 4, 2021, at 9:47 AM, Laura Smith n5d9xq3ti233xiyif2vp@protonmail.ch wrote:
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
);

<snip>

INSERT INTO test(t_val) values(p_val);

This will insert a t_range of ('-infinity','infinity'), won't it? Wouldn't you want to instead insert with t_range starting around now() rather than starting at -infinity?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

That seems to have done the trick. Thanks again Mark,

On Jun 4, 2021, at 11:55 AM, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:

That seems to have done the trick. Thanks again Mark

Glad to hear it. Good luck.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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@gmail.com> wrote:

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

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@aklaver.com