Thread: [HACKERS] Definitional questions for pg_sequences view
What exactly is the point of the new pg_sequences view?
It seems like it's intended to ease conversion of applications that
formerly did "select * from sequencename", but if so, there are some
fairly annoying discrepancies. The old way got you these columns:
regression=# \d s1 Sequence "public.s1" Column | Type | Value
---------------+---------+---------------------sequence_name | name | s1last_value | bigint | 1start_value |
bigint | 1increment_by | bigint | 1max_value | bigint | 9223372036854775807min_value | bigint |
1cache_value | bigint | 1log_cnt | bigint | 0is_cycled | boolean | fis_called | boolean | f
but now we offer
regression=# \d pg_sequences View "pg_catalog.pg_sequences" Column | Type | Collation | Nullable |
Default
---------------+---------+-----------+----------+---------schemaname | name | | | sequencename
| name | | | sequenceowner | name | | | data_type | regtype |
| | start_value | bigint | | | min_value | bigint | | | max_value
| bigint | | | increment_by | bigint | | | cycle | boolean |
| | cache_size | bigint | | | last_value | bigint | | |
Why aren't sequencename, cache_size, and cycle spelled consistently
with past practice? And is there a really good reason to order the
columns randomly differently from before?
The big problem, though, is that there's no convenient way to use
this view in a schema-safe manner. If you try to translateselect * from my_seq;
intoselect * from pg_sequences where sequencename = 'my_seq';
then you're going to get burnt if there's more than one my_seq
in different schemas. There's no easy way to get your search
path incorporated into the result. Maybe people will always know
how to constrain the schemaname too, but I wouldn't count on it.
This could be fixed if it were possible to translate toselect * from pg_sequences where seqoid = 'my_seq'::regclass;
but the view isn't exposing the sequence OID. Should it?
As things stand, it's actually considerably easier and safer to
use the pg_sequence catalog directly, because then you *can* doselect * from pg_sequence where seqrelid =
'my_seq'::regclass;
and you only have to deal with the different-from-before column names.
Which pretty much begs the question why we bothered to provide the
view.
regards, tom lane
On 20 July 2017 at 22:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
This could be fixed if it were possible to translate to
select * from pg_sequences where seqoid = 'my_seq'::regclass;
but the view isn't exposing the sequence OID. Should it?
It probably should. It's not part of information_schema, it's in pg_catalog, and it's entirely reasonable to join on oids.
The relfilenode for the sequence can change, but the sequence oid won't unless we actually drop and re-create it, so the weird issues with alter sequence operations being partly transactional and partly not shouldn't be a concern.
If it's to be a convenience view, it should possibly also expose the OWNED BY relation oid IMO, if any. You have the sequence oid you can join on pg_class and grab the relowner, so it's not a great hassle if it's missing, but if it's a view to help users out exposing that would seem sensible.
On 7/20/17 16:36, Tom Lane wrote: > What exactly is the point of the new pg_sequences view? It is analogous to pg_tables, pg_matviews, pg_indexes, and other such system views that are sort of half-way between system catalogs and information schema. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services