* Les colonnes `GENERATED ALWAYS AS () STORED` sont publiées si :
+ les colonnes sont dans la liste des colonnes spécifiées
+ ou : `publish_generated_columns` = `stored` (defaut : `none`)
* Les colonnes générées présentes dans l'identité de réplication doivent être
répliquées.
* Colonne virtuelles (v18) : jamais exportées
La réplication physique permet désormais de répliquer les modifications
effectuées sur des colonnes générées stockées (pas virtuellles).
Ce genre de colonne ne pose
habituellement pas de problème pour des réplications entre serveurs PostgreSQL
puisque le DDL déployé sur la souscription permet d'y regénérer les valeurs
(à condition que ce DDL soit correctement synchronisé bien sûr).
Pour des outils de _Change Data Capture_, c'est plus gênant.
La cible ne sait à priori pas regénérer les données calculées.
Note : dans les exemples qui suivent, nous partons du principe que la
configuration préalable de l'instance est effectuée (`wal_level`, `pg_hba.conf`
et `.pg_pass` principalement).
**Spécification d'une liste de colonne** :
Voici le cas d'une table avec des colonnes générées :
```sql
CREATE TABLE postgresql (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
major_version TEXT UNIQUE,
supported BOOLEAN,
first_release DATE,
final_release DATE GENERATED ALWAYS AS (first_release - INTERVAL '5 years' ) STORED,
designation TEXT GENERATED ALWAYS AS ('PostgreSQL ' || major_version ) STORED
);
INSERT INTO postgresql (major_version, supported, first_release)
VALUES
('17', true, '2024-09-26'::DATE),
('16', true, '2023-09-14'::DATE),
('15', true, '2022-10-13'::DATE),
('14', true, '2021-09-30'::DATE),
('13', true, '2020-09-24'::DATE),
('12', true, '2020-10-03'::DATE);
```
Nous créons une publication en prenant soin d'inclure les colonnes générées :
```sql
CREATE PUBLICATION pgversions
FOR TABLE postgresql (id, designation, first_release);
```
Dans la description de la publication, nous pouvons noter la colonne `Generated
columns` qui est à `none`, et sur laquelle nous reviendrons plus tard :
\tiny
```
\dRp+
Publication pgversions
Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
--------+------------+---------+---------+---------+-----------+-------------------+----------
benoit | f | t | t | t | t | none | f
Tables:
"public.postgresql" (id, first_release, designation)
```
\normalsize
Nous allons d'abord utiliser le plugin de
décodage logique `test_decoding`.
Il a besoin que l'on crée à la main le slot de réplication logique
avec cette commande :
```sql
SELECT *
FROM pg_create_logical_replication_slot('decode_generated_cols', 'test_decoding', false, true);
```
Mettons à jour les données :
```sql
INSERT INTO postgresql (major_version, supported, first_release)
VALUES ('18', true, '2025-09-25'::DATE);
UPDATE postgresql
SET supported = false
WHERE major_version = '12';
TABLE postgresql;
```
```text
id | major_version | supported | first_release | final_release | designation
----+---------------+-----------+---------------+---------------+---------------
1 | 17 | t | 2024-09-26 | 2019-09-26 | PostgreSQL 17
2 | 16 | t | 2023-09-14 | 2018-09-14 | PostgreSQL 16
3 | 15 | t | 2022-10-13 | 2017-10-13 | PostgreSQL 15
4 | 14 | t | 2021-09-30 | 2016-09-30 | PostgreSQL 14
5 | 13 | t | 2020-09-24 | 2015-09-24 | PostgreSQL 13
7 | 18 | t | 2025-09-25 | 2020-09-25 | PostgreSQL 18
6 | 12 | f | 2020-10-03 | 2015-10-03 | PostgreSQL 12
(7 rows)
```
Les modifications sont immédiatement visibles via le plugin, y compris les colonnes générées,
ce que nous voulions :
```sql
SELECT encode(data, 'escape') as conv_data
FROM pg_logical_slot_peek_binary_changes('decode_generated_cols', null, null);
```
```text
-- Note : la sortie mise en forme
conv_data
------------------------------------------------------------------------------
BEGIN 49770
table public.postgresql: INSERT: id[integer]:7
major_version[text]:'18'
supported[boolean]:true
first_release[date]:'2025-09-25'
final_release[date]:'2020-09-25'
designation[text]:'PostgreSQL 18'
COMMIT 49770
BEGIN 49771
table public.postgresql: UPDATE: id[integer]:6
major_version[text]:'12'
supported[boolean]:false
first_release[date]:'2020-10-03'
final_release[date]:'2015-10-03'
designation[text]:'PostgreSQL 12'
COMMIT 49771
(6 rows)
```
Comme second exemple illustrant la synchronisation initiale,
créons une souscription depuis un autre serveur
en ne redéfinissant qu'une des colonnes générées :
```sql
-- Serveur 1
CREATE ROLE repli WITH LOGIN REPLICATION;
\password repli
GRANT SELECT ON postgresql TO repli;
-- Serveur 2
CREATE TABLE postgresql (
id SERIAL PRIMARY KEY,
designation TEXT,
first_release DATE,
final_release DATE GENERATED ALWAYS AS (first_release - INTERVAL '5 years' ) STORED
);
CREATE SUBSCRIPTION pgversions
CONNECTION 'host=/var/run/postgresql port=5435 user=repli dbname=benoit application_name=serveur2'
PUBLICATION pgversions;
-- Attendre un peu
TABLE postgresql;
```
On constate que `designation` a bien été alimenté depuis la publication alors
que `final_release` a été recalculé.
```text
id | designation | first_release | final_release
----+---------------+---------------+---------------
1 | PostgreSQL 17 | 2024-09-26 | 2019-09-26
2 | PostgreSQL 16 | 2023-09-14 | 2018-09-14
3 | PostgreSQL 15 | 2022-10-13 | 2017-10-13
4 | PostgreSQL 14 | 2021-09-30 | 2016-09-30
5 | PostgreSQL 13 | 2020-09-24 | 2015-09-24
7 | PostgreSQL 18 | 2025-09-25 | 2020-09-25
6 | PostgreSQL 12 | 2020-10-03 | 2015-10-03
(7 rows)
```
**Nouveau paramètre : `publish_generated_columns`** :
Le paramétrage des publications a également été enrichi d'un nouveau
paramètre : `publish_generated_columns`. S'il est configuré à `stored`, les
colonnes générées stockées sont publiées par défaut.
`none`, la valeur par défaut, désactive leur
publication.
La liste des colonnes à publier a la priorité sur ce nouveau paramètre, si elle
est présente, comme dans les premiers exemples plus haut.
Créons une nouvelle table avec une colonne générée, et modifions la publication
pour y ajouter cette table sans spécifier de colonne et configurer
`publish_generated_columns` à `stored`.
```sql
-- Serveur 1
CREATE TABLE doc (
id SERIAL PRIMARY KEY,
major_version TEXT UNIQUE,
link TEXT GENERATED ALWAYS AS ('https://www.postgresql.org/docs/' || major_version ) STORED
);
INSERT INTO doc(major_version) SELECT generate_series(12, 18)::text;
GRANT SELECT ON doc TO repli;
ALTER PUBLICATION pgversions ADD TABLE doc;
ALTER PUBLICATION pgversions SET (publish_generated_columns = stored);
```
La méta-commande `\dRp+` permet de visualiser la changement de configuration :
\tiny
```
Publication pgversions
Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
--------+------------+---------+---------+---------+-----------+-------------------+----------
benoit | f | t | t | t | t | stored | f
Tables:
"public.doc"
"public.postgresql" (id, first_release, designation)
```
\normalsize
La table doit aussi être créée sur l'instance abonnée et la publication
rafraîchie sur la souscription.
```sql
-- Serveur 2
CREATE TABLE doc (
id SERIAL PRIMARY KEY,
major_version TEXT,
link TEXT
);
ALTER SUBSCRIPTION pgversions REFRESH PUBLICATION;
-- Attendre un peu
TABLE doc;
```
On constate, comme prévu, que les valeurs des colonnes générées ont été
répliquées :
```text
id | major_version | link
----+---------------+------------------------------------
1 | 12 | https://www.postgresql.org/docs/12
2 | 13 | https://www.postgresql.org/docs/13
3 | 14 | https://www.postgresql.org/docs/14
4 | 15 | https://www.postgresql.org/docs/15
5 | 16 | https://www.postgresql.org/docs/16
6 | 17 | https://www.postgresql.org/docs/17
7 | 18 | https://www.postgresql.org/docs/18
(7 rows)
```
**Cas particulier** :
Si un `replica identity` est défini sur une table et qu'il inclut une colonne
générée stockée, il faut que cette colonne soit répliquée. Dans le cas contraire
les `UPDATE` et `DELETE` échoueront.
(Rappelons que dans l'idéal, la réplication se fait sur une clé primaire ou
d'unicité.)
Voici un exemple (très artificiel) pour lequel nous allons créer une nouvelle
table :
```sql
-- Serveur 1
CREATE TABLE test(
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
id2 int GENERATED ALWAYS AS ( id + 1) STORED UNIQUE NOT NULL,
data text
);
ALTER TABLE test REPLICA IDENTITY USING INDEX test_id2_key;
INSERT INTO test (data) VALUES ('some data');
```
Utiliser `REPLICA IDENTITY FULL` aurait le même effet pour cette démonstration
puisqu'il prend l'ensemble de la ligne comme identité de réplication.
Modifions la publication en retirant les anciennes tables, en ajoutant la nouvelle
et en désactivant `publish_generated_columns` :
```sql
ALTER PUBLICATION pgversions DROP TABLE postgresql, doc;
ALTER PUBLICATION pgversions ADD TABLE test;
ALTER PUBLICATION pgversions SET (publish_generated_columns = none);
```
Créons ensuite la table sur l'autre serveur et rafraîchissons la
souscription :
```sql
-- Serveur 2
CREATE TABLE test(
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
id2 int GENERATED ALWAYS AS ( id + 1) STORED UNIQUE NOT NULL,
data text
);
ALTER SUBSCRIPTION pgversions REFRESH PUBLICATION;
```
Réaliser un `UPDATE` ou un `DELETE` est alors impossible car l'identité de
réplication ne fait pas partie des données publiées :
```sql
-- Serveur 1
UPDATE test SET data = 'other data' WHERE id = 1;
```
```text
ERROR: cannot update table "test"
DETAIL: Replica identity must not contain unpublished generated columns.
```
NB : Tout ce qui précède ne concerne pas les colonnes générées _virtuelles_,
apparues aussi dans PostgreSQL 18, qui sont complètement ignorées par
la réplication.