pg_extension_config_dump()

A system function for marking an extension table or a sequence as a "configuration relation"

pg_extension_config_dump() is a system function for marking an extension table or a sequence as a "configuration relation", which can be dumped by pg_dump.

pg_extension_config_dump() was added in PostgreSQL 9.1.

Usage

By default, pg_dump does not dump the contents of tables created as part of an extension. However if a table is intended to contain user-configurable data, it may be desirable to be able to dump the contents of that table and any associated sequences. pg_extension_config_dump() can be called in an SQL script executed by CREATE EXTENSION to mark any objects which need to be marked as such. Note that it cannot be executed directly.

pg_extension_config_dump() accepts two arguments: the name of a table or sequence to be marked as a "configuration relation", for tables an optional string containing a WHERE clause to restrict the data to be dumped.

As of PostgreSQL 13, this function is not used by any contrib modules. Examples of usage can be found in third-party extensions such as:

Caveats

Any WHERE clause is stored-as is and not evaluated when the extension is created or updated.
There is no corresponding SQL function to unmark a table or a sequence from being a "configuration relation".
It can only be executed from an extension SQL script.

Implementation

The oids of objects marked as "configuration relations" are written to column extconfig in pg_extension. Any WHERE clauses are tored in the extcondition column (see example below).

pg_extension_config_dump() is implemented in src/backend/commands/extension.c.

As noted above, there is no corresponding SQL function to unmark a table or a sequence from being a "configuration relation", however this file contains the internal function extension_config_remove(), which is invoked by ALTER EXTENSION ... DROP if necessary.

Change history

PostgreSQL 9.1
- added (commit d9572c4e)

Examples

Using the example of a simple test extension which contains two configration tables, base_config and extra_config:

postgres=# \dx+ configuration_relation 
Objects in extension "configuration_relation"
      Object description      
------------------------------
 function function_template()
 table base_config
 table extra_config
(3 rows)

the tables are defined in the extension's SQL file like this:

CREATE TABLE base_config (
  id INT,
  val TEXT
);

SELECT pg_catalog.pg_extension_config_dump('base_config', '');

CREATE TABLE extra_config (
  id INT,
  val TEXT,
  is_ext BOOL NOT NULL DEFAULT TRUE
);

SELECT pg_catalog.pg_extension_config_dump('extra_config', 'WHERE is_ext IS FALSE');

and the configuration relation definitions are stored in pg_extension like this:

postgres=# SELECT * FROM pg_extension WHERE extname='configuration_relation'\x\g\x
Expanded display is on.
-[ RECORD 1 ]--+-----------------------------
oid            | 24578
extname        | configuration_relation
extowner       | 10
extnamespace   | 2200
extrelocatable | t
extversion     | 0.1
extconfig      | {24580,24586}
extcondition   | {"","WHERE is_ext IS FALSE"}

pg_extension_config_dump() cannot be normally be executed:

postgres=# SELECT pg_catalog.pg_extension_config_dump('extra_config', 'WHERE is_ext IS FALSE');
ERROR:  pg_extension_config_dump() can only be called from an SQL script executed by CREATE EXTENSION

References

PostgreSQL documentation: Extension Configuration Tables

Useful articles

Useful links

Contents