DDL command handling matrix =========================== The following table describes the utility or DDL commands that are allowed, the ones that are replicated, and the type of global lock they take when they’re replicated. For some more complex statements like ``ALTER TABLE`` , these can differ depending on the subcommands executed. Every such command has detailed explanation under the following table. Command matrix -------------- .. csv-table:: :header: Command,Allowed,Replicated,Lock :widths: 15,12,12,20 :align: left :class: longtable ALTER AGGREGATE,Y,Y,DDL ALTER CAST,Y,Y,DDL ALTER COLLATION,Y,Y,DDL ALTER CONVERSION,Y,Y,DDL ALTER DATABASE,Y,N,N ALTER DATABASE LINK,Y,Y,DDL ALTER DEFAULT PRIVILEGES,Y,Y,DDL ALTER DIRECTORY,Y,Y,DDL ALTER DOMAIN,Y,Y,DDL ALTER EVENT TRIGGER,Y,Y,DDL ALTER EXTENSION,Y,Y,DDL ALTER FOREIGN DATA WRAPPER,Y,Y,DDL ALTER FOREIGN TABLE,Y,Y,DDL ALTER FUNCTION,Y,Y,DDL ALTER INDEX,Y,Y,DDL ALTER LANGUAGE,Y,Y,DDL ALTER LARGE OBJECT,N,N,N ALTER MATERIALIZED VIEW,Y,:ref:`Extensions providing replication or HA functionality` ,DML ALTER OPERATOR,Y,Y,DDL ALTER OPERATOR CLASS,Y,Y,DDL ALTER OPERATOR FAMILY,Y,Y,DDL ALTER PACKAGE,Y,Y,DDL ALTER POLICY,Y,Y,DDL ALTER PROCEDURE,Y,Y,DDL ALTER PROFILE,Y,Y,:ref:`DDL locking details` ALTER PUBLICATION,Y,Y,DDL ALTER QUEUE,Y,Y,DDL ALTER QUEUE TABLE,Y,Y,DDL ALTER REDACTION POLICY,Y,Y,DDL ALTER RESOURCE GROUP,Y,N,N ALTER ROLE,Y,Y,DDL ALTER ROUTINE,Y,Y,DDL ALTER RULE,Y,Y,DDL ALTER SCHEMA,Y,Y,DDL ALTER SEQUENCE,:ref:`DDL locking details` ,Y,DML ALTER SERVER,Y,Y,DDL ALTER SESSION,Y,N,N ALTER STATISTICS,Y,Y,DDL ALTER SUBSCRIPTION,Y,Y,DDL ALTER SYNONYM,Y,Y,DDL ALTER SYSTEM,Y,N,N ALTER TABLE,:ref:`DDL locking details` ,Y,:ref:`DDL locking details` ALTER TABLESPACE,Y,Y,DDL ALTER TEXT SEARCH CONFIGURATION,Y,Y,DDL ALTER TEXT SEARCH DICTIONARY,Y,Y,DDL ALTER TEXT SEARCH PARSER,Y,Y,DDL ALTER TEXT SEARCH TEMPLATE,Y,Y,DDL ALTER TRIGGER,Y,Y,DDL ALTER TYPE,Y,Y,DDL ALTER USER MAPPING,Y,Y,DDL ALTER VIEW,Y,Y,DDL ANALYZE,Y,N,N BEGIN,Y,N,N CHECKPOINT,Y,N,N CLOSE,Y,N,N CLOSE CURSOR,Y,N,N CLOSE CURSOR ALL,Y,N,N CLUSTER,Y,N,DML COMMENT,Y,:ref:`DDL locking details` ,DDL COMMIT,Y,N,N COMMIT PREPARED,Y,N,N COPY,Y,N,N COPY FROM,Y,N,N CREATE ACCESS METHOD,Y,Y,DDL CREATE AGGREGATE,Y,Y,DDL CREATE CAST,Y,Y,DDL CREATE COLLATION,Y,Y,DDL CREATE CONSTRAINT,Y,Y,DDL CREATE CONVERSION,Y,Y,DDL CREATE DATABASE,Y,N,N CREATE DATABASE LINK,Y,Y,DDL CREATE DIRECTORY,Y,Y,DDL CREATE DOMAIN,Y,Y,DDL CREATE EVENT TRIGGER,Y,Y,DDL CREATE EXTENSION,Y,Y,DDL CREATE FOREIGN DATA WRAPPER,Y,Y,DDL CREATE FOREIGN TABLE,Y,Y,DDL CREATE FUNCTION,Y,Y,DDL CREATE INDEX,Y,Y,DML CREATE LANGUAGE,Y,Y,DDL CREATE MATERIALIZED VIEW,Y,:ref:`Extensions providing replication or HA functionality` ,DML CREATE OPERATOR,Y,Y,DDL CREATE OPERATOR CLASS,Y,Y,DDL CREATE OPERATOR FAMILY,Y,Y,DDL CREATE PACKAGE,Y,Y,DDL CREATE PACKAGE BODY,Y,Y,DDL CREATE POLICY,Y,Y,DML CREATE PROCEDURE,Y,Y,DDL CREATE PROFILE,Y,Y,:ref:`DDL locking details` CREATE PUBLICATION,Y,Y,DDL CREATE QUEUE,Y,Y,DDL CREATE QUEUE TABLE,Y,Y,DDL CREATE REDACTION POLICY,Y,Y,DDL CREATE RESOURCE GROUP,Y,N,N CREATE ROLE,Y,Y,DDL CREATE ROUTINE,Y,Y,DDL CREATE RULE,Y,Y,DDL CREATE SCHEMA,Y,Y,DDL CREATE SEQUENCE,:ref:`DDL locking details` ,Y,DDL CREATE SERVER,Y,Y,DDL CREATE STATISTICS,Y,Y,DDL CREATE SUBSCRIPTION,Y,Y,DDL CREATE SYNONYM,Y,Y,DDL CREATE TABLE,Y,Y,DDL CREATE TABLE AS,:ref:`DDL locking details` ,Y,DDL CREATE TABLESPACE,Y,Y,DDL CREATE TEXT SEARCH CONFIGURATION,Y,Y,DDL CREATE TEXT SEARCH DICTIONARY,Y,Y,DDL CREATE TEXT SEARCH PARSER,Y,Y,DDL CREATE TEXT SEARCH TEMPLATE,Y,Y,DDL CREATE TRANSFORM,Y,Y,DDL CREATE TRIGGER,Y,Y,DDL CREATE TYPE,Y,Y,DDL CREATE TYPE BODY,Y,Y,DDL CREATE USER MAPPING,Y,Y,DDL CREATE VIEW,Y,Y,DDL DEALLOCATE,Y,N,N DEALLOCATE ALL,Y,N,N DECLARE CURSOR,Y,N,N DISCARD,Y,N,N DISCARD ALL,Y,N,N DISCARD PLANS,Y,N,N DISCARD SEQUENCES,Y,N,N DISCARD TEMP,Y,N,N DO,Y,N,N DROP ACCESS METHOD,Y,Y,DDL DROP AGGREGATE,Y,Y,DDL DROP CAST,Y,Y,DDL DROP COLLATION,Y,Y,DDL DROP CONSTRAINT,Y,Y,DDL DROP CONVERSION,Y,Y,DDL DROP DATABASE,Y,N,N DROP DATABASE LINK,Y,Y,DDL DROP DIRECTORY,Y,Y,DDL DROP DOMAIN,Y,Y,DDL DROP EVENT TRIGGER,Y,Y,DDL DROP EXTENSION,Y,Y,DDL DROP FOREIGN DATA WRAPPER,Y,Y,DDL DROP FOREIGN TABLE,Y,Y,DDL DROP FUNCTION,Y,Y,DDL DROP INDEX,Y,Y,DDL DROP LANGUAGE,Y,Y,DDL DROP MATERIALIZED VIEW,Y,:ref:`Extensions providing replication or HA functionality` ,DML DROP OPERATOR,Y,Y,DDL DROP OPERATOR CLASS,Y,Y,DDL DROP OPERATOR FAMILY,Y,Y,DDL DROP OWNED,Y,Y,DDL DROP PACKAGE,Y,Y,DDL DROP PACKAGE BODY,Y,Y,DDL DROP POLICY,Y,Y,DDL DROP PROCEDURE,Y,Y,DDL DROP PROFILE,Y,Y,DDL DROP PUBLICATION,Y,Y,DDL DROP QUEUE,Y,Y,DDL DROP QUEUE TABLE,Y,Y,DDL DROP REDACTION POLICY,Y,Y,DDL DROP RESOURCE GROUP,Y,N,N DROP ROLE,Y,Y,DDL DROP ROUTINE,Y,Y,DDL DROP RULE,Y,Y,DDL DROP SCHEMA,Y,Y,DDL DROP SEQUENCE,Y,Y,DDL DROP SERVER,Y,Y,DDL DROP STATISTICS,Y,Y,DDL DROP SUBSCRIPTION,Y,Y,DDL DROP SYNONYM,Y,Y,DDL DROP TABLE,Y,Y,DML DROP TABLESPACE,Y,Y,DDL DROP TEXT SEARCH CONFIGURATION,Y,Y,DDL DROP TEXT SEARCH DICTIONARY,Y,Y,DDL DROP TEXT SEARCH PARSER,Y,Y,DDL DROP TEXT SEARCH TEMPLATE,Y,Y,DDL DROP TRANSFORM,Y,Y,DDL DROP TRIGGER,Y,Y,DDL DROP TYPE,Y,Y,DDL DROP TYPE BODY,Y,Y,DDL DROP USER MAPPING,Y,Y,DDL DROP VIEW,Y,Y,DDL EXECUTE,Y,N,N EXPLAIN,Y,:ref:`DDL locking details` ,:ref:`DDL locking details` FETCH,Y,N,N GRANT,Y,:ref:`DDL locking details` ,DDL GRANT ROLE,Y,Y,DDL IMPORT FOREIGN SCHEMA,Y,Y,DDL LISTEN,Y,N,N LOAD,Y,N,N LOAD ROW DATA,Y,Y,DDL LOCK TABLE,Y,N,:ref:`DDL locking details` MOVE,Y,N,N NOTIFY,Y,N,N PREPARE,Y,N,N PREPARE TRANSACTION,Y,N,N REASSIGN OWNED,Y,Y,DDL REFRESH MATERIALIZED VIEW,Y,:ref:`Extensions providing replication or HA functionality` ,DML REINDEX,Y,Y,DDL RELEASE,Y,N,N RESET,Y,N,N REVOKE,Y,:ref:`DDL locking details` ,DDL REVOKE ROLE,Y,Y,DDL ROLLBACK,Y,N,N ROLLBACK PREPARED,Y,N,N SAVEPOINT,Y,N,N SECURITY LABEL,Y,:ref:`DDL locking details` ,DDL SELECT INTO,:ref:`DDL locking details` ,Y,DDL SET,Y,N,N SET CONSTRAINTS,Y,N,N SHOW,Y,N,N START TRANSACTION,Y,N,N TRUNCATE TABLE,Y,:ref:`DDL locking details` ,:ref:`DDL locking details` UNLISTEN,Y,N,N VACUUM,Y,N,N Command notes ------------- ALTER SEQUENCE ^^^^^^^^^^^^^^ Starting with PGD 6.3.0, ``ALTER SEQUENCE`` is fully supported. While PGD 6.2.0 introduced ``RENAME`` and ``SET SCHEMA`` functionality for ``galloc`` sequences, the feature did not work for sequences created in PGD versions earlier than 6.2.0 following an upgrade to 6.2.0. The reason for the failure was that PGD 6.2.0 introduced a global UUID identifier to track ``galloc`` sequences, which was generated at the time of sequence creation, but it was missing for sequences created on an earlier PGD version. PGD 6.3.0 removes that limitation as BDR manager generates the UUID for sequences if it is missing, enabling the feature for sequences created in previous PGD versions. ALTER TABLE ^^^^^^^^^^^ Generally, ``ALTER TABLE`` commands are allowed. However, several subcommands aren’t supported. ALTER TABLE disallowed commands ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Some variants of ``ALTER TABLE`` currently aren’t allowed on a PGD node: - ``ALTER COLUMN ... SET STORAGE external`` - Is rejected if the column is one of the columns of the replica identity for the table. You can override this behavior using ``bdr.permit_unsafe_commands`` if you’re sure the command is safe. - ``RENAME`` - Can’t rename an Autopartitioned table. - ``SET SCHEMA`` - Can’t set the schema of an Autopartitioned table. - ``ALTER TABLE ... ADD FOREIGN KEY`` - Isn’t supported if current user doesn’t have permission to read the referenced table or if the referenced table has RLS restrictions enabled that the current user can’t bypass. The following example fails because it tries to add a constant value of type ``timestamp`` onto a column of type ``timestamptz`` . The cast between ``timestamp`` and ``timestamptz`` relies on the time zone of the session and so isn’t immutable. .. code:: sql ALTER TABLE foo ADD expiry_date timestamptz DEFAULT timestamp 2100-01-01 00:00:00 NOT NULL; You can add certain types of constraints, such as ``CHECK`` and ``FOREIGN KEY`` constraints, without taking a DML lock. But this requires a two-step process of first creating a ``NOT VALID`` constraint and then validating the constraint in a separate transaction with the ``ALTER TABLE ... VALIDATE CONSTRAINT`` command. See :ref:`Adding a CONSTRAINT ` for more details. ALTER TABLE locking ^^^^^^^^^^^^^^^^^^^ The following variants of ``ALTER TABLE`` take only DDL lock and not a DML lock: - ``ALTER TABLE ... ADD COLUMN ... (immutable) DEFAULT`` - ``ALTER TABLE ... ALTER COLUMN ... SET DEFAULT expression`` - ``ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT`` - ``ALTER TABLE ... ALTER COLUMN ... TYPE`` if it doesn’t require rewrite - ``ALTER TABLE ... ALTER COLUMN ... SET STATISTICS`` - ``ALTER TABLE ... VALIDATE CONSTRAINT`` - ``ALTER TABLE ... ATTACH PARTITION`` - ``ALTER TABLE ... DETACH PARTITION`` - ``ALTER TABLE ... ENABLE TRIGGER`` (``ENABLE REPLICA TRIGGER`` still takes a DML lock) - ``ALTER TABLE ... CLUSTER ON`` - ``ALTER TABLE ... SET WITHOUT CLUSTER`` - ``ALTER TABLE ... SET ( storage_parameter = value [, ... ] )`` - ``ALTER TABLE ... RESET ( storage_parameter = [, ... ] )`` - ``ALTER TABLE ... OWNER TO`` All other variants of ``ALTER TABLE`` take a DML lock on the table being modified. Some variants of ``ALTER TABLE`` have restrictions, noted below. ALTER TABLE examples ^^^^^^^^^^^^^^^^^^^^ Different types of ``ALTER TABLE ... ALTER COLUMN TYPE`` (ATCT) operations are possible. Some ATCT operations update only the metadata of the underlying column type and don’t require a rewrite of the underlying table data. This is typically the case when the existing column type and the target type are binary coercible. .. code:: sql CREATE TABLE foo (id BIGINT PRIMARY KEY, description VARCHAR(20)); ALTER TABLE foo ALTER COLUMN description TYPE VARCHAR(128); However, making this change to reverse the command that is changed from VARCHAR(128) to VARCHAR(20) isn’t binary coercible and will result in table rewrite. .. code:: sql CREATE TABLE sample (col1 BIGINT PRIMARY KEY, col2 VARCHAR(128), col3 INT); ALTER TABLE sample ALTER COLUMN col2 TYPE VARCHAR(256); You can also change the column type to VARCHAR or TEXT data types because of binary coercibility. Again, this is just a metadata update of the underlying column type. .. code:: sql ALTER TABLE sample ALTER COLUMN col2 TYPE VARCHAR; ALTER TABLE sample ALTER COLUMN col2 TYPE TEXT; TABLE REWRITE examples: ''''''''''''''''''''''' Users may want to change data types of columns, add columns with a non-null default, or run maintenance jobs. These operations are supported by DDL commands in PostgreSQL which could trigger a table rewrite. The table rewrite operation involves creating a new version of the table on disk. DDL operations such as ``CLUSTER`` or ``VACUUM`` directly cause table rewriting without any change in table definition. DDL operations like ``ALTER TABLE…ALTER COLUMN TYPE`` might change columns and trigger a table rewrite. It copies all the existing data from the old version to the new one, and once the new version is ready and consistent, it replaces the old table with the new one. Examples of typical table rewrite ATCT operations: Consider the following table: .. code:: sql CREATE TABLE foo( c1 int, c2 int, c3 int, name text, time_now timestamptz, dollar numeric, indian_currency int GENERATED ALWAYS AS (dollar * 90.50) STORED ); Given that table, the following ALTER TABLE commands will result in triggering a table rewrite. Change column data type: .. code:: sql ALTER TABLE foo ALTER c1 TYPE bigint; Change column type to incompatible type with USING clause: .. code:: sql ALTER TABLE foo ALTER COLUMN name TYPE int USING name::integer; Volatile column types: .. code:: sql ALTER TABLE foo ALTER COLUMN time_now TYPE timestamp; Add column with mutable default: .. code:: sql ALTER TABLE foo ADD COLUMN time_clock timestamptz NOT NULL DEFAULT clock_timestamp(); Generated columns: .. code:: sql ALTER TABLE foo ALTER COLUMN indian_currency TYPE numeric; Run multiple ``ALTER TABLE`` DDL operations in single transaction block: .. code:: sql BEGIN; ALTER TABLE foo ALTER c2 TYPE bigint; ALTER TABLE foo ALTER c3 TYPE bigint; COMMIT; In a PGD cluster this involves a rewrite, therefore, the rewrite activity takes the global leader lock of type DML and thus requires that all group leaders in cluster are available. .. Note:: The `ALTER TABLE` examples provided aren't an exhaustive list of possibly allowable ATCT operations. ALTER TYPE ^^^^^^^^^^ ``ALTER TYPE`` is replicated, but a global DML lock isn’t applied to all tables that use that data type, since PostgreSQL doesn’t record those dependencies. See :ref:`Workarounds for DDL restrictions ` . COMMENT ON ^^^^^^^^^^ All variants of ``COMMENT ON`` are allowed, but ``COMMENT ON TABLESPACE/DATABASE/LARGE OBJECT`` isn’t replicated. CREATE PROFILE or ALTER PROFILE ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The ``PASSWORD_VERIFY_FUNCTION`` associated with the profile should be ``IMMUTABLE`` if the function is ``SECURITY DEFINER`` . Such a ``CREATE PROFILE`` or ``ALTER PROFILE`` command will be replicated but subsequent ``CREATE USER`` or ``ALTER USER`` commands using this profile will break the replication due to the ``writer`` worker throwing the error: ``cannot change current role within security-restricted operation`` . .. Note:: PGD doesn't execute `PASSWORD_VERIFY_FUNCTION` on witness servers, since database objects aren't replicated to these servers. The origin node already executed the function and verified the password, so a duplicate check on the witness is redundant. To run ``PASSWORD_VERIFY_FUNCTION`` on a witness server anyway, create the function manually there, then run ``CREATE PROFILE`` or ``ALTER PROFILE`` directly on the witness server. .. :: ### CREATE SEQUENCE Generally ``CREATE SEQUENCE`` is supported, but when using global sequences, some options have no effect. CREATE TABLE AS and SELECT INTO ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ``CREATE TABLE AS`` and ``SELECT INTO`` are allowed only if all subcommands are also allowed. EXPLAIN ^^^^^^^ Generally ``EXPLAIN`` is allowed, but because ``EXPLAIN ANALYZE`` can have side effects on the database, there are some restrictions on it. EXPLAIN ANALYZE Replication ^^^^^^^^^^^^^^^^^^^^^^^^^^^ ``EXPLAIN ANALYZE`` follows replication rules of the analyzed statement. EXPLAIN ANALYZE Locking ^^^^^^^^^^^^^^^^^^^^^^^ ``EXPLAIN ANALYZE`` follows locking rules of the analyzed statement. GRANT and REVOKE ^^^^^^^^^^^^^^^^ Generally ``GRANT`` and ``REVOKE`` statements are supported, however ``GRANT/REVOKE ON TABLESPACE/LARGE OBJECT`` aren’t replicated. LOCK TABLE ^^^^^^^^^^ LOCK TABLE isn’t replicated, but it might acquire the global DML lock when ``bdr.lock_table_locking`` is set ``on`` . You can also use The ``bdr.global_lock_table()`` function to explicitly request a global DML lock. MATERIALIZED VIEW ^^^^^^^^^^^^^^^^^ ``CREATE MATERIALIZED VIEW`` populates data on the upstream node only; the data is then replicated. ``REFRESH MATERIALIZED VIEW`` also runs on the upstream node only; the result is replicated to downstream nodes. Materialized view replication is available only on BDRPG, EDB Advanced Server (EDBAS), or 2QPG. A community Postgres build of PGD treats all four commands as unreplicated. SECURITY LABEL ^^^^^^^^^^^^^^ All variants of ``SECURITY LABEL`` are allowed, but ``SECURITY LABEL ON TABLESPACE/DATABASE/LARGE OBJECT`` isn’t replicated. TRUNCATE Replication ^^^^^^^^^^^^^^^^^^^^ ``TRUNCATE`` command is replicated as DML, not as a DDL statement. Whether the ``TRUNCATE`` on table is replicated depends on replication settings for each affected table. TRUNCATE Locking ^^^^^^^^^^^^^^^^ Even though ``TRUNCATE`` isn’t replicated the same way as other DDL, it can acquire the global DML lock when ``bdr.truncate_locking`` is set to ``on`` .