Partial index in Postgres

Question

In one of my projects I have a table that holds information on firmware updates of some sort of device. Initially any firmware update process is in ENQUEUED state. An update process may have other state values, e.g. ONGOING, COMPLETED, FAILED. For a single device multiple rows in state COMPLETED and FAILED may exist (finished update processes, final state!). But at anytime there must only be 1 update process for each device in state ENQUEUED or ONGOING - new firmware rollouts should only be possible for devices that have no 'active/non-final' update processes. I tried to implement this constraint with Postgres' partial indexing but it didn't work as I had expected.

Here's a simplified version of that table:

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)
);

Here's the unique partial index:

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

I then validated this constraint but I was still able to insert multiple 'active/non-final' update processes for a given device...

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

Is there any - maybe other - way to accomplish this on database level ? Any help is appreciated.

Jeremy · Accepted Answer · 2019-05-15 02:17:10Z

3

I think this should be:

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

With a unique index on (device, state) like you had, that would explicitly allow both the ENQUEUED and ONGOING state for each device. With the unique index on just the device, we are allowed only one state of either ENQUEUED or ONGOING for each device.

edited May 15, 2019 at 2:17

answered May 15, 2019 at 0:55

Jeremy

6,78322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Partial index in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related