2

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;

2 Answers 2

3

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.

2
  • Hello. I checked List of installed extensions and i see pg_buffercache installed. But way it dont work? Thank you Mar 15, 2018 at 9:03
  • What schema is it installed into (\dx will show you that)? Is that schema in your search_path?
    – jjanes
    Mar 15, 2018 at 15:04
0

The 'relation does not exist' message is usually displayed when you are not connected to any particular database. Make sure you are connected.

/c yourDb
yourDb=# CREATE EXTENSION IF NOT EXISTS pg_buffercache;
yourDb=# SELECT count(*) FROM pg_buffercache WHERE isdirty;
2
  • My yourDb is postgres. This is default DB. Mar 15, 2018 at 8:41
  • Then connected to postgres database execute \dx as @jjanes is suggesting to see what extensions are installed under that database.
    – Imanol Y.
    Mar 15, 2018 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.