Movatterモバイル変換

29.4.1. Row Filter Rules

29.4.2. Expression Restrictions

29.4.3. UPDATE Transformations

29.4.4. Partitioned Tables

29.4.5. Initial Data Synchronization

29.4.6. Combining Multiple Row Filters

29.4.7. Examples

Row filters are appliedbefore publishing the changes. If the row filter evaluates tofalse orNULL then the row is not replicated. TheWHERE clause expression is evaluated with the same role used for the replication connection (i.e. the role specified in theCONNECTION clause of theCREATE SUBSCRIPTION). Row filters have no effect forTRUNCATE command.

TheWHERE clause allows only simple expressions. It cannot contain user-defined functions, operators, types, and collations, system column references or non-immutable built-in functions.

If a publication publishesUPDATE orDELETE operations, the row filterWHERE clause must contain only columns that are covered by the replica identity (seeREPLICA IDENTITY). If a publication publishes onlyINSERT operations, the row filterWHERE clause can use any column.

Whenever anUPDATE is processed, the row filter expression is evaluated for both the old and new row (i.e. using the data before and after the update). If both evaluations aretrue, it replicates theUPDATE change. If both evaluations arefalse, it doesn't replicate the change. If only one of the old/new rows matches the row filter expression, theUPDATE is transformed toINSERT orDELETE, to avoid any data inconsistency. The row on the subscriber should reflect what is defined by the row filter expression on the publisher.

If the old row satisfies the row filter expression (it was sent to the subscriber) but the new row doesn't, then, from a data consistency perspective the old row should be removed from the subscriber. So theUPDATE is transformed into aDELETE.

If the old row doesn't satisfy the row filter expression (it wasn't sent to the subscriber) but the new row does, then, from a data consistency perspective the new row should be added to the subscriber. So theUPDATE is transformed into anINSERT.

Table 29.1 summarizes the applied transformations.

If the publication contains a partitioned table, the publication parameterpublish_via_partition_root determines which row filter is used. Ifpublish_via_partition_root istrue, theroot partitioned table's row filter is used. Otherwise, ifpublish_via_partition_root isfalse (default), eachpartition's row filter is used.

If the subscription requires copying pre-existing table data and a publication containsWHERE clauses, only data that satisfies the row filter expressions is copied to the subscriber.

If the subscription has several publications in which a table has been published with differentWHERE clauses, rows that satisfyany of the expressions will be copied. SeeSection 29.4.6 for details.

If the subscription has several publications in which the same table has been published with different row filters (for the samepublish operation), those expressions get ORed together, so that rows satisfyingany of the expressions will be replicated. This means all the other row filters for the same table become redundant if:

Create some tables to be used in the following examples.

test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));CREATE TABLEtest_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));CREATE TABLEtest_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));CREATE TABLE

Create some publications. Publicationp1 has one table (t1) and that table has a row filter. Publicationp2 has two tables. Tablet1 has no row filter, and tablet2 has a row filter. Publicationp3 has two tables, and both of them have a row filter.

test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');CREATE PUBLICATIONtest_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);CREATE PUBLICATIONtest_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);CREATE PUBLICATION

psql can be used to show the row filter expressions (if defined) for each publication.

test_pub=# \dRp+                               Publication p1  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root----------+------------+---------+---------+---------+-----------+---------- postgres | f          | t       | t       | t       | t         | fTables:    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))                               Publication p2  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root----------+------------+---------+---------+---------+-----------+---------- postgres | f          | t       | t       | t       | t         | fTables:    "public.t1"    "public.t2" WHERE (e = 99)                               Publication p3  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root----------+------------+---------+---------+---------+-----------+---------- postgres | f          | t       | t       | t       | t         | fTables:    "public.t2" WHERE (d = 10)    "public.t3" WHERE (g = 10)

psql can be used to show the row filter expressions (if defined) for each table. See that tablet1 is a member of two publications, but has a row filter only inp1. See that tablet2 is a member of two publications, and has a different row filter in each of them.

test_pub=# \d t1                 Table "public.t1" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- a      | integer |           | not null | b      | integer |           |          | c      | text    |           | not null |Indexes:    "t1_pkey" PRIMARY KEY, btree (a, c)Publications:    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))    "p2"test_pub=# \d t2                 Table "public.t2" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- d      | integer |           | not null | e      | integer |           |          | f      | integer |           |          |Indexes:    "t2_pkey" PRIMARY KEY, btree (d)Publications:    "p2" WHERE (e = 99)    "p3" WHERE (d = 10)test_pub=# \d t3                 Table "public.t3" Column |  Type   | Collation | Nullable | Default--------+---------+-----------+----------+--------- g      | integer |           | not null | h      | integer |           |          | i      | integer |           |          |Indexes:    "t3_pkey" PRIMARY KEY, btree (g)Publications:    "p3" WHERE (g = 10)

On the subscriber node, create a tablet1 with the same definition as the one on the publisher, and also create the subscriptions1 that subscribes to the publicationp1.

test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));CREATE TABLEtest_sub=# CREATE SUBSCRIPTION s1test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'test_sub-# PUBLICATION p1;CREATE SUBSCRIPTION

Insert some rows. Only the rows satisfying thet1 WHERE clause of publicationp1 are replicated.

test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');INSERT 0 1test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');INSERT 0 1test_pub=# SELECT * FROM t1; a |  b  |  c---+-----+----- 2 | 102 | NSW 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 6 | 106 | NSW 7 | 107 | NT 8 | 108 | QLD 9 | 109 | NSW(8 rows)

test_sub=# SELECT * FROM t1; a |  b  |  c---+-----+----- 6 | 106 | NSW 9 | 109 | NSW(2 rows)

Update some data, where the old and new row values both satisfy thet1 WHERE clause of publicationp1. TheUPDATE replicates the change as normal.

test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;UPDATE 1test_pub=# SELECT * FROM t1; a |  b  |  c---+-----+----- 2 | 102 | NSW 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 7 | 107 | NT 8 | 108 | QLD 9 | 109 | NSW 6 | 999 | NSW(8 rows)

test_sub=# SELECT * FROM t1; a |  b  |  c---+-----+----- 9 | 109 | NSW 6 | 999 | NSW(2 rows)

Update some data, where the old row values did not satisfy thet1 WHERE clause of publicationp1, but the new row values do satisfy it. TheUPDATE is transformed into anINSERT and the change is replicated. See the new row on the subscriber.

test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;UPDATE 1test_pub=# SELECT * FROM t1;  a  |  b  |  c-----+-----+-----   3 | 103 | QLD   4 | 104 | VIC   5 | 105 | ACT   7 | 107 | NT   8 | 108 | QLD   9 | 109 | NSW   6 | 999 | NSW 555 | 102 | NSW(8 rows)

test_sub=# SELECT * FROM t1;  a  |  b  |  c-----+-----+-----   9 | 109 | NSW   6 | 999 | NSW 555 | 102 | NSW(3 rows)

Update some data, where the old row values satisfied thet1 WHERE clause of publicationp1, but the new row values do not satisfy it. TheUPDATE is transformed into aDELETE and the change is replicated. See that the row is removed from the subscriber.

test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;UPDATE 1test_pub=# SELECT * FROM t1;  a  |  b  |  c-----+-----+-----   3 | 103 | QLD   4 | 104 | VIC   5 | 105 | ACT   7 | 107 | NT   8 | 108 | QLD   6 | 999 | NSW 555 | 102 | NSW   9 | 109 | VIC(8 rows)

test_sub=# SELECT * FROM t1;  a  |  b  |  c-----+-----+-----   6 | 999 | NSW 555 | 102 | NSW(2 rows)

The following examples show how the publication parameterpublish_via_partition_root determines whether the row filter of the parent or child table will be used in the case of partitioned tables.

Create a partitioned table on the publisher.

test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);CREATE TABLEtest_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;CREATE TABLE

Create the same tables on the subscriber.

test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);CREATE TABLEtest_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;CREATE TABLE

Create a publicationp4, and then subscribe to it. The publication parameterpublish_via_partition_root is set as true. There are row filters defined on both the partitioned table (parent), and on the partition (child).

test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5)test_pub-# WITH (publish_via_partition_root=true);CREATE PUBLICATION

test_sub=# CREATE SUBSCRIPTION s4test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'test_sub-# PUBLICATION p4;CREATE SUBSCRIPTION

Insert some values directly into theparent andchild tables. They replicate using the row filter ofparent (becausepublish_via_partition_root is true).

test_pub=# INSERT INTO parent VALUES (2), (4), (6);INSERT 0 3test_pub=# INSERT INTO child VALUES (3), (5), (7);INSERT 0 3test_pub=# SELECT * FROM parent ORDER BY a; a--- 2 3 4 5 6 7(6 rows)

test_sub=# SELECT * FROM parent ORDER BY a; a--- 2 3 4(3 rows)

Repeat the same test, but with a different value forpublish_via_partition_root. The publication parameterpublish_via_partition_root is set as false. A row filter is defined on the partition (child).

test_pub=# DROP PUBLICATION p4;DROP PUBLICATIONtest_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)test_pub-# WITH (publish_via_partition_root=false);CREATE PUBLICATION

test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;ALTER SUBSCRIPTION

Do the inserts on the publisher same as before. They replicate using the row filter ofchild (becausepublish_via_partition_root is false).

test_pub=# TRUNCATE parent;TRUNCATE TABLEtest_pub=# INSERT INTO parent VALUES (2), (4), (6);INSERT 0 3test_pub=# INSERT INTO child VALUES (3), (5), (7);INSERT 0 3test_pub=# SELECT * FROM parent ORDER BY a; a--- 2 3 4 5 6 7(6 rows)

test_sub=# SELECT * FROM child ORDER BY a; a--- 5 6 7(3 rows)