Return to Answer

Yes, the generated series is guaranteed to be strictly monotonically increasing.

But no, there can be gaps if concurrent transactions also draw from the same sequence. Use other tools for strictly sequential numbers. The manual:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

If each concurrent access runs in its own session, which is closed afterwards, you might use the CACHE setting of sequences. But that's primarily aimed at faster access, and may result in bigger sequence gaps for other access patterns. And I am not even sure that "gapless" is guaranteed for the cache.

More on the topic:

• Can a new transaction claim an older sequence id?

Get strictly sequential numbers with a hack

LOCK does not work for sequences.
But we can lock the sequence object indirectly with ALTER SEQUENCE. The manual:

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

Assuming the necessary privileges (!) we could do this, within the same transaction:

BEGIN; ALTER SEQUENCE public.my_sequence INCREMENT 1; -- the same as it was! SELECT nextval('public.my_sequence') FROM generate_series(1, 5); -- do stuff, but hurry up, the sequence is blocked COMMIT; 

We are not actually altering the sequence, but it still blocks all concurrent interactions with it. Hence, you get strictly sequential numbers. Other involved transactions have to wait until yours is finished and releases the lock. Kind of defies the purpose of a sequence to optimize concurrent write access, temporarily. But you get your sequential series.

You wouldn't normally want to grant the privilege to ALTER SEQUENCE to random roles. But you could wrap this command in a privileged function and grant access to that function to whoever - effectively granting the privilege to access the sequence exclusively ... Related:

• Permission for sequence in another schema

Another anomaly: if your transaction ends in a ROLLBACK, the sequence is reset to its original state, including the current number before your nextval() calls. That never happens in normal operation, even for rolled-back transactions. If you go on to use those numbers, you'll run into unique violations ...

Yes, the generated series is guaranteed to be strictly monotonically increasing - for this simple query, and a SEQUENCE with default settings.

A SEQUENCE can even be created with negative increment! And if you add joins, grouping, ORDER BY or anything else to the same query that might reorder rows, that can scramble the generated numbers accordingly, depending on the resulting query plan.

But no, there can always be gaps if concurrent transactions also draw from the same sequence. Use other tools for strictly sequential numbers. The manual:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

If each concurrent access runs in its own session, which is closed afterwards, you might use the CACHE setting of sequences. But that's primarily aimed at faster access, and may result in bigger sequence gaps for other access patterns. And I am not even sure that "gapless" is guaranteed for the cache.

More on the topic:

• Can a new transaction claim an older sequence id?

Get strictly sequential numbers with a hack

LOCK does not work for sequences.
But we can lock the sequence object indirectly with ALTER SEQUENCE. The manual:

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

Assuming the necessary privileges (!) we could do this, within the same transaction:

BEGIN; ALTER SEQUENCE public.my_sequence INCREMENT 1; -- the same as it was! SELECT nextval('public.my_sequence') FROM generate_series(1, 5); -- do stuff, but hurry up, the sequence is blocked COMMIT; 

We are not actually altering the sequence, but it still blocks all concurrent interactions with it. Hence, you get strictly sequential numbers. Other involved transactions have to wait until yours is finished and releases the lock. Kind of defies the purpose of a sequence to optimize concurrent write access, temporarily. But you get your sequential series.

You wouldn't normally want to grant the privilege to ALTER SEQUENCE to random roles. But you could wrap this command in a privileged function and grant access to that function to whoever - effectively granting the privilege to access the sequence exclusively ... Related:

• Permission for sequence in another schema

Another anomaly: if your transaction ends in a ROLLBACK, the sequence is reset to its original state, including the current number before your nextval() calls. That never happens in normal operation, even for rolled-back transactions. If you go on to use those numbers, you'll run into unique violations ...

Yes, the generated series is guaranteed to be strictly monotonically increasing.

But no, there can be gaps if concurrent transactions also draw from the same sequence. Use other tools for strictly sequential numbers. The manual:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

If each concurrent access runs in its own session, which is closed afterwards, you might use the CACHE setting of sequences. But that's primarily aimed at faster access, and may result in bigger sequence gaps for other access patterns. And I am not even sure that "gapless" is guaranteed for the cache.

More on the topic:

• Can a new transaction claim an older sequence id?

Get strictly sequential numbers with a hack

LOCK does not work for sequences.
But we can lock the sequence object indirectly with ALTER SEQUENCE. The manual:

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

Assuming the necessary privileges (!) we could do this, within the same transaction:

BEGIN; ALTER SEQUENCE public.my_sequence INCREMENT 1; -- the same as it was! SELECT nextval('public.my_sequence') FROM generate_series(1, 5); -- do stuff, but hurry up, the sequence is blocked COMMIT; 

We are not actually altering the sequence, but it still blocks all concurrent interactions with it. Hence, you get strictly sequential numbers. Other involved transactions have to wait until yours is finished and releases the lock. Kind of defies the purpose of a sequence to optimize concurrent write access, temporarily. But you get your sequential series.

You wouldn't normally want to grant the privilege to ALTER SEQUENCE to random roles. But you could wrap this command in a privileged function and grant access to that function to whoever - effectively granting the privilege to access the sequence exclusively ... Related:

• Permission for sequence in another schema

Another anomaly: if your transaction ends in a ROLLBACK, the sequence is reset to its original state, including the current number before your nextval() calls. That never happens in normal operation, even for rolled-back transactions. If you go on to use those numbers, you'll run into unique violations ...

Yes, the generated series is guaranteed to be strictly monotonically increasing.

But no, there can be gaps if concurrent transactions also draw from the same sequence. Use other tools for strictly sequential numbers. The manual:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

If each concurrent access runs in its own session, which is closed afterwards, you might use the CACHE setting of sequences. But that's primarily aimed at faster access, and may result in bigger sequence gaps for other access patterns. And I am not even sure that "gapless" is guaranteed for the cache.

More on the topic:

• Can a new transaction claim an older sequence id?

Get strictly sequential numbers with a hack

LOCK does not work for sequences.
But we can lock the sequence object indirectly with ALTER SEQUENCE. The manual:

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

Assuming the necessary privileges (!) we could do this, within the same transaction:

BEGIN; ALTER SEQUENCE public.my_sequence INCREMENT 1; -- the same as it was! SELECT nextval('public.my_sequence') FROM generate_series(1, 5); -- do stuff, but hurry up, the sequence is blocked COMMIT; 

We are not actually altering the sequence, but it still blocks all concurrent interactions with it. Hence, you get strictly sequential numbers. Other involved transactions have to wait until yours is finished and releases the lock. Kind of defies the purpose of a sequence to optimize concurrent write access, temporarily. But you get your sequential series.

You wouldn't normally want to grant the privilege to ALTER SEQUENCE to random roles. But you could wrap this command in a privileged function and grant access to that function to whoever - effectively granting the privilege to access the sequence exclusively ... Related:

• Permission for sequence in another schema

Another anomaly: if your transaction ends in a ROLLBACK, the sequence is reset to its original state, including the current number before your nextval() calls. That never happens in normal operation, even for rolled-back transactions. If you go on to use those numbers, you'll run into unique violations ...

Yes, the generated series is guaranteed to be strictly monotonically increasing.

But no, there can be gaps if concurrent transactions also draw from the same sequence. Use other tools for strictly sequential numbers. The manual:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

If each concurrent access runs in its own session, which is closed afterwards, you might use the CACHE setting of sequences. But that's primarily aimed at faster access, and may result in bigger sequence gaps for other access patterns. And I am not even sure that "gapless" is guaranteed for the cache.

More on the topic:

• Can a new transaction claim an older sequence id?

Get strictly sequential numbers with a hack

LOCK does not work for sequences.
But we can lock the sequence object indirectly with ALTER SEQUENCE. The manual:

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

So we could do this, within the same transaction:

BEGIN; ALTER SEQUENCE public.my_sequence INCREMENT 1; -- the same as it was! SELECT nextval('public.my_sequence') FROM generate_series(1, 5); -- do stuff, but hurry up, the sequence is blocked COMMIT; 

We are not actually altering the sequence, but it still blocks all concurrent interactions with it. Hence, you get strictly sequential numbers. Other involved transactions have to wait until yours is finished and releases the lock. Kind of defies the purpose of a sequence to optimize concurrent write access, temporarily. But you get your sequential series.

Another anomaly: if your transaction ends in a ROLLBACK, the sequence is reset to its original state, including the current number before your nextval() calls. That never happens in normal operation, even for rolled-back transactions. If you go on to use those numbers, you'll run into unique violations ...

Yes, the generated series is guaranteed to be strictly monotonically increasing.

But no, there can be gaps if concurrent transactions also draw from the same sequence. Use other tools for strictly sequential numbers. The manual:

Because nextval and setval calls are never rolled back, sequence objects cannot be used if “gapless” assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

If each concurrent access runs in its own session, which is closed afterwards, you might use the CACHE setting of sequences. But that's primarily aimed at faster access, and may result in bigger sequence gaps for other access patterns. And I am not even sure that "gapless" is guaranteed for the cache.

More on the topic:

• Can a new transaction claim an older sequence id?

Get strictly sequential numbers with a hack

LOCK does not work for sequences.
But we can lock the sequence object indirectly with ALTER SEQUENCE. The manual:

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

Assuming the necessary privileges (!) we could do this, within the same transaction:

BEGIN; ALTER SEQUENCE public.my_sequence INCREMENT 1; -- the same as it was! SELECT nextval('public.my_sequence') FROM generate_series(1, 5); -- do stuff, but hurry up, the sequence is blocked COMMIT; 

We are not actually altering the sequence, but it still blocks all concurrent interactions with it. Hence, you get strictly sequential numbers. Other involved transactions have to wait until yours is finished and releases the lock. Kind of defies the purpose of a sequence to optimize concurrent write access, temporarily. But you get your sequential series.

You wouldn't normally want to grant the privilege to ALTER SEQUENCE to random roles. But you could wrap this command in a privileged function and grant access to that function to whoever - effectively granting the privilege to access the sequence exclusively ... Related:

• Permission for sequence in another schema

Another anomaly: if your transaction ends in a ROLLBACK, the sequence is reset to its original state, including the current number before your nextval() calls. That never happens in normal operation, even for rolled-back transactions. If you go on to use those numbers, you'll run into unique violations ...