0

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?

  • 1
    What is the purpose behind wanting to "predict" the next value of a sequence? If you just want an idea would it not just be possible to select the max value where that sequence is used? – Joe W Nov 12 at 19:50
  • @JoeW Since you must know... 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 at 20:21
  • I would question the need to have a stored proc to rebuild the sequences in the first place and would wonder what is putting them in a state that needs them to be rebuilt. – Joe W Nov 12 at 20:39
  • 1
    Trust me, you're preaching to the choir!! 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 at 21:18
3

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;
  • Thanks! This is definitely on the right track. However it seems you need to an insert or nextval() in the session first since I'm getting error: ERROR: currval of sequence "cache_zed_inst_seq" is not yet defined in this session. If I update the sequence in the session then I can grab currval() – emmdee Nov 12 at 20:28
  • if currval() doesn't work, then SELECT last_value FROM cache_zed_inst_seq should – richyen Nov 12 at 21:11
  • 1
    updated my answer with last_value – richyen Nov 12 at 21:13
  • Viola! Perfect THANK YOU – emmdee Nov 12 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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