Sfrgttk

Using et al. for a last / senior author rather than for a first author

3 doors, three guards, one stone

What's the meaning of 間時肆拾貳 at a car parking sign

Echoing a tail command produces unexpected output?

Extract all GPU name, model and GPU ram

Short Story with Cinderella as a Voo-doo Witch

How does the particle を relate to the verb 行く in the structure「A を + B に行く」?

What is the meaning of the new sigil in Game of Thrones Season 8 intro?

What are the pros and cons of Aerospike nosecones?

Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?

How to find all the available tools in mac terminal?

Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?

Why didn't this character "real die" when they blew their stack out in Altered Carbon?

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

What is the role of the transistor and diode in a soft start circuit?

Do I really need recursive chmod to restrict access to a folder?

Should I discuss the type of campaign with my players?

Why did the IBM 650 use bi-quinary?

Can inflation occur in a positive-sum game currency system such as the Stack Exchange reputation system?

Storing hydrofluoric acid before the invention of plastics

Single word antonym of "flightless"

Can an alien society believe that their star system is the universe?

Using audio cues to encourage good posture

Is it fair for a professor to grade us on the possession of past papers?

postgresql update with a subquery limit 1 that has joins sometimes doesn't respect the limit?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join. However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually returns multiple records. Is there something I'm misunderstanding about this query like it's running one instance of the subquery for each tuple the update sees or is this a potential bug in postgresql?

UPDATE "users" SET touched_at = now() 

FROM (

   SELECT "some_table".* 

   FROM "some_table" 

     INNER JOIN "users" ON "users"."id" = "some_table"."user_id"

   WHERE "some_table"."handled_at" IS NULL 

     AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

  ORDER BY "some_table"."created_at" ASC 

  LIMIT 1 

  FOR UPDATE OF "users"

) dt 

WHERE "users".id = dt.user_id 

AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

RETURNING dt.*

postgresql update concurrency

share|improve this question

edited Apr 13 '17 at 12:42

Community♦

1

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

bumped to the homepage by Community♦ 15 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  The for update is not needed as the following update will lock the row anyway. And I think the derived table is wrong. In Postgres you should not repeat the target table in the from clause. As far as I can tell you can replace that with something like: update users set .. from some_table where some_table.handled_at is null and users.id = some_table.user_id (and users.touched_at is null ...) ...
  
  – a_horse_with_no_name
  Dec 21 '16 at 20:10
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Could you gives some minimum structure and data samples? I've invented some data to try to reproduce your problem, with no success. I have assumed that users.id is a primary key. This allows to eliminate the second ("users".touched_at ... ), because this condition has already been checked in your SELECT.
  
  – joanolo
  Dec 21 '16 at 22:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is users.id a primary key? Or unique constraint?
  
  – jjanes
  Dec 25 '16 at 19:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i can't reproduce this on my mac or amazon rds production with the exact same postgresql version but it happens ~10% of the time on travis: CREATE TABLE some_table ( id uuid DEFAULT uuid_generate_v1() NOT NULL, user_id uuid NOT NULL, handled_at timestamp without time zone, ); ALTER TABLE ONLY some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY (id); CREATE TABLE users ( id uuid DEFAULT uuid_generate_v1() NOT NULL, touched_at timestamp without time zone ); ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
  
  – Eric Jensen
  Jan 10 '17 at 20:19
  
  
  

  
  
  
  

add a comment | 

1

As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join. However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually returns multiple records. Is there something I'm misunderstanding about this query like it's running one instance of the subquery for each tuple the update sees or is this a potential bug in postgresql?

UPDATE "users" SET touched_at = now() 

FROM (

   SELECT "some_table".* 

   FROM "some_table" 

     INNER JOIN "users" ON "users"."id" = "some_table"."user_id"

   WHERE "some_table"."handled_at" IS NULL 

     AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

  ORDER BY "some_table"."created_at" ASC 

  LIMIT 1 

  FOR UPDATE OF "users"

) dt 

WHERE "users".id = dt.user_id 

AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

RETURNING dt.*

postgresql update concurrency

share|improve this question

edited Apr 13 '17 at 12:42

Community♦

1

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

bumped to the homepage by Community♦ 15 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  The for update is not needed as the following update will lock the row anyway. And I think the derived table is wrong. In Postgres you should not repeat the target table in the from clause. As far as I can tell you can replace that with something like: update users set .. from some_table where some_table.handled_at is null and users.id = some_table.user_id (and users.touched_at is null ...) ...
  
  – a_horse_with_no_name
  Dec 21 '16 at 20:10
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Could you gives some minimum structure and data samples? I've invented some data to try to reproduce your problem, with no success. I have assumed that users.id is a primary key. This allows to eliminate the second ("users".touched_at ... ), because this condition has already been checked in your SELECT.
  
  – joanolo
  Dec 21 '16 at 22:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is users.id a primary key? Or unique constraint?
  
  – jjanes
  Dec 25 '16 at 19:36
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  i can't reproduce this on my mac or amazon rds production with the exact same postgresql version but it happens ~10% of the time on travis: CREATE TABLE some_table ( id uuid DEFAULT uuid_generate_v1() NOT NULL, user_id uuid NOT NULL, handled_at timestamp without time zone, ); ALTER TABLE ONLY some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY (id); CREATE TABLE users ( id uuid DEFAULT uuid_generate_v1() NOT NULL, touched_at timestamp without time zone ); ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (id);
  
  – Eric Jensen
  Jan 10 '17 at 20:19
  
  
  

  
  
  
  

add a comment | 

As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join. However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually returns multiple records. Is there something I'm misunderstanding about this query like it's running one instance of the subquery for each tuple the update sees or is this a potential bug in postgresql?

UPDATE "users" SET touched_at = now() 

FROM (

   SELECT "some_table".* 

   FROM "some_table" 

     INNER JOIN "users" ON "users"."id" = "some_table"."user_id"

   WHERE "some_table"."handled_at" IS NULL 

     AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

  ORDER BY "some_table"."created_at" ASC 

  LIMIT 1 

  FOR UPDATE OF "users"

) dt 

WHERE "users".id = dt.user_id 

AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

RETURNING dt.*

postgresql update concurrency

share|improve this question

edited Apr 13 '17 at 12:42

Community♦

1

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

UPDATE "users" SET touched_at = now() 

FROM (

   SELECT "some_table".* 

   FROM "some_table" 

     INNER JOIN "users" ON "users"."id" = "some_table"."user_id"

   WHERE "some_table"."handled_at" IS NULL 

     AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

  ORDER BY "some_table"."created_at" ASC 

  LIMIT 1 

  FOR UPDATE OF "users"

) dt 

WHERE "users".id = dt.user_id 

AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21') 

RETURNING dt.*

share|improve this question

edited Apr 13 '17 at 12:42

Community♦

1

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

share|improve this question

edited Apr 13 '17 at 12:42

Community♦

1

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

edited Apr 13 '17 at 12:42

Community♦

1

edited Apr 13 '17 at 12:42

Community♦

1

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

asked Dec 21 '16 at 20:01

Eric JensenEric Jensen

463

1 Answer
1

active

oldest

votes

0

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

1. an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now() WHERE users.id = (SELECT id FROM users LIMIT 1)

OR

2. if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now() WHERE users.id = cte.id

share|improve this answer

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f158846%2fpostgresql-update-with-a-subquery-limit-1-that-has-joins-sometimes-doesnt-respe%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

1. an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now() WHERE users.id = (SELECT id FROM users LIMIT 1)

OR

2. if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now() WHERE users.id = cte.id

share|improve this answer

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463

add a comment | 

0

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

1. an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now() WHERE users.id = (SELECT id FROM users LIMIT 1)

OR

2. if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now() WHERE users.id = cte.id

share|improve this answer

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463

add a comment | 

In March Erwin Brandstetter revised his answer to mention "The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT": https://dba.stackexchange.com/posts/69497/revisions

There appear to be two options that actually work for limiting the number of rows updated:

1. an uncorrelated scalar subquery when you only need a single row, e.g. UPDATE users SET touched_at = now() WHERE users.id = (SELECT id FROM users LIMIT 1)

OR

2. if you need multiple rows then a common table expression (CTE) which is always materialized once and is a fence to the optimizer in postgresql (https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/), e.g. WITH cte AS (SELECT id FROM users LIMIT 10) UPDATE users SET touched_at = now() WHERE users.id = cte.id

share|improve this answer

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463

share|improve this answer

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463

answered Nov 9 '18 at 16:12

Eric JensenEric Jensen

463