pg_extension_config_dump()
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 stored in the extcondition
column (see example below).
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.
Source
pg_extension_config_dump()
is implemented in src/backend/commands/extension.c.
Change history
- PostgreSQL 9.1
- added (commit d9572c4e)
Examples
Using the example of a simple test extension which contains two configuration 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