Date: Tue, 21 Jan 2020 19:33:29 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Michael Paquier <michael@paquier.xyz>,
	Andres Freund <andres@anarazel.de>,
	Nikita Glukhov <n.gluhov@postgrespro.ru>,
	Thomas Munro <thomas.munro@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
	Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>,
	David Steele <david@pgmasters.net>
Subject: Re: Psql patch to show access methods info
Message-ID: <20200121223329.GA18317@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAPpHfdtxKTQFXTBrw_qdY8DUpLvQHxX63Euy3b1zR9SFO5J20w@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

I think I would like this feature to be in, but I'm not sure that the
shape is final yet.  My points:

a) I don't see any use for \dA as presented; I think the \dA+ output is
   useful.  Therefore my preference would be that \dA presents what the
   latest patch has as \dA+.  I think we should leave \dA+ unimplemented
   for now; maybe we can use some use for it later on.

b) I think \dAp should list the function used for each support proc.  I
   don't have any use for \dAp actually (I already said that upthread,
   sorry for repeating myself), but I think that if we have it, then
   showing only the proc number is pointless.

c) it would be damn handy if \dAf (maybe \dAf+) lists the datatypes that
   each opfamily has opclasses for.  Maybe make the output an array, like
   {int4,int8,numeric,...}  Something like [*] but somehow make it
   prettier?

d) This one I'm unsure about: should we list the opfamily for each
   opclass in \dAc?  I'm not sure whether it's useful for anything.

[*]
SELECT DISTINCT am.amname AS "AM",
  CASE
    WHEN pg_catalog.pg_opfamily_is_visible(f.oid)
    THEN format('%I', f.opfname)
    ELSE format('%I.%I', n.nspname, f.opfname)
  END AS "Operator family",
	string_agg(format_type(c.opcintype, -1), ', ') as "Applicable types",
  pg_catalog.pg_get_userbyid(f.opfowner) AS "Owner"

FROM pg_catalog.pg_opfamily f
  LEFT JOIN pg_catalog.pg_am am on am.oid = f.opfmethod
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = f.opfnamespace
left join pg_catalog.pg_opclass c on (f.oid = c.opcfamily)
group by 1, 2, 4 ORDER BY 1, 2;

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services