Update overwriting

From: "Campbell, Lance" <lance(at)illinois(dot)edu>
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Update overwriting
Date: 2018-03-07 21:08:05
Message-ID: 1ADF4886-8AFB-4B94-8077-9BD0AA2230E8@illinois.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

PostgreSQL 9.6.x
I am concerned about update commands overwriting each other.

Table:
CREATE TABLE work
(
id integer NOT NULL DEFAULT nextval(('work_id_seq'::text)::regclass),
server_id integer default 0,
// … other fields
CONSTRAINT work_pkey PRIMARY KEY (id)
);

Use Case:
I have a table that contains data that needs to be processed by N number of applications running in parallel. Each row in the table represents work that needs to be pulled off by an application server to be processed. When an application is ready to grab records to process, the application will update the server_id from 0 to the application servers ID.

WITH work_select AS (SELECT * FROM work WHERE server_id=0 ORDER BY id ASC LIMIT 100 FOR UPDATE SKIP LOCKED)
UPDATE work AS work_to_do SET server_id=1 FROM work_select WHERE work_to_do.id=work_select.id;

Is this the proper command to use to avoid applications assigning their server_id to one that has just been assigned a value?

Thanks,

Lance

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Doug Gorley 2018-03-07 23:23:38 How to monitor logical replication initial sync?
Previous Message Mark Kirkwood 2018-03-07 21:01:53 Re: Reliable WAL file shipping over unreliable network