Thread: pgsql: doc: add namespace column to pg_buffercache example query
doc: add namespace column to pg_buffercache example query Without the namespace, the table name could be ambiguous. Reported-by: adunham@arbormetrix.com Discussion: https://postgr.es/m/158155175140.23798.2189464781144503491@wrigleys.postgresql.org Backpatch-through: 9.5 Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d68470013618009241454b5578665994b7b36d02 Modified Files -------------- doc/src/sgml/pgbuffercache.sgml | 31 ++++++++++++++++--------------- 1 file changed, 16 insertions(+), 15 deletions(-)
On 2020-03-31 23:16, Bruce Momjian wrote: > doc: add namespace column to pg_buffercache example query > > Without the namespace, the table name could be ambiguous. > > Reported-by: adunham@arbormetrix.com > > Discussion: https://postgr.es/m/158155175140.23798.2189464781144503491@wrigleys.postgresql.org > > Backpatch-through: 9.5 <screen> -regression=# SELECT c.relname, count(*) AS buffers +regression=# SELECT n.nspname, c.relname, count(*) AS buffers FROM pg_buffercache b INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) - GROUP BY c.relname - ORDER BY 2 DESC + JOIN pg_namespace n ON n.oid = c.relnamespace + GROUP BY n.nspname, c.relname + ORDER BY 3 DESC LIMIT 10; Seems kind of confusing to use INNER JOIN once and then JOIN the other time. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, Apr 2, 2020 at 07:35:50AM +0200, Peter Eisentraut wrote: > On 2020-03-31 23:16, Bruce Momjian wrote: > > doc: add namespace column to pg_buffercache example query > > > > Without the namespace, the table name could be ambiguous. > > > > Reported-by: adunham@arbormetrix.com > > > > Discussion: https://postgr.es/m/158155175140.23798.2189464781144503491@wrigleys.postgresql.org > > > > Backpatch-through: 9.5 > > <screen> > -regression=# SELECT c.relname, count(*) AS buffers > +regression=# SELECT n.nspname, c.relname, count(*) AS buffers > FROM pg_buffercache b INNER JOIN pg_class c > ON b.relfilenode = pg_relation_filenode(c.oid) AND > b.reldatabase IN (0, (SELECT oid FROM pg_database > WHERE datname = current_database())) > - GROUP BY c.relname > - ORDER BY 2 DESC > + JOIN pg_namespace n ON n.oid = c.relnamespace > + GROUP BY n.nspname, c.relname > + ORDER BY 3 DESC > LIMIT 10; > > Seems kind of confusing to use INNER JOIN once and then JOIN the other time. Agreed, INNER removed. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +