postgresql - Postgres 中的部分索引

Question

在我的一个项目中，我有一个表格，其中包含有关某种设备固件更新的信息。最初，任何固件更新过程都处于 ENQUEUED 状态。更新过程可能有其他状态值，例如进行中，完成，失败。对于单个设备，可能存在处于 COMPLETED 和 FAILED 状态的多行(完成的更新过程，最终状态!)。但是在任何时候，每个处于 ENQUEUED 或 ONGOING 状态的设备都只能有 1 个更新过程 - 新固件推出应该只适用于没有“事件/非最终”更新过程的设备。我尝试使用 Postgres 的部分索引来实现此约束，但它没有像我预期的那样工作。

这是该表的简化版本:

CREATE TABLE firmware_update_processes
(
    id  uuid DEFAULT uuid_generate_v1(),
    device VARCHAR(10) NOT NULL,
    state VARCHAR (10) NOT NULL DEFAULT 'ENQUEUED',
    PRIMARY KEY (id)
);

这是唯一的部分索引:

CREATE UNIQUE INDEX unique_non_final_firmware_updates_processes
ON firmware_update_processes (device, state)
WHERE state IN ('ENQUEUED', 'ONGOING');

然后我验证了这个约束，但我仍然能够为给定设备插入多个“事件/非最终”更新过程......

INSERT INTO firmware_update_processes (device, state) VALUES ('device1', 'ENQUEUED');
INSERT INTO firmware_update_processes (device, state) VALUES ('device1', 'ONGOING');

是否有任何 - 也许其他 - 方法可以在数据库级别完成此操作？感谢您的帮助。

Answer 1

我认为这应该是:

CREATE UNIQUE INDEX unique_non_final_firmware_updates_processes
ON firmware_update_processes (device)
WHERE state IN ('ENQUEUED', 'ONGOING');

像您一样在 (device, state) 上使用唯一索引，这将明确允许每个设备的 ENQUEUED 和 ONGOING 状态。仅在设备上使用唯一索引，我们只允许每个设备处于 ENQUEUED 或 ONGOING 状态。