Cannot access to table belong to database after drop extension pg_dbms_stats

[SeanKimMel]

Hi there, I need your advice on the issue below.

In case of dropping the extension pg_dbms_stats from the database, I have experienced that sometimes we cannot access the table belong to the database.

postgres=# drop extension pg_dbms_stats ;
DROP EXTENSION
postgres=# select * from public.test_table ;
ERROR:  relation "dbms_stats.relation_stats_locked" does not exist
LINE 1: ...elpages, reltuples, curpages, relallvisible  FROM dbms_stats...
                                                             ^
HINT:  Check your settings of pg_dbms_stats.
QUERY:  SELECT relpages, reltuples, curpages, relallvisible  FROM dbms_stats.relation_stats_locked WHERE relid = $1
postgres=# drop extension pg_dbms_stats ;
ERROR:  extension "pg_dbms_stats" does not exist
postgres=# select * from public.test_table ;
ERROR:  relation "dbms_stats.relation_stats_locked" does not exist
LINE 1: ...elpages, reltuples, curpages, relallvisible  FROM dbms_stats...
                                                             ^
HINT:  Check your settings of pg_dbms_stats.
QUERY:  SELECT relpages, reltuples, curpages, relallvisible  FROM dbms_stats.relation_stats_locked WHERE relid = $1

these are the steps reroduce the issue.

1. create extension pg_dbms_stats ;
2. create table public.test_table as select * from pg_tables ;
3. select * from public.test_table;
4. drop extension pg_dbms_stats;
5. select * from public.test_table;

P.S. it does not always occur when I repeat the steps but it happens

[kasaharatt]

Hi, sorry for late reply.

If you want to disable pg_dbms_stats, please remove "pg_dbms_stats" from shared_preload_libraries at postgresql.conf, and restart PostgreSQL (pg_ctl restart).

regards,

[SeanKimMel]

Hi, sorry for late reply.

If you want to disable pg_dbms_stats, please remove "pg_dbms_stats" from shared_preload_libraries at postgresql.conf, and restart PostgreSQL (pg_ctl restart).

regards,

hi, thanks for the reply

is there any way to archive that without restarting the postgreSQL server ?

[kasaharatt]

is there any way to archive that without restarting the postgreSQL server ?

If you want to avoid server restart, please add "pg_dbms_stats.use_locked_stats = off" to postgresql.conf and reload PostgreSQL (pg_ctl reload).

[SeanKimMel]

is there any way to archive that without restarting the postgreSQL server ?

If you want to avoid server restart, please add "pg_dbms_stats.use_locked_stats = off" to postgresql.conf and reload PostgreSQL (pg_ctl reload).

I'll try that, sorry about late catch up.