28.4. Фильтры строк #
По умолчанию подписчикам передаются все данные из всех опубликованных таблиц. Однако множество реплицируемых данных можно ограничить, используя фильтр строк. Потребность использовать фильтры строк может быть вызвана соображениями безопасности, производительности или требованиями к поведению. Если для опубликованной таблицы устанавливается фильтр строк, строка реплицируется, только если её данные удовлетворяют выражению этого фильтра. Это позволяет организовать частичную репликацию данных набора таблиц. Определяются фильтры строк на уровне отдельных таблиц. Для каждой публикуемой таблицы, данные которой требуется отфильтровать, нужно добавить предложение WHERE
после её имени, при этом выражение условия WHERE
нужно заключить в круглые скобки. За подробностями обратитесь к CREATE PUBLICATION.
28.4.1. Правила фильтров строк #
Фильтры строк применяются перед публикацией изменений. Если результатом выражения фильтра для строки является false
или NULL
, эта строка не реплицируется. Выражение условия WHERE
вычисляется от имени роли, которая используется для подключения репликации (т. е. роли, указанной в предложении CONNECTION
CREATE SUBSCRIPTION). На команду TRUNCATE
фильтры строк не распространяются.
28.4.2. Ограничения выражений #
В предложении WHERE
допускаются только простые выражения, в которых нельзя использовать определяемые пользователем функции, операторы, типы и параметры сортировки, а также ссылки на системные столбцы и непостоянные встроенные функции.
Если через публикацию реплицируются операции UPDATE
или DELETE
, предложение WHERE
фильтра строк должно содержать только те столбцы, которые входят в идентификатор реплики (см. REPLICA IDENTITY
). Если же через публикацию реплицируются только операции INSERT
, в фильтре строк WHERE
можно использовать любой столбец.
28.4.3. Преобразования UPDATE #
При выполнении каждой операции UPDATE
выражение фильтра строк вычисляется и для старой, и для новой строки (т. е. проверяются данные до и после изменения). Если оба результата положительные (true
), изменение UPDATE
реплицируется. Если оба результата отрицательные (false
), изменение не реплицируется. Если же выражению фильтра удовлетворяет только одна строка, старая или новая, операция UPDATE
преобразуется в INSERT
или DELETE
, чтобы не нарушилась согласованность данных. Строка на стороне подписки должна соответствовать выражению фильтра строк, определённого на публикующем сервере.
Если старая строка соответствует выражению фильтра строк (она была передана подписчику), а новая строка — нет, то для обеспечения согласованности данных старую строку нужно удалить у подписчика. Таким образом, UPDATE
преобразуется в DELETE
.
Если старая строка не соответствует выражению фильтра строк (она не была передана подписчику), а новая строка соответствует, то для обеспечения согласованности данных новую строку нужно добавить в таблицу подписчика. Таким образом, UPDATE
преобразуется в INSERT
.
Производимые преобразования показаны в Таблице 28.1.
Таблица 28.1. Краткая схема преобразования UPDATE
Старая строка | Новая строка | Преобразование |
---|---|---|
не соответствует | не соответствует | не реплицировать |
не соответствует | соответствует | INSERT |
соответствует | не соответствует | DELETE |
соответствует | соответствует | UPDATE |
28.4.4. Секционированные таблицы #
Если публикация содержит секционированную таблицу, параметр публикации publish_via_partition_root
определяет, какой фильтр строк использовать. Если publish_via_partition_root
имеет значение true
, используется фильтр строк корневой секционированной таблицы. В противном случае, если publish_via_partition_root
имеет значение false
(по умолчанию), используются фильтры строк каждой секции.
28.4.5. Начальная синхронизация данных #
Если подписка требует копирования уже существующих данных, а публикация содержит предложения WHERE
, подписчику копируются только данные, удовлетворяющие выражениям фильтров строк.
Если подписка связана с несколькими публикациями, в которых одна таблица опубликована с разными предложениями WHERE
, будут скопированы строки, удовлетворяющие любому из фильтров. За подробностями обратитесь к Подразделу 28.4.6.
Предупреждение
Так как при начальной синхронизации данных не учитывается параметр publish
, когда копируются существующие данные таблиц, подписчику могут быть переданы строки, которые не реплицировались бы при выполнении операций DML. Примеры приведены в Подразделе 28.8.1 и Подразделе 28.2.2.
Примечание
Если на стороне подписчика используется версия ниже 15, при копировании существующих данных фильтры строк не обрабатываются, даже если они определены в публикации. Это связано с тем, что старые версии могут копировать только все данные таблицы.
28.4.6. Объединение нескольких фильтров строк #
Если подписка связана с несколькими публикациями, в которых одна и та же таблица опубликована с разными фильтрами строк (для одной и той же операции publish
), эти выражения объединяются вместе, так что реплицироваться будут строки, удовлетворяющие любому из выражений. Это означает, что все остальные фильтры строк для той же таблицы становятся избыточными, если:
У одной из публикаций нет фильтра строк.
Одна из публикаций была создана с предложением
FOR ALL TABLES
. Это предложение не позволяет использовать фильтры строк.Одна из публикаций была создана с предложением
FOR TABLES IN SCHEMA
и целевая таблица принадлежит указанной схеме. Это предложение не позволяет использовать фильтры строк.
28.4.7. Примеры #
Создайте несколько таблиц, которые будут использоваться в следующих примерах.
test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); CREATE TABLE test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d)); CREATE TABLE test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g)); CREATE TABLE
Создайте несколько публикаций. Публикация p1
содержит одну таблицу (t1
), и для неё определяется фильтр строк. Публикация p2
содержит две таблицы, и фильтр строк определён для t2
, но отсутствует для t1
. Публикация p3
содержит две таблицы, и фильтр строк определён для обеих.
test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW'); CREATE PUBLICATION test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99); CREATE PUBLICATION test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10); CREATE PUBLICATION
Выражения фильтров строк для каждой публикации можно просмотреть в psql
.
test_pub=# \dRp+ Publication p1 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: "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 | f Tables: "public.t1" "public.t2" WHERE (e = 99) Publication p3 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: "public.t2" WHERE (d = 10) "public.t3" WHERE (g = 10)
Также в psql
можно просмотреть выражения фильтров строк для каждой таблицы. Обратите внимание: таблица t1
является членом двух публикаций, но имеет фильтр строк только в p1
, а таблица t2
является членом двух публикаций и имеет в них разные фильтры строк.
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)
Создайте на сервере подписчика таблицу t1
с тем же определением, что и на публикующем сервере, а также создайте подписку s1
, связанную с публикацией p1
.
test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); CREATE TABLE test_sub=# CREATE SUBSCRIPTION s1 test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1' test_sub-# PUBLICATION p1; CREATE SUBSCRIPTION
Вставьте несколько строк. Реплицироваться будут только строки, соответствующие предложению t1 WHERE
публикации p1
.
test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW'); INSERT 0 1 test_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)
Измените некоторые данные, в которых и старое, и новое значения строки соответствуют условию t1 WHERE
публикации p1
. Операция UPDATE
будет реплицировать изменения как обычно.
test_pub=# UPDATE t1 SET b = 999 WHERE a = 6; UPDATE 1 test_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)
Измените некоторые данные, в которых старые значения строк не соответствуют условию t1 WHERE
публикации p1
, а новые значения строк — соответствуют. Операция UPDATE
будет преобразована в INSERT
и реплицируется в таком виде. Посмотрите на новую строку в таблице подписчика.
test_pub=# UPDATE t1 SET a = 555 WHERE a = 2; UPDATE 1 test_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)
Измените некоторые данные, в которых старые значения строк соответствуют условию t1 WHERE
публикации p1
, а новые значения — не соответствуют. Операция UPDATE
будет преобразована в DELETE
и реплицируется в таком виде. Обратите внимание, строка была удалена из таблицы подписчика.
test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9; UPDATE 1 test_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)
Следующие примеры показывают, как в зависимости от параметра публикации publish_via_partition_root
для секционированных таблиц используется фильтр строк родительской или дочерней таблицы.
Создайте секционированную таблицу на публикующем сервере.
test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); CREATE TABLE test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT; CREATE TABLE
Создайте такую же конфигурацию таблиц на подписчике.
test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a); CREATE TABLE test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT; CREATE TABLE
Создайте публикацию p4
, а затем подпишитесь на неё. Параметр публикации publish_via_partition_root
имеет значение true. При этом определяются фильтры строк для секционированной таблицы (parent
) и для её секции (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 s4 test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4' test_sub-# PUBLICATION p4; CREATE SUBSCRIPTION
Вставьте некоторые значения непосредственно в таблицы parent
и child
. При их репликации будет использоваться фильтр строк parent
(так как publish_via_partition_root
имеет значение true).
test_pub=# INSERT INTO parent VALUES (2), (4), (6); INSERT 0 3 test_pub=# INSERT INTO child VALUES (3), (5), (7); INSERT 0 3 test_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)
Повторите ту же проверку с другим значением publish_via_partition_root
. Теперь параметр публикации publish_via_partition_root
будет иметь значение false, а фильтр строк будет определён в секции (child
).
test_pub=# DROP PUBLICATION p4; DROP PUBLICATION test_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
Выполните на публикующем сервере те же операции INSERT. Они реплицируются с использованием фильтра строк в таблице child
(так как publish_via_partition_root
имеет значение false).
test_pub=# TRUNCATE parent; TRUNCATE TABLE test_pub=# INSERT INTO parent VALUES (2), (4), (6); INSERT 0 3 test_pub=# INSERT INTO child VALUES (3), (5), (7); INSERT 0 3 test_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)