Postgres冲突部分索引的唯一或排除约束无法更新票证

Question

典型票务系统的PostgreSQL数据库问题。为什么我的upsert不更新现有票证？

1. 门票表：

CREATE TABLE ticket (
    ticket_id SERIAL PRIMARY KEY,
    user_id uuid NOT NULL
    coach_id uuid,
    status text NOT NULL,
    last_message text NOT NULL,
    last_updated_at timestamp with time zone NOT NULL,
    completed_at timestamp with time zone
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX ticket_ak1 ON ticket(ticket_id int4_ops);
CREATE UNIQUE INDEX ticket_user_id_not_completed_idx ON ticket(user_id uuid_ops,(status <> 'completed'::text) bool_ops DESC NULLS LAST);

代码中开发的约束（不是db枚举类型的一部分）：

• 状态只能是以下值之一：open，unread或已完成。

之前的门票：

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at","ticket_id") VALUES ('d5948d24-6fce-4712-896a-e15cd6db6837',NULL,'open','Accusantium perferendis voluptatem sit aut consequatur.','2021-12-13 17:24:48.389',1) RETURNING "ticket_id";

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at","completed_at","ticket_id") VALUES ('d5948d24-6fce-4712-896a-e15cd6db6837',NULL,'completed','Aut consequatur perferendis sit accusantium voluptatem.','2021-12-13 17:24:48.391','2021-12-13 17:24:48.391',2) RETURNING "ticket_id";

运行此SQL以尝试向上插入第一个票证失败：

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at") VALUES ('ab45ae3f-e84a-4a0a-8072-8896a902d488',NULL,'unread','You are tearing me apart, Brandon!','2021-12-13 17:24:48.389')
    ON CONFLICT ("user_id") DO UPDATE SET "status"="excluded"."status",
        "last_updated_at"="excluded"."last_updated_at",
        "last_message"="excluded"."last_message"
WHERE "excluded"."status" <> 'completed' RETURNING "ticket_id"

带有错误消息：

错误：不存在与冲突规范匹配的唯一或排除约束

我试着把它改成：

INSERT INTO "ticket" ("user_id","coach_id","status","last_message","last_updated_at") VALUES ('ab45ae3f-e84a-4a0a-8072-8896a902d488',NULL,'unread','You are tearing me apart, Brandon!','2021-12-13 17:24:48.389')
    ON CONFLICT (user_id) WHERE status <> 'completed' DO UPDATE
    SET "status"="excluded"."status",
        "last_updated_at"="excluded"."last_updated_at",
        "last_message"="excluded"."last_message"

在DO UPDATE之前移动WHERE子句以触发部分索引查询，但没有效果。

我只想更新“未完成”票证的状态、上次更新时间和上次消息（根据该表中定义的部分唯一索引，每个用户只能更新一条）。那么，为什么这次升级不更新现有的票证呢？

Answer 1

所以，为了让一个售票系统每个用户都有一张未完成的票，我所要做的就是通过将两个列指定为索引的一部分来修复索引，即使有一个place子句来定义该索引的偏爱性：

CREATE UNIQUE INDEX ticket_user_id_not_completed_idx
  ON ticket(user_id, status) WHERE status <> 'completed'

而不是：

CREATE UNIQUE INDEX ticket_user_id_not_completed_idx
ON ticket(user_id uuid_ops,(status <> 'completed'::text) bool_ops DESC NULLS LAST);

这也是VynlJunkie在之前的评论中所说的。我只是想记录下我最终是如何解决的。

Answer 2

有一条错误信息是泄露的。您的on conflict约束与针对表声明的任何唯一约束都不匹配？

列user_id与索引ticket_user_id_not_completed_idx中的其他列一起存在，因此没有完全匹配。您的on冲突需要与索引完全匹配。要么将您的唯一索引更改为user_id列，要么将所有列添加到on冲突子句中。

或者

可以在on conflict子句中按名称引用约束。

从文件中