pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate overwriting
3+ messages / 2 participants
[nested] [flat]
* Update overwriting
@ 2018-03-07 21:08 Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Campbell, Lance @ 2018-03-07 21:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update overwriting
@ 2018-03-08 08:25 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2018-03-08 08:25 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Update overwriting
@ 2018-03-08 15:24 Campbell, Lance <lance@illinois.edu>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Campbell, Lance @ 2018-03-08 15:24 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.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. 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-03-08 15:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-07 21:08 Update overwriting Campbell, Lance <lance@illinois.edu>
2018-03-08 08:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-08 15:24 ` Campbell, Lance <lance@illinois.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox