Re: Update overwriting

Great. Thanks for your help. I found the SQL below in a web site but I wanted to check with the listserv before using it in production.

Lance

On 3/8/18, 2:25 AM, "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at> wrote:

Campbell, Lance wrote:
> 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?

Yes, that is safe.

I'd use "SELECT id" instead of "SELECT *" because that is all you need.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com