30.3. Фильтры строк#
30.3. Фильтры строк
По умолчанию все данные из всех опубликованных таблиц будут тиражированы на соответствующих подписчиках. Реплицированные данные можно сократить с помощью фильтра строк. Пользователь может выбрать использование фильтров строк по причинам поведения, безопасности или производительности. Если для опубликованной таблицы установлен фильтр строк, строка реплицируется только в том случае, если ее данные удовлетворяют выражению фильтра строк. Это позволяет частично тиражировать набор таблиц. Фильтр строк определяется для каждой таблицы. Используйте предложение WHERE после имени таблицы для каждой опубликованной таблицы, для которой требуется фильтрация данных. Предложение WHERE должно быть заключено в скобки. См. CREATE PUBLICATION для получения дополнительной информации.
30.3.1. Правила фильтрации строк
Все фильтры строк применяются перед публикацией изменений.
Если фильтр строки вычисляется как false или NULL,
то строка не реплицируется. Выражение предложения WHERE оценивается с учетом той же роли, которая используется для соединения репликации (т.е.
роль, указанная в предложении CONNECTION CREATE SUBSCRIPTION). Фильтры строк не влияют на
команду TRUNCATE.
30.3.2. Ограничения выражений
Предложение WHERE позволяет использовать только простые выражения. Она не может содержать пользовательских функций, операторов, типов и правил сортировки, ссылок на системные столбцы или не-постоянных встроенных функций.
Если публикация выполняет операции UPDATE или
DELETE, то предикат WHERE в
фильтре строк должен содержать только столбцы, которые покрываются
идентификатором реплики (см. REPLICA IDENTITY).
Если публикация выполняет только операции INSERT,
то предикат WHERE в фильтре строк может использовать
любой столбец.
30.3.3. ОБНОВИТЬ Преобразования
Когда выполняется команда UPDATE, выражение фильтра строки оценивается как для старой, так и для новой строки (т.е. используя данные до и после обновления). Если оба вычисления равны true, то изменение UPDATE реплицируется. Если оба вычисления равны false, то изменение не реплицируется. Если только одна из старой/новой строк соответствует выражению фильтра строки, то команда UPDATE преобразуется в INSERT или DELETE, чтобы избежать несогласованности данных. Строка на подписчике должна отражать то, что определено выражением фильтра строки на издателе.
Если старая строка удовлетворяет выражению фильтра строки (она была отправлена
подписчику), но новая строка нет, то с точки зрения согласованности данных
старая строка должна быть удалена у подписчика.
Таким образом, команда UPDATE преобразуется в DELETE.
Если старая строка не удовлетворяет выражению фильтра строки (она не была отправлена
подписчику), но новая строка удовлетворяет, то с точки зрения согласованности данных
новая строка должна быть добавлена подписчику.
Таким образом, команда UPDATE преобразуется в INSERT.
Таблица 30.1 подводит итоги примененных преобразований.
Таблица 30.1. UPDATE Сводка преобразования
| Старая строка | Новая строка | Преобразование |
|---|---|---|
| нет совпадений | нет совпадений | don't replicate |
| нет совпадений | есть совпадение | INSERT |
| есть совпадение | нет совпадения | DELETE |
| match | match | UPDATE |
30.3.4. Таблицы с разделением
Если публикация содержит секционированную таблицу, параметр публикации publish_via_partition_root определяет, какой фильтр строк используется. Если publish_via_partition_root равно true, используется фильтр строк корневой секционированной таблицы. В противном случае, если publish_via_partition_root равно false (по умолчанию), используется фильтр строк каждой секции.
30.3.5. Синхронизация начальных данных
Если подписка требует копирования существующих данных таблицы и публикация содержит предложения WHERE, то только данные, удовлетворяющие выражениям фильтрации строк, копируются на подписчика.
Если у подписки есть несколько публикаций, в которых таблица была опубликована с разными предложениями WHERE, будут скопированы строки, удовлетворяющие любому из выражений. Подробности см. в разделе Раздел 30.3.6.
Предупреждение
Поскольку при синхронизации исходных данных не учитывается параметр publish при копировании существующих данных таблицы, некоторые строки могут быть скопированы, но не будут тиражироваться с использованием DML. См. раздел Раздел 30.7.1 и примеры в разделе Раздел 30.2.2.
Примечание
Если абонент находится в версии ранее 15, копирование существующих данных не использует фильтры строк, даже если они определены в публикации. Это происходит потому, что старые версии могут копировать только все данные таблицы.
30.3.6. Комбинирование нескольких фильтров строк
Если подписка имеет несколько публикаций, в которых одна и та же таблица была опубликована с разными фильтрами строк (для одной и той же операции publish), эти выражения объединяются с помощью операции OR, так что строки, удовлетворяющие любому из выражений, будут тиражированы. Это означает, что все остальные фильтры строк для этой же таблицы становятся излишними, если:
Одна из публикаций не имеет фильтра строк.
Одна из публикаций был создан с использованием
FOR ALL TABLES. Это предложение не позволяет использовать фильтры строк.Одна из публикаций была создана с использованием
FOR TABLES IN SCHEMA, и таблица принадлежит к указанной схеме. Это предложение не позволяет использовать фильтры строк.
30.3.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 имеет две таблицы. Таблица t1 не имеет фильтра строк,
а таблица t2 имеет фильтр строк. Публикация
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
Выполните вставки в таблицу publisher так же, как и раньше. Они будут тиражироваться с использованием фильтра строк 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)