Movatterモバイル変換

35.15.1. Defining Extension Objects

35.15.2. Extension Files

35.15.3. Extension Relocatability

35.15.4. Extension Configuration Tables

35.15.5. Extension Updates

35.15.6. Extension Example

Widely-distributed extensions should assume little about the database they occupy. In particular, unless you issuedSET search_path = pg_temp, assume each unqualified name could resolve to an object that a malicious user has defined. Beware of constructs that depend onsearch_path implicitly:IN andCASEexpression WHEN always select an operator using the search path. In their place, useOPERATOR(schema.=) ANY andCASE WHENexpression.

TheCREATE EXTENSION command relies on a control file for each extension, which must be named the same as the extension with a suffix of.control, and must be placed in the installation'sSHAREDIR/extension directory. There must also be at least oneSQL script file, which follows the naming patternextension--version.sql (for example,foo--1.0.sql for version1.0 of extensionfoo). By default, the script file(s) are also placed in theSHAREDIR/extension directory; but the control file can specify a different directory for the script file(s).

The file format for an extension control file is the same as for thepostgresql.conf file, namely a list ofparameter_name=value assignments, one per line. Blank lines and comments introduced by# are allowed. Be sure to quote any value that is not a single word or number.

A control file can set the following parameters:

In addition to the primary control fileextension.control, an extension can have secondary control files named in the styleextension--version.control. If supplied, these must be located in the script file directory. Secondary control files follow the same format as the primary control file. Any parameters set in a secondary control file override the primary control file when installing or updating to that version of the extension. However, the parametersdirectory anddefault_version cannot be set in a secondary control file.

An extension'sSQL script files can contain any SQL commands, except for transaction control commands (BEGIN,COMMIT, etc) and commands that cannot be executed inside a transaction block (such asVACUUM). This is because the script files are implicitly executed within a transaction block.

An extension'sSQL script files can also contain lines beginning with\echo, which will be ignored (treated as comments) by the extension mechanism. This provision is commonly used to throw an error if the script file is fed topsql rather than being loaded viaCREATE EXTENSION (see example script below). Without that, users might accidentally load the extension's contents as“loose” objects rather than as an extension, a state of affairs that's a bit tedious to recover from.

While the script files can contain any characters allowed by the specified encoding, control files should contain only plain ASCII, because there is no way forPostgres Pro to know what encoding a control file is in. In practice this is only an issue if you want to use non-ASCII characters in the extension's comment. Recommended practice in that case is to not use the control filecomment parameter, but instead useCOMMENT ON EXTENSION within a script file to set the comment.

Users often wish to load the objects contained in an extension into a different schema than the extension's author had in mind. There are three supported levels of relocatability:

In all cases, the script file will be executed withsearch_path initially set to point to the target schema; that is,CREATE EXTENSION does the equivalent of this:

SET LOCAL search_path TO @extschema@;

This allows the objects created by the script file to go into the target schema. The script file can changesearch_path if it wishes, but that is generally undesirable.search_path is restored to its previous setting upon completion ofCREATE EXTENSION.

The target schema is determined by theschema parameter in the control file if that is given, otherwise by theSCHEMA option ofCREATE EXTENSION if that is given, otherwise the current default object creation schema (the first one in the caller'ssearch_path). When the control fileschema parameter is used, the target schema will be created if it doesn't already exist, but in the other two cases it must already exist.

If any prerequisite extensions are listed inrequires in the control file, their target schemas are appended to the initial setting ofsearch_path. This allows their objects to be visible to the new extension's script file.

Although a non-relocatable extension can contain objects spread across multiple schemas, it is usually desirable to place all the objects meant for external use into a single schema, which is considered the extension's target schema. Such an arrangement works conveniently with the default setting ofsearch_path during creation of dependent extensions.

Some extensions include configuration tables, which contain data that might be added or changed by the user after installation of the extension. Ordinarily, if a table is part of an extension, neither the table's definition nor its content will be dumped bypg_dump. But that behavior is undesirable for a configuration table; any data changes made by the user need to be included in dumps, or the extension will behave differently after a dump and reload.

To solve this problem, an extension's script file can mark a table it has created as a configuration table, which will causepg_dump to include the table's contents (not its definition) in dumps. To do that, call the functionpg_extension_config_dump(regclass, text) after creating the table, for example

CREATE TABLE my_config (key text, value text);SELECT pg_catalog.pg_extension_config_dump('my_config', '');

Any number of tables can be marked this way.

When the second argument ofpg_extension_config_dump is an empty string, the entire contents of the table are dumped bypg_dump. This is usually only correct if the table is initially empty as created by the extension script. If there is a mixture of initial data and user-provided data in the table, the second argument ofpg_extension_config_dump provides aWHERE condition that selects the data to be dumped. For example, you might do

CREATE TABLE my_config (key text, value text, standard_entry boolean);SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');

and then make sure thatstandard_entry is true only in the rows created by the extension's script.

More complicated situations, such as initially-provided rows that might be modified by users, can be handled by creating triggers on the configuration table to ensure that modified rows are marked correctly.

You can alter the filter condition associated with a configuration table by callingpg_extension_config_dump again. (This would typically be useful in an extension update script.) The only way to mark a table as no longer a configuration table is to dissociate it from the extension withALTER EXTENSION ... DROP TABLE.

Note that foreign key relationships between these tables will dictate the order in which the tables are dumped out by pg_dump. Specifically, pg_dump will attempt to dump the referenced-by table before the referencing table. As the foreign key relationships are set up at CREATE EXTENSION time (prior to data being loaded into the tables) circular dependencies are not supported. When circular dependencies exist, the data will still be dumped out but the dump will not be able to be restored directly and user intervention will be required.

One advantage of the extension mechanism is that it provides convenient ways to manage updates to the SQL commands that define an extension's objects. This is done by associating a version name or number with each released version of the extension's installation script. In addition, if you want users to be able to update their databases dynamically from one version to the next, you should provideupdate scripts that make the necessary changes to go from one version to the next. Update scripts have names following the patternextension--old_version--target_version.sql (for example,foo--1.0--1.1.sql contains the commands to modify version1.0 of extensionfoo into version1.1).

Given that a suitable update script is available, the commandALTER EXTENSION UPDATE will update an installed extension to the specified new version. The update script is run in the same environment thatCREATE EXTENSION provides for installation scripts: in particular,search_path is set up in the same way, and any new objects created by the script are automatically added to the extension.

If an extension has secondary control files, the control parameters that are used for an update script are those associated with the script's target (new) version.

The update mechanism can be used to solve an important special case: converting a“loose” collection of objects into an extension. Before the extension mechanism was added toPostgreSQL (in 9.1), many people wrote extension modules that simply created assorted unpackaged objects. Given an existing database containing such objects, how can we convert the objects into a properly packaged extension? Dropping them and then doing a plainCREATE EXTENSION is one way, but it's not desirable if the objects have dependencies (for example, if there are table columns of a data type created by the extension). The way to fix this situation is to create an empty extension, then useALTER EXTENSION ADD to attach each pre-existing object to the extension, then finally create any new objects that are in the current extension version but were not in the unpackaged release.CREATE EXTENSION supports this case with itsFROMold_version option, which causes it to not run the normal installation script for the target version, but instead the update script namedextension--old_version--target_version.sql. The choice of the dummy version name to use asold_version is up to the extension author, thoughunpackaged is a common convention. If you have multiple prior versions you need to be able to update into extension style, use multiple dummy version names to identify them.

ALTER EXTENSION is able to execute sequences of update script files to achieve a requested update. For example, if onlyfoo--1.0--1.1.sql andfoo--1.1--2.0.sql are available,ALTER EXTENSION will apply them in sequence if an update to version2.0 is requested when1.0 is currently installed.

Postgres Pro doesn't assume anything about the properties of version names: for example, it does not know whether1.1 follows1.0. It just matches up the available version names and follows the path that requires applying the fewest update scripts. (A version name can actually be any string that doesn't contain-- or leading or trailing-.)

Sometimes it is useful to provide“downgrade” scripts, for examplefoo--1.1--1.0.sql to allow reverting the changes associated with version1.1. If you do that, be careful of the possibility that a downgrade script might unexpectedly get applied because it yields a shorter path. The risky case is where there is a“fast path” update script that jumps ahead several versions as well as a downgrade script to the fast path's start point. It might take fewer steps to apply the downgrade and then the fast path than to move ahead one version at a time. If the downgrade script drops any irreplaceable objects, this will yield undesirable results.

To check for unexpected update paths, use this command:

SELECT * FROM pg_extension_update_paths('extension_name');

This shows each pair of distinct known version names for the specified extension, together with the update path sequence that would be taken to get from the source version to the target version, orNULL if there is no available update path. The path is shown in textual form with-- separators. You can useregexp_split_to_array(path,'--') if you prefer an array format.

Here is a complete example of anSQL-only extension, a two-element composite type that can store any type of value in its slots, which are named“k” and“v”. Non-text values are automatically coerced to text for storage.

The script filepair--1.0.sql looks like this:

-- complain if script is sourced in psql, rather than via CREATE EXTENSION\echo Use "CREATE EXTENSION pair" to load this file. \quitCREATE TYPE pair AS ( k text, v text );CREATE OR REPLACE FUNCTION pair(text, text)RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, PROCEDURE = pair);-- "SET search_path" is easy to get right, but qualified names perform better.CREATE OR REPLACE FUNCTION lower(pair)RETURNS pair LANGUAGE SQLAS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'SET search_path = pg_temp;CREATE OR REPLACE FUNCTION pair_concat(pair, pair)RETURNS pair LANGUAGE SQLAS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,               $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';

The control filepair.control looks like this:

# pair extensioncomment = 'A key/value pair data type'default_version = '1.0'relocatable = false

While you hardly need a makefile to install these two files into the correct directory, you could use aMakefile containing this:

EXTENSION = pairDATA = pair--1.0.sqlPG_CONFIG = pg_configPGXS := $(shell $(PG_CONFIG) --pgxs)include $(PGXS)

This makefile relies onPGXS, which is described inSection 35.16. The commandmake install will install the control and script files into the correct directory as reported bypg_config.

Once the files are installed, use theCREATE EXTENSION command to load the objects into any particular database.