Postgresql – extension “pg_buffercache” already exists and relation “pg_buffercache” does not exist

configurationpostgresqlpostgresql-9.5

When I try to create the extension pg_buffercahe using the following command: 

➤ psql://postgres@postgresql-95-2:5432/postgres 

CREATE EXTENSION pg_buffercache;

…it returns the error:

ERROR:  extension "pg_buffercache" already exists

If I try to retrieve information from the pg_buffercahe using the following command: 

➤ psql://postgres@postgresql-95-2:5432/postgres 

select count(*) from pg_buffercache where not isdirty;

…I receive the error:

ERROR:  relation "pg_buffercache" does not exist

The following packages are installed on my system:

yum list postgresql95*  
Installed Packages
postgresql95.x86_64         9.5.10-1PGDG.rhel7
postgresql95-contrib.x86_64 9.5.10-1PGDG.rhel7
postgresql95-libs.x86_64    9.5.10-1PGDG.rhel7
postgresql95-server.x86_64  9.5.10-1PGDG.rhel7

How can I fix this issue?

➤ psql://postgres@postgresql-95-2:5432/postgres 

# \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_buffercache     | 1.1     | public     | examine the shared buffer cache
 pg_stat_statements | 1.3     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

➤ psql://postgres@postgresql-95-2:5432/postgres 

# select count(*) from pg_buffercache where not isdirty;
ERROR:  relation "pg_buffercache" does not exist
LINE 1: select count(*) from pg_buffercache where not isdirty;

Best Answer

The extension was created in a schema which is not in your search_path. So you can't create another copy of it, but also can't find the view. You can use \dx to find what schema it was installed to.

Related Question