14

It looks like select nextval('table_name') actually does the value increment.

My goal is to "predict" the nextval value on all tables in the database without actually making any incrementation. This should be a read-only operation.

I cannot run the select nextval on a read-only connection since it's actually trying to make a transaction. I would like to be able to query this and monitor the sequences on a read-only replica database.

How would you tackle this and meet the goal?

2
  • An int id column overflowed due to a bug in the custom stored proc that rebuilds our sequences... Until the root cause can be identified and fixed, I want to have insight to any tables that might overflow their data types by a high sequence number as at least a preventative measure.
    – emmdee
    Nov 12, 2019 at 20:21
  • 1
    That would be great to redesign. However it's a 10 year old legacy software design stack and I'm just trying to earn my paycheck not here to redesign everything. I'm a sysadmin just trying to resolve a problem.
    – emmdee
    Nov 12, 2019 at 21:18

3 Answers 3

16

I would assume that SELECT currval('seq_name') + 1 or SELECT last_value + 1 FROM seq_name would be the answer, but that's only if you're incrementing by 1, or you know the increment off-hand.

If you're not incrementing by 1 and you don't know the increment, then try this:

SELECT currval('seq_name') + i.inc
  FROM (SELECT seqincrement AS inc
          FROM pg_sequence
         WHERE seqrelid = 'seq_name'::regclass::oid) AS i;

or

SELECT last_value + i.inc
  FROM seq_name,
      (SELECT seqincrement AS inc
         FROM pg_sequence
        WHERE seqrelid = 'seq_name'::regclass::oid) AS i;
0
3

Based on previous answers I realised that it fails for the first value added: The query returns 2 instead on 1 when there are no values added. Here is my solution:

SELECT (CASE WHEN is_called THEN last_value + i.inc 
        ELSE last_value END ) AS nextvalue
  FROM myserial,
      (SELECT seqincrement AS inc 
       FROM pg_sequence 
       WHERE seqrelid = 'myserial'::regclass::oid) AS i;
1

A little late to the game, but wanted to share this in case it is helpful.

SELECT sequencename 
    , data_type 
    , "last_value" 
    , start_value 
    , increment_by 
    , max_value 
FROM pg_catalog.pg_sequences
JOIN (
        SELECT regexp_split_to_array(pg_get_serial_sequence, E'\\.') AS seq_parts 
        FROM pg_get_serial_sequence('my_table', 'my_column')
    ) AS N
        ON N.seq_parts[2] = sequencename 

This should provide the data you're looking for to perform the calculations you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.