Table Filters v6.2

Table filters specify the selection criteria for rows in publication tables or views that are to be included during replications to subscriptions from the publication database in a single-master replication system or between primary nodes in a multi-master replication system. Rows that do not satisfy the selection criteria are excluded from replications to subscriptions or primary nodes on which these table filters have been enabled.

Implementing Table Filters

Implementing table filters is a two-part process. First, a set of available table filters must be defined. This can be done during the process of creating the publication by defining specific, named rules applicable to selected publication tables or views expressed in the form of SQL WHERE clauses.

Once a set of available table filters have been defined, they must be enabled only on those subscription tables of a single-master replication system or primary node tables of a multi-master replication system where filtering is to occur during replication to those particular target tables. No filtering occurs during replication to a target subscription table or primary node table if no filters have been specifically enabled on that table in the subscription or primary node.

It is strongly recommended that a snapshot replication be performed to the subscriptions or primary nodes that contain tables on which the filtering criteria has changed either by the addition of filter rules, the removal of filter rules, or the modification of existing filter rules.

A snapshot ensures that the content of the subscription tables or primary node tables is consistent with the updated filtering criteria.

Note

(For MMR only): When using table filters in a multi-master replication system, the primary definition node, which provides the source of the table content for a snapshot, should contain a superset of all the data contained in the other primary nodes of the multi-master replication system. This ensures that the target of a snapshot receives all of the data that satisfies any filtering criteria enabled on the other primary nodes.

On the contrary, if the primary definition node contains only a subset of all the data contained in the other primary nodes, then a snapshot to another primary node may not result in the complete set of data that is required for that target primary node.

Effects of Table Filtering

A filter enabled on a table only affects the results from snapshot or synchronization replications targeted to that table by the xDB Replication Server. Filtering has no effect on changes made directly on the target table by external user applications such as an SQL command line utility.

Filtering has the following effects on a targeted, filtered table.

Note

In the following discussion, a result set refers to the set of rows in a table satisfying the selection criteria of an UPDATE or DELETE statement executed on that table.

In a snapshot replication, a row from the source table of the snapshot is inserted into the target table if the row satisfies the filtering criteria. Otherwise the row is excluded from insertion into the target table.

When an INSERT statement is executed on a source table followed by a synchronization replication, the row is inserted into the target table of the synchronization if the row satisfies the filtering criteria. Otherwise the row is excluded from insertion into the target table.

When an UPDATE statement is executed on a source table followed by a synchronization replication, the UPDATE result set of the source table determines the action on the target table of the synchronization as follows.

  • If a row in the result set has no corresponding row in the target table with the same primary key value, and the updated row in the result set satisfies the filtering criteria, then the row is inserted into the target table. (That is, a row that was previously non-existent in the target table is added because the updated row in the source table now satisfies the filtering criteria.)
  • If a row in the result set has a corresponding row in the target table with the same primary key value, and the updated row in the result set satisfies the filtering criteria, then the row in the target table is updated accordingly. (That is, the update is applied to an existing, matching row in the target table that still satisfies the filtering criteria after the update.)
  • If a row in the result set has a corresponding row in the target table with the same primary key value, and the updated row in the result set no longer satisfies the filtering criteria, then the corresponding row in the target table is deleted. (That is, an existing, matching row in the target table no longer satisfies the filtering criteria after the update, so the row is removed from the target table.)

When a DELETE statement is executed on a source table followed by a synchronization replication, the DELETE result set of the source table determines the action on the target table of the synchronization as follows.

  • If a row in the result set has a corresponding row in the target table with the same primary key value, then the row with that primary key value is deleted from the target table. (That is, an existing, matching row in the target table is removed.)
  • If a row in the result set has no corresponding row in the target table with the same primary key value, then no action is taken on the target table for that row. (That is, there is no existing, matching row in the target table, so there is no row to remove from the target table.)

Thus, regardless of whether the transaction on the source table is an INSERT, UPDATE, or DELETE statement, the goal of a table filter is to ensure that all rows in the target table satisfy the filter rule.

Table Settings and Restrictions for Table Filters

This section lists specific table settings and restrictions on the use of table filters.

REPLICA IDENTITY Setting for Filtering in a Log-Based Replication System

For replication systems using the log-based method of synchronization replication, a publication table on which a filter is to be defined must have the REPLICA IDENTITY option set to FULL.

Note

This REPLICA IDENTITY FULL setting is not required for tables in single-master, snapshot-only publications, See Snapshot-Only Publications for information on snapshot-only publications.

This setting is done with the ALTER TABLE command as shown by the following:

ALTER TABLE schema.table_name REPLICA IDENTITY FULL

For additional information see the ALTER TABLE SQL command in the PostgreSQL Core Documentation located at:

https://www.postgresql.org/docs/current/static/sql-altertable.html

For example, for a publication table named edb.dept, use the following ALTER TABLE command:

ALTER TABLE edb.dept REPLICA IDENTITY FULL;

The REPLICA IDENTITY setting can be displayed by the PSQL utility using the \d+ command:

edb=# \d+ edb.dept
                                  Table "edb.dept"
 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 deptno | numeric(2,0)          | not null  | main     |              |
 dname  | character varying(14) |           | extended |              |
 loc    | character varying(13) |           | extended |              |
Indexes:
    "dept_pk" PRIMARY KEY, btree (deptno)
    "dept_dname_uq" UNIQUE CONSTRAINT, btree (dname)
Referenced by:
    TABLE "emp" CONSTRAINT "emp_ref_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno)
    TABLE "jobhist" CONSTRAINT "jobhist_ref_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL
Replica Identity: FULL

The REPLICA IDENTITY FULL setting is required on tables in the following databases of a log-based replication system:

  • In a single-master replication system, table filters are defined in the primary database. Thus, the publication tables in the primary database requiring filter definitions must be altered to a REPLICA IDENTITY FULL setting, but only if the publication is not a snapshot-only publication. See Snapshot-Only Publications for information on snapshot-only publications.
  • In a multi-master replication system, table filters are defined in the primary definition node. Thus, publication tables in the primary definition node requiring filter definitions must be altered to a REPLICA IDENTITY FULL setting.
  • In a multi-master replication system, non-MDN nodes should not have their tables’ REPLICA IDENTITY option set to FULL unless transactions are expected to be targeted on those non-MDN nodes, and the transactions are to be filtered when they are replicated to the other primary nodes.

The REPLICA IDENTITY FULL setting on a source table ensures that certain types of transactions on the source table result in the proper updates to the target tables on which filters have been enabled.

Note

In addition to table filtering requirements, the REPLICA IDENTITY FULL setting may be required on publication tables for other reasons in xDB Replication Server. See Configuration Parameter and Table Setting Requirements for additional requirements.

Filtering Restrictions on Data Types

Table filters are not supported on binary data type columns. A binary data type is the Postgres data type BYTEA. In addition, table filters are not supported on Advanced Server columns with data types BINARY, VARBINARY, BLOB, LONG RAW, and RAW as these are alias names for the BYTEA data type.

Filtering Restrictions on Operators

XDB supports modulus operator (denoted by %) to define a filter clause. However, the following restrictions apply:

  • You can have only a single filter condition using the modulus operator

  • You cannot use it with any other conditions using AND or OR operators

XDB supports the modulus filter in the following formats:

deptno%3=0

@deptno%3=0

Roadmap for Further Instructions

The specific details on implementing table filtering depend upon whether you are using a single-master replication system or a multi-master replication system. The following is a roadmap to the relevant sections for each type of replication system.

For using table filters in a single-master replication system see the following sections:

For using table filters in a multi-master replication system see the following sections: