0

I've a table with following structure:

CREATE TABLE web.cabinet_account_section (
    id serial NOT NULL,
    section_id text NULL,
    account_id int4 NULL,
    cabinet_param_id int4 NULL,
    CONSTRAINT cabinet_account_section_pkey PRIMARY KEY (id),
    CONSTRAINT cabinet_account_section_section_id_account_id_key UNIQUE (section_id),
    CONSTRAINT cabinet_account_section_account_id_fkey FOREIGN KEY (account_id) REFERENCES web.cabinet_account(id),
    CONSTRAINT cabinet_account_section_cabinet_param_id_fkey FOREIGN KEY (cabinet_param_id) REFERENCES web.cabinet_param(id)
);

The table is updated every day. Until recently, the table was updated as follows: if the UNIQUE section_id already exists (i.e., a regular SELECT EXISTS is executed), then nothing needs to be added. About 10,000 entries come to the entrance every day.

I decided to replace this logic with simple UPSERT:

INSERT INTO web.cabinet_account_section (section_id, account_id, cabinet_param_id)
VALUES ('${view.id}', '${account.id}', 2)
ON CONFLICT DO NOTHING;

But then there was a small problem with the serial field. With every INSERT INTO query, the id serial is automatically incremented, even if no record has been inserted. Those the last id in the column was conditionally = 1800. Yesterday several new records appeared in the table with the following id: 2300, 2560, 8120, 9982. Today the id has already exceeded 20,000. Those it tries to INSERT, increments the counter, does not insert, but does not roll back the counter.

I think that after a couple of months it will eventually hit the serial range limit even without adding new rows.

Sequence script:

CREATE SEQUENCE web.cabinet_account_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 2147483647
    START 1
    CACHE 1
    NO CYCLE;

Is it possible to somehow fix this case? It's one thing when you just delete a record from the table and some kind of ID just disappears. But here sequence grows exponentially even without inserting any rows. Or should i return to the old version with a check to see if such a section_id already exists?

funnelCONN
  • 149
  • 1
  • 11
  • Yes, this is how it works, check the manual: https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL – Frank Heikens Jan 20 '21 at 08:22
  • Sequence generated values are expected to have gaps. There is nothing to worry about. The **only** job for such a value is to be unique. It is totally irrelevant whether that value is 1,42,9982 or 932887 or -35428. You have nothing to worry –  Jan 20 '21 at 08:25

0 Answers0