Re: Psql patch to show access methods info
On Fri, Mar 6, 2020 at 11:46 AM Alexander Korotkov wrote: > On Fri, Mar 6, 2020 at 7:10 AM vignesh C 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
Re: Psql patch to show access methods info
On Fri, Mar 6, 2020 at 7:10 AM vignesh C 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 0001-Add-psql-AM-info-commands-v14.patch Description: Binary data
Re: Psql patch to show access methods info
On Fri, Mar 6, 2020 at 6:28 AM Alexander Korotkov wrote: > > On Thu, Mar 5, 2020 at 8:34 PM vignesh C wrote: > > On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov > > 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=# \dtt > 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
On Thu, Mar 5, 2020 at 8:34 PM vignesh C wrote: > On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov > 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=# \dtt 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 0001-Add-psql-AM-info-commands-v13.patch Description: Binary data
Re: Psql patch to show access methods info
On Wed, Mar 4, 2020 at 5:02 AM Alexander Korotkov 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
On 2020-Mar-04, Alexander Korotkov wrote: > On Wed, Jan 22, 2020 at 1:33 AM Alvaro Herrera > 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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
Hi!
Thank you for the review. Revised patch is attached.
On Wed, Jan 22, 2020 at 1:33 AM Alvaro Herrera 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
0001-Add-psql-AM-info-commands-v12.patch
Description: Binary data
Re: Psql patch to show access methods info
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
da...@pgmasters.net
Re: Psql patch to show access methods info
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
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 signature.asc Description: PGP signature
Re: Psql patch to show access methods info
On Wed, Sep 18, 2019 at 5:04 PM Alvaro Herrera wrote: > On 2019-Sep-18, Alexander Korotkov wrote: > > > On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera > > 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 0001-Add-psql-AM-info-commands-v10.patch Description: Binary data
Re: Psql patch to show access methods info
On 2019-Sep-18, Alexander Korotkov wrote: > On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera > 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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
On Tue, Sep 17, 2019 at 9:01 PM Alvaro Herrera 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
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
On Sat, Sep 14, 2019 at 1:45 PM Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > > On Sat, Sep 14, 2019 at 10:39 AM Alexander Korotkov > wrote: > > On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera > > 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
On Sat, Sep 14, 2019 at 10:39 AM Alexander Korotkov wrote: > On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera > 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
On Sat, Sep 14, 2019 at 12:36 AM Alvaro Herrera 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
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
On Wed, Jul 24, 2019 at 4:59 PM Alexander Korotkov wrote: > On Wed, Jul 24, 2019 at 9:01 AM Andres Freund 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 0001-Add-psql-AM-info-commands-v09.patch Description: Binary data
Re: Psql patch to show access methods info
On Wed, Jul 24, 2019 at 9:01 AM Andres Freund 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
Hi! On Wed, Jul 24, 2019 at 9:00 AM Andres Freund 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
Hi, On 2019-07-15 22:03:31 +0300, Nikita Glukhov wrote: > + > + > + \dAc[+] > +[ class="parameter">access-method-pattern > + [ class="parameter">input-type-pattern]] > + > + > + > + > +Shows info index access method operator classes listed in > +. > +If class="parameter">access-method-patttern > +is specified, only operator classes associated with access method > whose > +name matches pattern are shown. > +If input-type-pattern > +is specified, only procedures associated with families whose input > type > +matches the pattern are shown. > +If + is appended to the command name, operator > family > +and owner are listed. > + > + > + > + > + > + > + \dAo[+] > +[ class="parameter">access-method-pattern > + [ class="parameter">operator-family-pattern]] > + > + > + > + > + > +Lists operators () associated > +with access method operator families. If > +access-method-patttern > is > +specified, only operators associated with access method whose name > +matches pattern are shown. If > +operator-family-pattern > is > +specified, only operators associated with families whose name matches > +the pattern are shown. > +If + is appended to the command name, displays > +additional info. > + > + > + > + > + > + > + \dAp[+] > +[ class="parameter">access-method-pattern > + [ class="parameter">operator-family-pattern]] > + > + > + > + > +Lists procedures () > associated > +with access method operator families. > +If class="parameter">access-method-patttern > +is specified, only procedures associated with access method whose > name > +matches pattern are shown. > +If class="parameter">operator-family-pattern > +is specified, only procedures associated with families whose name > +matches the pattern are shown. > +If + is appended to the command name, procedures > +listed with its names. > 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
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
Hi, On 2019-07-15 22:03:31 +0300, Nikita Glukhov wrote: > + > + > + \dAc[+] > +[ class="parameter">access-method-pattern > + [ class="parameter">input-type-pattern]] > + > + > + > + > +Shows info index access method operator classes listed in > +. > +If class="parameter">access-method-patttern > +is specified, only operator classes associated with access method > whose > +name matches pattern are shown. > +If input-type-pattern > +is specified, only procedures associated with families whose input > type > +matches the pattern are shown. > +If + is appended to the command name, operator > family > +and owner are listed. > + > + > + > + > + > + > + \dAo[+] > +[ class="parameter">access-method-pattern > + [ class="parameter">operator-family-pattern]] > + > + > + > + > + > +Lists operators () associated > +with access method operator families. If > +access-method-patttern > is > +specified, only operators associated with access method whose name > +matches pattern are shown. If > +operator-family-pattern > is > +specified, only operators associated with families whose name matches > +the pattern are shown. > +If + is appended to the command name, displays > +additional info. > + > + > + > + > + > + > + \dAp[+] > +[ class="parameter">access-method-pattern > + [ class="parameter">operator-family-pattern]] > + > + > + > + > +Lists procedures () > associated > +with access method operator families. > +If class="parameter">access-method-patttern > +is specified, only procedures associated with access method whose > name > +matches pattern are shown. > +If class="parameter">operator-family-pattern > +is specified, only procedures associated with families whose name > +matches the pattern are shown. > +If + is appended to the command name, procedures > +listed with its names. > 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
On Mon, Jul 22, 2019 at 11:25 PM Nikita Glukhov 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
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 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 >From f2eadc2ca4e9593292748890fd62273c59e05b5f Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Mon, 15 Jul 2019 15:52:49 +0300 Subject: [PATCH 1/2] Add psql AM info commands --- doc/src/sgml/catalogs.sgml | 8 +- doc/src/sgml/ref/psql-ref.sgml | 81 ++- src/bin/psql/command.c | 20 +- src/bin/psql/describe.c| 430 ++--- src/bin/psql/describe.h| 19 +- src/bin/psql/help.c| 6 +- src/bin/psql/tab-complete.c| 16 +- src/test/regress/expected/psql.out | 141 src/test/regress/sql/psql.sql | 15 ++ 9 files changed, 696 insertions(+), 40 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 68ad507..ec79c11 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -681,7 +681,7 @@ search and ordering purposes.) - + pg_amop Columns @@ -824,7 +824,7 @@ is one row for each support function belonging to an operator family. - + pg_amproc Columns @@ -4467,7 +4467,7 @@ SCRAM-SHA-256$iteration count: Operator classes are described at length in . - + pg_opclass Columns @@ -4729,7 +4729,7 @@ SCRAM-SHA-256$iteration count: Operator families are described at length in . - + pg_opfamily Columns diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 7789fc6..e690c4d 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1222,11 +1222,82 @@ testdb= -Lists access methods. If pattern is specified, only access -methods whose names match the pattern are shown. If -+ is appended to the command name, each access -method is listed with its associated handler function and description. +Lists access methods with their associated handler function. If ++ is appended to the command name, additional +description is provided. +If pattern is specified, +the command displays the properties of the access methods whose names +match the search pattern. + + +
Re: Psql patch to show access methods info
On Mon, Jul 22, 2019 at 6:29 AM Alvaro Herrera 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
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
On Mon, Jul 15, 2019 at 10:05 PM Nikita Glukhov wrote: > On 01.07.2019 14:06, Thomas Munro wrote: > > > On Sun, Mar 31, 2019 at 2:13 PM 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
On 01.07.2019 14:06, Thomas Munro wrote:
On Sun, Mar 31, 2019 at 2:13 PM 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
>From c52bec3d2d5fa13b2b72a9e5367c98affa6b6227 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov
Date: Mon, 15 Jul 2019 15:52:49 +0300
Subject: [PATCH 1/2] Add psql AM info commands
---
doc/src/sgml/catalogs.sgml | 8 +-
doc/src/sgml/ref/psql-ref.sgml | 81 +++-
src/bin/psql/command.c | 20 +-
src/bin/psql/describe.c| 416 +++--
src/bin/psql/describe.h| 19 +-
src/bin/psql/help.c| 6 +-
src/bin/psql/tab-complete.c| 16 +-
src/test/regress/expected/psql.out | 55 +
src/test/regress/sql/psql.sql | 8 +
9 files changed, 596 insertions(+), 33 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 68ad507..ec79c11 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -681,7 +681,7 @@
search and ordering purposes.)
-
+
pg_amop Columns
@@ -824,7 +824,7 @@
is one row for each support function belonging to an operator family.
-
+
pg_amproc Columns
@@ -4467,7 +4467,7 @@ SCRAM-SHA-256$iteration count:
Operator classes are described at length in .
-
+
pg_opclass Columns
@@ -4729,7 +4729,7 @@ SCRAM-SHA-256$iteration count:
Operator families are described at length in .
-
+
pg_opfamily Columns
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7789fc6..e690c4d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1222,11 +1222,82 @@ testdb=
-Lists access methods. If pattern is specified, only access
-methods whose names match the pattern are shown. If
-+ is appended to the command name, each access
-method is listed with its associated handler function and description.
+Lists access methods with their associated handler function. If
++ is appended to the command name, additional
+description is provided.
+If pattern is specified,
+the command displays the properties of the access methods whose names
+match the search pattern.
+
+
+
+
+
+
+ \dAc[+]
+[access-method-pattern
+ [input-type-pattern]]
+
+
+
+
+Shows info index access method operator classes listed in
+.
+If access-method-patttern
+is specified, only operator classes associated with access method whose
+name matches pattern are shown.
+If input-type-pattern
+is specified, only procedures associated with families whose input type
+matches the pattern are shown.
+If + is appended to the command name, operator family
+and owner are listed.
+
+
+
+
+
+
+ \dAo[+]
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+
+Lists operators () associated
+with access method operator families. If
+access-method-patttern is
+specified, only operators associated with access method whose name
+matches pattern are shown. If
+operator-family-pattern is
+specified, only operators associated with families whose name matches
+the pattern are shown.
+If + is appended to the command name, displays
+additional info.
+
+
+
+
+
+
+ \dAp[+]
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+Lists procedures () associated
+with access method operator families.
+If access-method-patttern
+is specified, only procedures associated with access method whose name
+matches pattern are shown.
+If operator-family-pattern
+is specified, only procedures associated with families whose name
+matches the pattern are shown.
+If + is appended to the command name, procedures
+listed with its names.
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index c0a7a55..e6cb260 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -722,7 +722,25 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
-success = describeAccessMethods(pattern, show_verbose);
+{
+
Re: Psql patch to show access methods info
On Sun, Mar 31, 2019 at 2:13 PM 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
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 constraintsyes/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| <<
Re: Psql patch to show access methods info
Thank you for the new version. At Fri, 22 Mar 2019 21:29:09 +0300, Sergey Cherkashin wrote in > 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 constraintsyes/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 |
Re: Psql patch to show access methods info
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
Re: Psql patch to show access methods info
Hi. On 08.03.2019 7:52, Kyotaro HORIGUCHI wrote: Hello. At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkas...@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da...@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?
Re: Re: Psql patch to show access methods info
Hi Sergey, On 3/8/19 8:52 AM, Kyotaro HORIGUCHI wrote: At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkas...@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da...@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 seems to me to be the place. (It could be shown also in \di+, but that
Re: Psql patch to show access methods info
Hello. At Mon, 10 Dec 2018 19:38:39 +0300, s.cherkas...@postgrespro.ru wrote in <70e94e339dd0fa2be5d3eebec68da...@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 seems to me to be the place. (It could be shown also in \di+, but that looks a bit odd to me.) \d+ is already showing (ASC)/DESC, and
Re: Psql patch to show access methods info
On Mon, Dec 10, 2018 at 07:38:39PM +0300, s.cherkas...@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 signature.asc Description: PGP signature
Re: Psql patch to show access methods info
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.diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c134bca809..e25412b7ce 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -675,7 +675,7 @@
search and ordering purposes.)
-
+
pg_amop Columns
@@ -818,7 +818,7 @@
is one row for each support function belonging to an operator family.
-
+
pg_amproc Columns
@@ -4421,7 +4421,7 @@ SCRAM-SHA-256$iteration count:
Operator classes are described at length in .
-
+
pg_opclass Columns
@@ -4683,7 +4683,7 @@ SCRAM-SHA-256$iteration count:
Operator families are described at length in .
-
+
pg_opfamily Columns
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6e6d0f42d1..bee8987c85 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1204,6 +1204,83 @@ testdb=
+
+
+ \dAfo
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+
+Lists operators () associated
+with access method operator families. If
+access-method-patttern is
+specified, only operators associated with access method whose name
+matches pattern are shown. If
+operator-family-pattern is
+specified, only operators associated with families whose name matches
+the pattern are shown.
+
+
+
+
+
+
+ \dAfp
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+List procedures () associated
+with access method operator families.
+If access-method-patttern
+is specified, only procedures associated with access method whose name
+matches pattern are shown.
+If operator-family-pattern
+is specified, only procedures associated with families whose name
+matches the pattern are shown.
+
+
+
+
+
+
+ \dAoc
+[access-method-pattern
+ [operator-class-pattern]]
+
+
+
+
+Shows index access method operator classes listed in
+.
+If access-method-patttern
+is specified, only operator classes associated with access method whose
+name matches pattern are shown.
+If operator-class-pattern
+is specified, only procedures associated with families whose name
+matches the pattern are shown.
+
+
+
+
+
+\dAp [ pattern ]
+
+
+
+Shows access method properties listed in
+.
+If pattern is specified,
+only access methods whose names match the pattern are shown.
+
+
+
+
\db[+] [ pattern ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ee88e1ca5c..7c35aed018 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -719,7 +719,24 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
-success = describeAccessMethods(pattern, show_verbose);
+{
+ char *pattern2 = NULL;
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);
+
+ if (strncmp(cmd, "dAp", 3) == 0)
+ success = describeAccessMethodProperties(pattern);
+ else if (strncmp(cmd, "dAfo", 4) == 0)
+ success = listFamilyClassOperators(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAfp", 4) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAoc", 4) == 0)
+ success = describeAccessMethodOperatorClasses(pattern, pattern2, show_verbose);
+ else
+ success = describeAccessMethods(pattern, show_verbose);
+ if (pattern2)
+ free(pattern2);
+}
break;
case 'a':
success = describeAggregates(pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 0a181b01d9..4947fb69fa 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5611,3 +5611,305 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \dAp
+ * Describes access method properties.
+ *
+ * Takes an optional regexp to select particular access methods
+ */
+bool
+describeAccessMethodProperties(const char *pattern)
+{
+ PQExpBufferData buf;
+
Re: Psql patch to show access methods info
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
signature.asc
Description: PGP signature
Re: Psql patch to show access methods info
> \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.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c134bca809..e25412b7ce 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -675,7 +675,7 @@
search and ordering purposes.)
-
+
pg_amop Columns
@@ -818,7 +818,7 @@
is one row for each support function belonging to an operator family.
-
+
pg_amproc Columns
@@ -4421,7 +4421,7 @@ SCRAM-SHA-256$iteration count:
Operator classes are described at length in .
-
+
pg_opclass Columns
@@ -4683,7 +4683,7 @@ SCRAM-SHA-256$iteration count:
Operator families are described at length in .
-
+
pg_opfamily Columns
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6e6d0f42d1..fcde01b2d4 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1204,6 +1204,105 @@ testdb=
+
+
+ \dAf
+ [ access-method-pattern
+[operator-family-pattern]]
+
+
+
+
+
+Lists operator families ().
+If access-method-pattern
+is specified, only families whose access method name matches the pattern
+are shown. If
+operator-family-pattern
+is specified, only operator families associated with whose name matches
+the pattern are shown. If + is appended to the
+command name, each operator family is listed with it's owner.
+
+
+
+
+
+
+ \dAfo
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+
+Lists operators () associated
+with access method operator families. If
+access-method-patttern is
+specified, only operators associated with access method whose name
+matches pattern are shown. If
+operator-family-pattern is
+specified, only operators associated with families whose name matches
+the pattern are shown.
+
+
+
+
+
+
+ \dAfp
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+List procedures () associated
+with access method operator families.
+If access-method-patttern
+is specified, only procedures associated with access method whose name
+matches pattern are shown.
+If operator-family-pattern
+is specified, only procedures associated with families whose name
+matches the pattern are shown.
+
+
+
+
+
+
+ \dAoc
+[access-method-pattern
+ [operator-class-pattern]]
+
+
+
+
+Shows index access method operator classes listed in
+.
+If access-method-patttern
+is specified, only operator classes associated with access method whose
+name matches pattern are shown.
+If operator-class-pattern
+is specified, only procedures associated with families whose name
+matches the pattern are shown.
+
+
+
+
+
+\dAp [ pattern ]
+
+
+
+Shows access method properties listed in
+.
+If pattern is specified,
+only access methods whose names match the pattern are shown.
+
+
+
+
\db[+] [ pattern ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ee88e1ca5c..4d0f619186 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -719,7 +719,23 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
-success = describeAccessMethods(pattern, show_verbose);
+{
+ char *pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);
+
+ if (strncmp(cmd, "dAp", 3) == 0)
+ success = describeAccessMethodProperties(pattern);
+ else if (strncmp(cmd, "dAfo", 4) == 0)
+ success = listFamilyClassOperators(pattern, pattern2);
+ else if (strncmp(cmd, "dAfp", 4) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2);
+ else if (strncmp(cmd, "dAf", 3) == 0)
+ success = listAccessMethodOperatorFamilies(pattern, pattern2, show_verbose);
+ else if
Re: Psql patch to show access methods info
Hello, On 20.11.2018 16:08, s.cherkas...@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
On 2018-Nov-20, s.cherkas...@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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
Ok, I fixed this.
On 2018-11-20 13:41, Alvaro Herrera wrote:
On 2018-Nov-20, s.cherkas...@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".
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 8b7f169d50..565b1c396a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -675,7 +675,7 @@
search and ordering purposes.)
-
+
pg_amop Columns
@@ -818,7 +818,7 @@
is one row for each support function belonging to an operator family.
-
+
pg_amproc Columns
@@ -4430,7 +4430,7 @@ SCRAM-SHA-256$iteration count:
Operator classes are described at length in .
-
+
pg_opclass Columns
@@ -4692,7 +4692,7 @@ SCRAM-SHA-256$iteration count:
Operator families are described at length in .
-
+
pg_opfamily Columns
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6e6d0f42d1..7f2631d75d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1203,6 +1203,95 @@ testdb=
+
+
+
+ \dAf
+ [ access-method-pattern
+[operator-family-pattern]]
+
+
+
+
+
+Lists operator families (). If access-method-pattern is specified, only
+families whose access method name matches the pattern are shown.
+If operator-family-pattern is specified, only
+opereator families associated with whose name matches the pattern are shown.
+If + is appended to the command name, each operator
+family is listed with it's owner.
+
+
+
+
+
+
+ \dAfo
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+
+Lists operators () associated with access method operator families.
+If access-method-patttern is specified,
+only operators associated with access method whose name matches pattern are shown.
+If operator-family-pattern is specified, only
+opereators associated with families whose name matches the pattern are shown.
+
+
+
+
+
+
+ \dAfp
+[access-method-pattern
+ [operator-family-pattern]]
+
+
+
+
+List procedures () accociated with access method operator families.
+If access-method-patttern is specified,
+only procedures associated with access method whose name matches pattern are shown.
+If operator-family-pattern is specified, only
+procedures associated with families whose name matches the pattern are shown.
+
+
+
+
+
+
+ \dAop
+[access-method-pattern
+ [operator-class-pattern]]
+
+
+
+
+Shows index access method operator classes listed in .
+If access-method-patttern is specified,
+only operator classes associated with access method whose name matches pattern are shown.
+If operator-class-pattern is specified, only
+procedures associated with families whose name matches the pattern are shown.
+
+
+
+
+
+
+\dAp [ pattern ]
+
+
+
+Shows access method properties listed in . If pattern is specified, only access
+methods whose names match the pattern are shown.
+
+
+
\db[+] [ pattern ]
@@ -1351,6 +1440,35 @@ testdb=
+
+\dip [ pattern ]
+
+
+
+Shows index properties listed in . If pattern is specified, only access
+methods whose names match the pattern are shown.
+
+
+
+
+
+
+ \dicp [ index-name-pattern
+ [ column-name-pattern ]]
+
+
+
+
+
+Shows index column properties listed in . If column_name is specified, only column
+with such name is shown.
+
+
+
\des[+] [ pattern ]
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 04e227b5a6..0043e37040 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -703,7 +703,23 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
Re: Psql patch to show access methods info
On 2018-Nov-20, s.cherkas...@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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
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
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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Psql patch to show access methods info
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 () accociated with access method operator families. s/accociated/associated/. -- Michael signature.asc Description: PGP signature
Re: Psql patch to show access methods info
On 2018-Oct-01, Michael Paquier wrote: > On Tue, Jul 03, 2018 at 01:25:37PM +0300, s.cherkas...@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 Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >From 76c1c7e85963077b5665618bb3bcd7cf6c773dcd Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Sat, 17 Nov 2018 23:17:03 -0300 Subject: [PATCH] psql_add_am_info --- doc/src/sgml/catalogs.sgml | 8 +- doc/src/sgml/ref/psql-ref.sgml | 118 +++ src/bin/psql/command.c | 35 ++- src/bin/psql/describe.c| 560 - src/bin/psql/describe.h| 21 ++ src/bin/psql/help.c| 7 + src/bin/psql/tab-complete.c| 23 +- src/test/regress/expected/indexing.out | 25 ++ src/test/regress/expected/psql.out | 155 + src/test/regress/sql/indexing.sql | 5 + src/test/regress/sql/psql.sql | 23 ++ 11 files changed, 973 insertions(+), 7 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 8b7f169d50..565b1c396a 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -675,7 +675,7 @@ search and ordering purposes.) - + pg_amop Columns @@ -818,7 +818,7 @@ is one row for each support function belonging to an operator family. - + pg_amproc Columns @@ -4430,7 +4430,7 @@ SCRAM-SHA-256$iteration count: Operator classes are described at length in . - + pg_opclass Columns @@ -4692,7 +4692,7 @@ SCRAM-SHA-256$iteration count: Operator families are described at length in . - + pg_opfamily Columns diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 6e6d0f42d1..7f2631d75d 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1203,6 +1203,95 @@ testdb= + + + + \dAf + [ access-method-pattern +[operator-family-pattern]] + + + + + +Lists operator families (). If access-method-pattern is specified, only +families whose access method name matches the pattern are shown. +If operator-family-pattern is specified, only +opereator families associated with whose name matches the pattern are shown. +If + is appended to the command name, each operator +family is listed with it's owner. + + + + + + + \dAfo +[access-method-pattern + [operator-family-pattern]] + + + + + +Lists operators () associated with access method operator families. +If access-method-patttern is specified, +only operators associated with access method whose name matches pattern are shown. +If operator-family-pattern is specified, only +opereators associated with families whose name matches the pattern are shown. + + + + + + + \dAfp +[access-method-pattern + [operator-family-pattern]] + + + + +List procedures () accociated with access method operator families. +If access-method-patttern is specified, +only procedures associated with access method whose name matches pattern are shown. +If operator-family-pattern is specified, only +procedures associated with families whose name matches the pattern are shown. + + + + + + + \dAop +[access-method-pattern + [operator-class-pattern]] + + + + +Shows index access method operator classes listed in . +If access-method-patttern is specified, +only operator classes associated with access method whose name matches pattern are shown. +If operator-class-pattern is specified, only +procedures associated with families whose name matches the pattern are shown. + + + + + + +\dAp [ pattern ] + + + +Shows access method properties listed in . If pattern is specified, only access +methods whose names match the pattern are shown. + + + \db[+] [ pattern ] @@ -1351,6
Re: Psql patch to show access methods info
On Tue, Jul 03, 2018 at 01:25:37PM +0300, s.cherkas...@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 signature.asc Description: PGP signature
Re: Psql patch to show access methods info
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? 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 @@ -675,7 +675,7 @@ search and ordering purposes.) - + pg_amop Columns @@ -818,7 +818,7 @@ is one row for each support procedure belonging to an operator family. - + pg_amproc Columns @@ -4458,7 +4458,7 @@ SCRAM-SHA-256$iteration count: Operator classes are described at length in . - + pg_opclass Columns @@ -4720,7 +4720,7 @@ SCRAM-SHA-256$iteration count: Operator families are described at length in . - + pg_opfamily Columns diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index b17039d..273c3f7 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1203,6 +1203,95 @@ testdb= + + + + \dAf + [ access-method-pattern +[operator-family-pattern]] + + + + + +Lists operator families (). If access-method-pattern is specified, only +families whose access method name matches the pattern are shown. +If operator-family-pattern is specified, only +opereator families associated with whose name matches the pattern are shown. +If + is appended to the command name, each operator +family is listed with it's owner. + + + + + + + \dAfo +[access-method-pattern + [operator-family-pattern]] + + + + + +Lists operators () associated with access method operator families. +If access-method-patttern is specified, +only operators associated with access method whose name matches pattern are shown. +If operator-family-pattern is specified, only +opereators associated with families whose name matches the pattern are shown. + + + + + + + \dAfp +[access-method-pattern + [operator-family-pattern]] + + + + +List procedures () accociated with access method operator families. +
Re: Psql patch to show access methods info
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
Psql patch to show access methods info
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.cherkas...@postgrespro.rudiff --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 @@ -675,7 +675,7 @@ search and ordering purposes.) - + pg_amop Columns @@ -818,7 +818,7 @@ is one row for each support procedure belonging to an operator family. - + pg_amproc Columns @@ -4458,7 +4458,7 @@ SCRAM-SHA-256$iteration count: Operator classes are described at length in . - + pg_opclass Columns @@ -4720,7 +4720,7 @@ SCRAM-SHA-256$iteration count: Operator families are described at length in . - + pg_opfamily Columns diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 10b9795..b5d2095 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1203,6 +1203,95 @@ testdb= + + + + \dAf + [ access-method-pattern +[operator-family-pattern]] + + + + + +Lists operator families (). If access-method-pattern is specified, only +families whose access method name matches the pattern are shown. +If operator-family-pattern is specified, only +opereator families associated with whose name matches the pattern are shown. +If + is appended to the command name, each operator +family is listed with it's owner. + + + + + + + \dAfo +[access-method-pattern + [operator-family-pattern]] + + + + + +Lists operators () associated with access method operator families. +If access-method-patttern is specified, +only operators associated with access method whose name matches pattern are shown. +If operator-family-pattern is specified, only +opereators associated with families whose name matches the pattern are shown. + + + + + + + \dAfp +[access-method-pattern + [operator-family-pattern]] + + + + +List procedures () accociated with access method operator families. +If access-method-patttern is specified, +only procedures associated with access method whose name matches pattern are shown. +If operator-family-pattern is specified, only +procedures associated with families whose name matches the pattern are shown. + + + + + + + \dAop +[access-method-pattern + [operator-class-pattern]] + + + + +Shows index access method operator classes listed in . +If access-method-patttern is specified, +only operator classes associated with access method whose name matches pattern are shown. +If operator-class-pattern is specified, only +procedures associated with families whose name matches the pattern are shown. + + + + + + +\dAp [ pattern ] + + + +Shows access method properties listed in . If pattern is specified, only access +methods whose names match the pattern are shown. + + + \db[+] [ pattern ] @@ -1351,6 +1440,35 @@ testdb= + +\dip [ pattern ] + + + +Shows index properties listed in . If pattern
