DDL command handling matrix

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#

Command

Allowed

Replicated

Lock

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

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

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

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

DDL locking details

Y

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

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

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

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

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

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

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

DDL locking details

DDL locking details

FETCH

Y

N

N

GRANT

Y

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

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

Extensions providing replication or HA functionality

DML

REINDEX

Y

Y

DDL

RELEASE

Y

N

N

RESET

Y

N

N

REVOKE

Y

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

DDL locking details

DDL

SELECT INTO

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

DDL locking details

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.

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

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.

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.

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:

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:

ALTER TABLE foo ALTER c1 TYPE bigint;

Change column type to incompatible type with USING clause:

ALTER TABLE foo ALTER COLUMN name TYPE int USING name::integer;

Volatile column types:

ALTER TABLE foo ALTER COLUMN time_now TYPE timestamp;

Add column with mutable default:

ALTER TABLE foo ADD COLUMN time_clock timestamptz NOT NULL DEFAULT clock_timestamp();

Generated columns:

ALTER TABLE foo ALTER COLUMN indian_currency TYPE numeric;

Run multiple ALTER TABLE DDL operations in single transaction block:

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

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 .

Contents