DDL command handling matrix v6.1.0

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 notes

ALTER SEQUENCE

Generally ALTER SEQUENCE is supported, but when using global sequences, some options have no effect.

ALTER SEQUENCE ... RENAME isn't supported on galloc sequences (only). ALTER SEQUENCE ... SET SCHEMA isn't supported on galloc sequences (only).

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.

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 Restricted DDL workarounds.

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.

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.

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.