pg_global_catalog

This Release
pg_global_catalog 0.0.1
Date
Status
Testing
Abstract
PostgreSQL global catalog
Released By
pifor
License
PostgreSQL
Resources
Special Files
Tags

Extensions

pg_global_catalog 0.0.1
PostgreSQL global catalog

README

pg_global_catalog

PostgreSQL extension to consolidate pg_catalog for each database in a single schema named global_catalog.

Installation

pg_global_catalog creates a schema local_catalog in every database (except template0 and template1) and a schema global_catalog in the current database.

There is no CREATE EXTENSION statement to run: it's only needed to run setup.sh.

Following prerequisites must be met and are checked by setup.sh:

  • current Linux user must have password file ~./.pgpass.
  • passwordless superuser access by current Linux user
  • current Linux user must have an associated database with same name
  • extension postgres_fdw must be installed in default database.

What setup.sh is doing: * in local_catalog schema, for most of pg_catalog views, a new view is created with an additional column named dbid * in default database a foreign data wrapper (FDW) server is created for each database (except template0 and template1) * in global_catalog schema a global view is created for most pg_catalog view as the union of all related local_catalog views.

Usage

Most pg_catalog views are mapped to the same view in global_catalog schema and can by used to query catalog data at instance level.

For example, following query can be used to get number of objects referenced in pg_class by database at instance level:

select datname, count(*) from global_catalog.pg_class c join pg_database d on c.dbid = d.oid group by datname order by datname;