Thread: 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
Attachment
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@commandprompt.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@ij.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