[HACKERS] Definitional questions for pg_sequences view - Mailing list pgsql-hackers

From Tom Lane
Subject [HACKERS] Definitional questions for pg_sequences view
Date
Msg-id 8951.1500561392@sss.pgh.pa.us
Whole thread Raw
Responses Re: [HACKERS] Definitional questions for pg_sequences view
Re: [HACKERS] Definitional questions for pg_sequences view
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: David Fetter
Date:
Subject: Re: [HACKERS] JSONB - JSONB operator feature request
Next
From: Claudio Freire
Date:
Subject: Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise