How can I increment a sequence by more than 1 in Postgres?

user_15054634 0
Viewed 804

What's the best way to atomically update a sequence in Postgres?

Context: I'm bulk inserting objects with SQLAlchemy, and exectutemany can't return defaults, so I'd like to increment the primary key sequence by the amount of objects I need to insert.

I know I can do:

ALTER SEQUENCE seq INCREMENT BY 1000;

But I'm not sure if that's safe to do in concurrent environments.

postgresql
3 Answers

You can use setval() combined with nextval()

select setval('my_sequence', nextval('my_sequence') + 999);

This increments the current value by 1000, it does not set it to a fixed value.

user_3303150

As Laurenz Albe suggests: I called nextval 1000 times.

SELECT nextval('common_id_seq') FROM generate_series(1, 1000);

The advantage to this over a a_horse_with_no_name's suggestion is that I don't need to grant setval privileges to the user.

user_150546340

That would be safe, since ALTER SEQUENCE takes an ACCESS EXCLUSIVE lock on the sequence.

There are two problems:

  • this will block all concurrent usage of the sequence until your transaction is completed

  • you don't know the starting value

You could work around the second problem like this:

BEGIN;
ALTER SEQUENCE seq INCREMENT BY 1000;
SELECT nextval('seq');
COMMIT;

Then you know that that value and the preceding 999 ondes are yours.

But I think the best way is to call nextval 1000 times.

user_64643080
Related