如何确保对要替换的表进行同步DDL操作? [英] How can I ensure synchronous DDL operations on a table that is being replaced?

问题描述

我有多个进程正在Redshift中不断刷新数据.他们开始一个事务，创建一个新表，将来自S3的所有数据COPY都放入新表中，然后删除旧表并将新表重命名为旧表.

I have multiple processes which are continually refreshing data in Redshift. They start a transaction, create a new table, COPY all the data from S3 into the new table, then drop the old table and rename the new table to the old table.

伪代码:

start transaction;
create table foo_temp;
copy into foo_temp from S3;
drop table foo;
rename table foo_temp to foo;
commit;

我用这种方式更新了几十个表.效果很好，但我想让多个进程执行这些表更新以达到冗余目的，并确保数据相当新鲜(不同的进程可以同时更新不同表的数据).

I have several dozen tables that I update in this way. This works well but I would like to have multiple processes performing these table updates for redundancy purposes and to ensure that data is fairly fresh (different processes can update the data for different tables concurrently).

它正常工作，除非一个进程尝试刷新另一个进程正在处理的表.在那种情况下，第二个进程会被第一个进程阻塞，直到它提交为止，而当第二个进程提交时，就会出现错误:

It works fine unless one process attempts to refresh a table that another process is working on. In that case the second process gets blocked by the first until it commits, and when it commits the second process gets the error:

错误:表12345被并发事务删除

ERROR: table 12345 dropped by concurrent transaction

我是否可以通过一种简单的方法来确保只有一个进程正在刷新表，以便第二个进程不会进入这种情况?

我考虑为每个真实表创建一个特殊的锁定表.在处理伴随实表之前，该过程将LOCK特殊锁表.我认为可以，但是我想避免为我的每个表创建一个特殊的锁表.

I considered creating a special lock table for each of my real tables. The process would LOCK the special lock table before working on the companion real table. I think that will work but I would like to avoid creating a special lock table for each of my tables.

推荐答案

您需要保护读者免于跌落，请执行以下操作:

you need to protect readers from seeing the drop, do this by:

• 开始交易
• 将主表重命名为old_main_table
• 将tmp表重命名为主表
• 提交
• 删除表old_main_table

 
    Conn #1         Conn #2
    --------------  ------------------------------------------
                    > create table bar (id int,id2 int,id3 int);
                    CREATE TABLE
    > begin;
    BEGIN
                    > begin;
                    BEGIN
                    > alter table bar rename to bar2;
                    ALTER TABLE
    > select * from bar;  
                    > create table bar (id int,id2 int,id3 int,id4 int);
                    CREATE TABLE
                    > commit; drop table bar2;
                    COMMIT
    id | id2 | id3 
    ----+-----+-----
    (0 rows)
    > commit;
    COMMIT
                    DROP TABLE

这篇关于如何确保对要替换的表进行同步DDL操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！