RE: Incorrect messages emitted from pgoutput when using column lists

On Friday, November 25, 2022 1:14 AM Gunnar Morling <gunnar(dot)morling(at)googlemail(dot)com> wrote:

Hi,

> I'd like to test the column list feature for logical replication added
> in Postgres 15 [1], and it seems I am getting unexpected events for
> UPDATEs. Here are steps for reproducing:
>
> CREATE TABLE test (
> id SERIAL NOT NULL PRIMARY KEY,
> a VARCHAR(255) NOT NULL,
> b VARCHAR(255) NOT NULL
> );
>
> CREATE PUBLICATION test_publication FOR TABLE test (id, a);
> CREATE UNIQUE INDEX test_publication_idx ON test (id, a);
> ALTER TABLE test REPLICA IDENTITY USING INDEX test_publication_idx;
>
> SELECT * FROM pg_create_logical_replication_slot('test_slot', 'pgoutput');
>
> INSERT INTO test values (default, 'aaa', 'bbb');
> UPDATE test set b='bbbbbb' where id = 1;
> UPDATE test set a='aaaaaa' where id = 1;
>
> Then consume the events using any client, for instance Debezium, or a
> bespoke consumer application. What I observed is this:
>
> - The 'R' event sent for the table looks as expected, i.e. without the
> "b" column
> - The event sent for the INSERT looks as expected
> - The event for the first UPDATE (column not part of the column list)
> looks as expected
> - The event for the second UPDATE (column is part of the column list)
> looks wrong; specifically, its TupleData part: it has a column count
> of 3 (rather than 2), with the 'n' byte identifying the value of the
> third column (b) as a null value
>
> I would expect that I always get events which reflect the structure of
> the previously sent `R` event. If that's not the case, I'm not sure
> how I would interpret the subsequent events. So I suppose this is a
> bug?
>
> Thanks for any advice and help,
>

Thanks for reporting.

I think the reason is that we didn't filter the column when sending the old
tuple in pgoutput. We thought that the old tuple won't include columns that not
in RI, but it seems it will still be null values for such columns in the old
tuple. So, I think we'd better filter the column for old tuple as well.

Attach a small patch which fixes this.

Best regards,
Hou zj