Home • Explore • Blog • CI
☰



postgresql
17th chunk of `doc/src/sgml/extend.sgml`
d92d9d656001099c5ab590c1c87776e4d45ee8519e90d8f70000000100000fa2
 the extension's script.
    </para>

    <para>
     For sequences, the second argument of <function>pg_extension_config_dump</function>
     has no effect.
    </para>

    <para>
     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.
    </para>

    <para>
     You can alter the filter condition associated with a configuration table
     by calling <function>pg_extension_config_dump</function> 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 with <command>ALTER EXTENSION ... DROP TABLE</command>.
    </para>

    <para>
     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.
    </para>

    <para>
     Sequences associated with <type>serial</type> or <type>bigserial</type> columns
     need to be directly marked to dump their state. Marking their parent
     relation is not enough for this purpose.
    </para>
   </sect2>

   <sect2 id="extend-extensions-updates">
    <title>Extension Updates</title>

    <para>
     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 provide
     <firstterm>update scripts</firstterm> that make the necessary changes to go from
     one version to the next.  Update scripts have names following the pattern
     <literal><replaceable>extension</replaceable>--<replaceable>old_version</replaceable>--<replaceable>target_version</replaceable>.sql</literal>
     (for example, <literal>foo--1.0--1.1.sql</literal> contains the commands to modify
     version <literal>1.0</literal> of extension <literal>foo</literal> into version
     <literal>1.1</literal>).
    </para>

    <para>
     Given that a suitable update script is available, the command
     <command>ALTER EXTENSION UPDATE</command> will update an installed extension
     to the specified new version.  The update script is run in the same
     environment that <command>CREATE EXTENSION</command> provides for installation
     scripts: in particular, <varname>search_path</varname> is set up in the same
     way, and any new objects created by the script are automatically added
     to the extension.  Also, if the script chooses to drop extension member
     objects, they are automatically dissociated from the extension.
    </para>

    <para>
     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.
    </para>

    <para>
     <command>ALTER EXTENSION</command> is able to execute sequences of update
     script files to achieve a requested update.  For example, if only
     <literal>foo--1.0--1.1.sql</literal> and <literal>foo--1.1--2.0.sql</literal> are
     available, <command>ALTER EXTENSION</command> will apply them in sequence if an
     update to version <literal>2.0</literal> is requested when <literal>1.0</literal> is
     currently
Title: Extension Updates and Configuration in PostgreSQL
Summary
This section discusses the management of extension updates and configuration in PostgreSQL. It explains how to handle configuration tables, including the use of pg_extension_config_dump function to mark tables for inclusion in dumps. The text also covers update scripts for extensions, detailing how they're named and used to update from one version to another. It describes the ALTER EXTENSION UPDATE command, which runs these scripts in a controlled environment. The section also touches on handling foreign key relationships in dumps, sequence marking for serial columns, and the limitations of circular dependencies in configuration tables.