Re: pg_sequence catalog

On 9/5/16 10:35 PM, Tom Lane wrote:
> In this viewpoint, we'd keep the sequence-specific data in a pg_sequence
> catalog. pg_sequence rows would be extensions of the associated pg_class
> rows in much the same way that pg_index rows extend the pg_class entries
> for indexes. We should supply a view pg_sequences that performs the
> implied join, and encourage users to select from that rather than directly
> from pg_sequence (compare pg_indexes view).

Let's start with that. Here is a patch that adds a pg_sequences view in
the style of pg_tables, pg_indexes, etc. This seems useful independent
of anything else, but would give us more freedom to change things around
behind the scenes.

A slight naming wart: I added a function lastval(regclass) for internal
use to get a sequence's "last value". But we already have a public
function lastval(), which gets the most recent nextval() result of any
sequence. Those are two quite different things. I don't want to
abandon the term "last value" here, however, because that is what the
sequence relation uses internally, and also Oracle uses it in its system
views with the same semantics that I propose here. We could use a more
verbose name like sequence_last_value(regclass), perhaps.

lastval has been kept separate from pg_sequence_parameters, because if
we were to go ahead with a new catalog layout later, then
pg_sequence_parameters would become obsolescent while we would possibly
still need a lastval function.

The column names of the new view have been deliberately tuned to use a
more conventional style than the information schema while avoiding what
I would consider some past naming mistakes. (For example, I hate
"is_cycled", which reads like "sequence has wrapped around at least once
in the past").

Here are some similar views in other places:

https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2053.htm
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0004203.html
https://msdn.microsoft.com/en-us/library/ff877934.aspx

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services