Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Sign up to join this community

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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?

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 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

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;
SELECT last_value + i.inc
  FROM seq_name,
      (SELECT seqincrement AS inc
         FROM pg_sequence
        WHERE seqrelid = 'seq_name'::regclass::oid) AS i;
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;
        
Thanks for contributing an answer to Database Administrators Stack Exchange!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.