12

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

2 Answers 2

15

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;

Your Answer

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

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