Re: pgoutput incorrectly replaces missing values with NULL since PostgreSQL 15 - Mailing list pgsql-hackers

From Amit Kapila
Subject Re: pgoutput incorrectly replaces missing values with NULL since PostgreSQL 15
Date
Msg-id CAA4eK1JgXuigg41vS8jaQ=9k0t4-K89r6K=Mt3DetDnuVhns4w@mail.gmail.com
Whole thread Raw
In response to Re: pgoutput incorrectly replaces missing values with NULL since PostgreSQL 15  (Amit Kapila <amit.kapila16@gmail.com>)
Responses RE: pgoutput incorrectly replaces missing values with NULL since PostgreSQL 15
List pgsql-hackers
On Thu, Nov 23, 2023 at 2:33 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Thu, Nov 23, 2023 at 1:10 PM Nikhil Benesch <nikhil.benesch@gmail.com> wrote:
> >
> > While working on Materialize's streaming logical replication from Postgres [0],
> > my colleagues Sean Loiselle and Petros Angelatos (CC'd) discovered today what
> > appears to be a correctness bug in pgoutput, introduced in v15.
> >
> > The problem goes like this. A table with REPLICA IDENTITY FULL and some
> > data in it...
> >
> >     CREATE TABLE t (a int);
> >     ALTER TABLE t REPLICA IDENTITY FULL;
> >     INSERT INTO t VALUES (1), (2), (3), ...;
> >
> > ...undergoes a schema change to add a new column with a default:
> >
> >     ALTER TABLE t ADD COLUMN b bool DEFAULT false NOT NULL;
> >
> > PostgreSQL is smart and does not rewrite the entire table during the schema
> > change. Instead it updates the tuple description to indicate to future readers
> > of the table that if `b` is missing, it should be filled in with the default
> > value, `false`.
> >
> > Unfortunately, since v15, pgoutput mishandles missing attributes. If a
> > downstream server is subscribed to changes from t via the pgoutput plugin, when
> > a row with a missing attribute is updated, e.g.:
> >
> >     UPDATE t SET a = 2 WHERE a = 1
> >
> > pgoutput will incorrectly report b's value as NULL in the old tuple, rather than
> > false.
> >
>
> Thanks, I could reproduce this behavior. I'll look into your patch.
>

I verified your fix is good and made minor modifications in the
comment. Note, that the test doesn't work for PG15, needs minor
modifications.

--
With Regards,
Amit Kapila.

Attachment

pgsql-hackers by date:

Previous
From: Nikita Malakhov
Date:
Subject: Re: Patch: Global Unique Index
Next
From: Michael Banck
Date:
Subject: Re: [HACKERS] pg_upgrade vs vacuum_cost_delay