Table: test_seq

CREATE SEQUENCE cd_seq CYCLE START 1 MAXVALUE 2;
ALTER TABLE test_seq
ALTER COLUMN id SET DEFAULT ('cd_'||nextval('cd_seq'))::VARCHAR(8);

UPDATE test_seq
SET raw_data = 'New Text'
WHERE "id" = 'cd_'||nextval('cd_seq')::VARCHAR(8);

I am making a table that will store raw data as a short term backup, if for some reason the data ingestion fails and we need to go back without extracting it again. I'm trying to setup a way to have the records get replace when we have reached the ID limit.

So if I want 25 records in the table, when the SEQUENCE rolls back from the maximum ('cd_25') to ('cd_1'), I want raw_data to get updated to the new data.

I've come up with the SEQUENCE and the DEFAULT value for the first inserts but my UPDATE command won't update the records even when the "id" matches the 'cd_'||nextval('cd_seq') and it will sometimes UPDATE 9 rows at once.

I checked the values of "id" and 'cd_'||nextval('cd_seq') and they appear to be a match but the WHERE doesn't work properly.

Am I missing something or am I overcomplicating things? Thank you

CodePudding user response：

While I agree with Adrian Klaver's comments that this approach is pretty fragile due to how sequences work, if:

1. You can make sure the column default value is the only call to the sequence
2. You don't mind skipped rows if an insert fails, but sequence still increments its value
3. You can make sure all inserts handle conflicts like below

this can work. Instead of trying to insert data by updating existing rows - which by the way forces you to prepopulate the table - just actually insert it and handle the conflict.

insert into test_seq
(text_column)
values
('e')
on conflict(id) do update set text_column=excluded.text_column;

This also lets you insert more than one row at once (up to the max size of your table, the length of your sequence), compared to your current update approach, as I do in the test below.

drop sequence if exists cd_seq cascade;
create sequence cd_seq cycle start 1 maxvalue 4;

drop table if exists test_seq cascade;
create table test_seq 
(id text primary key default ('cd_'||nextval('cd_seq'))::VARCHAR(8),
text_column text);

insert into test_seq
(text_column)
values
('a'),
('b'),
('c'),
('d')
on conflict(id) do update set text_column=excluded.text_column;
select id, text_column from test_seq;
--  id  | text_column
-------- -------------
-- cd_1 | a
-- cd_2 | b
-- cd_3 | c
-- cd_4 | d
--(4 rows)
insert into test_seq
(text_column)
values
('e'),
('f')
on conflict(id) do update set text_column=excluded.text_column;
select id, text_column from test_seq;
--  id  | text_column
-------- -------------
-- cd_3 | c
-- cd_4 | d
-- cd_1 | e
-- cd_2 | f
--(4 rows)

If you try to insert more rows than the length of your sequence, you'll get

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

If in your current solution you gave your update a source table to get multiple rows from and their number also exceeded the sequence length, it wouldn't pose a problem - in conflicting pairs you'd just get the last one. Here's your update, fixed (but still requires that your table is pre-populated):

with new as (
    select ('cd_'||nextval('cd_seq'))::VARCHAR(8) id,'g' text_column union all 
    select ('cd_'||nextval('cd_seq'))::VARCHAR(8) id,'h' text_column union all 
    select ('cd_'||nextval('cd_seq'))::VARCHAR(8) id,'i' text_column union all 
    select ('cd_'||nextval('cd_seq'))::VARCHAR(8) id,'j' text_column union all 
    select ('cd_'||nextval('cd_seq'))::VARCHAR(8) id,'k' text_column)
update test_seq old
set text_column=new.text_column
from new
where old.id=new.id;