begriffs open source - ai-pg/blob - full-docs/txt/logical-replication-row-filter.txt

   1
   2 29.4. Row Filters #
   3
   4    29.4.1. Row Filter Rules
   5    29.4.2. Expression Restrictions
   6    29.4.3. UPDATE Transformations
   7    29.4.4. Partitioned Tables
   8    29.4.5. Initial Data Synchronization
   9    29.4.6. Combining Multiple Row Filters
  10    29.4.7. Examples
  11
  12    By default, all data from all published tables will be replicated to
  13    the appropriate subscribers. The replicated data can be reduced by
  14    using a row filter. A user might choose to use row filters for
  15    behavioral, security or performance reasons. If a published table sets
  16    a row filter, a row is replicated only if its data satisfies the row
  17    filter expression. This allows a set of tables to be partially
  18    replicated. The row filter is defined per table. Use a WHERE clause
  19    after the table name for each published table that requires data to be
  20    filtered out. The WHERE clause must be enclosed by parentheses. See
  21    CREATE PUBLICATION for details.
  22
  23 29.4.1. Row Filter Rules #
  24
  25    Row filters are applied before publishing the changes. If the row
  26    filter evaluates to false or NULL then the row is not replicated. The
  27    WHERE clause expression is evaluated with the same role used for the
  28    replication connection (i.e. the role specified in the CONNECTION
  29    clause of the CREATE SUBSCRIPTION). Row filters have no effect for
  30    TRUNCATE command.
  31
  32 29.4.2. Expression Restrictions #
  33
  34    The WHERE clause allows only simple expressions. It cannot contain
  35    user-defined functions, operators, types, and collations, system column
  36    references or non-immutable built-in functions.
  37
  38    If a publication publishes UPDATE or DELETE operations, the row filter
  39    WHERE clause must contain only columns that are covered by the replica
  40    identity (see REPLICA IDENTITY). If a publication publishes only INSERT
  41    operations, the row filter WHERE clause can use any column.
  42
  43 29.4.3. UPDATE Transformations #
  44
  45    Whenever an UPDATE is processed, the row filter expression is evaluated
  46    for both the old and new row (i.e. using the data before and after the
  47    update). If both evaluations are true, it replicates the UPDATE change.
  48    If both evaluations are false, it doesn't replicate the change. If only
  49    one of the old/new rows matches the row filter expression, the UPDATE
  50    is transformed to INSERT or DELETE, to avoid any data inconsistency.
  51    The row on the subscriber should reflect what is defined by the row
  52    filter expression on the publisher.
  53
  54    If the old row satisfies the row filter expression (it was sent to the
  55    subscriber) but the new row doesn't, then, from a data consistency
  56    perspective the old row should be removed from the subscriber. So the
  57    UPDATE is transformed into a DELETE.
  58
  59    If the old row doesn't satisfy the row filter expression (it wasn't
  60    sent to the subscriber) but the new row does, then, from a data
  61    consistency perspective the new row should be added to the subscriber.
  62    So the UPDATE is transformed into an INSERT.
  63
  64    Table 29.1 summarizes the applied transformations.
  65
  66    Table 29.1. UPDATE Transformation Summary
  67    Old row  New row  Transformation
  68    no match no match don't replicate
  69    no match match    INSERT
  70    match    no match DELETE
  71    match    match    UPDATE
  72
  73 29.4.4. Partitioned Tables #
  74
  75    If the publication contains a partitioned table, the publication
  76    parameter publish_via_partition_root determines which row filter is
  77    used. If publish_via_partition_root is true, the root partitioned
  78    table's row filter is used. Otherwise, if publish_via_partition_root is
  79    false (default), each partition's row filter is used.
  80
  81 29.4.5. Initial Data Synchronization #
  82
  83    If the subscription requires copying pre-existing table data and a
  84    publication contains WHERE clauses, only data that satisfies the row
  85    filter expressions is copied to the subscriber.
  86
  87    If the subscription has several publications in which a table has been
  88    published with different WHERE clauses, rows that satisfy any of the
  89    expressions will be copied. See Section 29.4.6 for details.
  90
  91 Warning
  92
  93    Because initial data synchronization does not take into account the
  94    publish parameter when copying existing table data, some rows may be
  95    copied that would not be replicated using DML. Refer to Section 29.9.1,
  96    and see Section 29.2.2 for examples.
  97
  98 Note
  99
 100    If the subscriber is in a release prior to 15, copy pre-existing data
 101    doesn't use row filters even if they are defined in the publication.
 102    This is because old releases can only copy the entire table data.
 103
 104 29.4.6. Combining Multiple Row Filters #
 105
 106    If the subscription has several publications in which the same table
 107    has been published with different row filters (for the same publish
 108    operation), those expressions get ORed together, so that rows
 109    satisfying any of the expressions will be replicated. This means all
 110    the other row filters for the same table become redundant if:
 111      * One of the publications has no row filter.
 112      * One of the publications was created using FOR ALL TABLES. This
 113        clause does not allow row filters.
 114      * One of the publications was created using FOR TABLES IN SCHEMA and
 115        the table belongs to the referred schema. This clause does not
 116        allow row filters.
 117
 118 29.4.7. Examples #
 119
 120    Create some tables to be used in the following examples.
 121 /* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
 122 /* pub # */ CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
 123 /* pub # */ CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
 124
 125    Create some publications. Publication p1 has one table (t1) and that
 126    table has a row filter. Publication p2 has two tables. Table t1 has no
 127    row filter, and table t2 has a row filter. Publication p3 has two
 128    tables, and both of them have a row filter.
 129 /* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
 130 /* pub # */ CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
 131 /* pub # */ CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10)
 132 ;
 133
 134    psql can be used to show the row filter expressions (if defined) for
 135    each publication.
 136 /* pub # */ \dRp+
 137                                          Publication p1
 138   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Generated col
 139 umns | Via root
 140 ----------+------------+---------+---------+---------+-----------+--------------
 141 -----+----------
 142  postgres | f          | t       | t       | t       | t         | none
 143      | f
 144 Tables:
 145     "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
 146
 147                                          Publication p2
 148   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Generated col
 149 umns | Via root
 150 ----------+------------+---------+---------+---------+-----------+--------------
 151 -----+----------
 152  postgres | f          | t       | t       | t       | t         | none
 153      | f
 154 Tables:
 155     "public.t1"
 156     "public.t2" WHERE (e = 99)
 157
 158                                          Publication p3
 159   Owner   | All tables | Inserts | Updates | Deletes | Truncates | Generated col
 160 umns | Via root
 161 ----------+------------+---------+---------+---------+-----------+--------------
 162 -----+----------
 163  postgres | f          | t       | t       | t       | t         | none
 164      | f
 165 Tables:
 166     "public.t2" WHERE (d = 10)
 167     "public.t3" WHERE (g = 10)
 168
 169    psql can be used to show the row filter expressions (if defined) for
 170    each table. See that table t1 is a member of two publications, but has
 171    a row filter only in p1. See that table t2 is a member of two
 172    publications, and has a different row filter in each of them.
 173 /* pub # */ \d t1
 174                  Table "public.t1"
 175  Column |  Type   | Collation | Nullable | Default
 176 --------+---------+-----------+----------+---------
 177  a      | integer |           | not null |
 178  b      | integer |           |          |
 179  c      | text    |           | not null |
 180 Indexes:
 181     "t1_pkey" PRIMARY KEY, btree (a, c)
 182 Publications:
 183     "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
 184     "p2"
 185
 186 /* pub # */ \d t2
 187                  Table "public.t2"
 188  Column |  Type   | Collation | Nullable | Default
 189 --------+---------+-----------+----------+---------
 190  d      | integer |           | not null |
 191  e      | integer |           |          |
 192  f      | integer |           |          |
 193 Indexes:
 194     "t2_pkey" PRIMARY KEY, btree (d)
 195 Publications:
 196     "p2" WHERE (e = 99)
 197     "p3" WHERE (d = 10)
 198
 199 /* pub # */ \d t3
 200                  Table "public.t3"
 201  Column |  Type   | Collation | Nullable | Default
 202 --------+---------+-----------+----------+---------
 203  g      | integer |           | not null |
 204  h      | integer |           |          |
 205  i      | integer |           |          |
 206 Indexes:
 207     "t3_pkey" PRIMARY KEY, btree (g)
 208 Publications:
 209     "p3" WHERE (g = 10)
 210
 211    On the subscriber node, create a table t1 with the same definition as
 212    the one on the publisher, and also create the subscription s1 that
 213    subscribes to the publication p1.
 214 /* sub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
 215 /* sub # */ CREATE SUBSCRIPTION s1
 216 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
 217 /* sub - */ PUBLICATION p1;
 218
 219    Insert some rows. Only the rows satisfying the t1 WHERE clause of
 220    publication p1 are replicated.
 221 /* pub # */ INSERT INTO t1 VALUES (2, 102, 'NSW');
 222 /* pub # */ INSERT INTO t1 VALUES (3, 103, 'QLD');
 223 /* pub # */ INSERT INTO t1 VALUES (4, 104, 'VIC');
 224 /* pub # */ INSERT INTO t1 VALUES (5, 105, 'ACT');
 225 /* pub # */ INSERT INTO t1 VALUES (6, 106, 'NSW');
 226 /* pub # */ INSERT INTO t1 VALUES (7, 107, 'NT');
 227 /* pub # */ INSERT INTO t1 VALUES (8, 108, 'QLD');
 228 /* pub # */ INSERT INTO t1 VALUES (9, 109, 'NSW');
 229
 230 /* pub # */ SELECT * FROM t1;
 231  a |  b  |  c
 232 ---+-----+-----
 233  2 | 102 | NSW
 234  3 | 103 | QLD
 235  4 | 104 | VIC
 236  5 | 105 | ACT
 237  6 | 106 | NSW
 238  7 | 107 | NT
 239  8 | 108 | QLD
 240  9 | 109 | NSW
 241 (8 rows)
 242
 243 /* sub # */ SELECT * FROM t1;
 244  a |  b  |  c
 245 ---+-----+-----
 246  6 | 106 | NSW
 247  9 | 109 | NSW
 248 (2 rows)
 249
 250    Update some data, where the old and new row values both satisfy the t1
 251    WHERE clause of publication p1. The UPDATE replicates the change as
 252    normal.
 253 /* pub # */ UPDATE t1 SET b = 999 WHERE a = 6;
 254
 255 /* pub # */ SELECT * FROM t1;
 256  a |  b  |  c
 257 ---+-----+-----
 258  2 | 102 | NSW
 259  3 | 103 | QLD
 260  4 | 104 | VIC
 261  5 | 105 | ACT
 262  7 | 107 | NT
 263  8 | 108 | QLD
 264  9 | 109 | NSW
 265  6 | 999 | NSW
 266 (8 rows)
 267
 268 /* sub # */ SELECT * FROM t1;
 269  a |  b  |  c
 270 ---+-----+-----
 271  9 | 109 | NSW
 272  6 | 999 | NSW
 273 (2 rows)
 274
 275    Update some data, where the old row values did not satisfy the t1 WHERE
 276    clause of publication p1, but the new row values do satisfy it. The
 277    UPDATE is transformed into an INSERT and the change is replicated. See
 278    the new row on the subscriber.
 279 /* pub # */ UPDATE t1 SET a = 555 WHERE a = 2;
 280
 281 /* pub # */ SELECT * FROM t1;
 282   a  |  b  |  c
 283 -----+-----+-----
 284    3 | 103 | QLD
 285    4 | 104 | VIC
 286    5 | 105 | ACT
 287    7 | 107 | NT
 288    8 | 108 | QLD
 289    9 | 109 | NSW
 290    6 | 999 | NSW
 291  555 | 102 | NSW
 292 (8 rows)
 293
 294 /* sub # */ SELECT * FROM t1;
 295   a  |  b  |  c
 296 -----+-----+-----
 297    9 | 109 | NSW
 298    6 | 999 | NSW
 299  555 | 102 | NSW
 300 (3 rows)
 301
 302    Update some data, where the old row values satisfied the t1 WHERE
 303    clause of publication p1, but the new row values do not satisfy it. The
 304    UPDATE is transformed into a DELETE and the change is replicated. See
 305    that the row is removed from the subscriber.
 306 /* pub # */ UPDATE t1 SET c = 'VIC' WHERE a = 9;
 307
 308 /* pub # */ SELECT * FROM t1;
 309   a  |  b  |  c
 310 -----+-----+-----
 311    3 | 103 | QLD
 312    4 | 104 | VIC
 313    5 | 105 | ACT
 314    7 | 107 | NT
 315    8 | 108 | QLD
 316    6 | 999 | NSW
 317  555 | 102 | NSW
 318    9 | 109 | VIC
 319 (8 rows)
 320
 321 /* sub # */ SELECT * FROM t1;
 322   a  |  b  |  c
 323 -----+-----+-----
 324    6 | 999 | NSW
 325  555 | 102 | NSW
 326 (2 rows)
 327
 328    The following examples show how the publication parameter
 329    publish_via_partition_root determines whether the row filter of the
 330    parent or child table will be used in the case of partitioned tables.
 331
 332    Create a partitioned table on the publisher.
 333 /* pub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
 334 /* pub # */ CREATE TABLE child PARTITION OF parent DEFAULT;
 335
 336    Create the same tables on the subscriber.
 337 /* sub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
 338 /* sub # */ CREATE TABLE child PARTITION OF parent DEFAULT;
 339
 340    Create a publication p4, and then subscribe to it. The publication
 341    parameter publish_via_partition_root is set as true. There are row
 342    filters defined on both the partitioned table (parent), and on the
 343    partition (child).
 344 /* pub # */ CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a
 345  >= 5)
 346 /* pub - */ WITH (publish_via_partition_root=true);
 347
 348 /* sub # */ CREATE SUBSCRIPTION s4
 349 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s4'
 350 /* sub - */ PUBLICATION p4;
 351
 352    Insert some values directly into the parent and child tables. They
 353    replicate using the row filter of parent (because
 354    publish_via_partition_root is true).
 355 /* pub # */ INSERT INTO parent VALUES (2), (4), (6);
 356 /* pub # */ INSERT INTO child VALUES (3), (5), (7);
 357
 358 /* pub # */ SELECT * FROM parent ORDER BY a;
 359  a
 360 ---
 361  2
 362  3
 363  4
 364  5
 365  6
 366  7
 367 (6 rows)
 368
 369 /* sub # */ SELECT * FROM parent ORDER BY a;
 370  a
 371 ---
 372  2
 373  3
 374  4
 375 (3 rows)
 376
 377    Repeat the same test, but with a different value for
 378    publish_via_partition_root. The publication parameter
 379    publish_via_partition_root is set as false. A row filter is defined on
 380    the partition (child).
 381 /* pub # */ DROP PUBLICATION p4;
 382 /* pub # */ CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
 383 /* pub - */ WITH (publish_via_partition_root=false);
 384
 385 /* sub # */ ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
 386
 387    Do the inserts on the publisher same as before. They replicate using
 388    the row filter of child (because publish_via_partition_root is false).
 389 /* pub # */ TRUNCATE parent;
 390 /* pub # */ INSERT INTO parent VALUES (2), (4), (6);
 391 /* pub # */ INSERT INTO child VALUES (3), (5), (7);
 392
 393 /* pub # */ SELECT * FROM parent ORDER BY a;
 394  a
 395 ---
 396  2
 397  3
 398  4
 399  5
 400  6
 401  7
 402 (6 rows)
 403
 404 /* sub # */ SELECT * FROM child ORDER BY a;
 405  a
 406 ---
 407  5
 408  6
 409  7
 410 (3 rows)