Thread: Show method of index
Hi,<br /><br /> I think that can be useful the command \di on psql show the method of index (hash, btree, ...) like:<br/><br /> test=# \di<br /> List of relations<br /> Schema | Name | Type | Owner | Table | Method <br /> --------+---------------+-------+------------+--------+--------<br /> public| test_id_idx | index | postgresql | table1 | btree<br /> public | test_name_idx | index | postgresql | table1| hash<br /> (2 rows)<br /> <br /> Or maybe only on \di+.<br /> <br /> test=# \di+ test_id_idx<br /> List of relations<br /> Schema | Name | Type | Owner | Table |Method | Size | Description <br /> --------+-------------+-------+------------+--------+--------+------------+-------------<br/> public | test_id_idx |index | postgresql | table1 | btree | 8192 bytes | <br /> (1 row)<br /><br /> If they wanna, i can submit the patchof this small change of psql.<br clear="all" /><br />-- <br />Ricardo Bessa<br />
Ricardo Bessa escribió: > Hi, > > I think that can be useful the command \di on psql show the method of > index (hash, btree, ...) like: > > test=# \di > List of relations > Schema | Name | Type | Owner | Table | Method > --------+---------------+-------+------------+--------+-------- > public | test_id_idx | index | postgresql | table1 | btree > public | test_name_idx | index | postgresql | table1 | hash > (2 rows) Well, you can see that with \d on the table, but IMHO this should be present on \di too, so +1. One gripe I had with \d and indexes the other day is that it sucks on functional indexes -- it just says "pg_expression_1". -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> Hi, > I think that can be useful the command \di on psql show the method of > index (hash, btree, ...) like: > test=# \di > List of relations > Schema | Name | Type | Owner | Table | Method > --------+---------------+-------+------------+--------+-------- > public | test_id_idx | index | postgresql | table1 | btree > public | test_name_idx | index | postgresql | table1 | hash > (2 rows) Attached is a simple patch which adds the above mentioned functionality to psql. Regards, Khee Chin.
Attachment
Khee Chin escribió: > > Hi, > > > I think that can be useful the command \di on psql show the method of > > index (hash, btree, ...) like: > > > test=# \di > > List of relations > > Schema | Name | Type | Owner | Table | Method > > --------+---------------+-------+------------+--------+-------- > > public | test_id_idx | index | postgresql | table1 | btree > > public | test_name_idx | index | postgresql | table1 | hash > > (2 rows) > > Attached is a simple patch which adds the above mentioned functionality to psql. Please add it to wiki.postgresql.org/wiki/CommitFestInProgress -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> > Please add it to wiki.postgresql.org/wiki/CommitFestInProgress > Submitted under http://wiki.postgresql.org/wiki/CommitFest_2009-First#Clients Regards, Khee Chin.
On Fri, May 08, 2009 at 04:43:44PM -0400, Alvaro Herrera wrote:
> Ricardo Bessa escribió:
> > Hi,
> >
> > I think that can be useful the command \di on psql show the method of
> > index (hash, btree, ...) like:
> >
> > test=# \di
> > List of relations
> > Schema | Name | Type | Owner | Table | Method
> > --------+---------------+-------+------------+--------+--------
> > public | test_id_idx | index | postgresql | table1 | btree
> > public | test_name_idx | index | postgresql | table1 | hash
> > (2 rows)
>
> Well, you can see that with \d on the table, but IMHO this should be
> present on \di too, so +1.
>
> One gripe I had with \d and indexes the other day is that it sucks
> on functional indexes -- it just says "pg_expression_1".
So after a little wrong-tree-up-barking, I grepped the source tree for
pg_expression, and it turns out that the fault lies not in psql, but
in src/backend/catalog/index.c's ConstructTupleDescriptor, which
automatically names the with this, um, somewhat uninformative name. I
see this comment just above the offending code:
/* * Make the attribute's name "pg_expresssion_nnn" (maybe * think of *
somethingbetter later) */
Any ideas for a better naming convention?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Fri, May 08, 2009 at 04:43:44PM -0400, Alvaro Herrera wrote:
>> One gripe I had with \d and indexes the other day is that it sucks
>> on functional indexes -- it just says "pg_expression_1".
> Any ideas for a better naming convention?
Changing the naming convention seems rather pointless --- I imagine what
Alvaro was wishing for was that \d would actually show the indexed
expression(s).
regards, tom lane
Tom Lane escribió: > David Fetter <david@fetter.org> writes: > > On Fri, May 08, 2009 at 04:43:44PM -0400, Alvaro Herrera wrote: > >> One gripe I had with \d and indexes the other day is that it sucks > >> on functional indexes -- it just says "pg_expression_1". > > > Any ideas for a better naming convention? > > Changing the naming convention seems rather pointless --- I imagine what > Alvaro was wishing for was that \d would actually show the indexed > expression(s). Yeah. (I note that the expressions are already shown as footers when you display the table instead of the index. It seems like the \d code for indexes did not get updated when that new code was added.) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sun, May 10, 2009 at 3:59 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Yeah. (I note that the expressions are already shown as footers when
> you display the table instead of the index. It seems like the \d code
> for indexes did not get updated when that new code was added.)
Made some changes to the patch to show expressions. Would appreciate
any comments as I am still fairly new to the pg codebase.
---
postgres=# CREATE TABLE foo(a bigserial, b text, PRIMARY KEY (a,b));
NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
serial column "foo.a"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# \div
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
am.amname as "Method",
COALESCE(pg_get_expr(i.indexprs,i.indrelid),pg_get_indexdef(i.indexrelid,-999,TRUE))
as "Expression"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('v','i','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Table | Method | Expression
--------+-------------------+-------+-------+-------+--------+---------------
public | foo_pkey | index | rubik | foo | btree | a, b
public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b))
public | idx_foo_hash | index | rubik | foo | hash | a
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(4 rows)
postgres=# \di idx_foo_hash_func
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table",
am.amname as "Method",
COALESCE(pg_get_expr(i.indexprs,i.indrelid),pg_get_indexdef(i.indexrelid,-999,TRUE))
as "Expression"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('i','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname ~ '^(idx_foo_hash_func)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner | Table | Method | Expression
--------+-------------------+-------+-------+-------+--------+---------------
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(1 row)
postgres=#
---
Regards,
Khee Chin.
Attachment
Updated with an additional line in the comments for get_indexdef * if colno == -999, we only want the name of the variables that make up the index Apologies for leaving this out in my earlier post. Regards, Khee Chin.
Attachment
Khee Chin escribió: > Updated with an additional line in the comments for get_indexdef > > * if colno == -999, we only want the name of the variables that > make up the index I don't think this hack is going to fly. I suggest you need to find some other way to implement this. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Khee Chin escribi�:
>> Updated with an additional line in the comments for get_indexdef
>>
>> * if colno == -999, we only want the name of the variables that
>> make up the index
> I don't think this hack is going to fly.
Yeah ... if it were local in describe.c that would be one thing, but
putting such a kluge in a public function API is pretty icky.
I think the proposed patch is doing pretty much the wrong thing anyhow.
As I understood it, the request was *not* to add a column to \di (which
would likely make it too wide to be readable, and would look rather
silly in a mixed-indexes-and-tables listing too). The idea was to add
a column to \d for an index, ie given something like
CREATE INDEX fooi ON foo (f1, (f2+f3))
then "\d fooi" would give
Index "public.fooi" Column | Type | Definition
-----------------+---------+------------f1 | integer | f1pg_expression_2 | integer | (f2+f3)
which you could do straight off with the existing behavior of
pg_get_indexdef().
BTW, if we're going to have a different columnset for \d on indexes,
it seems like it would be a good idea to include the opclass name too,
at least in \d+.
regards, tom lane
On Tue, May 12, 2009 at 12:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Index "public.fooi" > Column | Type | Definition > -----------------+---------+------------ > f1 | integer | f1 > pg_expression_2 | integer | (f2+f3) Is there any reason to expose "pg_expression_2" to the user at all? It's not like they can make use of it in any public interface. I would think we could just put the expression directly in the "Column" column. -- greg
Greg Stark <stark@enterprisedb.com> writes:
> On Tue, May 12, 2009 at 12:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>> Index "public.fooi"
>> Column | Type | Definition
>> -----------------+---------+------------
>> f1 | integer | f1
>> pg_expression_2 | integer | (f2+f3)
> Is there any reason to expose "pg_expression_2" to the user at all?
Perhaps not, but if they did have a reason to access the individual
index column then they'd need to know its name. I admit that there
may not be any such reason at present, but do you want to find us
having to change the definition back again sometime in the future?
regards, tom lane
>> On Tue, May 12, 2009 at 12:20 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> Index "public.fooi"
>>> Column | Type | Definition
>>> -----------------+---------+------------
>>> f1 | integer | f1
>>> pg_expression_2 | integer | (f2+f3)
>
Hi,
I'd agree that the mucking around with rulesutil is unorthodox.
Attached is a patch which does the above only modifying, describe . A
prerequisite for column expressions to show is 8.4, as it makes use of
array_agg, in pre 8.4-servers, it uses
pg_get_indexdef(i.indexrelid,0,TRUE)), which I am still unsure whether
we'd want as it stretches the output of \di extremely wide.
- Modifies \di and \d output for indexes
The output whilst connected to a 8.4 server and 8.3 server is as attached,
psql (8.4beta1)
Type "help" for help.
postgres=# CREATE TABLE foo(a int, b text);
CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE TABLE
postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING
btree(md5(a::text), md5(a||b));
CREATE INDEX
postgres=# \div;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-------------------+-------+-------+-------+--------+-------------------------------
public | idx_foo_bt_ab | index | rubik | foo | btree | a,b
public | idx_foo_bt_fooi | index | rubik | foo | btree |
md5((a)::text), md5((a || b))
public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b))
public | idx_foo_hash | index | rubik | foo | hash | a
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(5 rows)
postgres=# \di idx_foo_bt_ab;
List of relations
Schema | Name | Type | Owner | Table | Method | Definition
--------+---------------+-------+-------+-------+--------+------------
public | idx_foo_bt_ab | index | rubik | foo | btree | a,b
(1 row)
postgres=# \di idx_foo_bt_fooi;
List of relations
Schema | Name | Type | Owner | Table | Method | Definition
--------+-----------------+-------+-------+-------+--------+-------------------------------
public | idx_foo_bt_fooi | index | rubik | foo | btree |
md5((a)::text), md5((a || b))
(1 row)
postgres=#
psql (8.4beta1, server 8.3.6)
WARNING: psql version 8.4, server version 8.3.
Some psql features might not work.
Type "help" for help.
postgres=# CREATE TABLE foo(a int, b text);
CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX idx_foo_bt_fooi ON foo USING btree(md5(a::text), md5(a||b));
\div;
\di idx_foo_bt_ab;
\di idx_foo_bt_fooi;
CREATE TABLE
postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING
btree(md5(a::text), md5(a||b));
CREATE INDEX
postgres=# \div;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-------------------+-------+----------+-------+--------+------------------------------------------------------
public | idx_foo_bt_ab | index | postgres | foo | btree |
CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b)
public | idx_foo_bt_fooi | index | postgres | foo | btree |
md5((a)::text), md5((a || b))
public | idx_foo_bt_func | index | postgres | foo | btree | md5((a || b))
public | idx_foo_hash | index | postgres | foo | hash |
CREATE INDEX idx_foo_hash ON foo USING hash (a)
public | idx_foo_hash_func | index | postgres | foo | hash | md5((b || a))
(5 rows)
postgres=# \di idx_foo_bt_ab;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+---------------+-------+----------+-------+--------+------------------------------------------------------
public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE
INDEX idx_foo_bt_ab ON foo USING btree (a, b)
(1 row)
postgres=# \di idx_foo_bt_fooi;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-----------------+-------+----------+-------+--------+-------------------------------
public | idx_foo_bt_fooi | index | postgres | foo | btree |
md5((a)::text), md5((a || b))
(1 row)
postgres=#
Regards,
Khee Chin.
Attachment
My sincere apologies for flooding your mailboxes once again, as the
patch attached in the previous post was incorrect. Also, I had failed
to show test-cases of \d <index> in both 8.4 and 8.3 servers.
Attached are the test cases for psql connecting to 8.4 and 8.3.
psql (8.4beta1)
Type "help" for help.
postgres=# CREATE TABLE foo(a int, b text);
CREATE TABLE
postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING
btree(md5(a::text), md5(a||b));
CREATE INDEX
postgres=#
postgres=# \div
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-------------------+-------+-------+-------+--------+-------------------------------
public | idx_foo_bt_ab | index | rubik | foo | btree | a,b
public | idx_foo_bt_fooi | index | rubik | foo | btree |
md5((a)::text), md5((a || b))
public | idx_foo_bt_func | index | rubik | foo | btree | md5((a || b))
public | idx_foo_hash | index | rubik | foo | hash | a
public | idx_foo_hash_func | index | rubik | foo | hash | md5((b || a))
(5 rows)
postgres=# \di idx_foo_bt_ab;
List of relations
Schema | Name | Type | Owner | Table | Method | Definition
--------+---------------+-------+-------+-------+--------+------------
public | idx_foo_bt_ab | index | rubik | foo | btree | a,b
(1 row)
postgres=# \d idx_foo_bt_ab;
Index "public.idx_foo_bt_ab"
Column | Type | Definition
--------+---------+------------
a | integer | a
b | text | b
btree, for table "public.foo"
postgres=# \di idx_foo_bt_fooi;
List of relations
Schema | Name | Type | Owner | Table | Method | Definition
--------+-----------------+-------+-------+-------+--------+-------------------------------
public | idx_foo_bt_fooi | index | rubik | foo | btree |
md5((a)::text), md5((a || b))
(1 row)
postgres=# \d idx_foo_bt_fooi;
Index "public.idx_foo_bt_fooi"
Column | Type | Definition
-----------------+------+--------------
pg_expression_1 | text | md5(a::text)
pg_expression_2 | text | md5(a || b)
btree, for table "public.foo"
postgres=#
psql (8.4beta1, server 8.3.6)
WARNING: psql version 8.4, server version 8.3.
Some psql features might not work.
Type "help" for help.
postgres=# CREATE TABLE foo(a int, b text);
CREATE TABLE
postgres=# CREATE INDEX idx_foo_bt_ab ON foo USING btree(a,b);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash ON foo USING hash(a);
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_func ON foo USING btree(md5(a||b));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_hash_func ON foo USING hash(md5(b||a));
CREATE INDEX
postgres=# CREATE INDEX idx_foo_bt_fooi ON foo USING
btree(md5(a::text), md5(a||b));
CREATE INDEX
postgres=# \div;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-------------------+-------+----------+-------+--------+------------------------------------------------------
public | idx_foo_bt_ab | index | postgres | foo | btree |
CREATE INDEX idx_foo_bt_ab ON foo USING btree (a, b)
public | idx_foo_bt_fooi | index | postgres | foo | btree |
md5((a)::text), md5((a || b))
public | idx_foo_bt_func | index | postgres | foo | btree | md5((a || b))
public | idx_foo_hash | index | postgres | foo | hash |
CREATE INDEX idx_foo_hash ON foo USING hash (a)
public | idx_foo_hash_func | index | postgres | foo | hash | md5((b || a))
(5 rows)
postgres=# \di idx_foo_bt_ab;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+---------------+-------+----------+-------+--------+------------------------------------------------------
public | idx_foo_bt_ab | index | postgres | foo | btree | CREATE
INDEX idx_foo_bt_ab ON foo USING btree (a, b)
(1 row)
postgres=# \d idx_foo_bt_ab;
Index "public.idx_foo_bt_ab"
Column | Type | Definition
--------+---------+------------
a | integer | a
b | text | b
btree, for table "public.foo"
postgres=# \di idx_foo_bt_fooi;
List of relations
Schema | Name | Type | Owner | Table | Method |
Definition
--------+-----------------+-------+----------+-------+--------+-------------------------------
public | idx_foo_bt_fooi | index | postgres | foo | btree |
md5((a)::text), md5((a || b))
(1 row)
postgres=# \d idx_foo_bt_fooi;
Index "public.idx_foo_bt_fooi"
Column | Type | Definition
-----------------+------+--------------
pg_expression_1 | text | md5(a::text)
pg_expression_2 | text | md5(a || b)
btree, for table "public.foo"
postgres=#
--
Regards,
Khee Chin.
Attachment
Khee Chin <kheechin@gmail.com> writes:
> My sincere apologies for flooding your mailboxes once again, as the
> patch attached in the previous post was incorrect. Also, I had failed
> to show test-cases of \d <index> in both 8.4 and 8.3 servers.
This is still modifying the behavior of \di, which I thought was not
wanted.
regards, tom lane
On May 8, 2009, at 3:43 PM, Alvaro Herrera wrote: > Well, you can see that with \d on the table, but IMHO this should be > present on \di too, so +1. > > One gripe I had with \d and indexes the other day is that it sucks on > functional indexes -- it just says "pg_expression_1". The gripe I have with \d is that the "footnotes" are very hard to scan through once you have more than a few things on a table. What I'd like to see is a version that provides the same information, but in a tabular output. Thoughts? I don't have time to submit a patch for this, but I could probably get CashNetUSA to pay to have it done. :) -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
decibel <decibel@decibel.org> writes:
> The gripe I have with \d is that the "footnotes" are very hard to
> scan through once you have more than a few things on a table. What
> I'd like to see is a version that provides the same information, but
> in a tabular output.
Hmm, I'm not visualizing what you have in mind that would be better?
The difficulty with the "footnotes" is exactly that the information
isn't very tabular ...
regards, tom lane
On May 18, 2009, at 10:25 PM, Tom Lane wrote: > decibel <decibel@decibel.org> writes: >> The gripe I have with \d is that the "footnotes" are very hard to >> scan through once you have more than a few things on a table. What >> I'd like to see is a version that provides the same information, but >> in a tabular output. > > Hmm, I'm not visualizing what you have in mind that would be better? > The difficulty with the "footnotes" is exactly that the information > isn't very tabular ... Instead of... Indexes: "debit_cards_pkey" PRIMARY KEY, btree (payment_instrument_id) Check constraints: "debit_cards__payment_instrument_type_id_must_equal_1" CHECK (payment_instrument_type_id = 1) Foreign-key constraints: "debit_cards_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) "debit_cards_payment_instrument_status_id_fkey" FOREIGN KEY (payment_instrument_status_id) REFERENCES payment_instruments.payment_instrument_statuses(id) "debit_cards_payment_instrument_type_id_fkey" FOREIGN KEY (payment_instrument_type_id) REFERENCES payment_instruments.payment_instrument_types(id) Triggers: debit_cards__deny_delete BEFORE DELETE ON payment_instruments.debit_cards FOR EACH STATEMENT EXECUTE PROCEDURE tools.tg_disallow() debit_cards__dupe_id BEFORE INSERT OR UPDATE ON payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE payment_instruments.tg_payment_instruments_unique() payment_instrument_status_history AFTER INSERT OR UPDATE ON payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE payment_instruments.tg_payment_instrument_status_history() Inherits: payment_instruments Something more like... Inherits: payment_instruments Indexes: Name | Options | Method | Columns ------------------+---------+--------+--------------------------- debit_cards_pkey | PRIMARY | btree | payment_instrument_id,... Check constraints: Name | Constraint ------------------------------------------------------ +------------------------------- debit_cards__payment_instrument_type_id_must_equal_1 | payment_instrument_type_id = 1 Foreign-key constraints: Name | Key Fields | Schema | Table | Foreign Keys ----------------------------------------------- +------------------------------+--------------------- +-----------------------------+-------------- debit_cards_customer_id_fkey | customer_id | public | customers | id debit_cards_payment_instrument_status_id_fkey | payment_instrument_status_id | payment_instruments | payment_instrument_statuses | id debit_cards_payment_instrument_type_id_fkey | payment_instrument_type_id | payment_instruments | payment_instrument_types | id Triggers: Name | When | DIU | Level | Schema | Function -----------------------------------+--------+-----+----------- +---------------------+--------------------------------------- debit_cards__deny_delete | BEFORE | D | STATEMENT| tools | tg_disallow() debit_cards__dupe_id | BEFORE | I | ROW | payment_instruments | tg_payment_instruments_unique() payment_instrument_status_history | AFTER | IU | ROW | payment_instruments | tg_payment_instrument_status_history() This format is a bit longer, but I think it makes it much easier to find information, especially on tables that have a lot of footnotes. It might also be nice to have a command that just shows the options on a table, and one that just shows the table columns... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On May 19, 2009, at 9:41 AM, decibel <decibel@decibel.org> wrote: > On May 18, 2009, at 10:25 PM, Tom Lane wrote: >> decibel <decibel@decibel.org> writes: >>> The gripe I have with \d is that the "footnotes" are very hard to >>> scan through once you have more than a few things on a table. What >>> I'd like to see is a version that provides the same information, but >>> in a tabular output. >> >> Hmm, I'm not visualizing what you have in mind that would be better? >> The difficulty with the "footnotes" is exactly that the information >> isn't very tabular ... > > Instead of... > > Indexes: > "debit_cards_pkey" PRIMARY KEY, btree (payment_instrument_id) > Check constraints: > "debit_cards__payment_instrument_type_id_must_equal_1" CHECK > (payment_instrument_type_id = 1) > Foreign-key constraints: > "debit_cards_customer_id_fkey" FOREIGN KEY (customer_id) > REFERENCES customers(id) > "debit_cards_payment_instrument_status_id_fkey" FOREIGN KEY > (payment_instrument_status_id) REFERENCES > payment_instruments.payment_instrument_statuses(id) > "debit_cards_payment_instrument_type_id_fkey" FOREIGN KEY > (payment_instrument_type_id) REFERENCES > payment_instruments.payment_instrument_types(id) > Triggers: > debit_cards__deny_delete BEFORE DELETE ON > payment_instruments.debit_cards FOR EACH STATEMENT EXECUTE PROCEDURE > tools.tg_disallow() > debit_cards__dupe_id BEFORE INSERT OR UPDATE ON > payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE > payment_instruments.tg_payment_instruments_unique() > payment_instrument_status_history AFTER INSERT OR UPDATE ON > payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE > payment_instruments.tg_payment_instrument_status_history() > Inherits: payment_instruments > > Something more like... > > Inherits: payment_instruments > > Indexes: > Name | Options | Method | Columns > ------------------+---------+--------+--------------------------- > debit_cards_pkey | PRIMARY | btree | payment_instrument_id, ... > > Check constraints: > Name | > Constraint > ------------------------------------------------------ > +------------------------------- > debit_cards__payment_instrument_type_id_must_equal_1 | > payment_instrument_type_id = 1 > > Foreign-key constraints: > Name | Key > Fields | Schema | > Table | Foreign Keys > ----------------------------------------------- > +------------------------------+--------------------- > +-----------------------------+-------------- > debit_cards_customer_id_fkey | > customer_id | public | > customers | id > debit_cards_payment_instrument_status_id_fkey | > payment_instrument_status_id | payment_instruments | > payment_instrument_statuses | id > debit_cards_payment_instrument_type_id_fkey | > payment_instrument_type_id | payment_instruments | > payment_instrument_types | id > > Triggers: > Name | When | DIU | Level | > Schema | Function > -----------------------------------+--------+-----+----------- > +---------------------+--------------------------------------- > debit_cards__deny_delete | BEFORE | D | STATEMENT | > tools | tg_disallow() > debit_cards__dupe_id | BEFORE | I | ROW | > payment_instruments | tg_payment_instruments_unique() > payment_instrument_status_history | AFTER | IU | ROW | > payment_instruments | tg_payment_instrument_status_history() > > This format is a bit longer, but I think it makes it much easier to > find information, especially on tables that have a lot of footnotes. > > It might also be nice to have a command that just shows the options > on a table, and one that just shows the table columns... Yes, please! Many of my tables have as many footnotes as they do columns, and it's really annoying when you just want the columns. But what should the syntax be? I like your other idea too, though it should be an optional behavior, I think. ...Robert > > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
One advantage of the current arrangement is that the constraints and triggers are almost (though not quite) in the same form as the command to create them. It would be sad to lose that competely. Is there any room for a compromise? Something that just reduces the clutter incrementally instead of completely reorganizing it? Are there any commonalities between footnotes that could be elided if they were grouped together differently? -- Greg On 19 May 2009, at 09:41, decibel <decibel@decibel.org> wrote: > On May 18, 2009, at 10:25 PM, Tom Lane wrote: >> decibel <decibel@decibel.org> writes: >>> The gripe I have with \d is that the "footnotes" are very hard to >>> scan through once you have more than a few things on a table. What >>> I'd like to see is a version that provides the same information, but >>> in a tabular output. >> >> Hmm, I'm not visualizing what you have in mind that would be better? >> The difficulty with the "footnotes" is exactly that the information >> isn't very tabular ... > > Instead of... > > Indexes: > "debit_cards_pkey" PRIMARY KEY, btree (payment_instrument_id) > Check constraints: > "debit_cards__payment_instrument_type_id_must_equal_1" CHECK > (payment_instrument_type_id = 1) > Foreign-key constraints: > "debit_cards_customer_id_fkey" FOREIGN KEY (customer_id) > REFERENCES customers(id) > "debit_cards_payment_instrument_status_id_fkey" FOREIGN KEY > (payment_instrument_status_id) REFERENCES > payment_instruments.payment_instrument_statuses(id) > "debit_cards_payment_instrument_type_id_fkey" FOREIGN KEY > (payment_instrument_type_id) REFERENCES > payment_instruments.payment_instrument_types(id) > Triggers: > debit_cards__deny_delete BEFORE DELETE ON > payment_instruments.debit_cards FOR EACH STATEMENT EXECUTE PROCEDURE > tools.tg_disallow() > debit_cards__dupe_id BEFORE INSERT OR UPDATE ON > payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE > payment_instruments.tg_payment_instruments_unique() > payment_instrument_status_history AFTER INSERT OR UPDATE ON > payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE > payment_instruments.tg_payment_instrument_status_history() > Inherits: payment_instruments > > Something more like... > > Inherits: payment_instruments > > Indexes: > Name | Options | Method | Columns > ------------------+---------+--------+--------------------------- > debit_cards_pkey | PRIMARY | btree | payment_instrument_id, ... > > Check constraints: > Name | > Constraint > ------------------------------------------------------ > +------------------------------- > debit_cards__payment_instrument_type_id_must_equal_1 | > payment_instrument_type_id = 1 > > Foreign-key constraints: > Name | Key > Fields | Schema | > Table | Foreign Keys > ----------------------------------------------- > +------------------------------+--------------------- > +-----------------------------+-------------- > debit_cards_customer_id_fkey | > customer_id | public | > customers | id > debit_cards_payment_instrument_status_id_fkey | > payment_instrument_status_id | payment_instruments | > payment_instrument_statuses | id > debit_cards_payment_instrument_type_id_fkey | > payment_instrument_type_id | payment_instruments | > payment_instrument_types | id > > Triggers: > Name | When | DIU | Level | > Schema | Function > -----------------------------------+--------+-----+----------- > +---------------------+--------------------------------------- > debit_cards__deny_delete | BEFORE | D | STATEMENT | > tools | tg_disallow() > debit_cards__dupe_id | BEFORE | I | ROW | > payment_instruments | tg_payment_instruments_unique() > payment_instrument_status_history | AFTER | IU | ROW | > payment_instruments | tg_payment_instrument_status_history() > > This format is a bit longer, but I think it makes it much easier to > find information, especially on tables that have a lot of footnotes. > > It might also be nice to have a command that just shows the options > on a table, and one that just shows the table columns... > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
On May 19, 2009, at 10:02 AM, Greg Stark <greg.stark@enterprisedb.com> wrote: > One advantage of the current arrangement is that the constraints and > triggers are almost (though not quite) in the same form as the > command to create them. It would be sad to lose that competely. Agreed. What I most often want to do is either (a) suppress all the footnotes or (b) get just the footnotes of type X and nothing else (not even the columns). But I think the tabular output is a good *option* for the second of these. I don't think I'd favor making it the ONLY option. ...Robert > > > Is there any room for a compromise? Something that just reduces the > clutter incrementally instead of completely reorganizing it? Are > there any commonalities between footnotes that could be elided if > they were grouped together differently? > > > -- > Greg > > > On 19 May 2009, at 09:41, decibel <decibel@decibel.org> wrote: > >> On May 18, 2009, at 10:25 PM, Tom Lane wrote: >>> decibel <decibel@decibel.org> writes: >>>> The gripe I have with \d is that the "footnotes" are very hard to >>>> scan through once you have more than a few things on a table. What >>>> I'd like to see is a version that provides the same information, >>>> but >>>> in a tabular output. >>> >>> Hmm, I'm not visualizing what you have in mind that would be better? >>> The difficulty with the "footnotes" is exactly that the information >>> isn't very tabular ... >> >> Instead of... >> >> Indexes: >> "debit_cards_pkey" PRIMARY KEY, btree (payment_instrument_id) >> Check constraints: >> "debit_cards__payment_instrument_type_id_must_equal_1" CHECK >> (payment_instrument_type_id = 1) >> Foreign-key constraints: >> "debit_cards_customer_id_fkey" FOREIGN KEY (customer_id) >> REFERENCES customers(id) >> "debit_cards_payment_instrument_status_id_fkey" FOREIGN KEY >> (payment_instrument_status_id) REFERENCES >> payment_instruments.payment_instrument_statuses(id) >> "debit_cards_payment_instrument_type_id_fkey" FOREIGN KEY >> (payment_instrument_type_id) REFERENCES >> payment_instruments.payment_instrument_types(id) >> Triggers: >> debit_cards__deny_delete BEFORE DELETE ON >> payment_instruments.debit_cards FOR EACH STATEMENT EXECUTE >> PROCEDURE tools.tg_disallow() >> debit_cards__dupe_id BEFORE INSERT OR UPDATE ON >> payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE >> payment_instruments.tg_payment_instruments_unique() >> payment_instrument_status_history AFTER INSERT OR UPDATE ON >> payment_instruments.debit_cards FOR EACH ROW EXECUTE PROCEDURE >> payment_instruments.tg_payment_instrument_status_history() >> Inherits: payment_instruments >> >> Something more like... >> >> Inherits: payment_instruments >> >> Indexes: >> Name | Options | Method | Columns >> ------------------+---------+--------+--------------------------- >> debit_cards_pkey | PRIMARY | btree | payment_instrument_id, ... >> >> Check constraints: >> Name | >> Constraint >> ------------------------------------------------------ >> +------------------------------- >> debit_cards__payment_instrument_type_id_must_equal_1 | >> payment_instrument_type_id = 1 >> >> Foreign-key constraints: >> Name | Key >> Fields | Schema | >> Table | Foreign Keys >> ----------------------------------------------- >> +------------------------------+--------------------- >> +-----------------------------+-------------- >> debit_cards_customer_id_fkey | >> customer_id | public | >> customers | id >> debit_cards_payment_instrument_status_id_fkey | >> payment_instrument_status_id | payment_instruments | >> payment_instrument_statuses | id >> debit_cards_payment_instrument_type_id_fkey | >> payment_instrument_type_id | payment_instruments | >> payment_instrument_types | id >> >> Triggers: >> Name | When | DIU | Level | >> Schema | Function >> -----------------------------------+--------+-----+----------- >> +---------------------+--------------------------------------- >> debit_cards__deny_delete | BEFORE | D | STATEMENT | >> tools | tg_disallow() >> debit_cards__dupe_id | BEFORE | I | ROW | >> payment_instruments | tg_payment_instruments_unique() >> payment_instrument_status_history | AFTER | IU | ROW | >> payment_instruments | tg_payment_instrument_status_history() >> >> This format is a bit longer, but I think it makes it much easier to >> find information, especially on tables that have a lot of footnotes. >> >> It might also be nice to have a command that just shows the options >> on a table, and one that just shows the table columns... >> -- >> Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org >> Give your computer some brain candy! www.distributed.net Team #1828 >> >> >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
> On May 19, 2009, at 10:02 AM, Greg Stark <greg.stark@enterprisedb.com>
> wrote:
>> One advantage of the current arrangement is that the constraints and
>> triggers are almost (though not quite) in the same form as the
>> command to create them. It would be sad to lose that competely.
> Agreed.
+1 --- I *very* often find myself copying-and-pasting from \d output,
and the proposed tabular format would be a huge step backwards for
that. Personally I kinda wish that the column display were closer
to what CREATE TABLE wants ...
> What I most often want to do is either (a) suppress all the
> footnotes or (b) get just the footnotes of type X and nothing else
> (not even the columns).
+1 for a way to do that, too. But it seems pretty much orthogonal to
what the display format details are.
regards, tom lane
On May 19, 2009, at 10:27 AM, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On May 19, 2009, at 10:02 AM, Greg Stark >> <greg.stark@enterprisedb.com> >> wrote: >>> One advantage of the current arrangement is that the constraints and >>> triggers are almost (though not quite) in the same form as the >>> command to create them. It would be sad to lose that competely. > >> Agreed. > > +1 --- I *very* often find myself copying-and-pasting from \d output, > and the proposed tabular format would be a huge step backwards for > that. Personally I kinda wish that the column display were closer > to what CREATE TABLE wants ... Hmm.... what if we made the default to be all-tabular output, but had a different command that would spit out the SQL to re-create something? (I agree that the cut-and-paste ability is extremely handy and wouldn't want to remove it.) -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
decibel <decibel@decibel.org> writes:
> On May 19, 2009, at 10:27 AM, Tom Lane wrote:
>> +1 --- I *very* often find myself copying-and-pasting from \d output,
>> and the proposed tabular format would be a huge step backwards for
>> that. Personally I kinda wish that the column display were closer
>> to what CREATE TABLE wants ...
> Hmm.... what if we made the default to be all-tabular output, but had
> a different command that would spit out the SQL to re-create something?
Well, we already have something that's intended to recreate stuff;
it's called pg_dump. The issue from my point of view is trying to
reproduce problems based on what people post to the lists --- which
very often is \d output. It will not help me if there is some
nondefault variant of \d that emits clean SQL, because that won't be
what gets posted.
regards, tom lane
On May 19, 2009, at 11:31 AM, decibel <decibel@decibel.org> wrote: > On May 19, 2009, at 10:27 AM, Tom Lane wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>> On May 19, 2009, at 10:02 AM, Greg Stark <greg.stark@enterprisedb.com >>> > >>> wrote: >>>> One advantage of the current arrangement is that the constraints >>>> and >>>> triggers are almost (though not quite) in the same form as the >>>> command to create them. It would be sad to lose that competely. >> >>> Agreed. >> >> +1 --- I *very* often find myself copying-and-pasting from \d output, >> and the proposed tabular format would be a huge step backwards for >> that. Personally I kinda wish that the column display were closer >> to what CREATE TABLE wants ... > > Hmm.... what if we made the default to be all-tabular output, but > had a different command that would spit out the SQL to re-create > something? How 'bout we flip that around? :-) ...Robert > > > (I agree that the cut-and-paste ability is extremely handy and > wouldn't want to remove it.) > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > >
On May 19, 2009, at 10:52 AM, Robert Haas wrote: > How 'bout we flip that around? :-) +1 (BTW, I know there's pg_dump, but being able to get SQL out of psql is just a lot more convenient) -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
On Tuesday 12 May 2009 08:36:20 Khee Chin wrote: > postgres=# \di idx_foo_bt_fooi; > List of relations > Schema | Name | Type | Owner | Table | Method | > Definition > --------+-----------------+-------+-------+-------+--------+--------------- >---------------- public | idx_foo_bt_fooi | index | rubik | foo | btree | > md5((a)::text), md5((a || b)) > (1 row) > > postgres=# \d idx_foo_bt_fooi; > Index "public.idx_foo_bt_fooi" > Column | Type | Definition > -----------------+------+-------------- > pg_expression_1 | text | md5(a::text) > pg_expression_2 | text | md5(a || b) > btree, for table "public.foo" I have committed the second part, the additions to the \d output. I think the sentiment was against changing the \di output.