MIME-Version: 1.0
From: Stuart Campbell <stuart.campbell@ridewithvia.com>
Date: Wed, 3 Jul 2024 10:47:32 +1000
Message-ID: <CAAZ6SnzRU=qo2nedBa6ESOHfV=WaRJk+UW5DUrxkVMu6Gjpwng@mail.gmail.com>
Subject: Logical replication with temporary tables
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a3bacd061c4d2d4d"
Archived-At: <https://www.postgresql.org/message-id/CAAZ6SnzRU%3Dqo2nedBa6ESOHfV%3DWaRJk%2BUW5DUrxkVMu6Gjpwng%40mail.gmail.com>
Precedence: bulk

--000000000000a3bacd061c4d2d4d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,

I'm trying to use a blue/green deployment in Amazon RDS, which uses
Postgres logical replication to keep blue and green environments in sync.

Some parts of our app use temporary tables, and in some cases we CREATE
INDEX on those tables and sometimes DROP and recreate them.

The CREATE INDEX and DROP TABLE statements are causing problems with the
blue/green replication. (They are detected by RDS, and cause the deployment
to be marked as "degraded" and subsequently unusable.)

My (limited) understanding is that this happens because CREATE TEMPORARY
TABLE doesn't get written to the WAL, but the other statements do.

My question is whether there is some workaround that will let me create
indexes on a temporary table, and also let me drop the temporary table, in
a way that doesn't end up "degrading" replication? (Presumably that means
avoiding writing to the WAL?)

I've discovered that I can at least create a primary key inline as part of
the temporary table definition, which gives me a way to create a single
(unique) index, i.e.:

    CREATE TEMPORARY TABLE foo (id INT PRIMARY KEY, =E2=80=A6)

But I haven't discovered a way to drop the table, or to create other
indexes.

Cheers,
Stuart

--=20
This communication and any attachments may contain confidential information=
=20
and are intended to be viewed only by the intended recipients. If you have=
=20
received this message in error, please notify the sender immediately by=20
replying to the original message and then delete all copies of the email=20
from your systems.


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

<div dir=3D"ltr"><div>Hello,</div><div><br></div><div>I&#39;m trying to use=
 a blue/green deployment in Amazon RDS, which uses Postgres logical replica=
tion to keep blue and green environments in sync.<br></div><div><br></div><=
div>Some parts of our app use temporary tables, and in some cases we CREATE=
 INDEX on those tables and sometimes DROP and recreate them.</div><div><br>=
</div><div>The CREATE INDEX and DROP TABLE statements are causing problems =
with the blue/green replication. (They are detected by RDS, and cause the d=
eployment to be marked as &quot;degraded&quot; and subsequently unusable.)<=
/div><div><br></div><div>My (limited) understanding is that this happens be=
cause CREATE TEMPORARY TABLE doesn&#39;t get written to the WAL, but the ot=
her statements do.<br></div><div><br></div><div>My question is whether ther=
e is some workaround that will let me create indexes on a temporary table, =
and also let me drop the temporary table, in a way that doesn&#39;t end up =
&quot;degrading&quot; replication? (Presumably that means avoiding writing =
to the WAL?)</div><div><br></div><div>I&#39;ve discovered that I can at lea=
st create a primary key inline as part of the temporary table definition, w=
hich gives me a way to create a single (unique) index, i.e.:</div><div><br>=
</div><div>=C2=A0=C2=A0=C2=A0 CREATE TEMPORARY TABLE foo (id INT PRIMARY KE=
Y, =E2=80=A6)</div><div><br></div><div>But I haven&#39;t discovered a way t=
o drop the table, or to create other indexes.</div><div><br></div><div>Chee=
rs,</div><div>Stuart<br></div></div>

<br>
<div>This communication and any attachments may contain confidential inform=
ation and are intended to be viewed only by the intended recipients. If you=
 have received this message in error, please notify the sender immediately =
by replying to the original message and then delete all copies of the email=
 from your systems.</div><div><br></div><div><br></div>
--000000000000a3bacd061c4d2d4d--