Postgres中的部分索引

Question

在我的一个项目中，我有一个表，其中保存了某种设备的固件更新信息。最初，任何固件更新进程都处于排队状态。更新过程可能有其他状态值，例如正在进行、已完成、失败。对于单个设备，可能存在状态为“已完成”和“已失败”的多行（已完成的更新过程，最终状态！）。但在任何时候，处于排队或正在进行状态的每个设备都必须只有一个更新过程——新固件的推出应该只适用于没有“活动/非最终”更新过程的设备。我试图用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');

在（设备、状态）上有一个唯一的索引，这将显式地允许每个设备的排队状态和正在进行的状态。由于只有设备上有唯一的索引，我们只允许每个设备处于一种排队状态或进行中状态。