How to retrieve Postgresql Sequence-cache value from Postgresql Catalog tables?

Question

I have used this below query to get the complete information of Sequence objects from the Postgresql catalog table

select s.sequence_name, s.start_value, s.minimum_value, s.maximum_value, s.increment, s.cycle_option 
from information_schema.sequences s 
 where s.sequence_schema='schema1' 

One more attribute value am not able to get is "Cache" value.

Am using Postgresql 9.2

Here is the DDL syntax for the sequence with cache,

ALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ]

[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]

[ START [ WITH ] start ]

[ RESTART [ [ WITH ] restart ] ]

[ CACHE cache ] [ [ NO ] CYCLE ]

[ OWNED BY { table_name.column_name | NONE } ]

Is there any Postgres functions to get this Sequence cache value ?

Thanks,

Ravi

Answer 1

With PostgreSQL 10 or newer, the cache size can be obtained from the system view pg_sequences or the system table pg_sequence:

SELECT cache_size FROM pg_catalog.pg_sequences
 WHERE schemaname='public' and sequencename='s';

or alternatively

SELECT seqcache FROM pg_catalog.pg_sequence
  WHERE seqrelid = 'public.s'::regclass;

Omit the schema qualification (public or more generally the name of the schema) in the 2nd query to use automatically search_path instead of a fixed schema.

With versions older than v10, you may query the sequence itself as if it was a table.

For example:

CREATE SEQUENCE s CACHE 10;
SELECT cache_value FROM s;

Result:

 cache_value 
-------------
          10

Or

\x
SELECT * FROM s;

Result:

-[ RECORD 1 ]-+--------------------
sequence_name | s
last_value    | 1
start_value   | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 10
log_cnt       | 0
is_cycled     | f
is_called     | f

Answer 2

This no longer works in Postgres 10. You can use

select seqcache from pg_sequence where seqrelid = 's'::regclass;