Waiting for PostgreSQL 10 – Add pg_sequence system catalog

On 20th of December, Peter Eisentraut committed patch:

Add pg_sequence system catalog
 
Move sequence metadata (start, increment, etc.) into a proper system
catalog instead of storing it in the sequence heap object.  This
separates the metadata from the sequence data.  Sequence metadata is now
operated on transactionally by DDL commands, whereas previously
rollbacks of sequence-related DDL commands would be ignored.
 
Reviewed-by: Andreas Karlsson

If you ever created a table with serial column (usually for primary key) then you used sequence.

Like here:

$ CREATE TABLE test (
    id serial PRIMARY KEY,
    whatever text
);
CREATE TABLE
 
$ \d test
                             TABLE "public.test"
  COLUMN  |  TYPE   | Collation | NULLABLE |             DEFAULT              
----------+---------+-----------+----------+----------------------------------
 id       | INTEGER |           | NOT NULL | NEXTVAL('test_id_seq'::regclass)
 whatever | text    |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

Created sequence is named test_id_seq. When I'll insert some data:

$ INSERT INTO test (whatever)
    SELECT 'whatever #' || i FROM generate_series(1,3) i;
INSERT 0 3

sequence generates values for id column:

$ SELECT * FROM test;
 id |  whatever   
----+-------------
  1 | whatever #1
  2 | whatever #2
  3 | whatever #3
(3 ROWS)

Since “forever" you could check sequence state by doing:

$ SELECT * FROM test_id_seq ;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 test_id_seq   |          3 |           1 |            1 | 9223372036854775807 |         1 |           1 |      30 | f         | t
(1 ROW)

But if you wanted to get data on multiple sequences you had to either issue multiple queries, or build complex queries with union, like:

$ SELECT * FROM test_id_seq UNION ALL SELECT * FROM other UNION ALL SELECT * FROM another;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 test_id_seq   |          3 |           1 |            1 | 9223372036854775807 |         1 |           1 |      30 | f         | t
 other         |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
 another       |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(3 ROWS)

Now, luckily, this becomes much simpler, as we get the pg_sequence table that shows:

$ SELECT * FROM pg_sequence;
 seqrelid | seqcycle | seqstart | seqincrement |       seqmax        | seqmin | seqcache 
----------+----------+----------+--------------+---------------------+--------+----------
    22222 | f        |        1 |            1 | 9223372036854775807 |      1 |        1
    22233 | f        |        1 |            1 | 9223372036854775807 |      1 |        1
    22235 | f        |        1 |            1 | 9223372036854775807 |      1 |        1
(3 ROWS)

Please note that it doesn't show last_value, as it's value can be misleading. So if you need to get last value of a sequence, you still need to query sequence data directly:

$ SELECT * FROM test_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
          3 |      30 | t
(1 ROW)

But at the very least you can get the basic information about sequence is available in consistent, easy to query, way.

Nice, thanks guys.

4 thoughts on “Waiting for PostgreSQL 10 – Add pg_sequence system catalog”

  1. Your comment about last_value is a bit misleading. The reason you do not get last_value when selecting from pg_sequence is because the value of the sequence is not stored in the catalog table, it is still stored in the index relation. Peter’s commit only moved the more static data from the sequence relations to the catalog. There is also a view called pg_sequences which includes the last_value column (similar to pg_index vs pg_indexes).

  2. To clarify my last comment:

    The catalog is updated using normal transactional MVCC semantics while the sequence relation is updated outside the normal transactions (which is the main benefit of using sequences). Peter moved all fields other than the three absolutely necessary ones out from the non-transactional sequence relation (the ones which nextval() updates).

    This is much cleaner and makes almost all sequence DDL transactional.

  3. So even with this new catalog it will not be possible to include sequence (meta)data automatically in the new core logical replication to upgrade PostgreSQL even more completely while saving cumbersome manual afterwork?

Comments are closed.