PostgreSQL – Get nextval in Sequence Without Incrementing

postgresqlsequence

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?

Best Answer

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;

Related Solutions

Postgresql – Postgres sequence usage

The warning

WARNING:  no privileges were granted for "serial"

indicates that the granting role doesn't have the necessary privilege to grant privileges on the object in question.

Either execute the GRANT command as the role owning the sequence.
Or do the same as superuser (default superuser is postgres).
Or GRANT the necessary privilege to the role dev so that this role can in turn grant it to others:

GRANT ALL ON SEQUENCE serial TO dev WITH GRANT OPTION;

The name serial for a sequence isn't particularly wise, but since serial is not a name of a base type and not a reserved word, it wouldn't cause any immediate trouble.

Aside:
Unfortunately the manual uses the name serial in the CREATE SEQUENCE and ALTER SEQUENCE examples. I feel like a suggestion to pgsql-docs@postgresql.org might be in order ...

PostgreSQL 9.3 – Permission for Sequence in Another Schema

This can be done.
The column default for your serial primary key is typically defined as:

ALTER TABLE schema_a.tbl ALTER COLUMN tbl_id
SET DEFAULT nextval('schema_a.tbl_tbl_id_seq'::regclass);

Two options:

1. Move sequence (my preference)

to the public schema - or any schema with sufficient privileges:

GRANT USAGE ON SCHEMA public TO public; -- or: my_group

Moving the sequence to another schema is easy:

ALTER SEQUENCE schema_a.tbl_tbl_id_seq SET SCHEMA public;

Now you can grant USAGE:

GRANT USAGE ON SEQUENCE public.tbl_tbl_id_seq TO public; -- or: my_group

That preserves all references (incl. column defaults).

2. Function wrapper with `SECURITY DEFINER`

If you cannot move the sequence for some reason (can't think of one), you can alternatively wrap access to it in functions of your own with SECURITY DEFINER. Again, create those functions in the public schema (or any schema with sufficient privileges for your users):

CREATE OR REPLACE FUNCTION public.next_tbl_tbl_id_seq()
  RETURNS bigint AS
$func$
SELECT nextval('schema_a.tbl_tbl_id_seq'::regclass)
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = schema_a, pg_temp;
ALTER FUNCTION shop.f_deswap_name(text) OWNER TO owning_role;

Where owning_role is the owner of the sequence or any role with sufficient privileges. Similar function for currval() ...

Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.