Thread: rehashing catalog cache id 14 for pg_opclass; 17 tups, 8 buckets

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

On 04/17/2018 01:14 PM, Thiemo Kellner wrote:
> 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.
> 

> 
> 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.

What do you have your log levels set to?

The only place I can find this message is in:

src/backend/utils/cache/catcache.c

elog(DEBUG1, "rehashing catalog cache id %d for %s; %d tups, %d buckets",
                  cp->id, cp->cc_relname, cp->cc_ntup, cp->cc_nbuckets);

If I am following correctly it is fairly low level message.

> 
> Kind regards Thiemo
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Zitat von Adrian Klaver <adrian.klaver@aklaver.com>:

> What do you have your log levels set to?

Thanks for pointing this out. I put client level to debug1. So, I am
just lucky not to have got flooded with Messages?


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

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.

Thiemo Kellner, NHC Barhufpflege wrote:
> > What do you have your log levels set to?
> 
> Thanks for pointing this out. I put client level to debug1. So, I am  
> just lucky not to have got flooded with Messages?

Sort of. This is a normal operation and should not worry you.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com