Re: pg_sequence catalog

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andres Freund <andres(at)anarazel(dot)de>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Greg Stark <stark(at)mit(dot)edu>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Craig Ringer <craig(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_sequence catalog
Date: 2016-09-10 11:17:41
Message-ID: 54f58392-fd44-2c94-2297-1cd47f1c61e4@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Add-pg_sequences-view.patch text/x-patch 15.9 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2016-09-10 11:25:14 Re: improved DefElem list processing
Previous Message Pavan Deolasee 2016-09-10 10:44:24 Re: Block level parallel vacuum WIP