From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
Cc: | pgsql-hackers(at)postgreSQL(dot)org |
Subject: | Definitional questions for pg_sequences view |
Date: | 2017-07-20 14:36:32 |
Message-ID: | 8951.1500561392@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | s1
last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_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 translate
select * from my_seq;
into
select * 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 to
select * 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* do
select * 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
From | Date | Subject | |
---|---|---|---|
Next Message | Claudio Freire | 2017-07-20 14:45:55 | Re: autovacuum can't keep up, bloat just continues to rise |
Previous Message | David Fetter | 2017-07-20 14:24:24 | Re: JSONB - JSONB operator feature request |