PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Optimistic Lock Update Query

I want to execute a single update query and have a result of two values, the first a boolean (true if there is a record matching the ID), and the second a rows affected count or some other value to signify that an update was applied. This will enable me to differentiate between ENTITY_NOT_FOUND and VERSION_CONFLICT.

Example UPDATE query

    update vessel_clearance vc
        set status = "blah",
            last_updated_at = {new_ts},
            expiry = {some_time}
        where vc.id = sqlc.arg(vesselclearanceid)
            and vc.last_updated_at = {current_version_ts}

Is this possible? I have experimented with the WITH clause but has been unsuccessful so far.

Tags:

2

Answers


  1. 0

    Please try the below. It will return the number of rows.

    That said, if you are using a library from another language, update/delete/insert methods often will return the number of rows affected.

    with update_query as (
  update vessel_clearance vc
     set status = "blah",
         last_updated_at = {new_ts},
         expiry = {some_time}
   where vc.id = sqlc.arg(vesselclearanceid)
     and vc.last_updated_at = {current_version_ts}
)
select count(*) from update_query;
    Login or Signup to reply.
  2. 0

    Good question.

    I don’t think you can do this in a single query.

    The UPDATE statement will return the number of affected rows, and that corresponds directly to the rows matching a search predicate value of true (not unknown, not false). If the search predicate includes the version verification then it won’t match a different version value and will return zero, even if there are rows for a partial match of the predicate ( only for id in this case).

    Having said that, you can do this using Pessimitic Locking. Running one query to check for predicate #1 (without the version verification) and then a second one with full predicate (with version verification). This, of course, defeats the goal of using Optimistic Locking.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search