Re: [HACKERS] Quick patch: Display sequence owner
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick patch: Display sequence owner
Alvaro Herrera [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick patch: Display sequence owner
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick patch: Display sequence owner
On Tue, Dec 9, 2008 at 10:46 AM, Josh Williams [EMAIL PROTECTED] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick patch: Display sequence owner
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick patch: Display sequence owner
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick patch: Display sequence owner
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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
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));
922a923,939
printfPQExpBuffer(buf,
SELECT d.refobjid::regclass\n
FROM pg_catalog.pg_depend d\n
WHERE d.deptype = 'a' AND d.objid = '%s',
oid);
result = PSQLexec(buf.data, false);
if (!result)
goto error_return;
if (PQntuples(result))
seq_values[10] = pg_strdup(PQgetvalue(result, 0, 0));
else
seq_values[10] = ;
PQclear(result);
940c957,966
appendPQExpBuffer(buf, \nORDER BY a.attnum);
---
/* For sequences we'll 'fake' an additional column to show the owning relation */
if (tableinfo.relkind == 'S')
{
appendPQExpBuffer(buf, \nUNION SELECT 'owned_by', 'regclass', NULL, true, 11);
if (verbose)
appendPQExpBuffer(buf, , 'p', 'Owning relation');
appendPQExpBuffer(buf, \nORDER BY attnum);
}
else
appendPQExpBuffer(buf, \nORDER BY a.attnum);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
