Обсуждение: Logical replication with temporary tables
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.
> On Jul 2, 2024, at 17:47, Stuart Campbell <stuart.campbell@ridewithvia.com> wrote: > My question is whether there is some workaround that will let me create indexes on a temporary table, and also let me dropthe temporary table, in a way that doesn't end up "degrading" replication? (Presumably that means avoiding writing tothe WAL?) This is a question for AWS. Community PostgreSQL doesn't have any of these concepts, and this is all proprietary modificationsto PostgreSQL by Amazon.
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.
> On Jul 2, 2024, at 18:16, Stuart Campbell <stuart.campbell@ridewithvia.com> wrote: > > 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. Yes, but: The idea of a "degraded" replication is an AWS thing, so it's hard to say what does or does not cause that stateto occur without access to proprietary AWS code. > Maybe my question can be re-summarised as: do DDL operations on temporary tables necessarily have to be written to theWAL? Is there a way to avoid that? Yes, they do (because they involve catalog changes that need to be WAL-logged), and there is no way of avoiding that in currentversions of PostgreSQL.
On Tuesday, July 2, 2024, Stuart Campbell <stuart.campbell@ridewithvia.com> 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.