postgresql - 这是在 Postgres 中批量插入冲突的正确方法吗？

Question

我将提供我的问题的简化示例。

我有两个表:reviews 和 users。

reviews 更新了用户发布的一堆评论。获取评论的过程还会返回提交评论的用户的信息(并且某些用户数据经常更改)。

每当我更新 reviews 时，我想更新 users，使用 COPY 批量更新。当获取的数据包含来自同一用户的两条或更多评论时，users 就会出现问题。如果我执行一个简单的 INSERT ON CONFLICT，我可能会以错误告终，因为 INSERT 语句不能将同一行更新两次。

SELECT DISTINCT 可以解决该问题，但我还想保证将最新数据插入到 users 表中。我就是这样做的。请记住，我正在批量执行此操作:

<强>1。创建一个临时表，以便我们可以COPY 到/从它。

CREATE TEMPORARY TABLE users_temp (
    id uuid, 
    stat_1 integer, 
    stat_2 integer, 
    account_age_in_mins integer);

<强>2。 COPY 数据到临时表

COPY users_temp (
    id,
    stat_1,
    stat_2,
    account_age_in_mins) FROM STDIN CSV ENCODING 'utf-8';

<强>3。锁定 users 表并执行 INSERT ON CONFLICT

LOCK TABLE users in EXCLUSIVE MODE;

INSERT INTO users SELECT DISTINCT ON (1) 
    users_temp.id,
    users_temp.stat_1,
    users_temp.stat_2,
    users_temp.account_age_in_mins
FROM users_temp
ORDER BY 1, 4 DESC, 2, 3
ON CONFLICT (id) DO UPDATE
SET
    stat_1 = EXCLUDED.stat_1,
    stat_2 = EXCLUDED.stat_2,
    account_age_in_mins = EXCLUDED.account_age_in_mins';

我在步骤 3) 中执行 SELECT DISTINCT 和 ORDER BY 的原因是因为我:

1. 只想返回重复行的一个实例。
2. 从那些重复确保我得到最新的记录按 account_age_in_mins 排序。

这是实现我目标的正确方法吗？

Answer 1

这是一个很好的方法。当你只锁定临时表中的元组时，也许你可以避免表锁。 https://dba.stackexchange.com/questions/106121/locking-in-postgres-for-update-insert-combination