Documentation PostgreSQL 15.15 » Administration du serveur » Réplication logique » Filtres de lignes

Précédent | Niveau supérieur | Suivant
31.2. Abonnement | Sommaire | 31.4. Listes de colonnes

## 31.3. Filtres de lignes

Par défaut, toutes les données de toutes les tables publiées seront répliquées aux abonnés appropriés. Les données répliquées peuvent être réduites en utilisant un filtre de lignes. Un utilisateur pourrait choisir d'utiliser des filtres de lignes pour des raisons de comportement, de sécurité ou de performance. Si une table publiée configure un filtre de lignes, une ligne est répliquée seulement si ses données satisfont l'expression du filtre de lignes. Cela permet à un ensemble de tables d'être répliquées partiellement. Le filtre de lignes est défini par table. Utilisez une clause `WHERE` après le nom de la table pour chaque table publiée dont les données doivent être filtrées. La clause `WHERE` doit être entourée de parenthèses. Voir CREATE PUBLICATION pour les détails.

### 31.3.1. Règles des filtres de lignes

Les filtres de lignes sont appliqués avant de publier les changements. Si le filtre de ligne est évalué à `false` ou `NULL`, alors la ligne n'est pas répliquée. L'expression de la clause `WHERE` est évaluée avec le même rôle utilisé pour la connexion de réplication (soit le rôle indiqué dans la clause `CONNECTION` de l'instruction CREATE SUBSCRIPTION). Les filtres de ligne n'ont pas deffet sur la commande `TRUNCATE`.

### 31.3.2. Restrictions de l'expression

La clause `WHERE` autorise uniquement des expressions simples. Elle ne peut pas contenir de fonctions, opérateurs, types et collations définis par les utilisateurs, des références aux colonnes système ou à des fonctions internes non immutables.

Si une publication publie des opérations `UPDATE` ou `DELETE`, la clause `WHERE` du filtre de lignes doit contenir uniquement les colonnes couvertes par l'identité de réplica (voir `REPLICA IDENTITY`). Si une publication publie uniquement des opérations `INSERT`, la clause `WHERE` du filtre de ligne peut utiliser toute colonne.

### 31.3.3. Transformations d'un UPDATE

À chaque fois qu'un `UPDATE` est traité, l'expression du filtre de lignes est évaluée pour l'ancienne et la nouvelle ligne (autrement dit, en utilisant les données avant et après la mise à jour). Si les deux évaluations valent `true`, les modifications de l'`UPDATE` sont répliquées. Si les deux évaluations valent `false`, les modifications ne sont pas répliquées. Si seulement une des deux évaluations vaut true, la commande `UPDATE` est transformée en `INSERT` ou `DELETE`, pour éviter toute incohérence de données. La ligne sur l'abonné doit refléter ce qui est défini par l'expression de filtre de lignes sur le publieur.

Si l'ancienne ligne satisfait l'expression du filtre de lignes (elle a été envoyée sur l'abonné) contrairement à la nouvelle ligne, alors, d'un point de vue de la cohérence des données, l'ancienne ligne devrait être supprimée de l'abonné. Donc la commande `UPDATE` est transformée en commande `DELETE`.

Si l'ancienne ligne ne satisfait pas l'expression du filtre de lignes (elle n'a pas été envoyée sur l'abonné) contrairement à la nouvelle ligne, alors, d'un point de vue de la cohérence des données, la nouvelle ligne doit être ajoutée à l'abonné. Donc la commande `UPDATE` est transformée en commande `INSERT`.

Tableau 31.1 résume les transformations appliquées.

Tableau 31.1. Résumé des transformations d'un `UPDATE`

Ancienne ligne | Nouvelle ligne | Transformation
--- | --- | ---
pas de correspondance | pas de correspondance | ne réplique pas
pas de correspondance | correspondance | `INSERT`
correspondance | pas de correspondance | `DELETE`
correspondance | correspondance | `UPDATE`

### 31.3.4. Tables partitionnées

Si la publication contient une table partitionnée, le paramètre de publication `publish_via_partition_root` détermine le filtre de lignes à utiliser. Si `publish_via_partition_root` vaut `true`, le filtre de ligne de la table partitionnée racine est utilisé. Si `publish_via_partition_root` vaut `false` (ce qui est le cas par défaut), le filtre de lignes de chaque partition est utilisé.

### 31.3.5. Synchronisation initiale des données

Si la souscription requiert une copie des données pré-existantes de la table et qu'une publication contient des clauses `WHERE`, seules les données qui satisfont les expressions des filtres de ligne sont copiées sur l'abonné.

Si la souscription a plusieurs publications dans lesquelles une table a été publiée avec différentes clauses `WHERE`, les lignes qui satisfont une des expressions seront copiées. Voir Section 31.3.6 pour les détails.

### Avertissement

Comme la synchronisation initiale des données ne prend pas en compte le paramètre `publish` lors de la copie des données pré-existantes de la table, certaines lignes pourraient être copiées alors qu'elles n'auraient pas été répliquées avec des instructions DML. Référez-vous à Section 31.7.1, et à Section 31.2.2 pour des exemples.

### Note

Si l'abonné est d'une version antérieure à la 15, la copie des données pré-existantes n'utilise pas les filtres de ligne même s'ils sont définis dans la publication. Ceci est dû au fait que les anciennes versions peuvent seulement copier les données de la table entière.

### 31.3.6. Combiner plusieurs filtres de lignes

Si la souscription a plusieurs publications pour lesquelles la même table a été publiée avec des filtres de lignes différents (pour la même opération `publish`, ces expressions sont assemblées avec un OU logique, pour que les lignes satisfaisant au moins une expression seront répliquées. Cela signifie que les autres filtres de lignes pour la même table deviennent redondants si :

- Une des publications n'a pas de filtres de lignes ;

- Une des publications a été créée en utilisant `FOR ALL TABLES`. Cette clause n'autorise pas les filtres de lignes ;

- Une des publications a été créée en utilisant `FOR ALL TABLES IN SCHEMA` et la table appartient au schéma référencé. Cette clause n'autorise pas les filtres de lignes.

### 31.3.7. Exemples

Créer quelques tables à utiliser dans les exemples suivants.

    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

Créer quelques publications. La publication `p1` a une table (`t1`) et cette table a un filtre de lignes. La publication `p2` a deux tables. La table `t1` n'a pas de filtre de lignes, et la table `t2` a un filtre de lignes. La publication `p3` a deux tables, qui ont toutes les deux un filtre de lignes.

    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` peut être utilisé pour afficher les expressions de filtres de lignes (si défini) pour chaque publication.

    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` peut être utilisé pour afficher les expressions des filtres de lignes (si défini) pour chaque table. La table `t1` est membre de deux publications, mais a un filtre de lignes uniquement dans `p1`. La table `t2` est membre de deux publications, et a un filtre de lignes différent sur chacune.

    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)

Sur le nœud abonné, créer une table `t1` avec la même définition que celle du publieur, et créer aussi la souscription `s1` vers la publication `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

Insérer quelques lignes. Seules les lignes satisfaisant la clause `t1 WHERE` de la publication `p1` sont répliquées.

    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)

Mettre à jour quelques données, où les valeurs de l'ancienne et de la nouvelle ligne satisfont la clause `t1 WHERE` de la publication `p1`. La commande `UPDATE` réplique les changements de façon normale.

    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)

Mettre à jour des données de façon à ce que les valeurs de l'ancienne ligne ne satisfont pas la clause `t1 WHERE` de la publication `p1`, mais que les valeurs de la nouvelle ligne la satisfont. La commande `UPDATE` est transformée en une commande `INSERT` et les changements sont répliqués. Voir la nouvelle ligne sur l'abonné.

    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)

Mettre à jour des données de façon à ce que les valeurs de l'ancienne ligne satisfont la clause `t1 WHERE` de la publication `p1`, mais que les valeurs de la nouvelle ligne ne la satisfont pas. La commande `UPDATE` est transformée en une commande `DELETE` et les changements sont répliqués. Voir la nouvelle ligne supprimée sur l'abonné.

    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)

Les exemples suivants montrent comment le paramètre de publication `publish_via_partition_root` détermine si le filtre de ligne de la table parent ou enfant sera utilisé dans le cas de tables partitionnées.

Créer une table partitionnée sur le publieur.

    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

Créer les mêmes tables sur l'abonné.

    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

Créer une publication `p4`, puis abonnez-vous à cette publication. Le paramètre de publication `publish_via_partition_root` est configuré à true. Il existe des filtres de lignes définis sur la table partitionnée (`parent`), et sur sa 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 s4
    test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
    test_sub-# PUBLICATION p4;
    CREATE SUBSCRIPTION

Insérer quelques valeurs directement dans les tables `parent` et `child`. Elles se répliquent en utilisant le filtre de lignes de `parent` (parce que `publish_via_partition_root` vaut 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)

Répéter le même test, mais avec une valeur différent pour `publish_via_partition_root`. Le paramètre de publication `publish_via_partition_root` est configuré à false. Un filtre de lignes est défini sur la partition (`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

Faire les insertions sur le publieur de la même façon qu'avant. Elles se répliquent en utilisant le filtre de lignes de `child` (parce que `publish_via_partition_root` vaut 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)