PostgreSQL :- Extensions :: pg_buffercache

Background

As we used the pg_buffercache extension in our last post, let us talk about how we register it.

Extensions

Available Extensions

pg_catalog.pg_available_extensions

Outline

Available extensions are exposed via the pg_catalog.pg_available_extensions view.

Columns

  1. name
    • Name of the extension
  2. default_version
    • The default version number
    • This is the version of the extension that is installed when “CREATE EXTENSION” is invoked
  3. installed_version
    • The installed version number
    • If extension is installed, this column contains data
    • On the other hand, if extension is not installed, this column is empty
  4. comment
    • Comment

SQL


SELECT * 

FROM   pg_catalog.pg_available_extensions tblPAE

where  name like '%buffer%' 

Output

pg_catalog.pg_available_extensions.self.01.20190803.0940AM

Registered Extensions

pg_catalog.pg_extensions

Outline

Registered extensions are exposed via the pg_catalog.pg_extensions view.

Here is a Link.

Columns

  1. extname
    • Name of the extension
  2. extowner
    • The extension’s owner
  3. extnamespace
    • Extension Namespace
  4. extrelocatable
    • If true, then extension can be safely moved to another schema
  5.  extversion
    • This is the version of the extension that is currently installed
  6. extconfig
    • This is an array list that shows configuration for the extension
  7. comment
    • Comment

SQL


SELECT
          tblE.*

        , tblPCA.rolname as "owner"

        , tblNS.nspname as "namespace"

        , tblNS.nspacl as "acl"

FROM   pg_catalog.pg_extension tblE

left OUTER JOIN  pg_catalog.pg_authid tblPCA 

        ON tblE.extowner = tblPCA.oid

left OUTER JOIN pg_catalog.pg_namespace tblNS 

        ON tblE.extnamespace = tblNS.oid

order by
        1 asc

Output

pg_catalog.pg_extension.01.20190803.1010AM

Register Extension

CREATE EXTENSION

Outline

Extensions are registered via the Create Extension statement.

SQL

SQL – Syntax

CREATE EXTENSION {extension};

SQL – Sample

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

Artifacts

For shipped extensions, we can consult the OS File System and review some of the files.

File System

MS Windows

Folders

share\extension

Our version is v11, here is what our share\extension folder looks like.

fs.share.extension.folder.20190803.1029AM

File – pg_buffercache.control
Image

pg_buffercache.control.01.20190803.1034AM

Explanation
  1. Comment
  2. Default Version
  3. Module Pathname
  4. Relocatable
File – pg_buffercache—version—.sql
Image

pg_buffercache.sql.01.20190803.1039AM

Explanation
  1. Exposes the pg_buffercache_pages function as a view.

Summary

There is quite a bit of discussion around which namespace public, pg_catalog, or other to register the pg_buffercache extension.

References

 

  1. PostgreSQL
    • Additional Supplied Modules
      • pg_buffercache
        Link
    • System Catalogs
  2. StackOveflow
    • Installing PostgreSQL Extension to all schemas
      Link

One thought on “PostgreSQL :- Extensions :: pg_buffercache

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s