Thread: Struggling with EXCLUDE USING gist
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.
updatetstzrange 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
counterthat 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/exclusionconstraints 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;
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.
updatetstzrange 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
counterthat 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/exclusionconstraints 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/exclusionconstraints 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 amnot able to use "infinity" as bounds for "current" version then clearly I'm wasting my time trying to use EXCLUDE AS forversion tracking because clearly using fixed timestamps instead of "infinity" for tstzrange would be a hacky fix thatwill 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/exclusionconstraints 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'veseen 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
startingaround 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/exclusionconstraints 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.
ButI'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/exclusionconstraints 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.
ButI'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
startingaround 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
startingaround 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