public inbox for [email protected]
help / color / mirror / Atom feedLogical replication with temporary tables
4+ messages / 3 participants
[nested] [flat]
* Logical replication with temporary tables
@ 2024-07-03 00:47 Stuart Campbell <[email protected]>
2024-07-03 00:49 ` Re: Logical replication with temporary tables Christophe Pettus <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Stuart Campbell @ 2024-07-03 00:47 UTC (permalink / raw)
To: [email protected]
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, …)
But I haven't discovered a way to drop the table, or to create other
indexes.
Cheers,
Stuart
--
This communication and any attachments may contain confidential information
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logical replication with temporary tables
2024-07-03 00:47 Logical replication with temporary tables Stuart Campbell <[email protected]>
@ 2024-07-03 00:49 ` Christophe Pettus <[email protected]>
2024-07-03 01:16 ` Re: Logical replication with temporary tables Stuart Campbell <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Christophe Pettus @ 2024-07-03 00:49 UTC (permalink / raw)
To: Stuart Campbell <[email protected]>; +Cc: [email protected]
> On Jul 2, 2024, at 17:47, Stuart Campbell <[email protected]> wrote:
> 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?)
This is a question for AWS. Community PostgreSQL doesn't have any of these concepts, and this is all proprietary modifications to PostgreSQL by Amazon.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logical replication with temporary tables
2024-07-03 00:47 Logical replication with temporary tables Stuart Campbell <[email protected]>
2024-07-03 00:49 ` Re: Logical replication with temporary tables Christophe Pettus <[email protected]>
@ 2024-07-03 01:16 ` Stuart Campbell <[email protected]>
2024-07-03 01:19 ` Re: Logical replication with temporary tables David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Stuart Campbell @ 2024-07-03 01:16 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: [email protected]
>
> This is a question for AWS. Community PostgreSQL doesn't have any of
> these concepts, and this is all proprietary modifications to PostgreSQL by
> Amazon.
My understanding was that under the hood, AWS uses the logical replication
features that are present in community Postgres. If that's incorrect then
I'm sorry for the off-topic post.
Maybe my question can be re-summarised as: do DDL operations on temporary
tables necessarily have to be written to the WAL? Is there a way to avoid
that?
--
This communication and any attachments may contain confidential information
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Logical replication with temporary tables
2024-07-03 00:47 Logical replication with temporary tables Stuart Campbell <[email protected]>
2024-07-03 00:49 ` Re: Logical replication with temporary tables Christophe Pettus <[email protected]>
2024-07-03 01:16 ` Re: Logical replication with temporary tables Stuart Campbell <[email protected]>
@ 2024-07-03 01:19 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2024-07-03 01:19 UTC (permalink / raw)
To: Stuart Campbell <[email protected]>; +Cc: Christophe Pettus <[email protected]>; [email protected] <[email protected]>
On Tuesday, July 2, 2024, Stuart Campbell <[email protected]>
wrote:
> This is a question for AWS. Community PostgreSQL doesn't have any of
>> these concepts, and this is all proprietary modifications to PostgreSQL by
>> Amazon.
>
>
> Maybe my question can be re-summarised as: do DDL operations on temporary
> tables necessarily have to be written to the WAL? Is there a way to avoid
> that?
>
>
Yes, and no. The tables those commands update are WAL-logged and that
status is all-or-nothing.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-07-03 01:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-03 00:47 Logical replication with temporary tables Stuart Campbell <[email protected]>
2024-07-03 00:49 ` Christophe Pettus <[email protected]>
2024-07-03 01:16 ` Stuart Campbell <[email protected]>
2024-07-03 01:19 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox