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/
Attachments:
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.
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. +
Attachments:
/rtmp/difftext/x-diffDownload
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 sequence
o 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. +