Re: Pgoutput not capturing the generated columns

On Fri, 1 Nov 2024 at 13:27, Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:
>
> Dear Vignesh,
>
> Thanks for rebasing the patch! Before reviewing deeply, I want to confirm the specification.
> I understood like below based on the documentation.
>
> - If publish_generated_columns is false, the publication won't replicate generated columns
> - If publish_generated_columns is true, the behavior on the subscriber depends on the table column:
>   - If it is a generated column even on the subscriber, it causes an ERROR.
>   - If it is a regular column, the generated value is replicated.
>   - If the column is missing, it causes an ERROR.

This is correct.

> However, below test implies that generated columns can be replicated even if
> publish_generated_columns is false. Not sure...
>
> ```
> # Verify that incremental replication of generated columns occurs
> # when they are included in the column list, regardless of the
> # publish_generated_columns option.
> $result =
>   $node_subscriber->safe_psql('postgres', "SELECT * FROM tab3 ORDER BY a");
> is( $result, qq(|2
> |4
> |6
> |8),
>         'tab3 incremental replication, when publish_generated_columns=false');
> ```

Yes, this is a special case where the column list will take priority
over the publish_generated_columns option. The same was discussed at
[1].

> Also, I've tested the case both pub and sub had the generated column, but the ERROR was strange for me.
>
> ```
> test_pub=# CREATE TABLE gencoltable (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
> test_pub=# CREATE PUBLICATION pub FOR TABLE gencoltable(a, b) WITH (publish_generated_columns = true);
> test_pub=# INSERT INTO gencoltable (a) VALUES (generate_series(1, 10));
>
> test_sub=# CREATE TABLE gencoltable (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
> test_sub=# CREATE SUBSCRIPTION sub CONNECTION ... PUBLICATION pub;
>
> -> ERROR: logical replication target relation "public.gencoltable" is missing replicated column: "b"
> ```
>
> The attribute existed on the sub but it was reported as missing column. I think
> we should somehow report like "generated column on publisher is replicated the
> generated column on the subscriber".

Agree on this, we will include a fix for this in one of the upcoming versions.

[1] - https://www.postgresql.org/message-id/CAA4eK1JgdyLYGo%2BG%3Db90VCqpbtwGMV8Su5Cuafo_hByWNTbkBg%40mail.gmail.com

Regards,
Vignesh