This page in other versions: 1.0 / 2.0

8.2. Statement specific DDL replication concerns

Not all commands can be replicated automatically. Some are allowed regardless - generally ones that have affect on more than one database. Others are disallowed.

Important: Global DDL, like CREATE ROLE, CREATE USER etc is not replicated and should be applied on each node if the created objects will be referenced by a BDR-enabled database.

8.2.1. Not replicated DDL statements

Some DDL statements, mainly those that affect objects that are PostgreSQL-instance-wide rather than database-sepecific, are not replicated. They are applied on the node that executes them without taking the global DDL lock and are not sent to other nodes.

If you create non-replicated objects that are to be referenced by replicated objects (e.g. creating a role, not replicated, then creating a table, replicated, that's owned by that role) you must ensure that the non-replicated object is created on all BDR nodes. You can do this manually, by running the statement on each node. Or you can use bdr.bdr_replicate_ddl_command to apply the statement on the local node and manually enqueue it for replication on all nodes.

Using bdr.bdr_replicate_ddl_command is the recommended approach, e.g.:

     SELECT bdr.bdr_replicate_ddl_command('CREATE USER myuser;');
    

Note: It is not necessary that the definition of objects like roles be the same on all nodes, only that they exist. You can for example CREATE ROLE somerole WITH NOLOGIN on most nodes, but on one node you can create them WITH LOGIN.

The statements that are applied locally but not replicated are:

CREATE DATABASE

CREATE DATABASE cannot be replicated because BDR works on a per database level.

CREATE ROLE/USER/GROUP

CREATE ROLE cannot be replicated because BDR works on a per database level. It is possible that a workaround for this will be added.

Warning

Not creating roles of the same name (not necessarily with the same access details otherwise though) on all systems can break replication when statements like ALTER TABLE ... OWNER TO are replicated.

CREATE TABLESPACE

CREATE TABLESPACE cannot be replicated because BDR works on a per database level.

Warning

Not creating tablespaces of the same name (not necessarily with the same location though) on all systems can break replication when statements like ALTER TABLE ... SET TABLESPACE are replicated.

DROP DATABASE

DROP DATABASE cannot be replicated because BDR works on a per database level.

Note that a database that is configured for BDR cannot be dropped while that is the case.

DROP TABLESPACE

DROP TABLESPACE cannot be replicated because BDR works on a per database level.

Warning

Dropping tablespaces only on some nodes can cause problems when relations on other nodes are moved into the tablespace that does not exist locally anymore.

DROP ROLE/USER/GROUP

DROP ROLE cannot be replicated because BDR works on a per database level. It is possible that a workaround for this will be added.

Warning

Dropping role only on some nodes can cause problems when objects on other nodes are assigned to roles that do not exist locally anymore.

ALTER ROLE/USER/GROUP

ALTER ROLE cannot be replicated because BDR works on a per database level. It is possible that a workaround for this will be added.

Normally all commands but ALTER ROLE ... RENAME TO ... should be safe to execute in the sense that doing so won't cause replication to break.

Warning

Renaming a role only on some nodes can lead to problems due to replicated DDL statmeents not being able to execute anymore.

ALTER DATABASE

ALTER DATABASE cannot be replicated because BDR works on a per database level.

In practice the primary problematic case is when trying to change settings on a per database basis using ALTER DATABASE ... SET ..., these have to be executed on every database for now.

Warning

Renaming a database can lead to the connection information stored on some of the nodes not being valid anymore.

ALTER TABLESPACE

ALTER TABLSPACE cannot be replicated because BDR works on a per database level. It is safe to execute on the individual nodes though.

8.2.2. Prohibited DDL statements

BDR prevents some DDL statements from running when it is active on a database. This protects the consistency of the system by disallowing statements that cannot be replicated correctly, or for which replication is not yet supported. Statements that are supported with some restrictions are covered in DDL statements with restrictions; commands that are entirely disallowed in BDR are covered below.

Generally unsupported statements are prevented from being executed, raising a feature_not_supported (SQLSTATE 0A000) error.

The following DDL commands are rejected by BDR when BDR is active on a database, and will fail with an ERROR:

CREATE TABLE AS/SELECT INTO

CREATE TABLE AS/SELECT INTO are prohibited unless UNLOGGED or UNLOGGED temporary is specified.

CREATE TABLE ... OF TYPE

CREATE TABLE ... OF TYPE is prohibited unless UNLOGGED or UNLOGGED temporary is specified.

CREATE TEXT SEARCH PARSER

CREATE TEXT SEARCH PARSER is prohibited.

CREATE TEXT SEARCH DICTIONARY

CREATE TEXT SEARCH DICTIONARY is prohibited.

ALTER TEXT SEARCH DICTIONARY

ALTER TEXT SEARCH DICTIONARY is prohibited.

CREATE TEXT SEARCH TEMPLATE

CREATE TEXT SEARCH TEMPLATE is prohibited.

CREATE TEXT SEARCH CONFIGURATION

CREATE TEXT SEARCH template is prohibited.

ALTER TEXT SEARCH CONFIGURATION

ALTER TEXT SEARCH template is prohibited.

CREATE COLLATION

CREATE CREATE COLLATION is prohibited.

ALTER EXTENSION

ALTER EXTENSION currently is prohibited.

CREATE FOREIGN DATA WRAPPER

CREATE FOREIGN DATA WRAPPER currently is prohibited.

ALTER FOREIGN DATA WRAPPER

ALTER FOREIGN DATA WRAPPER currently is prohibited.

CREATE SERVER

CREATE SERVER currently is prohibited.

ALTER SERVER

ALTER SERVER currently is prohibited.

CREATE USER MAPPING

CREATE USER MAPPING currently is prohibited.

ALTER USER MAPPING

ALTER USER MAPPING currently is prohibited.

DROP USER MAPPING

DROP USER MAPPING currently is prohibited.

REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW currently is prohibited.

CREATE LANGUAGE

CREATE LANGUAGE currently is prohibited. Note that nearly all procedual languages are available as an extension and CREATE EXTENSION is supported.

CREATE CONVERSION

CREATE CONVERSION currently is prohibited.

CREATE CAST

Note: CREATE CAST currently is prohibited. Note that CREATE CAST inside an extension is supported.

CREATE OPERATOR FAMILY

Note: CREATE OPERATOR FAMILY currently is prohibited. Note that CREATE OPERATOR FAMILY inside an extension is supported.

ALTER OPERATOR FAMILY

ALTER OPERATOR FAMILY currently is prohibited.

Note: Note that ALTER OPERATOR FAMILY inside an extension is supported.

CREATE OPERATOR CLASS

CREATE OPERATOR CLASS currently is prohibited.

Note: Note that CREATE OPERATOR CLASS inside an extension is supported.

DROP OWNED

DROP OWNED is prohibited.

SECURITY LABEL

Except for some BDR internal use SECURITY LABEL is prohibited.

8.2.3. DDL statements with restrictions

BDR prevents some DDL statements from running when it is active on a database. This protects the consistency of the system by disallowing statements that cannot be replicated correctly, or for which replication is not yet supported. Entirely prohibited statements are covered above in Prohibited DDL statements; commands where some subcommands or features are limited are covered below.

If a statement is not permitted under BDR it is often possible to find another way to do the same thing. For example, you can't do a ALTER TABLE that'll cause a full table rewrite, but it's generally possible to rephrase that as a series of independent ALTER TABLE and UPDATE statements that don't do the full table rewrite. See ALTER TABLE below for details on that example.

Generally unsupported statements are prevented from being executed, raising a feature_not_supported (SQLSTATE 0A000) error.

The following statements or statement options are not currently permitted when BDR is active on a database:

CREATE TABLE

Generally CREATE TABLE is allowed. There are a few options/subcommands that are not supported.

Not supported commands are:

  • WITH OIDS - outdated option, not deemed worth to add support for

  • OF TYPE - not supported yet

  • CONSTRAINT ... EXCLUDE - not supported yet

ALTER TABLE

Generally ALTER TABLE commands are allowed. There are a however several sub-commands that are not supported, mainly those that perform a full-table re-write.

Not supported commands are:

  • ADD COLUMN ... DEFAULT - this option can unfortunately not be supported. It is however often possible to rewrite this into several, supported, commands:

                BEGIN;
                ALTER TABLE mtab ADD COLUMN newcol;
                ALTER TABLE mtab ALTER COLUMN newcal SET DEFAULT default_value;
                UPDATE mtab SET newcol = default_value;
               

    If preexisiting rows do not need the default value, the UPDATE can obviously be avoided. In that case doing separate ADD COLUMN and ALTER COLUMN ... SET DEFAULT commands are advantageous anyway as they don't rewrite the whole table.

    Unlike the single-statement version there will be a period in which rows have the new column, but the column value is null.

  • ADD CONSTRAINT ... EXCLUDE - exclusion are not supported for now. Exclusion constraints don't make much sense in an asynchronous system and lead to changes that cannot be replayed.

  • ALTER CONSTRAINT - changing constraint settings is not supported for now.

  • ALTER COLUMN ... TYPE - changing a column's type is not supported. Chaning a column in a way that doesn't require table rewrites may be suppported at some point.

  • ENABLE .. RULE - is not supported.

  • DISABLE .. RULE - is not supported.

  • [NO] INHERIT - is not supported.

  • [NOT] OF TYPE - is not supported.

  • ALTER COLUMN ... SET (..) - is not supported at the moment. Note however that ALTER COLUMN ... SET [NOT] NULL is supported.

  • SET (..) - is not supported at the moment.

CREATE INDEX

Generally CREATE INDEX is supported, but CREATE UNIQUE INDEX ... WHERE, i.e. partial unique indexes are not allowed.

CREATE SEQUENCE

Generally CREATE SEQUENCE is supported, but when using BDR's distributed sequences, some options are prohibited.

ALTER SEQUENCE

Generally ALTER SEQUENCE is supported, but when using BDR's distributed sequences, some options like START are prohibited. Several of them, like the aforementioned START can be specified during CREATE SEQUENCE.