CODEWITHSUNDEEP
postgresqlsequence
Danny W. Adair
Danny W. Adair

Reputation: 12968

How can I forward a primary key sequence in PostgreSQL safely?

Using PostgreSQL (8.x), I need to skip a block of sequence numbers automatically, e.g. after giving out 49999 I want the next id to be 70000 not 50000

What is the correct/safest way of doing this?

NB: I originally posted a very similar question How can I forward a primary key sequence in Django safely? but I have the feeling that this must be done at database level (concurrency issues)...

Upvotes: 0

Views: 503

Answers (2)

Francisco Puga
Francisco Puga

Reputation: 25158

You can use a trigger, that checks the actual value of the sequence after an insert statement and change it for the value that you want.

I think that this snippet should do the job

CREATE OR REPLACE FUNCTION change_sequence() RETURNS TRIGGER AS $change_sequence$
DECLARE
  current_value INTEGER;

BEGIN
  current_value := currval('your_sequence');
  IF current_value = 49999 THEN
    setval ('you_sequence', 70000, false);
  END IF;
  RETURN NULL;
END;
$change_sequence$ LANGUAGE plpgsql;

-- DROP TRIGGER check_pkey ON your_table;
CREATE TRIGGER check_pkey
AFTER INSERT ON your_table
EXECUTE PROCEDURE change_sequence();

Upvotes: 0

Danny W. Adair
Danny W. Adair

Reputation: 12968

I believe I've found a way:

  • Don't use SERIAL for the primary key, use DEFAULT my_next_id()
  • Follow the same logic as for "single level gapless sequence" - http://www.varlena.com/GeneralBits/130.php - my_next_id() does an update followed by a select
  • Instead of just increasing by 1, check if a boundary was crossed and if so, increase even further

Upvotes: 1

Related Questions