rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets - Mailing list pgsql-general

From Thiemo Kellner
Subject rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets
Date
Msg-id 846968ae-705d-d7ba-e134-f5b17ea2d59c@gelassene-pferde.biz
Whole thread Raw
Responses Re: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Hi all

When running following query in psql (server and client version 10 with 
replication on Debian 9), I get the message mentioned in the subject. I 
have not found much searching the internet. There were suggestions on 
bloat so I ran "vacuum (verbose, full, analyze)" but the message remains.

with PRO_UNNESTED_TYPES as(
   select
     oid as PROOID,
     PRONAME,
     unnest(PROARGTYPES) as PROARGTYPE,
     PRONAMESPACE,
     PROOWNER
   from
     PG_CATALOG.PG_PROC
) select
   P.PRONAME,
   string_agg(
     T.TYPNAME,
     ', '
   ) as PARAMETER_LIST_STRING,
   G.GRANTEE
from
   PRO_UNNESTED_TYPES P
inner join PG_CATALOG.PG_TYPE T on
   P.PROARGTYPE = T.OID
inner join PG_CATALOG.PG_NAMESPACE N on
   P.PRONAMESPACE = N.OID
inner join INFORMATION_SCHEMA.ROUTINE_PRIVILEGES G on
   -- copied from INFORMATION_SCHEMA.ROUTINE_PRIVILEGES source
  -- as seen in DBeaver 4.3.2
(
     (
       P.PRONAME::text || '_'::text
     )|| P.PROOID::text
   )::INFORMATION_SCHEMA.SQL_IDENTIFIER = G.SPECIFIC_NAME
where
   N.NSPNAME = current_user
   and G.GRANTEE != current_user
group by
   P.PROOID,
   P.PRONAME,
   G.GRANTEE
order by
   P.PRONAME asc,
   G.GRANTEE asc,
   PARAMETER_LIST_STRING asc;

I use this installation to develop and for the time being I install and 
re-install a couple of functions only 3 tables an a single view. I 
install in a proper schema which gets re-created at the beginning of my 
install script.

I ran also following statement I found on the net to get an idea on 
bloat in my database.

pg_depend_reference_index    944 kB
pg_proc    904 kB
pg_depend_depender_index    880 kB
pg_largeobject_metadata_oid_index    8192 bytes
pg_enum_typid_sortorder_index    8192 bytes
pg_enum_typid_label_index    8192 bytes
pg_largeobject_loid_pn_index    8192 bytes
pg_enum_oid_index    8192 bytes
pg_statistic_ext_oid_index    8192 bytes
pg_statistic_ext_name_index    8192 bytes

I am quite ok with pg_proc, however I do not know why the depend tables 
are so big and whether this is normal. The rest is fine by me too.

select
   relname,
   pg_size_pretty(
     pg_relation_size(C.oid)
   )
from
   pg_class C
left join pg_namespace N on
   (
     N.oid = C.relnamespace
   )
where
   nspname = 'pg_catalog'
order by
   2 desc limit 10;

I do not feel that my DB has a problem but I was taken aback a bit when 
I first saw the message in the subject.

I would be grateful about a bit shed light.

Kind regards Thiemo

-- 
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC

Attachment

pgsql-general by date:

Previous
From: David Pacheco
Date:
Subject: Re: Can PostgreSQL create new WAL files instead of reusing old ones?
Next
From: Thiemo Kellner
Date:
Subject: array UNNESTed to rows stable with respect to order?