Re: Show method of index

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)

Or maybe only on \di+.

test=# \di+ test_id_idx
List of relations
Schema | Name | Type | Owner | Table | Method |
Size | Description

--------+-------------+-------+------------+--------+--------+------------+-------------
public | test_id_idx | index | postgresql | table1 | btree | 8192
bytes |
(1 row)

If they wanna, i can submit the patch of this small change of psql.

--
Ricardo Bessa

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.

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
* something better later)
*/

Any ideas for a better naming convention?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)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(at)fetter(dot)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(at)commandprompt(dot)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.

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.

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(at)commandprompt(dot)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 | f1
pg_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(at)sss(dot)pgh(dot)pa(dot)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(at)enterprisedb(dot)com> writes:
> On Tue, May 12, 2009 at 12:20 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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.

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.

Khee Chin <kheechin(at)gmail(dot)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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

decibel <decibel(at)decibel(dot)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(at)decibel(dot)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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On May 19, 2009, at 9:41 AM, decibel <decibel(at)decibel(dot)org> wrote:

> On May 18, 2009, at 10:25 PM, Tom Lane wrote:
>> decibel <decibel(at)decibel(dot)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(at)decibel(dot)org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)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(at)decibel(dot)org> wrote:

> On May 18, 2009, at 10:25 PM, Tom Lane wrote:
>> decibel <decibel(at)decibel(dot)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(at)decibel(dot)org
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)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(dot)stark(at)enterprisedb(dot)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(at)decibel(dot)org> wrote:
>
>> On May 18, 2009, at 10:25 PM, Tom Lane wrote:
>>> decibel <decibel(at)decibel(dot)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(at)decibel(dot)org
>> Give your computer some brain candy! www.distributed.net Team #1828
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On May 19, 2009, at 10:02 AM, Greg Stark <greg(dot)stark(at)enterprisedb(dot)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(at)gmail(dot)com> writes:
>> On May 19, 2009, at 10:02 AM, Greg Stark
>> <greg(dot)stark(at)enterprisedb(dot)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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

decibel <decibel(at)decibel(dot)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(at)decibel(dot)org> wrote:

> On May 19, 2009, at 10:27 AM, Tom Lane wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On May 19, 2009, at 10:02 AM, Greg Stark <greg(dot)stark(at)enterprisedb(dot)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(at)decibel(dot)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(at)decibel(dot)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.