Обсуждение: Update overwriting
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
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
rowin the table represents work that needs to be pulled off by an application server to be processed. When an
applicationis ready to grab records to process, the application will update the server_id from 0 to the application
serversID.
>
> 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
avalue?
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
Great. Thanks for your help. I found the SQL below in a web site but I wanted to check with the listserv before using
itin production.
Lance
On 3/8/18, 2:25 AM, "Laurenz Albe" <laurenz.albe@cybertec.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.
Eachrow in the table represents work that needs to be pulled off by an application server to be processed. When an
applicationis ready to grab records to process, the application will update the server_id from 0 to the application
serversID.
>
> 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
assigneda 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