0

I'm struggling to handle concurrent insert in my table on PostgreSQL. I have the table monitoring_services with the following structure:

create table monitoring_services(
  id integer default nextval('seq'),
  user_id bigint not null,
  status varchar(25) default 'active',
  ...
)

And the possible status for a services are: ['active', 'completed', 'cancelled']

Each user can have just one active service at a time. When a user starts a service, a new record is created in the table with the default status "active".

For instance, this is posible:

user_id: 1, status: 'active'
user_id: 2, status: 'active'
user_id: 1, status: 'completed'
user_id: 1, status: 'completed'

But this isn't:

user_id: 1, status: 'active'
user_id: 2, status: 'active'
user_id: 1, status: 'active'
...

I created this index to avoid concurrent inserts and the user can't have more than one active service:

create unique index active_monitoring_service_idx on monitoring_services (user_id) where status = 'active';

I don't know if this is the right approach.

What would be the best way to handle it? Thanks!

Gutty
  • 1
  • 1
    The partial unique index enforces your requirement, the approach is good. Do you need anything else? Or can we mark this as duplicate of https://dba.stackexchange.com/q/37427/3684 ? – Erwin Brandstetter Sep 29 '21 at 16:27
  • Hi Erwin! You're totally right, the other post is basically the same as mine! We can mark this as duplicated – Gutty Sep 29 '21 at 16:46

0 Answers0