Quick patch: Display sequence owner

Hi folks,

Was recently poked and reminded that this patch may be of interest to
the community. It was mostly done as an academic exercise, just to see
how it works, and so it has a rather hackish feel. The patch adds the
sequence owner, if available, to psql's \d <sequence> output, as
suggested in a recent thread:

http://archives.postgresql.org/pgsql-general/2008-11/msg01300.php

The patch adds a query against pg_depend, then fakes an extra column
"owned_by" in the output:

# \d tablename_columnname_seq
Sequence "public.tablename_columnname_seq"
Column | Type | Value
---------------+----------+--------------------------
sequence_name | name | tablename_columnname_seq
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 | 1
is_cycled | boolean | f
is_called | boolean | f
owned_by | regclass | tablename

Now for the snags and additional thoughts:

The query against pg_depend looks for relations for which the sequence
is auto-dependent. It wouldn't make any sense, but is it at all
possible for a sequence to auto-depend on something else?

An earlier version of the patch pulled the owning table and schema names
directly, rather than casting to regclass, so the schema name was always
shown. Would this be preferable, in case there's some ambiguity in
similarly named tables between schemas?

I'd pondered briefly whether there should be a real attribute to
represent the sequence owner, just for display purposes. But I'm
assuming that would present a big concurrency issue, as other
transactions would see the change on the sequence immediately while
pg_depend wouldn't be seen to change until committed. That, and
ROLLBACK wouldn't work at all...

The column info query is getting messy. Could probably clean that up a
bit if anyone thinks it'd be worth it?

- Josh Williams

Josh Williams wrote:
> Hi folks,
>
> Was recently poked and reminded that this patch may be of interest to
> the community. It was mostly done as an academic exercise, just to see
> how it works, and so it has a rather hackish feel. The patch adds the
> sequence owner, if available, to psql's \d <sequence> output, as
> suggested in a recent thread:
>
> http://archives.postgresql.org/pgsql-general/2008-11/msg01300.php
>
> The patch adds a query against pg_depend, then fakes an extra column
> "owned_by" in the output:
>
> # \d tablename_columnname_seq
> Sequence "public.tablename_columnname_seq"
> Column | Type | Value
> ---------------+----------+--------------------------
> sequence_name | name | tablename_columnname_seq
> 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 | 1
> is_cycled | boolean | f
> is_called | boolean | f
> owned_by | regclass | tablename
>
>
> Now for the snags and additional thoughts:
>
> The query against pg_depend looks for relations for which the sequence
> is auto-dependent. It wouldn't make any sense, but is it at all
> possible for a sequence to auto-depend on something else?
>
> An earlier version of the patch pulled the owning table and schema names
> directly, rather than casting to regclass, so the schema name was always
> shown. Would this be preferable, in case there's some ambiguity in
> similarly named tables between schemas?
>
> I'd pondered briefly whether there should be a real attribute to
> represent the sequence owner, just for display purposes. But I'm
> assuming that would present a big concurrency issue, as other
> transactions would see the change on the sequence immediately while
> pg_depend wouldn't be seen to change until committed. That, and
> ROLLBACK wouldn't work at all...
>
> The column info query is getting messy. Could probably clean that up a
> bit if anyone thinks it'd be worth it?
>
> - Josh Williams

Josh,

We've just gone into a feature freeze for the 8.4 release. So I've added a
link to your patch in the first Commitfest for 8.5 here:

http://wiki.postgresql.org/wiki/CommitFest_2009-First#Pending_patches

This Commitfest is scheduled to start March 2009. Though it's possible that
it may be later. Hopefully not though.

Much of the community is quite busy reviewing current patches and doing last
minute changes to their own patches for 8.4. If you don't get much response
then that's probably why. Please don't mistakenly assume we're not
interested. If you make changes to the patch please post it back to the list
and you can update the CommitFest page too if you like.

David.

Josh Williams wrote:

> The patch adds a query against pg_depend, then fakes an extra column
> "owned_by" in the output:

Please send a context diff (diff -c)

> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.188
> diff -r1.188 describe.c
> 917c917
> < seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values));
> ---
> > seq_values = pg_malloc_zero((SEQ_NUM_COLS+2) * sizeof(*seq_values));

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Josh Williams wrote:
>> The patch adds a query against pg_depend, then fakes an extra column
>> "owned_by" in the output:

> Please send a context diff (diff -c)

Don't bother --- it's a really bad idea as designed anyway. owned_by
is not a column of a sequence relation and pretending that it is one
will just cause confusion.

I think the place that such information could most naturally be squeezed
into psql's \d commands would be to add another type of footer
information to \dt, eg

Table "foo.bar"
...
Indexes:
"bari" ...
Owned sequences:
"baz" owned by col1
...

If you really want to attach the information to the \d output for the
sequence instead of the table, consider a similar footer-style display
instead of making it look like something it's not.

regards, tom lane

On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote:
> I think the place that such information could most naturally be squeezed
> into psql's \d commands would be to add another type of footer
> information to \dt, eg
>
> Table "foo.bar"
> ...
> Indexes:
> "bari" ...
> Owned sequences:
> "baz" owned by col1

That makes more sense, though isn't that a little repetitive when
"default nextval(...)" is visible immediately above it?

Doesn't guarantee the sequence is owned by the table of course, but I'd
imagine to most people it'd just be noise. Could see it being shown in
the verbose version, \d+ foo.bar.

I certainly like that better than "making up" an nonexistent column. :)

> If you really want to attach the information to the \d output for the
> sequence instead of the table, consider a similar footer-style display
> instead of making it look like something it's not.

For the sequences themselves, it'd be nice to show somewhere, at least
for tracking down stray sequences and identifying relationships.
Perhaps a function to do the reverse of pg_get_serial_sequence()? Or
better yet if no one else is already working on it, a more generic way
to get readable information out of pg_depend?

>
> regards, tom lane
>

- Josh Williams

On Tue, Dec 9, 2008 at 10:46 AM, Josh Williams <joshwilliams(at)ij(dot)net> wrote:
> On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote:
>> I think the place that such information could most naturally be squeezed
>> into psql's \d commands would be to add another type of footer
>> information to \dt, eg
>>
>> Table "foo.bar"
>> ...
>> Indexes:
>> "bari" ...
>> Owned sequences:
>> "baz" owned by col1
>
> That makes more sense, though isn't that a little repetitive when
> "default nextval(...)" is visible immediately above it?
>

actually, when a try your patch i have to look at the code to find
where you put such information... i tried \dt first...

> Doesn't guarantee the sequence is owned by the table of course, but I'd
> imagine to most people it'd just be noise. Could see it being shown in
> the verbose version, \d+ foo.bar.
>

that's exactly why we want the aditional info... the idea of putting
it on \d+ doesn't sounds too bad... to me at least...

> For the sequences themselves, it'd be nice to show somewhere, at least
> for tracking down stray sequences and identifying relationships.

in \ds maybe

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Josh Williams wrote:
> On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote:
> > I think the place that such information could most naturally be squeezed
> > into psql's \d commands would be to add another type of footer
> > information to \dt, eg
> >
> > Table "foo.bar"
> > ...
> > Indexes:
> > "bari" ...
> > Owned sequences:
> > "baz" owned by col1
>
> That makes more sense, though isn't that a little repetitive when
> "default nextval(...)" is visible immediately above it?

I don't think that it is all that repetitive. It's not uncommon to see
people creating sequences and assigning to default values, without
setting the OWNED BY bits. It's good that this information is very
visible. It's only a couple more lines in the common case anyway (if
you want to save half of that overhead, make it a single line when
there's a single sequence.)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tuesday 09 December 2008 15:49:17 Alvaro Herrera wrote:
> Josh Williams wrote:
> > On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote:
> > > I think the place that such information could most naturally be
> > > squeezed into psql's \d commands would be to add another type of footer
> > > information to \dt, eg
> > >
> > > Table "foo.bar"
> > > ...
> > > Indexes:
> > > "bari" ...
> > > Owned sequences:
> > > "baz" owned by col1
> >
> > That makes more sense, though isn't that a little repetitive when
> > "default nextval(...)" is visible immediately above it?
>
> I don't think that it is all that repetitive. It's not uncommon to see
> people creating sequences and assigning to default values, without
> setting the OWNED BY bits. It's good that this information is very
> visible. It's only a couple more lines in the common case anyway (if
> you want to save half of that overhead, make it a single line when
> there's a single sequence.)
>

It feels like noise to me; showing indexes/triggers/constraints affect how you
interact with a table, but whether a sequence is owned or not doesn't make a
significant difference. Given we don't list other dependencies
(views/functions/etc...) I'm not excited about adding this one.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com