如何在Postgres中发现异常，同时识别对表的修改

Question

我有两个表在Postgres,t_product和t_product_modifications具有以下各自的结构:

t_product

product_code    product_category     owner
============    ================     =====
A                home               Jack
B                office             Daniel
C                outdoor            Jack
D                home               Susan
(the 'product_code' and 'product_category' are unique together and is a composite primary key. 
There is also a NOT NULL constraint on the 'owner' column)

t_product_modifications

product_code       last_modified_time
============       ==================
A                 2020-04-07 16:10:30
B                 2020-04-07 16:10:30
C                 2020-04-07 16:10:30
D                 2020-04-07 16:10:30

我基本上需要做一个批量插入/更新到t_product表。只有当对记录进行了修改时，我才应该更新t_product_modifications表中的last_modified_time列。除此之外，重要的是，如果某些记录的其他约束失败，那么整个批量upsert不应该失败，而应该只是返回一个product_codes列表或一个错误日志，对于这些日志，upserts是不可能的。(另外，由于某些原因，我不能将两个表合并为一个)

例如，假设我正在尝试将以下值批量插入t_product表:

1. ('A','home', 'Susan')
2. ('B','office', 'Daniel')
3. ('E','office', NULL)
4. ('F','home', NULL)

当尝试插入以上四个值时，需要这样做

1. ('A','home')主键的第一条记录应该被成功更新，owner列的Susan值应该被更新。由于这条记录是对t_product表的更新，相应产品的last_modified_time应该在t_product_modifications表中更新。
2. 忽略第二条记录。基本上它不应该对t_product_modifications表做任何改变，因为没有对t_product表做任何修改
3. 第三条记录应该是输出错误日志或异常的一部分，因为所有者字段不能是NULL
4. 第四个记录应该是输出错误日志或异常的一部分，因为所有者字段不能是NULL

我将从Python脚本执行此Postgres查询，并希望保存在upsert期间发生的所有错误，而不会导致整个查询失败。我无法在StackOverflow上找到一个足够有效的解决方案。

Answer 1

触发过程非常适合解决您的问题。您需要创建一个过程，该过程将在t_product表的记录更新时执行，并检查列的值是否已更改，如果为真，则从t_product_modifications表更新last_modified_time列。

你的触发:

CREATE FUNCTION update_product() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'UPDATE') AND OLD!=NEW THEN
UPDATE t_product_modifications SET last_modified_time = NOW() WHERE product_code = New.product_code;
END IF;
RETURN NEW; 
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER update_product_modified_time BEFORE UPDATE ON t_product FOR EACH ROW EXECUTE PROCEDURE update_product();

DBfiddle

---

-- This will update the last_modified_time in the t_product_modifications table
UPDATE t_product SET product_category = 'home', owner = 'Susan'  WHERE product_code = 'A'; 
-- Nothing will happen
UPDATE t_product SET product_category = 'office', owner = 'Daniel' WHERE product_code = 'B';