MIME-Version: 1.0
From: Koen De Groote <kdg.dev@gmail.com>
Date: Wed, 16 Oct 2024 23:58:46 +0200
Message-ID: <CAGbX52EDfA5DbD5qTsyHxGnZy_+cKup0=us7kskx932pwZ=xkQ@mail.gmail.com>
Subject: Logical replication disabled, recovery not possible because of 1
 large transaction with schema changes?
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000044b28706249f2dd2"
Archived-At: <https://www.postgresql.org/message-id/CAGbX52EDfA5DbD5qTsyHxGnZy_%2BcKup0%3Dus7kskx932pwZ%3DxkQ%40mail.gmail.com>
Precedence: bulk

--00000000000044b28706249f2dd2
Content-Type: text/plain; charset="UTF-8"

If this question is more suitable for another mailing list, please let me
know.

I've set up the following table on both publisher and subscriber, both are
pg16:

CREATE TABLE dummy_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Added to publication, refreshed subscription.

Add some data on the publisher side:
INSERT INTO dummy_table (name, email, age)
VALUES
('John Doe', 'john.doe@example.com', 25),
('Jane Smith', 'jane.smith@example.com', 30),
('Michael Johnson', 'michael.j@example.com', 45),
('Emily Davis', 'emily.d@example.com', 27),
('Robert Brown', 'robert.brown@example.com', 40);

The data can be seen on the subscriber. So far, so good.

I then execute the following patch on the publisher:
https://gist.github.com/KoenDG/d1c06d8c740c64e4e5884d0c64b81f11

It is a single transaction that does the following:

1/ Insert data, 1000 items
2/ Drop a column
3/ Alter a column name
4/ Add 2 columns, nullable
5/ Add a column and give it a unique constraint
6/ Update values for a column with NULL values, added in step 4.
7/ Set the column updated in step 6 to be NOT NULL
8/ Create a unique index with the columns from step 3 and 6
9/ Insert a column with a default value
10/ Insert data for this schema, another 1000 items.

The subscription disabled, this is to be expected, there are new columns
names, the schema needs to be updated on the subscriber side.

However, it seems I'm stuck.

I can't enable the subscription. This is to be expected, it will try to
resume and run into the same issues.

Ok, I update the schema and enable again. It runs into an error for the
inserts of step 1. These set values for columns dropped in step 2.

I revert to the old schema and enable again. It runs into an error again,
this time for values that don't exist yet at step 1.

I tried dropping the table at the subscriber side, recreating the correct
schema, but this runs into the same error.

I remove the table from the publication and retry. Same error. Even with
the table no longer in the publication, and the table on the subscriber
side dropped and re-created, I'm still getting the exact same errors of
"logical replication target relation "public.dummy_table" is missing
replicated columns: "contact_email", "status", "phone_number", "username""


The only solution I've found is to drop the table from the publication, and
then drop the entire subscription and set it back up again, with the
correct schema.

Am I making a mistake? Or does putting all these commands in a single
transaction ruin my chances?

Clarification much appreciated.

Regards,
Koen De Groote

--00000000000044b28706249f2dd2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>If this question is more suitable for another mailing=
 list, please let me know.</div><div><br></div><div>I&#39;ve set up the fol=
lowing table on both publisher and subscriber, both are pg16:</div><div><br=
></div><div>CREATE TABLE dummy_table (<br>	id SERIAL PRIMARY KEY,<br>	name =
VARCHAR(100) NOT NULL,<br>	email VARCHAR(100) UNIQUE NOT NULL,<br>	age INT,=
<br>	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP<br>);</div><div><br></d=
iv><div>Added to publication, refreshed subscription.</div><div><br></div><=
div>Add some data on the publisher side:</div><div>INSERT INTO dummy_table =
(name, email, age)<br>VALUES<br>(&#39;John Doe&#39;, &#39;<a href=3D"mailto=
:john.doe@example.com">john.doe@example.com</a>&#39;, 25),<br>(&#39;Jane Sm=
ith&#39;, &#39;<a href=3D"mailto:jane.smith@example.com">jane.smith@example=
.com</a>&#39;, 30),<br>(&#39;Michael Johnson&#39;, &#39;<a href=3D"mailto:m=
ichael.j@example.com">michael.j@example.com</a>&#39;, 45),<br>(&#39;Emily D=
avis&#39;, &#39;<a href=3D"mailto:emily.d@example.com">emily.d@example.com<=
/a>&#39;, 27),<br>(&#39;Robert Brown&#39;, &#39;<a href=3D"mailto:robert.br=
own@example.com">robert.brown@example.com</a>&#39;, 40);</div><div><br></di=
v><div>The data can be seen on the subscriber. So far, so good.</div><div><=
br></div><div>I then execute the following patch on the publisher:=C2=A0<a =
href=3D"https://gist.github.com/KoenDG/d1c06d8c740c64e4e5884d0c64b81f11">ht=
tps://gist.github.com/KoenDG/d1c06d8c740c64e4e5884d0c64b81f11</a></div><div=
><br></div><div>It is a single transaction that does the following:</div><d=
iv><br></div><div>1/ Insert data, 1000 items</div><div>2/ Drop a column</di=
v><div>3/ Alter a column name</div><div>4/ Add 2 columns, nullable</div><di=
v>5/ Add a column and give it a unique constraint</div><div>6/ Update value=
s for a column with NULL values, added in step 4.</div><div>7/ Set the colu=
mn updated in step 6 to be NOT NULL</div><div>8/ Create a unique index with=
 the columns from step 3 and 6</div><div>9/ Insert a column with a default =
value</div><div>10/ Insert data for this schema, another 1000 items.</div><=
div><br></div><div>The subscription disabled, this is to be expected, there=
 are new columns names, the schema needs to be updated on the subscriber si=
de.</div><div><br></div><div>However, it seems I&#39;m stuck.</div><div><br=
></div><div>I can&#39;t enable the subscription. This is to be expected, it=
 will try to resume and run into the same issues.</div><div><br></div><div>=
Ok, I update the schema and enable again. It runs into an error for the ins=
erts of step 1. These set values for columns dropped in step 2.</div><div><=
br></div><div>I revert to the old schema and enable again. It runs into an =
error again, this time for values that don&#39;t exist yet at step 1.</div>=
<div><br></div><div>I tried dropping the table at the subscriber side, recr=
eating the correct schema, but this runs into the same error.</div><div><br=
></div><div>I remove the table from the publication and retry. Same error. =
Even with the table no longer in the publication, and the table on the subs=
criber side dropped and re-created, I&#39;m still getting the exact same er=
rors of &quot;logical replication target relation &quot;public.dummy_table&=
quot; is missing replicated columns: &quot;contact_email&quot;, &quot;statu=
s&quot;, &quot;phone_number&quot;, &quot;username&quot;&quot;</div><div><br=
></div><div><br></div><div>The only solution I&#39;ve found is to drop the =
table from the publication, and then drop the entire subscription and set i=
t back up again, with the correct schema.</div><div><br></div><div>Am I mak=
ing a mistake? Or does putting all these commands in a single transaction r=
uin my chances?</div><div><br></div><div>Clarification much appreciated.</d=
iv><div><br></div><div>Regards,</div><div>Koen De Groote</div><div><br></di=
v></div>

--00000000000044b28706249f2dd2--