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. 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; |
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 [hidden email] |
In reply to this post by Laura Smith
On Sat, Jun 5, 2021 at 12:48 AM Laura Smith
<[hidden email]> 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. |
In reply to this post by Adrian Klaver-4
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, 4 June 2021 18:07, Adrian Klaver <[hidden email]> 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. |
In reply to this post by Julien Rouhaud
Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, 4 June 2021 18:11, Julien Rouhaud <[hidden email]> wrote: > On Sat, Jun 5, 2021 at 12:48 AM Laura Smith > [hidden email] 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 ? |
In reply to this post by Laura Smith
> On Jun 4, 2021, at 9:47 AM, Laura Smith <[hidden email]> 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 |
In reply to this post by Laura Smith
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 <[hidden email]> wrote: > >> On Sat, Jun 5, 2021 at 12:48 AM Laura Smith >> [hidden email] 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 [hidden email] |
In reply to this post by Laura Smith
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 <[hidden email]> wrote: > >> On Sat, Jun 5, 2021 at 12:48 AM Laura Smith >> [hidden email] 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 [hidden email] |
In reply to this post by Laura Smith
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 |
In reply to this post by Mark Dilger-5
Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, 4 June 2021 18:45, Mark Dilger <[hidden email]> wrote: > > On Jun 4, 2021, at 9:47 AM, Laura Smith [hidden email] 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. |
In reply to this post by Mark Dilger-5
Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Friday, 4 June 2021 18:45, Mark Dilger <[hidden email]> wrote: > > On Jun 4, 2021, at 9:47 AM, Laura Smith [hidden email] 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 <[hidden email]> 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 |
In reply to this post by Adrian Klaver-4
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 <[hidden email]> wrote: >> >>> On Sat, Jun 5, 2021 at 12:48 AM Laura Smith >>> [hidden email] 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 [hidden email] |
Free forum by Nabble | Edit this page |