Using a primary key with a sequence that cycles results in "duplicate key violates unique constraint" errors after wrap around in Postgresql

Question

BACKGROUND/CONTEXT

If I define a sequence and a table as follows:

CREATE SEQUENCE public.test_sequence
    CYCLE
    MINVALUE 1
    MAXVALUE 2147483647;

CREATE TABLE public.test_table
(
    id integer NOT NULL DEFAULT nextval('test_sequence'::regclass),
    name text,
    CONSTRAINT test_table_pkey PRIMARY KEY (id)
);

And insert a ton of values. Eventually I will reach the end of the sequence and because of the CYCLE keyword nextval will go back to the beginning of the sequence and once again return 1, 2, etc. Unfortunately, if I am inserting and the old record still exists with the same id, then I end up with a collision and the insert call will result in a "duplicate key violates unique constraint" error. I was somewhat surprised with this result in that I made the obviously incorrect assumption in believing that "nextval" was slightly more intelligent and meant the next free id value which would obviously be more useful in this context.

According to the following thread it appears that I am perhaps not alone in making this assumption and that others may be running into this same issue: How do I avoid nextval() colliding with existing entries on wrapping?

There are obviously a few possible workarounds to this problem:

1. Migrate to a bigger data type such as bigint.
2. Migrate to using UUID values instead.
3. Introduce an ON CONFLICT DO NOTHING clause to the INSERTs.
4. Introduce an ON CONFLICT DO UPDATE clause to the INSERTs.
5. Add a BEFORE INSERT trigger to increment the nextval of the sequence until we reach a free one.
6. etc.?

For those who are interested, the following article explores the differences between a few of the above options: Choosing a Postgres Primary Key

For a variety of reasons, and for better or worse, I chose option 5 to solve this issue. I would appreciate if we could avoid debating which is the better solution here. Especially considering the best choice can depend on a person's particular requirements. I would however be interested if there are other unique workarounds that I did not consider above?

To further clarify our requirements. We are running on an embedded device and space is finite. We have recently run out of space and we have barely started populating our major tables. Consequently, any option that increases our space usage is not necessarily our best option. We do not currently have any limits on our time so we can currently afford a performance penalty here. Consequently, for our needs we have already chosen option 5. For other people's requirements, any of the other above options may indeed be a better choice.

QUESTION

Considering that we have already chosen option 5. What is the best way to implement this?

I am interested in getting feedback on the database triggers I wrote to handle this situation. Specifically:

• If anybody notices a concurrency problem with the locking solution I went with?

• If there are better ways to write this trigger?

• etc.

• What are some of the gotchas/footguns of this approach?

  A1 - @Ry- makes a good point that the key reuse can lead to some race conditions (if I'm paraphrasing correctly here). One particular scenario as an example is when you have 3 transactions: the first does a DELETE on id 5, the second later does an INSERT on id 5, the third is trying to do an UPDATE on id 5. Depending on when the third transaction arrives (before or after the other two), it will either update the old record or the new, one of which might not have been the intended update. Hence the race and possible data corruption. I suppose this particular case could possibly be mitigated by always doing a "SELECT FOR UPDATE" to check the previous state before doing the "UPDATE" (within the same transaction). However, this would obviously incur further performance overhead and risks users forgetting to do so.

A2 - @Atmo makes a good point that there will be a performance penalty for this function depending how full (or sparse), the values are after you wrap around. This culminates in a worst case scenario if there are no more free ids where you would deadlock if you are locking or infinite loop (at least until someone else deletes a record) if you are not locking. It should be noted that this time penalty is linear time as it only needs to check each record once and will only be hitting the index and not the records. Mitigation techniques for this scenario include regular cleanup (if possible) to maintain fewer entries in the table and checking for wrap around in the trigger to avoid deadlock/looping.

A3 - Cycling/wrapping the id values means that you can no longer rely on the id values for chronological ordering/sorting (if you happen to be doing so).

And here is the associated trigger code:

CREATE OR REPLACE FUNCTION fix_id_collision()
RETURNS TRIGGER AS $$
DECLARE
  found_id integer;
  column_default text;
BEGIN
  -- Loop until we find a free id value.
  LOOP
    -- Check if the id already exists in the table.
    -- Use row level "FOR UPDATE" locking to hopefully ensure
    -- that concurrent INSERT queries don't receive the same id
    -- and collide.
    EXECUTE format('SELECT id FROM %I.%I WHERE id = %L FOR UPDATE', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id) INTO found_id;
    IF found_id IS NULL THEN
      RETURN NEW;
    END IF;
    EXECUTE format('SELECT column_default FROM
information_schema.columns WHERE table_schema=%L AND table_name=%L', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO column_default;
    EXECUTE format('SELECT %s', column_default) INTO NEW.id;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

And some code to install it for all the tables in my database:

CREATE OR REPLACE FUNCTION install_id_collision_triggers()
RETURNS VOID
AS $$
DECLARE
  tbl_schema text;
  tbl_name text;
BEGIN
  FOR tbl_schema, tbl_name IN SELECT table_schema, table_name FROM information_schema.columns WHERE column_name='id'
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS id_collision_trigger_%s ON %I.%I', tbl_name, tbl_schema, tbl_name);
    EXECUTE format('CREATE TRIGGER id_collision_trigger_%s BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION fix_id_collision()', tbl_name, tbl_schema, tbl_name);
  END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT install_id_collision_triggers();

P.S. I am already aware that I can do a "CREATE OR REPLACE TRIGGER" in recent PostgreSQL versions but I happen to be using an older version for testing so please excuse the extra step.

FOLLOW UP

@klin makes an interesting point here that there might not be a space difference between using the integer and bigint/bigserial data types (on 64-bit systems) due to data type alignment issues as described in his following post: Benchmark: bigint vs int on PostgreSQL . I have run a very very basic test and confirm that there is only a very marginal increase in space usage between choosing integer vs bigint. It worked out to approximately 4% more space usage for bigint in my case. Consequently, there were not as great space savings as I originally thought in choosing this particular approach and I can recommend against using this workaround. Especially in consideration of the potential for data corruption that it introduces.

Answer 1

Personally, I have always and will always solve this problem using bigint/bigserial.

According to the documentation, bigserial gives you 9,223,372,036,854,775,807 values. Sure, it may wrap at some point but let us be realistic for a minute.

Let us go big imagine your database reserves 1M values per second. It will take you a little bit more than 292,271 years to use them all. Even if you somehow managed to use 1 billion values per second, it still leaves more time than enough to turn it into your great-great-...-great-grandchilren's problem, not yours.

I do not see any way a database could support saving 1 billion records per second for almost 300 years. The only possible way you could reserve so many values on the sequence would be by discarding over 99% of them, in which case I would say your design has big issues.

IMHO, you can safely assume a sequence over bigint will never wrap back (as long as you do not discard an insane number of generated values).

---

Edit:

As I feel, from the comments left under the question and this answer, I am not getting through, let me comment the 5 solutions presented:

1. Contrary to what is mentioned in the question and as I explain above, a BIGINT sequence requires so many records to be inserted before cycling that it will realisticly never happen.

2. UUIDs could be a solution to have unique ids without having to care for cycling. It is the solution of choice to have a sharded database (if you show your primary key to users).
   However, data security is off-topic in a SQL question about avoiding primary key conflicts, in the sense that even if it was generating more collisions than other solutions, it would still need to be chosen, and in the sense a table created with a UUID will "never" have PK conflicts (actual probabilities have been answered there), hence does not need to be improved.
   Limiting the answer to only conflict resolution and setting aside the security, solution 1 will not cycle either, is more simple and is faster.
   ->UUIDs should be ruled out (except for security reasons, off-topic here).

3. The only purpose of ON CONFLICT DO NOTHING is to avoid receiving an error message. However, error messages, outside of getting syntax error when manually typing a query, is only a way for the database to inform the client application and should be considered a good thing. Rather than testing the number of records that were inserted, simply catch and interpret the error message number according to the table provided in documentation.
   -> To be ruled out.

4. ON CONFLICT DO UPDATE: Same as the above remark + it will destroy existing data that should perhaps be left untouched.
   -> To be ruled out.

5. The TRIGGER solution can be easily tested. The below script simulates a sequence conflict on table containing 10k values.

   CREATE TABLE A (
       ID BIGSERIAL PRIMARY KEY
   );
   INSERT INTO A SELECT generate_series(1,10000);
   /* The sequence cycling back to 1 is simulated here: netval() will return 1 but the table is already filled */
   CREATE OR REPLACE FUNCTION fix_id_collision()
   RETURNS TRIGGER AS $$
   DECLARE
     found_id integer;
     column_default text;
   BEGIN
     -- Loop until we find a free id value.
     LOOP
     -- Check if the id already exists in the table.
     -- Use row level "FOR UPDATE" locking to hopefully ensure
     -- that concurrent INSERT queries don't receive the same id
     -- and collide.
     EXECUTE format('SELECT id FROM %I.%I WHERE id = %L FOR UPDATE', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id) INTO found_id;
     IF found_id IS NULL THEN
       RETURN NEW;
     END IF;
     RAISE NOTICE 'Id taken %', NEW.id;
     EXECUTE format('SELECT column_default FROM
   information_schema.columns WHERE table_schema=%L AND table_name=%L', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO column_default;
     EXECUTE format('SELECT %s', column_default) INTO NEW.id;
     RAISE NOTICE 'Trying with id %', NEW.id;
   END LOOP;
   END;
   $$
   LANGUAGE plpgsql;
   CREATE TRIGGER id_collision_trigger_A BEFORE INSERT ON A FOR EACH ROW EXECUTE FUNCTION fix_id_collision();
   

   I added some RAISE NOTICE that can prove concurrent queries work.
   First comment: I do not see point locking the records since nothing is going to happen to them. If anything, doing that would prevent the locked records from being updated/deleted concurrently, while being useless for the other clients to insert new records.
   Secondly, a simple INSERT INTO A VALUES(default) with, I repeat, only 10k values in the table (and 10k conflicts to solve), takes about 30 seconds to complete. On that test sample, the next queries will be faster since there will be no more conflicts to resolve but in that case, a sequence without trigger will work just as well too.
   Outside of the test case I built above, in a situation where you expect conflicts to arise often, the performance is abysmal and I cannot imagine what would happen with more records in the table or a longer series of consecutive values in the primary key.
   -> To be ruled out.

At the risk of repeating my point: solution 1 and solution 2 are the only valid ones. Since solution 1 also happens to be the most simple and the fastest and limiting my answer to id collision (not security), that is definitely the correct way to go.

Solution 5:

• albeit eventually solving conflicts too,
• uses orders of magnitude more resources, in the form of processor time and reading from the disk, than solution 1, making it orders of magnitude slower, to the point it feels queries simply do not work.
• is useless for anything other than a sequence cycling:
  • For UUIDs, even if you are unlucky enough to generate a collision, you are better off letting 1 insertion out of trillions fail rather than executing a trigger on all the insertions,
  • For custom-generated values, you should improve how the values are generated to avoid collisions before they reach the database.

It is not the way to go.