Show method of index

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.

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.

Hola lista:
Estoy intentando implementar un entorno de producción con PostgreSQL utilizando Cybercluster, esta herramienta cuenta con un servicio de balance de carga pglb basado en pgPool, tengo el siguiente problema:
En el archivo de configuración del balanceador pglb.conf aparece la opción <Use_Connection_Pooling>yes/no</ Use_Connection_Pooling>, mi pregunta es a cuantas conexiones él le hace pooling, en pgPool estos parámetros son configurables:

num_init_children
child_max_connections
max_pool

pero en el el pglb no aparece manera de configurar estos parámetros sin embargo en el código fuente del pglb sí se tienen en cuenta e incluso aparentemente referencian a archivos de configuración del pgPool:

en pglb.h

/* configuration file name */
#define POOL_CONF_FILE_NAME "pgpool.conf"
#define HBA_CONF_FILE_NAME "pool_hba.conf"

Ahora, estos ficheros no están en el sistema en ninguna parte, como puedo configurar estos parámetros??

Gracias de antemano.
Eddy
Linux Registered User #411078

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@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.

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@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 | 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@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.

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@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