Thread
Commits
-
Show opclass and opfamily related information in psql
- b0b5e20cd8d1 13.0 landed
-
Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2018-06-22T13:48:44Z
Hello! There are command in psql to list access methods, but there are no fast way to look detailed info about them. So here a patch with new commands: \dAp [PATTERN] list access methods with properties (Table pg_am) \dAf[+] [AMPTRN [OPFPTRN]] list operator families of access method. + prints owner of operator family. (Table pg_opfamily) \dAfp [AMPTRN [OPFPTRN]] list procedures of operator family related to access method (Table pg_amproc) \dAfo [AMPTRN [OPFPTRN]] list operators of family related to access method (Table pg_amop) \dAoc[+] [AMPTRN [OPCPTRN]] list operator classes of index access methods. + prints owner of operator class. (Table pg_opclass) \dip[S] [PATTERN] list indexes with properties (Table pg_class) \dicp[S] [IDXNAME [COLNAME]] show index column properties (Table pg_class) You can display information only on the access methods, specified by a template. You can also filter operator classes, operator families, or the name of the indexed column. I also have a question about testing commands \dAf+ and \dAoc+: is it good idea to test them by changing an owner of one operator family or class to created new one, checking the output, and restoring the owner back? Or we should create a new opclass or opfamily with proper owner. Or maybe it is not necesary to test these commands? Best regards, Sergey Cherkashin s.cherkashin@postgrespro.ru
-
Re: Psql patch to show access methods info
Nikita Glukhov <n.gluhov@postgrespro.ru> — 2018-06-25T22:26:18Z
On 22.06.2018 16:48, Sergey Cherkashin wrote: > Hello! > > There are command in psql to list access methods, but there are no fast > way to look detailed info about them. So here a patch with new > commands: Hi! I've done a preliminary in-company review of this patch several times. Here is my review of its first published version. > \dAp [PATTERN] list access methods with properties (Table > pg_am) * Should we rename it to \dAip and include "index" word into the table header? As you know, we are going to support table AMs in the future. > \dAf[+] [AMPTRN [OPFPTRN]] list operator families of access method. + > prints owner of operator family. (Table pg_opfamily) > \dAfp [AMPTRN [OPFPTRN]] list procedures of operator family related > to access method (Table pg_amproc) * Reorder "Left"/"Right" and "Strategy"/"Proc name" columns. * Include "Left"/"Right" columns into ORDER BY clause. * Show procedure's argument types, because procedure's name does not completely identify procedure (for example, in_range() is used in several opclasses with different signatures). Or maybe show arguments only if procedure name is not unique? > \dAfo [AMPTRN [OPFPTRN]] list operators of family related to access > method (Table pg_amop) * Reorder "Left"/"Right" and "Strategy"/"Operator" columns. * Include "Left"/"Right" columns into ORDER BY clause. * Operator's schema is shown only if operator is invisible for the current user -- I'm not sure if this is correct. > \dAoc[+] [AMPTRN [OPCPTRN]] list operator classes of index access > methods. + prints owner of operator class. (Table pg_opclass) * Maybe it would be better to show stored type only if it differs from the indexed type? > \dip[S] [PATTERN] list indexes with properties (Table > pg_class) > \dicp[S] [IDXNAME [COLNAME]] show index column properties (Table > pg_class) * Fix duplicate rows that appear in the table for composite indices. * Include "Column #" into ORDER BY clause. * Rename column "Null first" to "Nulls First" or "NULLS LAST". * Maybe it is not necessary to show "Access method" column here? * ASC, NULLS are shown as TRUE/FALSE only if the index is orderable, and as NULL if unorderable -- I'm not sure if this is correct. Maybe we should simply show these properties in the literal form, not as booleans (as strings 'ASC'/'DESC', 'NULLS FIRST'/'NULLS LAST')? * I think we should show column's properties in the separate table for each index, because it is not so easy to understand the combined table. The same, perhaps, can be applied to \dAfp and \dAfo commands. > I also have a question about testing commands \dAf+ and \dAoc+: is it > good idea to test them by changing an owner of one operator family or > class to created new one, checking the output, and restoring the owner > back? Or we should create a new opclass or opfamily with proper owner. > Or maybe it is not necesary to test these commands? -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2018-07-03T10:25:37Z
Following issues are solved: >> \dAf[+] [AMPTRN [OPFPTRN]] list operator families of access method. >> + >> prints owner of operator family. (Table pg_opfamily) > >> \dAfp [AMPTRN [OPFPTRN]] list procedures of operator family >> related >> to access method (Table pg_amproc) > > * Reorder "Left"/"Right" and "Strategy"/"Proc name" columns. > * Include "Left"/"Right" columns into ORDER BY clause. > * Show procedure's argument types, because procedure's name does not > completely > identify procedure (for example, in_range() is used in several > opclasses with > different signatures). Or maybe show arguments only if procedure > name is not > unique? > >> \dAfo [AMPTRN [OPFPTRN]] list operators of family related to >> access >> method (Table pg_amop) > > * Reorder "Left"/"Right" and "Strategy"/"Operator" columns. > * Include "Left"/"Right" columns into ORDER BY clause. >> \dAoc[+] [AMPTRN [OPCPTRN]] list operator classes of index access >> methods. + prints owner of operator class. (Table pg_opclass) > > * Maybe it would be better to show stored type only if it differs from > the > indexed type? > >> \dip[S] [PATTERN] list indexes with properties (Table >> pg_class) > >> \dicp[S] [IDXNAME [COLNAME]] show index column properties (Table >> pg_class) > > * Fix duplicate rows that appear in the table for composite indices. > * Include "Column #" into ORDER BY clause. > * Rename column "Null first" to "Nulls First" or "NULLS LAST". > * Maybe it is not necessary to show "Access method" column here? > * I think we should show column's properties in the separate table for > each > index, because it is not so easy to understand the combined table. Following issues require discussion: >> \dAp > * Should we rename it to \dAip and include "index" word into the table > header? > As you know, we are going to support table AMs in the future. >> \dAfo > * Operator's schema is shown only if operator is invisible for the > current > user -- I'm not sure if this is correct. \dAfo and \dAfp * Should we put info in separate table for each Operator family? >> \dicp > * ASC, NULLS are shown as TRUE/FALSE only if the index is orderable, > and as > NULL if unorderable -- I'm not sure if this is correct. Maybe we > should > simply show these properties in the literal form, not as booleans > (as strings 'ASC'/'DESC', 'NULLS FIRST'/'NULLS LAST')? > >> I also have a question about testing commands \dAf+ and \dAoc+: is it >> good idea to test them by changing an owner of one operator family or >> class to created new one, checking the output, and restoring the owner >> back? Or we should create a new opclass or opfamily with proper owner. >> Or maybe it is not necesary to test these commands? -
Re: Psql patch to show access methods info
Michael Paquier <michael@paquier.xyz> — 2018-10-01T02:27:06Z
On Tue, Jul 03, 2018 at 01:25:37PM +0300, s.cherkashin@postgrespro.ru wrote: > diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml > index 3ed9021..b699548 100644 > --- a/doc/src/sgml/catalogs.sgml > +++ b/doc/src/sgml/catalogs.sgml Please note that the latest patch proposed does not apply anymore. This has been moved to CF 2018-11 with waiting on author as new status. -- Michael
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-11-18T02:20:50Z
On 2018-Oct-01, Michael Paquier wrote: > On Tue, Jul 03, 2018 at 01:25:37PM +0300, s.cherkashin@postgrespro.ru wrote: > > diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml > > index 3ed9021..b699548 100644 > > --- a/doc/src/sgml/catalogs.sgml > > +++ b/doc/src/sgml/catalogs.sgml > > Please note that the latest patch proposed does not apply anymore. This > has been moved to CF 2018-11 with waiting on author as new status. Here's a rebased version, fixing the rejects, pgindenting, and fixing some "git show --check" whitespace issues. Haven't reviewed any further than that. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
Michael Paquier <michael@paquier.xyz> — 2018-11-19T02:26:31Z
On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote: > Here's a rebased version, fixing the rejects, pgindenting, and fixing > some "git show --check" whitespace issues. Haven't reviewed any further > than that. Schema qualifications are missing in many places, and they are added sometimes. The character limit in documentation paragraph could be more respected as well. + opereator families associated with whose name matches the pattern are shown. s/opereator/operator/. + List procedures (<xref linkend="catalog-pg-amproc-table"/>) accociated with access method operator families. s/accociated/associated/. -- Michael
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-11-19T02:38:24Z
On 2018-Nov-19, Michael Paquier wrote: > On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote: > > Here's a rebased version, fixing the rejects, pgindenting, and fixing > > some "git show --check" whitespace issues. Haven't reviewed any further > > than that. > > Schema qualifications are missing in many places, and they are added > sometimes. The character limit in documentation paragraph could be more > respected as well. Sergey, are you available to fix these issues? Nikita? Thanks -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2018-11-20T10:14:47Z
Yes, I am available to finish this patch. I’m sorry that I hadn’t updated patch for new commitfest and I grateful to you for doing it and fixing some issues. I would like to clarify which commands lack the output of the schema names? Because I tried to display them for all objects that have a schema. Best regards, Sergej Cherkashin. On 2018-11-19 05:38, Alvaro Herrera wrote: > On 2018-Nov-19, Michael Paquier wrote: > >> On Sat, Nov 17, 2018 at 11:20:50PM -0300, Alvaro Herrera wrote: >> > Here's a rebased version, fixing the rejects, pgindenting, and fixing >> > some "git show --check" whitespace issues. Haven't reviewed any further >> > than that. >> >> Schema qualifications are missing in many places, and they are added >> sometimes. The character limit in documentation paragraph could be >> more >> respected as well. > > Sergey, are you available to fix these issues? Nikita? > > Thanks
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-11-20T10:41:53Z
On 2018-Nov-20, s.cherkashin@postgrespro.ru wrote: > Yes, I am available to finish this patch. > I’m sorry that I hadn’t updated patch for new commitfest and I grateful to > you for doing it and fixing some issues. > I would like to clarify which commands lack the output of the schema names? > Because I tried to display them for all objects that have a schema. I think Michael is referring to the queries used to obtain the data. For example "FROM pg_class c" is bogus -- it must be "FROM pg_catalog.pg_class c". -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2018-11-20T13:08:42Z
Ok, I fixed this. On 2018-11-20 13:41, Alvaro Herrera wrote: > On 2018-Nov-20, s.cherkashin@postgrespro.ru wrote: > >> Yes, I am available to finish this patch. >> I’m sorry that I hadn’t updated patch for new commitfest and I >> grateful to >> you for doing it and fixing some issues. >> I would like to clarify which commands lack the output of the schema >> names? >> Because I tried to display them for all objects that have a schema. > > I think Michael is referring to the queries used to obtain the data. > For example "FROM pg_class c" is bogus -- it must be "FROM > pg_catalog.pg_class c".
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-11-20T13:42:41Z
On 2018-Nov-20, s.cherkashin@postgrespro.ru wrote: > Ok, I fixed this. Cool. I'm not sure this is a good idea: "c.relname::pg_catalog.regclass" I would use c.oid::pg_catalog.regclass instead. But before getting into those details, I think we should discuss the user interface that this patch is offering: \dip [am pattern] lists index properties (according to doc patch) * OK, but why do we need an AM pattern? ... reads regress output ... oh, actually it's an index name pattern, not an AM pattern. Please fix docs. \dicp [idx pattern] [column pattern] list index column properties * I think the column pattern part is pointless. \dA{f,p,fo,fp,oc} Please explain what these are. I think this is two patches -- one being the \dip/\dicp part, the other the \dA additions. Let's deal with them separately? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -
Re: Psql patch to show access methods info
Artur Zakirov <a.zakirov@postgrespro.ru> — 2018-11-20T14:32:57Z
Hello, On 20.11.2018 16:08, s.cherkashin@postgrespro.ru wrote: > Ok, I fixed this. I looked at the patch. It is in good shape. It compiles and tests are passed. I have few a questions related with throwing errors. They might be silly :) \dAp as well as \dA command throw an error if a server's version below 9.6: "The server (version %s) does not support access methods" But other \dA commands don't. It seems that there is enough information in catalog for servers below 9.6. That is there are pg_am, pg_opfamily, pg_amop and other catalog tables related with access methods. \dAp calls pg_indexam_has_property() function, which doesn't exist in servers 9.5 and below. Is this the reason that it throws an error? If so then describeOneIndexColumnProperties() also should throw an error, because it calls pg_index_column_has_property() function, which doesn't exist in servers 9.5 and below. What do you think? -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
-
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2018-11-23T14:13:24Z
> \dA{f,p,fo,fp,oc} > Please explain what these are. We adhere to the following logic f - families fo - operators in families fp - procedures in families p - access method properties oc - operator classes > I think this is two patches -- one being the \dip/\dicp part, the > other > the \dA additions. Let's deal with them separately? The attached patches are applied sequentially: first 0003- psql_add_am_info.patch, then 0003-psql_add_index_info.patch. Best regards, Sergey Cherkashin. -
Re: Psql patch to show access methods info
Michael Paquier <michael@paquier.xyz> — 2018-11-29T06:47:41Z
On Fri, Nov 23, 2018 at 05:13:24PM +0300, Sergey Cherkashin wrote: > The attached patches are applied sequentially: first 0003- > psql_add_am_info.patch, then 0003-psql_add_index_info.patch. Thanks for doing a split. I have been looking at add_am to being with, which is the first one in the set. + char *pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true); The set of meta commands with a one-one mapping with the system catalogs looks sensible to me, one suggestion I have would be to consider the verbose option of all commands: - \dAfp could have the strategy, purpose and sort purpose in its verbose part. - \dAfp could move the proc name with its arguments to the verbose portion. I would imagine that removing the arguments could make sense. - Is \dAf really useful as \dAfp actually proposes all the information that really matters? And \dAfp joins with pg_opfamily. - default and stored type could be moved to the verbose output of \dAoc. The columns names from \dAp could be better. What does "Can multi col" mean? Well that's index support for multiple columns but that's rather unclear for the user, no? Wouldn't it be cleaner here to set the second pattern only if the first pattern is defined? +-- check printing info about access methods +\dA +List of access method Regression tests are good for psql with deterministic matching patterns, but I am not much a fan of things which print global results as they result in more potential failures, and actually noise at the end. All the tests checking unexisting patterns don't bring much either I think. + command name, each operator family is listed with it's owner. s/it's/its/. tab-complete.c:463:26: warning: ‘Query_for_list_of_operator_families’ defined but not used [-Wunused-const-variable=] static const SchemaQuery Query_for_list_of_operator_families = { Compiler complains. -- Michael -
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2018-12-10T16:38:39Z
Here are some fixes. But I'm not sure that the renaming of columns for the '\dAp' command is sufficiently laconic and informative. If you have any suggestions on how to improve them, I will be very grateful. Best regards, Sergey Cherkashin.
-
Re: Psql patch to show access methods info
Michael Paquier <michael@paquier.xyz> — 2019-02-04T01:42:57Z
On Mon, Dec 10, 2018 at 07:38:39PM +0300, s.cherkashin@postgrespro.ru wrote: > Here are some fixes. But I'm not sure that the renaming of columns for the > '\dAp' command is sufficiently laconic and informative. If you have any > suggestions on how to improve them, I will be very grateful. I have not put much thougts into that to be honest. For now I have moved the patch to next CF. -- Michael
-
Re: Psql patch to show access methods info
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> — 2019-03-08T04:52:36Z
Hello. At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkashin@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru> > Here are some fixes. But I'm not sure that the renaming of columns for > the '\dAp' command is sufficiently laconic and informative. If you > have any suggestions on how to improve them, I will be very grateful. \dA: This is showing almost nothing. I think it's better that this command shows the same content with \dA+. As per Nikita's comment upthread, "Table" addition to "Index" is needed. \dAp: As the result \dAp gets useless. It cannot handle both Index and Table AMs at once. So, I propose the following behavior instead. It is similar to what \d does. =# \dA List of access methods Name | Type | Handler --------+-------+---------------------- brin | Index | brinhandler .. heap | Table | heap_tableam_handler =# \dA+ Name | Type | Handler | Description --------+-------+----------------------+---------------------------------------- brin | Index | brinhandler | block range index (BRIN) access method .. heap | Table | heap_tableam_handler | heap table access method =# \dA brin Index access method "brin" Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints --------+----------+--------+--------------+--------------+--------------------- brin | No | Yes | No | No | No \dA heap Table access method "heap" (I don't have an idea what to show here..) \dAfo: I don't get the point of the command. \dAoc: This seems more useful than \dAfo but the information that the command shows seems a bit pointless. We sometimes want to know the name of operator class usable in a CREATE INDEX. So I suppose that something like the following might be useful instead. SELECT DISTINCT a.amname AS "Acess method", (case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type", n.nspname || '.' || o.opcname AS "Operator class", (case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?" FROM pg_catalog.pg_opclass o JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily) JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod) JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace) ORDER BY 1, 2, 4 desc, 3; \dAoc List of operator classes for access methods Access method | Key type | Operator class | Default for type? ---------------+----------+-----------------------------+------------------- brin | bytea | pg_catalog.bytea_minmax_ops | Yes brin | "char" | pg_catalog.char_minmax_ops | Yes brin | name | pg_catalog.name_minmax_ops | Yes brin | bigint | pg_catalog.int8_minmax_ops | Yes .. \dAoc btree List of operator classes for access method 'btree' Access method | Key type | Operator class | Default for type? ---------------+----------+-----------------------------+------------------- btree | boolean | pg_catalog.bool_ops | Yes ... btree | text | pg_catalog.text_ops | Yes btree | text | pg_catalog.text_pattern_ops | No btree | text | pg_catalog.varchar_ops | No \dAoc btree text List of operator classes for access method 'btree', type 'text' List of operator classes for access method 'btree' Access method | Key type | Operator class | Default for type? ---------------+----------+--------------------------------+------------------ btree | text | pg_catalog.text_ops | Yes btree | text | pg_catalog.text_pattern_ops | No btree | text | pg_catalog.varchar_ops | No btree | text | pg_catalog.varchar_pattern_ops | No I'm not sure it's useful, but \dAoc+ may print owner. 0002 no longer applies. \dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'. \dip shows the following rseult. Index properties Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B ackward scan --------+-----------+---------------+-------------+------------+-------------+-- ------------- public | x_a_idx | btree | t | t | t | t public | tt_a_idx | brin | f | f | t | f public | tt_a_idx1 | brin | f | f | t | f The colums arfter "Access method" don't seem informatitve for users since they are fixed properties of an access method, and they doesn't make difference in what users can do. "Clusterable" seems useful in certain extent, but it doesn't fit here. Instaed \d <table> seems to me to be the place. (It could be shown also in \di+, but that looks a bit odd to me.) \d+ <table> is already showing (ASC)/DESC, and (NULLS FIRST)/NULLS LAST. Clusterable could be added in the Indexes: section. \d+ x Table "public.x" > Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc > ription > --------+------+-----------+----------+---------+----------+--------------+----- > -------- > a | text | | | | extended | | > Indexes: > "x_a_idx" btree (a varchar_ops) - "x_a_idx1" btree (a DESC NULLS LAST) + "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble > Access method: heap # I'm not sure "clusterable" makes sense.. regards. -- Kyotaro Horiguchi NTT Open Source Software Center -
Re: Re: Psql patch to show access methods info
David Steele <david@pgmasters.net> — 2019-03-20T11:02:25Z
Hi Sergey, On 3/8/19 8:52 AM, Kyotaro HORIGUCHI wrote: > > At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkashin@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru> >> Here are some fixes. But I'm not sure that the renaming of columns for >> the '\dAp' command is sufficiently laconic and informative. If you >> have any suggestions on how to improve them, I will be very grateful. > > \dA: > > This is showing almost nothing. I think it's better that this > command shows the same content with \dA+. As per Nikita's comment > upthread, "Table" addition to "Index" is needed. > > \dAp: > > As the result \dAp gets useless. It cannot handle both Index > and Table AMs at once. > > So, I propose the following behavior instead. It is similar to > what \d does. > > =# \dA > List of access methods > Name | Type | Handler > --------+-------+---------------------- > brin | Index | brinhandler > .. > heap | Table | heap_tableam_handler > > > =# \dA+ > Name | Type | Handler | Description > --------+-------+----------------------+---------------------------------------- > brin | Index | brinhandler | block range index (BRIN) access method > .. > heap | Table | heap_tableam_handler | heap table access method > > > =# \dA brin > Index access method "brin" > Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints > --------+----------+--------+--------------+--------------+--------------------- > brin | No | Yes | No | No | No > > \dA heap > Table access method "heap" > (I don't have an idea what to show here..) > > > > \dAfo: I don't get the point of the command. > > \dAoc: This seems more useful than \dAfo but the information that > the command shows seems a bit pointless. We sometimes want to > know the name of operator class usable in a CREATE INDEX. So I > suppose that something like the following might be useful > instead. > > SELECT DISTINCT a.amname AS "Acess method", > (case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type", > n.nspname || '.' || o.opcname AS "Operator class", > (case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?" > FROM pg_catalog.pg_opclass o > JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily) > JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod) > JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace) > ORDER BY 1, 2, 4 desc, 3; > > \dAoc > List of operator classes for access methods > Access method | Key type | Operator class | Default for type? > ---------------+----------+-----------------------------+------------------- > brin | bytea | pg_catalog.bytea_minmax_ops | Yes > brin | "char" | pg_catalog.char_minmax_ops | Yes > brin | name | pg_catalog.name_minmax_ops | Yes > brin | bigint | pg_catalog.int8_minmax_ops | Yes > .. > > > \dAoc btree > List of operator classes for access method 'btree' > Access method | Key type | Operator class | Default for type? > ---------------+----------+-----------------------------+------------------- > btree | boolean | pg_catalog.bool_ops | Yes > ... > btree | text | pg_catalog.text_ops | Yes > btree | text | pg_catalog.text_pattern_ops | No > btree | text | pg_catalog.varchar_ops | No > > \dAoc btree text > List of operator classes for access method 'btree', type 'text' > > List of operator classes for access method 'btree' > Access method | Key type | Operator class | Default for type? > ---------------+----------+--------------------------------+------------------ > btree | text | pg_catalog.text_ops | Yes > btree | text | pg_catalog.text_pattern_ops | No > btree | text | pg_catalog.varchar_ops | No > btree | text | pg_catalog.varchar_pattern_ops | No > > I'm not sure it's useful, but \dAoc+ may print owner. > > > > 0002 no longer applies. > > \dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'. > > \dip shows the following rseult. > > Index properties > Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B > ackward scan > --------+-----------+---------------+-------------+------------+-------------+-- > ------------- > public | x_a_idx | btree | t | t | t | t > public | tt_a_idx | brin | f | f | t | f > public | tt_a_idx1 | brin | f | f | t | f > > > The colums arfter "Access method" don't seem informatitve for > users since they are fixed properties of an access method, and > they doesn't make difference in what users can do. "Clusterable" > seems useful in certain extent, but it doesn't fit here. Instaed > \d <table> seems to me to be the place. (It could be shown also > in \di+, but that looks a bit odd to me.) > > > \d+ <table> is already showing (ASC)/DESC, and (NULLS > FIRST)/NULLS LAST. Clusterable could be added in the Indexes: > section. > > \d+ x > Table "public.x" >> Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc >> ription >> --------+------+-----------+----------+---------+----------+--------------+----- >> -------- >> a | text | | | | extended | | >> Indexes: >> "x_a_idx" btree (a varchar_ops) > - "x_a_idx1" btree (a DESC NULLS LAST) > + "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble >> Access method: heap > > # I'm not sure "clusterable" makes sense.. Your thoughts on these comments? Regards, -- -David david@pgmasters.net
-
Re: Psql patch to show access methods info
Nikita Glukhov <n.gluhov@postgrespro.ru> — 2019-03-21T01:09:52Z
Hi. On 08.03.2019 7:52, Kyotaro HORIGUCHI wrote: > Hello. > > At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkashin@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da7bf@postgrespro.ru> >> Here are some fixes. But I'm not sure that the renaming of columns for >> the '\dAp' command is sufficiently laconic and informative. If you >> have any suggestions on how to improve them, I will be very grateful. > \dA: > > This is showing almost nothing. I think it's better that this > command shows the same content with \dA+. As per Nikita's comment > upthread, "Table" addition to "Index" is needed. > > \dAp: > > As the result \dAp gets useless. It cannot handle both Index > and Table AMs at once. > > So, I propose the following behavior instead. It is similar to > what \d does. > > =# \dA > List of access methods > Name | Type | Handler > --------+-------+---------------------- > brin | Index | brinhandler > .. > heap | Table | heap_tableam_handler > > > =# \dA+ > Name | Type | Handler | Description > --------+-------+----------------------+---------------------------------------- > brin | Index | brinhandler | block range index (BRIN) access method > .. > heap | Table | heap_tableam_handler | heap table access method > > > =# \dA brin > Index access method "brin" > Name | Ordering | Unique | Multicol key | Non-key cols | Excl Constraints > --------+----------+--------+--------------+--------------+--------------------- > brin | No | Yes | No | No | No I completely agree. Also I propose the following renaming of commands after \dAp removing: \dAfo => \dAo \dAfp => \dAp \dAoc => \dAc > \dA heap > Table access method "heap" > (I don't have an idea what to show here..) Yes, there are no functions like pg_tableam_has_property() yet. > \dAfo: I don't get the point of the command. This commands helps to remember which operators can be accelerated up by each index AM. Maybe operator name and its operand type would be better to put into a single column. Also schema can be shown only when opfamily is not visible, or in verbose mode. For example, for jsonb type we could have: \dAfo * jsonb* List operators of family related to access method AM | Schema | Opfamily | Operator -------+------------+----------------+-------------------- btree | pg_catalog | jsonb_ops | < (jsonb, jsonb) btree | pg_catalog | jsonb_ops | <= (jsonb, jsonb) btree | pg_catalog | jsonb_ops | = (jsonb, jsonb) btree | pg_catalog | jsonb_ops | >= (jsonb, jsonb) btree | pg_catalog | jsonb_ops | > (jsonb, jsonb) gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb) gin | pg_catalog | jsonb_ops | ? (jsonb, text) gin | pg_catalog | jsonb_ops | ?| (jsonb, text[]) gin | pg_catalog | jsonb_ops | ?& (jsonb, text[]) gin | pg_catalog | jsonb_path_ops | @> (jsonb, jsonb) hash | pg_catalog | jsonb_ops | = (jsonb, jsonb) (11 rows) > \dAoc: This seems more useful than \dAfo but the information that > the command shows seems a bit pointless. We sometimes want to > know the name of operator class usable in a CREATE INDEX. So I > suppose that something like the following might be useful > instead. > > SELECT DISTINCT a.amname AS "Acess method", > (case when o.opckeytype <> 0 then o.opckeytype else o.opcintype end)::regtype AS "Key type", > n.nspname || '.' || o.opcname AS "Operator class", > (case when o.opcdefault then 'Yes' else 'No' end) AS "Default for type?" > FROM pg_catalog.pg_opclass o > JOIN pg_catalog.pg_opfamily f ON (f.oid = o.opcfamily) > JOIN pg_catalog.pg_am a ON (a.oid = f.opfmethod) > JOIN pg_catalog.pg_namespace n ON (n.oid = o.opcnamespace) > ORDER BY 1, 2, 4 desc, 3; > > \dAoc > List of operator classes for access methods > Access method | Key type | Operator class | Default for type? > ---------------+----------+-----------------------------+------------------- > brin | bytea | pg_catalog.bytea_minmax_ops | Yes > brin | "char" | pg_catalog.char_minmax_ops | Yes > brin | name | pg_catalog.name_minmax_ops | Yes > brin | bigint | pg_catalog.int8_minmax_ops | Yes > .. > > > \dAoc btree > List of operator classes for access method 'btree' > Access method | Key type | Operator class | Default for type? > ---------------+----------+-----------------------------+------------------- > btree | boolean | pg_catalog.bool_ops | Yes > ... > btree | text | pg_catalog.text_ops | Yes > btree | text | pg_catalog.text_pattern_ops | No > btree | text | pg_catalog.varchar_ops | No > > \dAoc btree text > List of operator classes for access method 'btree', type 'text' > > List of operator classes for access method 'btree' > Access method | Key type | Operator class | Default for type? > ---------------+----------+--------------------------------+------------------ > btree | text | pg_catalog.text_ops | Yes > btree | text | pg_catalog.text_pattern_ops | No > btree | text | pg_catalog.varchar_ops | No > btree | text | pg_catalog.varchar_pattern_ops | No > > I'm not sure it's useful, but \dAoc+ may print owner. Mostly I agree with this idea. I think opfamily should be shown too, if we want to list the corresponding operators then. But \dAfo could take a type name pattern instead of opfamily pattern. Also it seems that the same multi-table showing method can be used in \dAfo too. Does AM/type name really need to be duplicated in "AM", "Type" columns, if we will show each AM/type in the separate table? > 0002 no longer applies. > > \dip: It works, but you are catching 'd[tvmi]' for 'dip' and 'dicp'. > > \dip shows the following rseult. > > Index properties > Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | B > ackward scan > --------+-----------+---------------+-------------+------------+-------------+-- > ------------- > public | x_a_idx | btree | t | t | t | t > public | tt_a_idx | brin | f | f | t | f > public | tt_a_idx1 | brin | f | f | t | f > > > The colums arfter "Access method" don't seem informatitve for > users since they are fixed properties of an access method, and > they doesn't make difference in what users can do. "Clusterable" > seems useful in certain extent, but it doesn't fit here. Instead > \d <table> seems to me to be the place. (It could be shown also > in \di+, but that looks a bit odd to me.) These index properties are really not fixed properties of AM, because AMs have ability to override them in its amproperty() method, however, none of the core AM does this. > \d+ <table> is already showing (ASC)/DESC, and (NULLS > FIRST)/NULLS LAST. Clusterable could be added in the Indexes: > section. > > \d+ x > Table "public.x" >> Column | Type | Collation | Nullable | Default | Storage | Stats target | Desc >> ription >> --------+------+-----------+----------+---------+----------+--------------+----- >> -------- >> a | text | | | | extended | | >> Indexes: >> "x_a_idx" btree (a varchar_ops) > - "x_a_idx1" btree (a DESC NULLS LAST) > + "x_a_idx1" btree (a DESC NULLS LAST), Clusteratble >> Access method: heap > # I'm not sure "clusterable" makes sense.. > > regards. -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company -
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2019-03-22T18:29:09Z
Taking into account the wishes of all the reviewers, the current position of the patch is as follows: The \dA command displays a list of access methods. # \dA List of access methods Name | Type | Handler --------+-------+---------------------- brin | index | brinhandler btree | index | bthandler gin | index | ginhandler gist | index | gisthandler hash | index | hashhandler heap | table | heap_tableam_handler spgist | index | spghandler (7 rows) With + it shows description: # \dA+ List of access methods Name | Type | Handler | Description --------+-------+----------------------+------------------------------- --------- brin | index | brinhandler | block range index (BRIN) access method btree | index | bthandler | b-tree index access method gin | index | ginhandler | GIN index access method gist | index | gisthandler | GiST index access method hash | index | hashhandler | hash index access method heap | table | heap_tableam_handler | heap table access method spgist | index | spghandler | SP-GiST index access method (7 rows) The functionality of the \dAp command has been moved to \dA NAME. Now the user can query the properties of a particular AM (or several, using the search pattern) as follows: # \dA h* Index access method properties AM | Can order | Support unique indexes | Support indexes with multiple columns | Support exclusion constraints | Can include non-key columns ------+-----------+------------------------+--------------------------- ------------+-------------------------------+------------------------ ----- hash | no | no | no | yes | no (1 row) Table access method properties Name | Type | Handler | Description ------+-------+----------------------+-------------------------- heap | table | heap_tableam_handler | heap table access method (1 row) Note that for heap, as well as for future table AM, a separate table is displayed, since it is not clear which properties can be displayed for them. The \dAoc command has been renamed to \dAc. The command displays information about operator classes. The "Input type" field was left, because the user may first be interested in what type of data opclass can work with, and in the second - how it will keep this type inside. Nikita also chose to leave the opfamily field as additional information. # \dAc btree name Index access method operator classes AM | Input type | Storage type | Operator class | Default? -------+------------+--------------+----------------+---------- btree | name | cstring | name_ops | yes (1 row) # \dAc+ btree record Index access method operator classes AM | Input type | Storage type | Operator class | Default? | Operator family | Owner -------+------------+--------------+------------------+----------+----- -------------+------- btree | record | | record_image_ops | no | record_image_ops | zloj btree | record | | record_ops | yes | record_ops | zloj (2 rows) The \dAfo command has been renamed to \dAo. \dAo displays information about operators as follows: # \dAo gin jsonb_ops List operators of family related to access method AM | Opfamily Schema | Opfamily Name | Operator -----+-----------------+---------------+-------------------- gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb) gin | pg_catalog | jsonb_ops | ? (jsonb, text) gin | pg_catalog | jsonb_ops | ?| (jsonb, text[]) gin | pg_catalog | jsonb_ops | ?& (jsonb, text[]) (4 rows) # \dAo+ gist circle_ops List operators of family related to access method AM | Opfamily Schema | Opfamily Name | Operator | Strategy | Purpose | Sort family ------+-----------------+---------------+----------------------+------- ---+----------+------------- gist | pg_catalog | circle_ops | << (circle, circle) | 1 | search | ... gist | pg_catalog | circle_ops | <-> (circle, point) | 15 | ordering | float_ops The \dAop command has been renamed to \dAp. It displays list of support procedures associated with access method operator families. # \dAp hash array_ops List of operator family procedures AM | Family schema | Family name | Left | Right | Number ------+---------------+-------------+----------+----------+-------- hash | pg_catalog | array_ops | anyarray | anyarray | 1 hash | pg_catalog | array_ops | anyarray | anyarray | 2 (2 rows) # \dAp+ hash array_ops List of operator family procedures AM | Family schema | Family name | Left | Right | Number | Proc name ------+---------------+-------------+----------+----------+--------+--- ------------------ hash | pg_catalog | array_ops | anyarray | anyarray | 1 | hash_array hash | pg_catalog | array_ops | anyarray | anyarray | 2 | hash_array_extended (2 rows) It may be easier for the user to navigate in this list if the defining feature in addition to the number is also the procedure name. Even if it does not carry important information, it improves the readability of the list. Maybe it makes sense to return field "Proc name" to the main output? 0002-psql_add_index_info-v5.patch The commands \dip and \dicp have so far been left in the current form, because although they display properties common to the whole AM, as Nikita already wrote, this properties can be redefined. # \dip pg_am_oid_index Index properties Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan ------------+-----------------+---------------+-------------+-------- ----+-------------+--------------- pg_catalog | pg_am_oid_index | btree | yes | yes | yes | yes (1 row) # \dicp pg_amop_opr_fam_index Index pg_catalog.pg_amop_opr_fam_index Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls -------------+-------------+----------+-----+-------------+----------- +--------------------+------------+--------------+-------------- amopopr | amopopr | oid_ops | yes | no | yes | no | yes | yes | yes amoppurpose | amoppurpose | char_ops | yes | no | yes | no | yes | yes | yes amopfamily | amopfamily | oid_ops | yes | no | yes | no | yes | yes | yes Table: pg_amop Access method: btree Also please look through the documentation for these features. I am sure that the information specified there can be submitted in a more accurate and convenient form. P.S. Since the formatting of the letter can brake the form of the tables, I attach a text file with the same content so that you do not have to do too much copy/paste to see original view =) Sincerely Sergey Cherkashin. -
Re: Psql patch to show access methods info
Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> — 2019-03-26T11:12:20Z
Thank you for the new version. At Fri, 22 Mar 2019 21:29:09 +0300, Sergey Cherkashin <s.cherkashin@postgrespro.ru> wrote in <fd9f7eb2ffc800157fbf35fa8aa7733a9cbce7cb.camel@postgrespro.ru> > Taking into account the wishes of all the reviewers, the current > position of the patch is as follows: > > The \dA command displays a list of access methods. > > # \dA > List of access methods > Name | Type | Handler > --------+-------+---------------------- > brin | index | brinhandler > btree | index | bthandler > gin | index | ginhandler > gist | index | gisthandler > hash | index | hashhandler > heap | table | heap_tableam_handler > spgist | index | spghandler > (7 rows) > > With + it shows description: > # \dA+ > List of access methods > Name | > Type | Handler | Description > --------+-------+----------------------+------------------------------- > --------- > brin | index | brinhandler | block range index (BRIN) > access method > btree | index | bthandler | b-tree index access method > gin | index | ginhandler | GIN index access method > gist | index | gisthandler | GiST index access method > hash | index | hashhandler | hash index access method > heap | table | heap_tableam_handler | heap table access method > spgist | index | spghandler | SP-GiST index access method > (7 rows) Looks nice, but this fails for 9.4 or 9.5 server. I'm not sure how far back versions we should support, though. > The functionality of the \dAp command has been moved to \dA NAME. > Now the user can query the properties of a particular AM (or several, > using the search pattern) as follows: > > # \dA h* > Index access > method properties > AM | Can order | Support unique indexes | Support indexes with > multiple columns | Support exclusion constraints | Can include non-key > columns > ------+-----------+------------------------+--------------------------- > ------------+-------------------------------+------------------------ > ----- > hash | no | no | > no | yes | > no > (1 row) In the earlier patches they were "Can order", "Can unique", "Can multi col", "Can exclude" and they indeed look too-short. Nevertheless the current column names occupies the top four places on the podium by their length. "Foreign-data wrapeer" is on the fifth place. Most of them are just one noun. Some of them are two-or-three-word nouns. Some of them are single-word adjective followed by '?'. \dicp uses single-word adverbs or a-few-words nouns without trailing '?'. How about the following? 8 Ordering yes/no 14 Unique indexes yes/no 16 Multicol indexes yes/no 21 Exclusion constraints yes/no 23 Include non-key columns yes/no ===== 20 Foreign-data wrapper Does anyone have better wordings? Or, are the current wordings OK? > Table access method properties > Name | Type | Handler | Description > ------+-------+----------------------+-------------------------- > heap | table | heap_tableam_handler | heap table access method > (1 row) > > Note that for heap, as well as for future table AM, a separate table is > displayed, since it is not clear which properties can be displayed for > them. Yeah. I think that's fine. > The \dAoc command has been renamed to \dAc. > The command displays information about operator classes. The "Input > type" field was left, because the user may first be interested in what > type of data opclass can work with, > and in the second - how it will keep this type inside. Nikita also > chose to leave the opfamily field as additional information. > > # \dAc btree name > Index access method operator classes > AM | Input type | Storage type | Operator class | Default? > -------+------------+--------------+----------------+---------- > btree | name | cstring | name_ops | yes > (1 row) > > # \dAc+ btree record > Index access method operator classes > AM | Input type | Storage type | Operator class | Default? | > Operator family | Owner > -------+------------+--------------+------------------+----------+----- > -------------+------- > btree | record | | record_image_ops | no | > record_image_ops | zloj > btree | record | | record_ops | yes | > record_ops | zloj > (2 rows) > > The \dAfo command has been renamed to \dAo. > \dAo displays information about operators as follows: > > # \dAo gin jsonb_ops > List operators of family related to access method > AM | Opfamily Schema | Opfamily Name | Operator > -----+-----------------+---------------+-------------------- > gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb) > gin | pg_catalog | jsonb_ops | ? (jsonb, text) > gin | pg_catalog | jsonb_ops | ?| (jsonb, text[]) > gin | pg_catalog | jsonb_ops | ?& (jsonb, text[]) > (4 rows) I'm not sure but couldn't we show the opfamily name in full qualified? The schema is not a property of the AM. > # \dAo+ gist circle_ops > List operators of family related to access > method > AM | Opfamily Schema | Opfamily Name | Operator | > Strategy | Purpose | Sort family > ------+-----------------+---------------+----------------------+------- > ---+----------+------------- > gist | pg_catalog | circle_ops | << (circle, > circle) | 1 | search | > ... > gist | pg_catalog | circle_ops | <-> (circle, > point) | 15 | ordering | float_ops "Sort family" doesn't make sense. "Sort opfamily" or "Sort operator family"? > The \dAop command has been renamed to \dAp. > It displays list of support procedures associated with access method > operator families. > # \dAp hash array_ops > List of operator family procedures > AM | Family schema | Family name | Left | Right | Number > ------+---------------+-------------+----------+----------+-------- > hash | pg_catalog | array_ops | anyarray | anyarray | 1 > hash | pg_catalog | array_ops | anyarray | anyarray | 2 > (2 rows) > > # \dAp+ hash array_ops > List of operator family procedures > AM | Family schema | Family name | Left | Right | Number > | Proc name > ------+---------------+-------------+----------+----------+--------+--- > ------------------ > hash | pg_catalog | array_ops | anyarray | anyarray | 1 | > hash_array > hash | pg_catalog | array_ops | anyarray | anyarray | 2 | > hash_array_extended > (2 rows) > > It may be easier for the user to navigate in this list if the defining > feature in addition to the number is also the procedure name. > Even if it does not carry important information, it improves the > readability of the list. Maybe it makes sense to return field "Proc > name" to the main output? "Number", "Proc name" doens't seem descriptive enough. It is mentioned as support function number in the documentation. The "Left" and "Right" are not necessarily parameter types of "Proc name". But I don't come up with better namings. It is a bit different thing, but "Left/Right arg type" is used elsewhere as parameter types. How about "AM", "Operator family", "Left arg type", "Right arg type" and "Support function number", "Support function"? The second from the last is 23 characters long. It could be "Support number" instead. > 0002-psql_add_index_info-v5.patch > > The commands \dip and \dicp have so far been left in the current form, > because although they display properties common to the whole AM, > as Nikita already wrote, this properties can be redefined. > > # \dip pg_am_oid_index > Index properties > Schema | Name | Access method | Clusterable | Index > scan | Bitmap scan | Backward scan > ------------+-----------------+---------------+-------------+-------- > ----+-------------+--------------- > pg_catalog | pg_am_oid_index | btree | yes | > yes | yes | yes > (1 row) I'm fine with this, but fails for 9.4 and 9.5. Also \dicp fails. > # \dicp pg_amop_opr_fam_index > Index > pg_catalog.pg_amop_opr_fam_index > Column name | Expr | Opclass | ASC | Nulls first | Orderable | > Distance orderable | Returnable | Search array | Search nulls > -------------+-------------+----------+-----+-------------+----------- > +--------------------+------------+--------------+-------------- > amopopr | amopopr | oid_ops | yes | no | yes | > no | yes | yes | yes > amoppurpose | amoppurpose | char_ops | yes | no | yes | > no | yes | yes | yes > amopfamily | amopfamily | oid_ops | yes | no | yes | > no | yes | yes | yes > Table: pg_amop > Access method: btree > > Also please look through the documentation for these features. I am > sure that the information specified there can be submitted in a more > accurate and convenient form. > > P.S. Since the formatting of the letter can brake the form of the > tables, I attach a text file with the same content so that you do not > have to do too much copy/paste to see original view =) regards. -- Kyotaro Horiguchi NTT Open Source Software Center
-
Re: Psql patch to show access methods info
s.cherkashin@postgrespro.ru — 2019-03-31T01:13:07Z
Thanks for review. >> With + it shows description: >> # \dA+ >> List of access methods >> Name | >> Type | Handler | Description >> --------+-------+----------------------+------------------------------- >> --------- >> brin | index | brinhandler | block range index (BRIN) >> access method >> btree | index | bthandler | b-tree index access method >> gin | index | ginhandler | GIN index access method >> gist | index | gisthandler | GiST index access method >> hash | index | hashhandler | hash index access method >> heap | table | heap_tableam_handler | heap table access method >> spgist | index | spghandler | SP-GiST index access method >> (7 rows) > > Looks nice, but this fails for 9.4 or 9.5 server. I'm not sure > how far back versions we should support, though. The command \dA initially displayed an error message when working on a server version below 9.6, and I did not change this logic. I'm not sure, but it probably makes sense for versions 9.4 and 9.5 to output something like this query does: SELECT a.amname AS "AM", d.description AS "Description" FROM pg_am a JOIN pg_description d ON a.oid = d.objoid ORDER BY 1; #\dA AM | Description --------+----------------------------- btree | b-tree index access method gin | GIN index access method gist | GiST index access method hash | hash index access method spgist | SP-GiST index access method SELECT a.amname AS "AM", CASE WHEN a.amcanorder THEN 'yes' ELSE 'no' END AS "Ordering", CASE WHEN a.amcanunique THEN 'yes' ELSE 'no' END AS "Unique indexes", CASE WHEN a.amcanmulticol THEN 'yes' ELSE 'no' END AS "Multicol indexes", CASE WHEN a.amsearchnulls THEN 'yes' ELSE 'no' END AS "Searching NULLs", CASE WHEN a.amclusterable THEN 'yes' ELSE 'no' END AS "Clusterale" FROM pg_am a JOIN pg_description d ON a.oid = d.objoid ORDER BY 1; #dA NAME AM | Ordering | Unique indexes | Multicol indexes | Searching NULLs | Clusterale --------+----------+----------------+------------------+-----------------+------------ btree | yes | yes | yes | yes | yes gin | no | no | yes | no | no gist | no | no | yes | yes | yes hash | no | no | no | no | no spgist | no | no | no | yes | no (5 rows) > >> The functionality of the \dAp command has been moved to \dA NAME. >> Now the user can query the properties of a particular AM (or several, >> using the search pattern) as follows: >> >> # \dA h* >> Index access >> method properties >> AM | Can order | Support unique indexes | Support indexes with >> multiple columns | Support exclusion constraints | Can include non-key >> columns >> ------+-----------+------------------------+--------------------------- >> ------------+-------------------------------+------------------------ >> ----- >> hash | no | no | >> no | yes >> | >> no >> (1 row) > > In the earlier patches they were "Can order", "Can unique", "Can > multi col", "Can exclude" and they indeed look > too-short. Nevertheless the current column names occupies the top > four places on the podium by their length. "Foreign-data wrapeer" > is on the fifth place. Most of them are just one noun. Some of > them are two-or-three-word nouns. Some of them are single-word > adjective followed by '?'. \dicp uses single-word adverbs or > a-few-words nouns without trailing '?'. How about the following? > > 8 Ordering yes/no > 14 Unique indexes yes/no > 16 Multicol indexes yes/no > 21 Exclusion constraints yes/no > 23 Include non-key columns yes/no > ===== > 20 Foreign-data wrapper > > > Does anyone have better wordings? Or, are the current wordings OK? I like this version. >> # \dAo gin jsonb_ops >> List operators of family related to access method >> AM | Opfamily Schema | Opfamily Name | Operator >> -----+-----------------+---------------+-------------------- >> gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb) >> gin | pg_catalog | jsonb_ops | ? (jsonb, text) >> gin | pg_catalog | jsonb_ops | ?| (jsonb, text[]) >> gin | pg_catalog | jsonb_ops | ?& (jsonb, text[]) >> (4 rows) > > I'm not sure but couldn't we show the opfamily name in full > qualified? The schema is not a property of the AM. Now Opfamily Schema is shown if opfamily name is not visible in the current schema search path (check by pg_opfamily_is_visible(). > >> # \dAo+ gist circle_ops >> List operators of family related to access >> method >> AM | Opfamily Schema | Opfamily Name | Operator | >> Strategy | Purpose | Sort family >> ------+-----------------+---------------+----------------------+------- >> ---+----------+------------- >> gist | pg_catalog | circle_ops | << (circle, >> circle) | 1 | search | >> ... >> gist | pg_catalog | circle_ops | <-> (circle, >> point) | 15 | ordering | float_ops > > "Sort family" doesn't make sense. "Sort opfamily" or "Sort > operator family"? Renamed. >> The \dAop command has been renamed to \dAp. >> It displays list of support procedures associated with access method >> operator families. >> # \dAp hash array_ops >> List of operator family procedures >> AM | Family schema | Family name | Left | Right | Number >> ------+---------------+-------------+----------+----------+-------- >> hash | pg_catalog | array_ops | anyarray | anyarray | 1 >> hash | pg_catalog | array_ops | anyarray | anyarray | 2 >> (2 rows) >> >> # \dAp+ hash array_ops >> List of operator family procedures >> AM | Family schema | Family name | Left | Right | Number >> | Proc name >> ------+---------------+-------------+----------+----------+--------+--- >> ------------------ >> hash | pg_catalog | array_ops | anyarray | anyarray | 1 | >> hash_array >> hash | pg_catalog | array_ops | anyarray | anyarray | 2 | >> hash_array_extended >> (2 rows) >> >> It may be easier for the user to navigate in this list if the defining >> feature in addition to the number is also the procedure name. >> Even if it does not carry important information, it improves the >> readability of the list. Maybe it makes sense to return field "Proc >> name" to the main output? > > "Number", "Proc name" doens't seem descriptive enough. It is > mentioned as support function number in the documentation. The > "Left" and "Right" are not necessarily parameter types of "Proc > name". But I don't come up with better namings. It is a bit > different thing, but "Left/Right arg type" is used elsewhere as > parameter types. > > How about "AM", "Operator family", "Left arg type", "Right arg > type" and "Support function number", "Support function"? The > second from the last is 23 characters long. It could be "Support > number" instead. I have no better idea how to improve naming so I used the names you suggested. > >> 0002-psql_add_index_info-v5.patch >> > I'm fine with this, but fails for 9.4 and 9.5. Also \dicp fails. Maybe I missed something, but it works well on 9.4 and 9.5 for me. Regards, Sergey Cherkashin. -
Re: Psql patch to show access methods info
Thomas Munro <thomas.munro@gmail.com> — 2019-07-01T11:06:54Z
On Sun, Mar 31, 2019 at 2:13 PM <s.cherkashin@postgrespro.ru> wrote: > Thanks for review. Hi Sergey, A new Commitfest is here and this doesn't apply -- could you please post a rebase? Thanks, -- Thomas Munro https://enterprisedb.com
-
Re: Psql patch to show access methods info
Nikita Glukhov <n.gluhov@postgrespro.ru> — 2019-07-15T19:03:31Z
On 01.07.2019 14:06, Thomas Munro wrote: > On Sun, Mar 31, 2019 at 2:13 PM <s.cherkashin@postgrespro.ru> wrote: >> Thanks for review. > Hi Sergey, > > A new Commitfest is here and this doesn't apply -- could you please > post a rebase? > > Thanks, Attached 7th version of the patches rebased onto current master. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-07-21T12:54:03Z
On Mon, Jul 15, 2019 at 10:05 PM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > On 01.07.2019 14:06, Thomas Munro wrote: > > > On Sun, Mar 31, 2019 at 2:13 PM <s.cherkashin@postgrespro.ru> wrote: > >> Thanks for review. > > Hi Sergey, > > > > A new Commitfest is here and this doesn't apply -- could you please > > post a rebase? > > > > Thanks, > > Attached 7th version of the patches rebased onto current master. Thank you for posting this patch. It looks good to me. I've one note. Behavior of "\dA" and "\dA pattern" look counter-intuitive to me. I would rather expect that "\dA pattern" would just filter results of "\dA", but it displays different information. I suggest rename displaying access method properties from "\dA pattern" to different. And leave "\dA pattern" just filter results of "\dA". ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-07-22T03:29:18Z
On 2019-Jul-21, Alexander Korotkov wrote: > I've one note. Behavior of "\dA" and "\dA pattern" look > counter-intuitive to me. I would rather expect that "\dA pattern" > would just filter results of "\dA", but it displays different > information. I suggest rename displaying access method properties > from "\dA pattern" to different. \dA+ maybe? Then ... > And leave "\dA pattern" just filter results of "\dA". "\dA+ pattern" works intuitively, I think. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-07-22T12:58:38Z
On Mon, Jul 22, 2019 at 6:29 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > On 2019-Jul-21, Alexander Korotkov wrote: > > I've one note. Behavior of "\dA" and "\dA pattern" look > > counter-intuitive to me. I would rather expect that "\dA pattern" > > would just filter results of "\dA", but it displays different > > information. I suggest rename displaying access method properties > > from "\dA pattern" to different. > > \dA+ maybe? Then ... > > > And leave "\dA pattern" just filter results of "\dA". > > "\dA+ pattern" works intuitively, I think. Sounds good for me. We already have some functionality for \dA+. # \dA+ List of access methods Name | Type | Handler | Description --------+-------+----------------------+---------------------------------------- brin | index | brinhandler | block range index (BRIN) access method btree | index | bthandler | b-tree index access method gin | index | ginhandler | GIN index access method gist | index | gisthandler | GiST index access method hash | index | hashhandler | hash index access method heap | table | heap_tableam_handler | heap table access method spgist | index | spghandler | SP-GiST index access method (7 rows) What we need is that new \dA+ functionality cover existing one. That it, we should add Handler and Description column to the output. # \dA+ * Index access method properties AM | Ordering | Unique indexes | Multicol indexes | Exclusion constraints | Include non-key columns --------+----------+----------------+------------------+-----------------------+------------------------- brin | no | no | yes | no | no btree | yes | yes | yes | yes | yes gin | no | no | yes | no | no gist | no | no | yes | yes | yes hash | no | no | no | yes | no spgist | no | no | no | yes | no (6 rows) Table access method properties Name | Type | Handler | Description ------+-------+----------------------+-------------------------- heap | table | heap_tableam_handler | heap table access method (1 row) ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -
Re: Psql patch to show access methods info
Andres Freund <andres@anarazel.de> — 2019-07-22T17:37:04Z
Hi, On 2019-07-15 22:03:31 +0300, Nikita Glukhov wrote: > + <varlistentry> > + <term> > + <literal>\dAc[+] > + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link> > + [<link linkend="app-psql-patterns"><replaceable class="parameter">input-type-pattern</replaceable></link>]] > + </literal> > + </term> > + <listitem> > + <para> > + Shows info index access method operator classes listed in > + <xref linkend="catalog-pg-opclass-table"/>. > + If <replaceable class="parameter">access-method-patttern</replaceable> > + is specified, only operator classes associated with access method whose > + name matches pattern are shown. > + If <replaceable class="parameter">input-type-pattern</replaceable> > + is specified, only procedures associated with families whose input type > + matches the pattern are shown. > + If <literal>+</literal> is appended to the command name, operator family > + and owner are listed. > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > + <term> > + <literal>\dAo[+] > + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link> > + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]] > + </literal> > + </term> > + > + <listitem> > + <para> > + Lists operators (<xref linkend="catalog-pg-amop-table"/>) associated > + with access method operator families. If > + <replaceable class="parameter">access-method-patttern</replaceable> is > + specified, only operators associated with access method whose name > + matches pattern are shown. If > + <replaceable class="parameter">operator-family-pattern</replaceable> is > + specified, only operators associated with families whose name matches > + the pattern are shown. > + If <literal>+</literal> is appended to the command name, displays > + additional info. > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > + <term> > + <literal>\dAp[+] > + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link> > + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]] > + </literal> > + </term> > + <listitem> > + <para> > + Lists procedures (<xref linkend="catalog-pg-amproc-table"/>) associated > + with access method operator families. > + If <replaceable class="parameter">access-method-patttern</replaceable> > + is specified, only procedures associated with access method whose name > + matches pattern are shown. > + If <replaceable class="parameter">operator-family-pattern</replaceable> > + is specified, only procedures associated with families whose name > + matches the pattern are shown. > + If <literal>+</literal> is appended to the command name, procedures > + listed with its names. > </para> Based on a quick skim of the thread - which means I most definitely missed things - there's not been discussion of why we actually want to add this. Who's the prospective user of this facility? And why wouldn't they just query pg_am[proc]? None of this information seems like it's going to be even remotely targeted towards even advanced users. For developers it's not clear what these add? Adding stuff to psql isn't free. It adds clutter to psql's help output, the commands need to be maintained (including cross-version code). Greetings, Andres Freund
-
Re: Psql patch to show access methods info
Nikita Glukhov <n.gluhov@postgrespro.ru> — 2019-07-22T20:23:22Z
Attached 8th version of the patches. On 22.07.2019 15:58, Alexander Korotkov wrote: > On Mon, Jul 22, 2019 at 6:29 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >> On 2019-Jul-21, Alexander Korotkov wrote: >>> I've one note. Behavior of "\dA" and "\dA pattern" look >>> counter-intuitive to me. I would rather expect that "\dA pattern" >>> would just filter results of "\dA", but it displays different >>> information. I suggest rename displaying access method properties >>> from "\dA pattern" to different. >> \dA+ maybe? Then ... >> >>> And leave "\dA pattern" just filter results of "\dA". >> "\dA+ pattern" works intuitively, I think. > Sounds good for me. > > We already have some functionality for \dA+. > > # \dA+ > List of access methods > Name | Type | Handler | Description > --------+-------+----------------------+---------------------------------------- > brin | index | brinhandler | block range index (BRIN) access method > btree | index | bthandler | b-tree index access method > gin | index | ginhandler | GIN index access method > gist | index | gisthandler | GiST index access method > hash | index | hashhandler | hash index access method > heap | table | heap_tableam_handler | heap table access method > spgist | index | spghandler | SP-GiST index access method > (7 rows) > > What we need is that new \dA+ functionality cover existing one. That > it, we should add Handler and Description column to the output. > > # \dA+ * > Index access method properties > AM | Ordering | Unique indexes | Multicol indexes | Exclusion > constraints | Include non-key columns > --------+----------+----------------+------------------+-----------------------+------------------------- > brin | no | no | yes | no > | no > btree | yes | yes | yes | yes > | yes > gin | no | no | yes | no > | no > gist | no | no | yes | yes > | yes > hash | no | no | no | yes > | no > spgist | no | no | no | yes > | no > (6 rows) > > Table access method properties > Name | Type | Handler | Description > ------+-------+----------------------+-------------------------- > heap | table | heap_tableam_handler | heap table access method > (1 row) Columns "Handler" and "Description" were added to \dA+. \dA [NAME] now shows only amname and amtype. Also added support for pre-9.6 server versions to both \dA and \dA+. -- Nikita Glukhov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-07-22T22:57:29Z
On Mon, Jul 22, 2019 at 11:25 PM Nikita Glukhov <n.gluhov@postgrespro.ru> wrote: > Columns "Handler" and "Description" were added to \dA+. > > \dA [NAME] now shows only amname and amtype. Cool! > Also added support for pre-9.6 server versions to both \dA and \dA+. I was going to ask about that. You got ahead of me :-) In general, patchset is very cool. It was always scary there is no way in psql to see am/opclass/opfamily information rather than query catalog directly. Shape of patches also looks good. I'm going to push it. Probably, someone find that commands syntax and output formats are not well discussed yet. But we're pretty earlier in 13 release cycle. So, we will have time to work out a criticism if any. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Andres Freund <andres@anarazel.de> — 2019-07-23T02:40:27Z
Hi, On 2019-07-15 22:03:31 +0300, Nikita Glukhov wrote: > + <varlistentry> > + <term> > + <literal>\dAc[+] > + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link> > + [<link linkend="app-psql-patterns"><replaceable class="parameter">input-type-pattern</replaceable></link>]] > + </literal> > + </term> > + <listitem> > + <para> > + Shows info index access method operator classes listed in > + <xref linkend="catalog-pg-opclass-table"/>. > + If <replaceable class="parameter">access-method-patttern</replaceable> > + is specified, only operator classes associated with access method whose > + name matches pattern are shown. > + If <replaceable class="parameter">input-type-pattern</replaceable> > + is specified, only procedures associated with families whose input type > + matches the pattern are shown. > + If <literal>+</literal> is appended to the command name, operator family > + and owner are listed. > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > + <term> > + <literal>\dAo[+] > + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link> > + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]] > + </literal> > + </term> > + > + <listitem> > + <para> > + Lists operators (<xref linkend="catalog-pg-amop-table"/>) associated > + with access method operator families. If > + <replaceable class="parameter">access-method-patttern</replaceable> is > + specified, only operators associated with access method whose name > + matches pattern are shown. If > + <replaceable class="parameter">operator-family-pattern</replaceable> is > + specified, only operators associated with families whose name matches > + the pattern are shown. > + If <literal>+</literal> is appended to the command name, displays > + additional info. > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > + <term> > + <literal>\dAp[+] > + [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link> > + [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]] > + </literal> > + </term> > + <listitem> > + <para> > + Lists procedures (<xref linkend="catalog-pg-amproc-table"/>) associated > + with access method operator families. > + If <replaceable class="parameter">access-method-patttern</replaceable> > + is specified, only procedures associated with access method whose name > + matches pattern are shown. > + If <replaceable class="parameter">operator-family-pattern</replaceable> > + is specified, only procedures associated with families whose name > + matches the pattern are shown. > + If <literal>+</literal> is appended to the command name, procedures > + listed with its names. > </para> Based on a quick skim of the thread - which means I most definitely missed things - there's not been discussion of why we actually want to add this. Who's the prospective user of this facility? And why wouldn't they just query pg_am[proc]? None of this information seems like it's going to be even remotely targeted towards even advanced users. For developers it's not clear what these add? Adding stuff to psql isn't free. It adds clutter to psql's help output, the commands need to be maintained (including cross-version code). Greetings, Andres Freund
-
Re: Psql patch to show access methods info
Andres Freund <andres@anarazel.de> — 2019-07-23T02:41:39Z
Hi, On 2019-07-23 01:57:29 +0300, Alexander Korotkov wrote: > It was always scary there is no way in psql to see am/opclass/opfamily > information rather than query catalog directly. What does make that scary? > I'm going to push it. Probably, someone find that commands syntax and > output formats are not well discussed yet. But we're pretty earlier > in 13 release cycle. So, we will have time to work out a criticism if > any. Please don't before we've had some discussion as to why we want this additional code, and who'd be helped by it. Greetings, Andres Freund
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-07-24T13:54:15Z
Hi! On Wed, Jul 24, 2019 at 9:00 AM Andres Freund <andres@anarazel.de> wrote: > On 2019-07-23 01:57:29 +0300, Alexander Korotkov wrote: > > It was always scary there is no way in psql to see am/opclass/opfamily > > information rather than query catalog directly. > > What does make that scary? For it's unclear why do we have backslash commands for observing almost every part of system catalog, but this quite large part is missed. > > I'm going to push it. Probably, someone find that commands syntax and > > output formats are not well discussed yet. But we're pretty earlier > > in 13 release cycle. So, we will have time to work out a criticism if > > any. > > Please don't before we've had some discussion as to why we want this > additional code, and who'd be helped by it. OK. Given that few senior developers participate in discussion of details, I thought we kind of agree that need this. Now you've explicitly express other opinion, so let's discuss. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-07-24T13:59:11Z
On Wed, Jul 24, 2019 at 9:01 AM Andres Freund <andres@anarazel.de> wrote: > Based on a quick skim of the thread - which means I most definitely > missed things - there's not been discussion of why we actually want to > add this. Who's the prospective user of this facility? And why wouldn't > they just query pg_am[proc]? None of this information seems like it's > going to be even remotely targeted towards even advanced users. For > developers it's not clear what these add? I see your point regarding pg_am details. Probably nobody expect developers need this. And probably even developers don't need this, because it's easier to see IndexAmRoutine directly with more details. So, +1 for removing this. pg_amproc for gin/gist/sp-gist/brin is probably for developers. But I think pg_amproc for btree/hash could be useful for advanced users. btree/hash opclasses could be written by advanced users using pl/something, I've faced that several times. > Adding stuff to psql isn't free. It adds clutter to psql's help output, > the commands need to be maintained (including cross-version code). Sure. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-08-05T23:57:10Z
On Wed, Jul 24, 2019 at 4:59 PM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > On Wed, Jul 24, 2019 at 9:01 AM Andres Freund <andres@anarazel.de> wrote: > > Based on a quick skim of the thread - which means I most definitely > > missed things - there's not been discussion of why we actually want to > > add this. Who's the prospective user of this facility? And why wouldn't > > they just query pg_am[proc]? None of this information seems like it's > > going to be even remotely targeted towards even advanced users. For > > developers it's not clear what these add? > > I see your point regarding pg_am details. Probably nobody expect > developers need this. And probably even developers don't need this, > because it's easier to see IndexAmRoutine directly with more details. > So, +1 for removing this. > > pg_amproc for gin/gist/sp-gist/brin is probably for developers. But I > think pg_amproc for btree/hash could be useful for advanced users. > btree/hash opclasses could be written by advanced users using > pl/something, I've faced that several times. Revised patch is attached. Changes to \dA+ command are reverted. It also contains some minor improvements. Second patch looks problematic for me, because it provides index description alternative to \d+. IMHO, if there is something really useful to display about index, we should keep it in \d+. So, I propose to postpone this. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-13T21:36:12Z
On 2019-Aug-06, Alexander Korotkov wrote: > Revised patch is attached. Changes to \dA+ command are reverted. It > also contains some minor improvements. > > Second patch looks problematic for me, because it provides index > description alternative to \d+. IMHO, if there is something really > useful to display about index, we should keep it in \d+. So, I > propose to postpone this. Are you saying that we should mark this entire CF entry as Returned with Feedback? Or do you see a subset of your latest 0001 as a commitable patch? Thanks -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-14T07:39:55Z
On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > On 2019-Aug-06, Alexander Korotkov wrote: > > > Revised patch is attached. Changes to \dA+ command are reverted. It > > also contains some minor improvements. > > > > Second patch looks problematic for me, because it provides index > > description alternative to \d+. IMHO, if there is something really > > useful to display about index, we should keep it in \d+. So, I > > propose to postpone this. > > Are you saying that we should mark this entire CF entry as Returned with > Feedback? Or do you see a subset of your latest 0001 as a commitable > patch? Still hope to commit 0001. Please, don't mark RFC for now. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-14T08:14:59Z
On Sat, Sep 14, 2019 at 10:39 AM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera > <alvherre@2ndquadrant.com> wrote: > > On 2019-Aug-06, Alexander Korotkov wrote: > > > > > Revised patch is attached. Changes to \dA+ command are reverted. It > > > also contains some minor improvements. > > > > > > Second patch looks problematic for me, because it provides index > > > description alternative to \d+. IMHO, if there is something really > > > useful to display about index, we should keep it in \d+. So, I > > > propose to postpone this. > > > > Are you saying that we should mark this entire CF entry as Returned with > > Feedback? Or do you see a subset of your latest 0001 as a commitable > > patch? > > Still hope to commit 0001. Please, don't mark RFC for now. Sorry, I meant don't mark it RWF for now :) ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
vignesh C <vignesh21@gmail.com> — 2019-09-17T10:39:45Z
On Sat, Sep 14, 2019 at 1:45 PM Alexander Korotkov < a.korotkov@postgrespro.ru> wrote: > > On Sat, Sep 14, 2019 at 10:39 AM Alexander Korotkov > <a.korotkov@postgrespro.ru> wrote: > > On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera > > <alvherre@2ndquadrant.com> wrote: > > > On 2019-Aug-06, Alexander Korotkov wrote: > > > > > > > Revised patch is attached. Changes to \dA+ command are reverted. It > > > > also contains some minor improvements. > > > > > > > > Second patch looks problematic for me, because it provides index > > > > description alternative to \d+. IMHO, if there is something really > > > > useful to display about index, we should keep it in \d+. So, I > > > > propose to postpone this. > > > > > > Are you saying that we should mark this entire CF entry as Returned with > > > Feedback? Or do you see a subset of your latest 0001 as a commitable > > > patch? > > > > Still hope to commit 0001. Please, don't mark RFC for now. > > Sorry, I meant don't mark it RWF for now :) > Few Comments: + +\dA+ + List of access methods + Name | Type | Handler | Description +--------+-------+----------------------+---------------------------------------- + brin | Index | brinhandler | block range index (BRIN) access method We can add test for \dA+ brin btree When we specify multiple arguments along with \dA+, like in case of: \dA+ brin btree We should display a message like \d+: extra argument "btree" ignored. postgres=# \dA+ brin btree List of access methods Name | Type | Handler | Description ------+-------+-------------+---------------------------------------- brin | Index | brinhandler | block range index (BRIN) access method (1 row) Like in case of \d+ we get the message: postgres=# \d+ t1 t2 Table "public.t1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- c1 | integer | | | | plain | | Access method: heap \d+: extra argument "t2" ignored Regards, Vignesh EnterpriseDB: http://www.enterprisedb.com -
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-17T18:01:38Z
It seems strange that there's a way to display AMs, and a way to display ops and procs in an opfamily; but there's no way to list what opfamilies exist (possibly given an AM as pattern). Should we add that too? We had \dAf in the original submission, but that seems to have lost along the way, not sure why. I think \dAf is just as critical as \dAo; the former lets you know which opfamilies you can use in CREATE INDEX, while the latter lets you know which operators would be helped by such an index. (But, really, only if the opfamily name is printed in \d of the index, which we currently don't print unless it's non-default ... which is an omission that perhaps we should consider fixing). On the other hand, from a user perspective, what you really want to know is: what opfamilies exist for datatype T, and what operators are supported by the opfamily I have chosen? The current patch doesn't really help you find that out. I think \dAp isn't terribly informative from a user perspective. The support procs are just an opfamily implementation detail. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-18T10:37:51Z
On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > It seems strange that there's a way to display AMs, and a way to display > ops and procs in an opfamily; but there's no way to list what opfamilies > exist (possibly given an AM as pattern). Should we add that too? We > had \dAf in the original submission, but that seems to have lost along > the way, not sure why. > > I think \dAf is just as critical as \dAo; the former lets you know which > opfamilies you can use in CREATE INDEX, while the latter lets you know > which operators would be helped by such an index. (But, really, only if > the opfamily name is printed in \d of the index, which we currently > don't print unless it's non-default ... which is an omission that > perhaps we should consider fixing). > > On the other hand, from a user perspective, what you really want to know > is: what opfamilies exist for datatype T, and what operators are > supported by the opfamily I have chosen? The current patch doesn't > really help you find that out. I think you have a point. Will add \dAf command to the patch. > I think \dAp isn't terribly informative from a user perspective. The > support procs are just an opfamily implementation detail. I've expressed my opinion regarding \dAp in [1]. In my observations, some advanced users can write btree/hash opclasses in pl/* languages. This doesn't require knowledge of core developer. And they may find \dAp command useful. What do you think? Links 1. https://www.postgresql.org/message-id/CAPpHfdtj_w20hTr4fHW4MnpL-pPGU3Mw0A9pRTRBL_XP-WGsyQ%40mail.gmail.com ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2019-09-18T14:04:40Z
On 2019-Sep-18, Alexander Korotkov wrote: > On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > I think \dAf is just as critical as \dAo; the former lets you know which > > opfamilies you can use in CREATE INDEX, while the latter lets you know > > which operators would be helped by such an index. (But, really, only if > > the opfamily name is printed in \d of the index, which we currently > > don't print unless it's non-default ... which is an omission that > > perhaps we should consider fixing). > I think you have a point. Will add \dAf command to the patch. Great, thanks. I think in order for this feature to be more complete "\d index" should show the opfamily name, also, even when it's the default one. (Let's not put the opfamily when it's the default in "\d table", as we do when the opfamily is not default; that would lead, I think, to too much clutter.) > > On the other hand, from a user perspective, what you really want to know > > is: what opfamilies exist for datatype T, and what operators are > > supported by the opfamily I have chosen? The current patch doesn't > > really help you find that out. I hope that in some future somebody will contribute towards this, which I think is more important (from users POV) than the below one: > > I think \dAp isn't terribly informative from a user perspective. The > > support procs are just an opfamily implementation detail. > > I've expressed my opinion regarding \dAp in [1]. In my observations, > some advanced users can write btree/hash opclasses in pl/* languages. > This doesn't require knowledge of core developer. And they may find > \dAp command useful. What do you think? I have never tried or had the need to do that. I'll take your word for it, so I have no objection. I do wonder if \? is going to end up with too much clutter, and if so do we need to make \? show only the most important commands and relegate some others to \?+ ... however, going over the existing \? I see no command that I would move to \?+ so \dAp would be alone there, which would be pretty strange. So let's forget this angle for now; but if psql acquires too much "system innards" functionality then I say we should consider it. Thanks -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2019-09-19T09:47:08Z
Hello. FWIW.. At Wed, 18 Sep 2019 11:04:40 -0300, Alvaro Herrera <alvherre@2ndquadrant.com> wrote in <20190918140440.GA28323@alvherre.pgsql> > I think in order for this feature to be more complete "\d index" should > show the opfamily name, also, even when it's the default one. (Let's > not put the opfamily when it's the default in "\d table", as we do when > the opfamily is not default; that would lead, I think, to too much > clutter.) > > > > On the other hand, from a user perspective, what you really want to know > > > is: what opfamilies exist for datatype T, and what operators are > > > supported by the opfamily I have chosen? The current patch doesn't > > > really help you find that out. I have thought that several times. > I hope that in some future somebody will contribute towards this, which > I think is more important (from users POV) than the below one: > > > > I think \dAp isn't terribly informative from a user perspective. The > > > support procs are just an opfamily implementation detail. > > > > I've expressed my opinion regarding \dAp in [1]. In my observations, > > some advanced users can write btree/hash opclasses in pl/* languages. > > This doesn't require knowledge of core developer. And they may find > > \dAp command useful. What do you think? > > I have never tried or had the need to do that. I'll take your word for > it, so I have no objection. > > I do wonder if \? is going to end up with too much clutter, and if so do > we need to make \? show only the most important commands and relegate > some others to \?+ ... however, going over the existing \? I see no > command that I would move to \?+ so \dAp would be alone there, which > would be pretty strange. So let's forget this angle for now; but if > psql acquires too much "system innards" functionality then I say we > should consider it. Before the fact that usable slot of two-letter commands is almost filled, my poor memory rejects to remember the commands that is used infrequently.. ctrl-I suggests many two-or-three letter meta commands but I can't tell what is the command I'm searching for. \? shows too many commands as you mentioned. If something like "\? | grep index" works, it would be helpful. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2019-09-23T19:54:51Z
On Wed, Sep 18, 2019 at 5:04 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > On 2019-Sep-18, Alexander Korotkov wrote: > > > On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > > > I think \dAf is just as critical as \dAo; the former lets you know which > > > opfamilies you can use in CREATE INDEX, while the latter lets you know > > > which operators would be helped by such an index. (But, really, only if > > > the opfamily name is printed in \d of the index, which we currently > > > don't print unless it's non-default ... which is an omission that > > > perhaps we should consider fixing). > > > I think you have a point. Will add \dAf command to the patch. > > Great, thanks. Revised patch is attached. 1) It adds \dAf[+] command showing opfamilies, which belong to given AM and have opclasses for given datatype. 2) It turns back warning when running \dA[+] with 2 or more arguments. Two questions are open for me: 1) Currently we allow to filter opfamilies by type, but supported types aren't displayed. Should we display datatypes? Should we aggregate them into comma-separated list? 2) Given we now can display the list of opfamilies, it would be reasonable to be able to see list of opclasses belonging to particular opfamily. But currently \dAc doesn't have filter by opclass. Should we implement this as an separate command? I'll be very glad for feedback. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Michael Paquier <michael@paquier.xyz> — 2019-11-27T08:05:08Z
Hi Alexander, On Mon, Sep 23, 2019 at 10:54:51PM +0300, Alexander Korotkov wrote: > Revised patch is attached. The commit log of the patch reads like that: "Fix handling Inf and Nan values in GiST pairing heap comparator" That's obviously incorrect. Do you have an updated patch? I am moving that to next CF waiting on author. -- Michael
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2020-01-21T02:59:18Z
Hi, Michael! On Wed, Nov 27, 2019 at 11:05 AM Michael Paquier <michael@paquier.xyz> wrote: > On Mon, Sep 23, 2019 at 10:54:51PM +0300, Alexander Korotkov wrote: > > Revised patch is attached. > > The commit log of the patch reads like that: > "Fix handling Inf and Nan values in GiST pairing heap comparator" > > That's obviously incorrect. Do you have an updated patch? I am > moving that to next CF waiting on author. Sorry for this stupid error and for fixing it this late. Correct patch is attached. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-01-21T22:33:29Z
I think I would like this feature to be in, but I'm not sure that the shape is final yet. My points: a) I don't see any use for \dA as presented; I think the \dA+ output is useful. Therefore my preference would be that \dA presents what the latest patch has as \dA+. I think we should leave \dA+ unimplemented for now; maybe we can use some use for it later on. b) I think \dAp should list the function used for each support proc. I don't have any use for \dAp actually (I already said that upthread, sorry for repeating myself), but I think that if we have it, then showing only the proc number is pointless. c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that each opfamily has opclasses for. Maybe make the output an array, like {int4,int8,numeric,...} Something like [*] but somehow make it prettier? d) This one I'm unsure about: should we list the opfamily for each opclass in \dAc? I'm not sure whether it's useful for anything. [*] SELECT DISTINCT am.amname AS "AM", CASE WHEN pg_catalog.pg_opfamily_is_visible(f.oid) THEN format('%I', f.opfname) ELSE format('%I.%I', n.nspname, f.opfname) END AS "Operator family", string_agg(format_type(c.opcintype, -1), ', ') as "Applicable types", pg_catalog.pg_get_userbyid(f.opfowner) AS "Owner" FROM pg_catalog.pg_opfamily f LEFT JOIN pg_catalog.pg_am am on am.oid = f.opfmethod LEFT JOIN pg_catalog.pg_namespace n ON n.oid = f.opfnamespace left join pg_catalog.pg_opclass c on (f.oid = c.opcfamily) group by 1, 2, 4 ORDER BY 1, 2; -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-01-21T22:37:26Z
On 2020-Jan-21, Alvaro Herrera wrote: > c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that > each opfamily has opclasses for. Maybe make the output an array, like > {int4,int8,numeric,...} Something like [*] but somehow make it > prettier? Sorry, I forgot to copy-edit my text here: I said "make it prettier", but the query I submitted is already pretty enough ISTM; I had written that comment when I only had the array_agg() version, but then I changed it to string_agg() and that seems to have mostly done the trick. Maybe improve the format_type() bit to omit the quotes, if possible, but that doesn't seem a big deal. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -
Re: Psql patch to show access methods info
David Steele <david@pgmasters.net> — 2020-03-02T13:33:04Z
Hi Alexander, On 1/21/20 5:37 PM, Alvaro Herrera wrote: > On 2020-Jan-21, Alvaro Herrera wrote: > >> c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that >> each opfamily has opclasses for. Maybe make the output an array, like >> {int4,int8,numeric,...} Something like [*] but somehow make it >> prettier? > > Sorry, I forgot to copy-edit my text here: I said "make it prettier", > but the query I submitted is already pretty enough ISTM; I had written > that comment when I only had the array_agg() version, but then I changed > it to string_agg() and that seems to have mostly done the trick. Maybe > improve the format_type() bit to omit the quotes, if possible, but that > doesn't seem a big deal. The last CF for PG13 has now started. Do you know when you'll be able to supply a new patch to address Álvaro's review? Regards, -- -David david@pgmasters.net -
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2020-03-03T23:31:43Z
Hi! Thank you for the review. Revised patch is attached. On Wed, Jan 22, 2020 at 1:33 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > I think I would like this feature to be in, but I'm not sure that the > shape is final yet. My points: > > a) I don't see any use for \dA as presented; I think the \dA+ output is > useful. Therefore my preference would be that \dA presents what the > latest patch has as \dA+. I think we should leave \dA+ unimplemented > for now; maybe we can use some use for it later on. Neither \dA or \dA+ are introduced or affected by this patch. If we like to change their behavior, we should probably do this separately from this patch. > b) I think \dAp should list the function used for each support proc. I > don't have any use for \dAp actually (I already said that upthread, > sorry for repeating myself), but I think that if we have it, then > showing only the proc number is pointless. It was shown by \dAp+. But I agree that it's essential information that is unreasonable to hide under verbose option. So, procedure name is always shown now. I've also renamed "Support function" column to "Number". > c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that > each opfamily has opclasses for. Maybe make the output an array, like > {int4,int8,numeric,...} Something like [*] but somehow make it > prettier? I made this change, but using subselect in target list. It's probably slower query, but better code readability IMHO. > d) This one I'm unsure about: should we list the opfamily for each > opclass in \dAc? I'm not sure whether it's useful for anything. It's already shown by \dAc+ and I think this behavior is fine. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -
Re: Psql patch to show access methods info
Alvaro Herrera <alvherre@2ndquadrant.com> — 2020-03-04T02:59:00Z
On 2020-Mar-04, Alexander Korotkov wrote: > On Wed, Jan 22, 2020 at 1:33 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > I think I would like this feature to be in, but I'm not sure that the > > shape is final yet. My points: > > > > a) I don't see any use for \dA as presented; I think the \dA+ output is > > useful. Therefore my preference would be that \dA presents what the > > latest patch has as \dA+. I think we should leave \dA+ unimplemented > > for now; maybe we can use some use for it later on. > > Neither \dA or \dA+ are introduced or affected by this patch. If we > like to change their behavior, we should probably do this separately > from this patch. Doh, you're right, sorry. Looking only at the regress/expected/psql.out changes, I'm satisfied with this version of the patch. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
Re: Psql patch to show access methods info
vignesh C <vignesh21@gmail.com> — 2020-03-05T17:34:04Z
On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > > Hi! > > Thank you for the review. Revised patch is attached. > Thanks for working on comments and providing a new patch. One small observation I noticed: postgres=# \*dAc* brin oid Index access method operator classes AM | Input type | Storage type | Operator class | Default? ------+------------+--------------+----------------+---------- brin | oid | | oid_minmax_ops | yes (1 row) postgres=# \*dAcx* brin oid Index access method operator classes AM | Input type | Storage type | Operator class | Default? ------+------------+--------------+----------------+---------- brin | oid | | oid_minmax_ops | yes (1 row) Output of \dAc and \dAcx seems to be same. Is this expected? Regards, Vignesh EnterpriseDB: http://www.enterprisedb.com -
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2020-03-06T00:57:57Z
On Thu, Mar 5, 2020 at 8:34 PM vignesh C <vignesh21@gmail.com> wrote: > On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > > > > Hi! > > > > Thank you for the review. Revised patch is attached. > > > > Thanks for working on comments and providing a new patch. > One small observation I noticed: > postgres=# \dAc brin oid > Index access method operator classes > AM | Input type | Storage type | Operator class | Default? > ------+------------+--------------+----------------+---------- > brin | oid | | oid_minmax_ops | yes > (1 row) > > postgres=# \dAcx brin oid > Index access method operator classes > AM | Input type | Storage type | Operator class | Default? > ------+------------+--------------+----------------+---------- > brin | oid | | oid_minmax_ops | yes > (1 row) > > Output of \dAc and \dAcx seems to be same. Is this expected? It might seem strange, but majority of psql commands allows arbitrary suffixes and ignore them. For instance: postgres=# \dt Did not find any relations. postgres=# \dtttttt Did not find any relations. I think if we want to fix this, we should do it in a separate path, which would fix at the psql commands. BTW, new revision of the patch is attached. It contains cosmetic changes to the documentation, comments etc. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
vignesh C <vignesh21@gmail.com> — 2020-03-06T04:09:51Z
On Fri, Mar 6, 2020 at 6:28 AM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > > On Thu, Mar 5, 2020 at 8:34 PM vignesh C <vignesh21@gmail.com> wrote: > > On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > > > > > > Hi! > > > > > > Thank you for the review. Revised patch is attached. > > > > > > > Thanks for working on comments and providing a new patch. > > One small observation I noticed: > > postgres=# \dAc brin oid > > Index access method operator classes > > AM | Input type | Storage type | Operator class | Default? > > ------+------------+--------------+----------------+---------- > > brin | oid | | oid_minmax_ops | yes > > (1 row) > > > > postgres=# \dAcx brin oid > > Index access method operator classes > > AM | Input type | Storage type | Operator class | Default? > > ------+------------+--------------+----------------+---------- > > brin | oid | | oid_minmax_ops | yes > > (1 row) > > > > Output of \dAc and \dAcx seems to be same. Is this expected? > > It might seem strange, but majority of psql commands allows arbitrary > suffixes and ignore them. For instance: > > postgres=# \dt > Did not find any relations. > postgres=# \dtttttt > Did not find any relations. > > I think if we want to fix this, we should do it in a separate path, > which would fix at the psql commands. > I feel your explanation sounds fair to me. Regards, Vignesh EnterpriseDB: http://www.enterprisedb.com
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2020-03-06T08:46:26Z
On Fri, Mar 6, 2020 at 7:10 AM vignesh C <vignesh21@gmail.com> wrote: > I feel your explanation sounds fair to me. Thanks. I've also revised tab-completion code. I'm going to push this if no objections. ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
-
Re: Psql patch to show access methods info
Alexander Korotkov <a.korotkov@postgrespro.ru> — 2020-03-08T10:47:31Z
On Fri, Mar 6, 2020 at 11:46 AM Alexander Korotkov <a.korotkov@postgrespro.ru> wrote: > On Fri, Mar 6, 2020 at 7:10 AM vignesh C <vignesh21@gmail.com> wrote: > > I feel your explanation sounds fair to me. > > Thanks. > > I've also revised tab-completion code. I'm going to push this if no objections. So, pushed! ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company