Thread: TODO item: Have psql show current values for a sequence
Hi all, These patch implements the TODO item: Have psql show current values for a sequence. Comments are welcome. * Credits The original patch were developed by Euler Taveira de Oliveira <euler@timbira.com> but how he is a little busy, he sends it to me and I made some changes to satisfy the TODO item above. * Discussions http://archives.postgresql.org/pgsql-hackers/2007-12/msg00102.php http://archives.postgresql.org/pgsql-hackers/2007-12/msg00605.php * Outputs # \d foo_bar_seq Sequence "public.foo_bar_seq" +---------------+---------+---------------------+ | Column | Type | Value | +---------------+---------+---------------------+ | sequence_name | name | foo_bar_seq | | last_value | bigint | 11 | | start_value | bigint | 1 | | increment_by | bigint | 1 | | max_value | bigint | 9223372036854775807 | | min_value | bigint | 1 | | cache_value | bigint | 1 | | log_cnt | bigint | 31 | | is_cycled | boolean | f | | is_called | boolean | t | +---------------+---------+---------------------+ # \d+ foo_bar_seq Sequence "public.foo_bar_seq" +---------------+---------+---------------------+-------------+ | Column | Type | Value | Description | +---------------+---------+---------------------+-------------+ | sequence_name | name | foo_bar_seq | | | last_value | bigint | 11 | | | start_value | bigint | 1 | | | increment_by | bigint | 1 | | | max_value | bigint | 9223372036854775807 | | | min_value | bigint | 1 | | | cache_value | bigint | 1 | | | log_cnt | bigint | 31 | | | is_cycled | boolean | f | | | is_called | boolean | t | | +---------------+---------+---------------------+-------------+ -- []s Dickson S. Guedes -------------------------------------- Projeto Colmeia - Curitiba - PR +55 (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/
Attachment
On Sat, May 24, 2008 at 12:27:16AM -0300, Dickson S. Guedes wrote: > Hi all, > > These patch implements the TODO item: Have psql show current values > for a sequence. > Comments are welcome. > > Sequence "public.foo_bar_seq" > +---------------+---------+---------------------+ > | Column | Type | Value | > +---------------+---------+---------------------+ > | sequence_name | name | foo_bar_seq | > | last_value | bigint | 11 | > | start_value | bigint | 1 | > | increment_by | bigint | 1 | > | max_value | bigint | 9223372036854775807 | > | min_value | bigint | 1 | > | cache_value | bigint | 1 | > | log_cnt | bigint | 31 | > | is_cycled | boolean | f | > | is_called | boolean | t | > +---------------+---------+---------------------+ Is it now the style to draw a complete box around /d* displays? Or can we dispense with the top and bottom rows of dashes? -dg -- David Gould daveg@sonic.net 510 536 1443 510 282 0869 If simplicity worked, the world would be overrun with insects.
Re: [PATCHES] TODO item: Have psql show current values for a sequence
From
"Dickson S. Guedes"
Date:
On Sat, May 24, 2008 at 2:25 AM, daveg <daveg@sonic.net> wrote: > On Sat, May 24, 2008 at 12:27:16AM -0300, Dickson S. Guedes wrote: >> Hi all, >> >> These patch implements the TODO item: Have psql show current values >> for a sequence. >> Comments are welcome. >> >> Sequence "public.foo_bar_seq" >> +---------------+---------+---------------------+ >> | Column | Type | Value | >> +---------------+---------+---------------------+ >> | sequence_name | name | foo_bar_seq | >> | last_value | bigint | 11 | >> | start_value | bigint | 1 | >> | increment_by | bigint | 1 | >> | max_value | bigint | 9223372036854775807 | >> | min_value | bigint | 1 | >> | cache_value | bigint | 1 | >> | log_cnt | bigint | 31 | >> | is_cycled | boolean | f | >> | is_called | boolean | t | >> +---------------+---------+---------------------+ > > Is it now the style to draw a complete box around /d* displays? Or can we > dispense with the top and bottom rows of dashes? Hi Dave, This box around the display is because I'm using \pset border 2. -- []s Dickson S. Guedes ------------------------------------- Projeto Colmeia - Curitiba - PR +55 (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/
On Sat, May 24, 2008 at 12:27 AM, Dickson S. Guedes <guediz@gmail.com> wrote: > Hi all, > > These patch implements the TODO item: Have psql show current values > for a sequence. Hi all, There are some comments or suggestions about this patch? Thanks all. -- []s Dickson S. Guedes ------------------------------------- Projeto Colmeia - Curitiba - PR +55 (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/
Wow. I adjusted the patch slightly and applied it; the updated version is attached. We have been waiting for this to be done for quite some time. Thanks. --------------------------------------------------------------------------- Dickson S. Guedes wrote: > Hi all, > > These patch implements the TODO item: Have psql show current values > for a sequence. > Comments are welcome. > > * Credits > > The original patch were developed by Euler Taveira de Oliveira > <euler@timbira.com> > but how he is a little busy, he sends it to me and I made some changes > to satisfy > the TODO item above. > > * Discussions > > http://archives.postgresql.org/pgsql-hackers/2007-12/msg00102.php > http://archives.postgresql.org/pgsql-hackers/2007-12/msg00605.php > > * Outputs > > # \d foo_bar_seq > Sequence "public.foo_bar_seq" > +---------------+---------+---------------------+ > | Column | Type | Value | > +---------------+---------+---------------------+ > | sequence_name | name | foo_bar_seq | > | last_value | bigint | 11 | > | start_value | bigint | 1 | > | increment_by | bigint | 1 | > | max_value | bigint | 9223372036854775807 | > | min_value | bigint | 1 | > | cache_value | bigint | 1 | > | log_cnt | bigint | 31 | > | is_cycled | boolean | f | > | is_called | boolean | t | > +---------------+---------+---------------------+ > > # \d+ foo_bar_seq > Sequence "public.foo_bar_seq" > +---------------+---------+---------------------+-------------+ > | Column | Type | Value | Description | > +---------------+---------+---------------------+-------------+ > | sequence_name | name | foo_bar_seq | | > | last_value | bigint | 11 | | > | start_value | bigint | 1 | | > | increment_by | bigint | 1 | | > | max_value | bigint | 9223372036854775807 | | > | min_value | bigint | 1 | | > | cache_value | bigint | 1 | | > | log_cnt | bigint | 31 | | > | is_cycled | boolean | f | | > | is_called | boolean | t | | > +---------------+---------+---------------------+-------------+ > > -- > []s > Dickson S. Guedes > -------------------------------------- > Projeto Colmeia - Curitiba - PR > +55 (41) 3254-7130 ramal: 27 > http://makeall.wordpress.com/ > http://pgcon.postgresql.org.br/ > http://planeta.postgresql.org.br/ [ Attachment, skipping... ] > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/bin/psql/describe.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.179 diff -c -c -r1.179 describe.c *** src/bin/psql/describe.c 14 Jul 2008 23:13:04 -0000 1.179 --- src/bin/psql/describe.c 15 Jul 2008 03:06:24 -0000 *************** *** 811,817 **** printTableContent cont; int i; char *view_def = NULL; ! char *headers[5]; char **modifiers = NULL; char **ptr; PQExpBufferData title; --- 811,818 ---- printTableContent cont; int i; char *view_def = NULL; ! char *headers[6]; ! char **seq_values = NULL; char **modifiers = NULL; char **ptr; PQExpBufferData title; *************** *** 869,874 **** --- 870,904 ---- tableinfo.tablespace = (pset.sversion >= 80000) ? atooid(PQgetvalue(res, 0, 6)) : 0; PQclear(res); + + /* + * This is used to get the values of a sequence and store it in an + * array that will be used later. + */ + if (tableinfo.relkind == 'S') + { + PGresult *result; + + #define SEQ_NUM_COLS 10 + printfPQExpBuffer(&buf, + "SELECT sequence_name, last_value, \n" + " start_value, increment_by, \n" + " max_value, min_value, cache_value, \n" + " log_cnt, is_cycled, is_called \n" + "FROM \"%s\"", + relationname); + + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + + seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values)); + + for (i = 0; i < SEQ_NUM_COLS; i++) + seq_values[i] = pg_strdup(PQgetvalue(result, 0, i)); + + PQclear(result); + } /* Get column info (index requires additional checks) */ printfPQExpBuffer(&buf, "SELECT a.attname,"); *************** *** 932,938 **** } /* Set the number of columns, and their names */ ! cols = 2; headers[0] = gettext_noop("Column"); headers[1] = gettext_noop("Type"); --- 962,968 ---- } /* Set the number of columns, and their names */ ! cols += 2; headers[0] = gettext_noop("Column"); headers[1] = gettext_noop("Type"); *************** *** 943,948 **** --- 973,981 ---- modifiers = pg_malloc_zero((numrows + 1) * sizeof(*modifiers)); } + if (tableinfo.relkind == 'S') + headers[cols++] = gettext_noop("Value"); + if (verbose) { headers[cols++] = gettext_noop("Storage"); *************** *** 980,986 **** /* Type */ printTableAddCell(&cont, PQgetvalue(res, i, 1), false); ! /* Extra: not null and default */ if (show_modifiers) { --- 1013,1023 ---- /* Type */ printTableAddCell(&cont, PQgetvalue(res, i, 1), false); ! ! /* A special 'Value' column for sequences */ ! if (tableinfo.relkind == 'S') ! printTableAddCell(&cont, seq_values[i], false); ! /* Extra: not null and default */ if (show_modifiers) { *************** *** 1543,1549 **** termPQExpBuffer(&buf); termPQExpBuffer(&title); termPQExpBuffer(&tmpbuf); ! if (show_modifiers) { for (ptr = modifiers; *ptr; ptr++) --- 1580,1593 ---- termPQExpBuffer(&buf); termPQExpBuffer(&title); termPQExpBuffer(&tmpbuf); ! ! if (tableinfo.relkind == 'S') ! { ! for (ptr = seq_values; *ptr; ptr++) ! free(*ptr); ! free(seq_values); ! } ! if (show_modifiers) { for (ptr = modifiers; *ptr; ptr++)
Bruce Momjian <bruce@momjian.us> writes: > Wow. I adjusted the patch slightly and applied it; the updated version > is attached. We have been waiting for this to be done for quite some > time. Thanks. Hmm ... I don't think that this patch actually addresses the TODO item. The TODO item seems to have originated here http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1220;r2=1.1221;f=h in response to this question on pgsql-novice: > How can I list all the sequences in the database, with their > attributes (such as last_value)? (I'm having a hard time guessing > 'seq-name'; the 'A_id_seq' formula did not work.) http://archives.postgresql.org/pgsql-novice/2004-02/msg00148.php This applied-with-little-discussion patch only shows the sequence values if you do a \d on a specific sequence, or \d on a wildcard that happens to include some sequences (and probably a lot of other stuff too, causing the resulting display to be far too long to be useful). My interpretation of the TODO item has always been that we should improve \ds to include all the useful information in a format that requires only one line per sequence. The reason it has remained undone for four years is that that's hard given the existing catalog representation of sequences and the constraints of describe.c's implementation. (I recall at least one failed patch that tried to do this, though I can't find it in the archives right now.) I find the present patch to be pretty useless: it's not a material advance over doing "select * from sequence-name". I think it should be reverted and the TODO item reinstated --- perhaps with more detail about what the item really is requesting. regards, tom lane
Sorry for the delay on this.
What I did was to mark the simple TODO items as done and add an
additional TODO item to list all sequence settings:
D o Have psql show current values for a sequenceo Have psql \ds show all sequences and their settings *
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00916.php
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Wow. I adjusted the patch slightly and applied it; the updated version
> > is attached. We have been waiting for this to be done for quite some
> > time. Thanks.
>
> Hmm ... I don't think that this patch actually addresses the TODO item.
> The TODO item seems to have originated here
> http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1220;r2=1.1221;f=h
> in response to this question on pgsql-novice:
>
> > How can I list all the sequences in the database, with their
> > attributes (such as last_value)? (I'm having a hard time guessing
> > 'seq-name'; the 'A_id_seq' formula did not work.)
> http://archives.postgresql.org/pgsql-novice/2004-02/msg00148.php
>
> This applied-with-little-discussion patch only shows the sequence
> values if you do a \d on a specific sequence, or \d on a wildcard
> that happens to include some sequences (and probably a lot of other
> stuff too, causing the resulting display to be far too long to be
> useful).
>
> My interpretation of the TODO item has always been that we should
> improve \ds to include all the useful information in a format that
> requires only one line per sequence. The reason it has remained
> undone for four years is that that's hard given the existing catalog
> representation of sequences and the constraints of describe.c's
> implementation. (I recall at least one failed patch that tried to
> do this, though I can't find it in the archives right now.)
>
> I find the present patch to be pretty useless: it's not a material
> advance over doing "select * from sequence-name". I think it should
> be reverted and the TODO item reinstated --- perhaps with more detail
> about what the item really is requesting.
>
> regards, tom lane
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +