Thread: Queries getting older values (autocommit enabled)
Hello all,
I'm facing what seems like a bug and I've reached a dead end without finding a solution.
I'm executing a script that work on a postgresql table. This script is executed more than once at a time.
The table contains a column called status.
One of the scripts is checking the value on the column status and, if it's different than 1, it should do nothing. If it's 1 it changes its value to the new status (new value can be 1,2,3)
I provide logs from postgresql:
2020-04-24 02:27:02.694 CEST [704] uxie@log_central LOG: statement: SELECT * FROM table1 WHERE column1='XXXXXXXXXXX' ORDER BY creation DESC LIMIT 1
2020-04-24 02:27:02.698 CEST [704] uxie@log_central LOG: statement: UPDATE table1 SET column2=to_timestamp(1587688014),status=2 WHERE column1= 'XXXXXXXXXXX ' AND column3='YYYYYYYYYYYY'
2020-04-24 02:27:02.698 CEST [704] uxie@log_central LOG: statement: UPDATE table1 SET column2=to_timestamp(1587688014),status=2 WHERE column1= 'XXXXXXXXXXX ' AND column3='YYYYYYYYYYYY'
2020-04-24 02:27:02.759 CEST [735] uxie@log_central LOG: statement: SELECT * FROM table1 WHERE column1='XXXXXXXXXXX' AND column3= 'YYYYYYYYYYYY' ORDER BY creation DESC LIMIT 1
2020-04-24 02:27:02.762 CEST [735] uxie@log_central LOG: statement: UPDATE table1 SET column2=to_timestamp(1587688014),status=1 WHERE column1= 'XXXXXXXXXXX' AND column3='YYYYYYYYYYYY'
2020-04-24 02:27:02.762 CEST [735] uxie@log_central LOG: statement: UPDATE table1 SET column2=to_timestamp(1587688014),status=1 WHERE column1= 'XXXXXXXXXXX' AND column3='YYYYYYYYYYYY'
Here's a sample of the script used: https://pastebin.com/AcYQkDku
I did some verbose from the script in order to confirm the value OldStatusVal (which is status received with the SELECT query), and returned the following:
2020-04-24 02:27:02,762 2020-04-24 02:26:54 - Column3: VAR3 - Old Status: 1 - New Status: 1
So the SELECT is taking a value (Old Status) that should be evaluated to 2, but it returns 1 instead.
Could it be possible that, somehow, the select query starts before the update one, although it's printed as if it's being executed after it?
I'd love to know why it happens in order to understand how postgresql queries work and find a solution to it.
Thank you,
Eudald
On Fri, Apr 24, 2020 at 9:04 AM Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> wrote:
Hello all,I'm facing what seems like a bug and I've reached a dead end without finding a solution.I'm executing a script that work on a postgresql table. This script is executed more than once at a time.The table contains a column called status.One of the scripts is checking the value on the column status and, if it's different than 1, it should do nothing. If it's 1 it changes its value to the new status (new value can be 1,2,3)
Read "The Locking Clause" in the SELECT command's documentation page and see if that helps.
David J.
=?UTF-8?Q?Eudald_Valc=C3=A0rcel_Lacasa?= <eudald.valcarcel@gmail.com> writes: > Could it be possible that, somehow, the select query starts before the > update one, although it's printed as if it's being executed after it? It's certainly possible that the select is using a snapshot that was taken before the update commits. You should read this chapter of the manual for background and some hints: https://www.postgresql.org/docs/current/mvcc.html regards, tom lane
Hello!
Thanks David and Tom for your answer.
I'll check out mvcc. Would user defined locks on a specific table make PostgreSql underperform too much?
Thanks again,
Eudald
El vie., 24 abr. 2020 a las 18:15, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:
Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> writes:
> Could it be possible that, somehow, the select query starts before the
> update one, although it's printed as if it's being executed after it?
It's certainly possible that the select is using a snapshot that was
taken before the update commits. You should read this chapter of
the manual for background and some hints:
https://www.postgresql.org/docs/current/mvcc.html
regards, tom lane
Hello again,
I've been looking for this issue and I'd like to know the behavior of FOR UPDATE SKIP LOCKED in the following scenario:
* One query does an UPDATE targeting a row in the table
* Another query run in parallel does a SELECT...FOR UPDATE SKIP LOCKED targeting the same (being updated) row on the table.
From SKIP LOCKED definition: . With
SKIP LOCKED, any selected rows that cannot be immediately locked are skipped.Would it mean that the 2nd query wouldn't check the affected row since it's locked by the first query?
If that's the behavior, is there any way I could make the SELECT query wait for the UPDATE LOCK? Is it recommended? Are there downsides to this approach?
Thank you,
Eudald
El vie., 24 abr. 2020 a las 18:30, Eudald Valcàrcel Lacasa (<eudald.valcarcel@gmail.com>) escribió:
Hello!Thanks David and Tom for your answer.I'll check out mvcc. Would user defined locks on a specific table make PostgreSql underperform too much?Thanks again,EudaldEl vie., 24 abr. 2020 a las 18:15, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> writes:
> Could it be possible that, somehow, the select query starts before the
> update one, although it's printed as if it's being executed after it?
It's certainly possible that the select is using a snapshot that was
taken before the update commits. You should read this chapter of
the manual for background and some hints:
https://www.postgresql.org/docs/current/mvcc.html
regards, tom lane
On Sat, Apr 25, 2020 at 12:07 PM Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> wrote:
Hello again,I've been looking for this issue and I'd like to know the behavior of FOR UPDATE SKIP LOCKED in the following scenario:* One query does an UPDATE targeting a row in the table* Another query run in parallel does a SELECT...FOR UPDATE SKIP LOCKED targeting the same (being updated) row on the table.From SKIP LOCKED definition: . WithSKIP LOCKED, any selected rows that cannot be immediately locked are skipped.Would it mean that the 2nd query wouldn't check the affected row since it's locked by the first query?
Yes. What else would it mean?
If that's the behavior, is there any way I could make the SELECT query wait for the UPDATE LOCK? Is it recommended? Are there downsides to this approach?
"To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option."
Which means that if you don't include the NOWAIT clause you, well, wait.
David J.
Hello David,
Thanks for your answer.
The actual problem is that my SELECT query is being executed meanwhile the UPDATE one, but instead of waiting for the UPDATE to be executed, it doesn't wait and gets the value from the field prior to the update.
I believed it was because of the MVCC, and I was looking for a workaround to force the select to wait for the UPDATE to finish, instead of taking values of a snapshot.
Eudald
El sáb., 25 abr. 2020 a las 21:36, David G. Johnston (<david.g.johnston@gmail.com>) escribió:
On Sat, Apr 25, 2020 at 12:07 PM Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> wrote:Hello again,I've been looking for this issue and I'd like to know the behavior of FOR UPDATE SKIP LOCKED in the following scenario:* One query does an UPDATE targeting a row in the table* Another query run in parallel does a SELECT...FOR UPDATE SKIP LOCKED targeting the same (being updated) row on the table.From SKIP LOCKED definition: . WithSKIP LOCKED, any selected rows that cannot be immediately locked are skipped.Would it mean that the 2nd query wouldn't check the affected row since it's locked by the first query?Yes. What else would it mean?If that's the behavior, is there any way I could make the SELECT query wait for the UPDATE LOCK? Is it recommended? Are there downsides to this approach?"To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option."Which means that if you don't include the NOWAIT clause you, well, wait.David J.
On Sat, Apr 25, 2020 at 12:44 PM Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> wrote:
Hello David,Thanks for your answer.The actual problem is that my SELECT query is being executed meanwhile the UPDATE one, but instead of waiting for the UPDATE to be executed, it doesn't wait and gets the value from the field prior to the update.I believed it was because of the MVCC, and I was looking for a workaround to force the select to wait for the UPDATE to finish, instead of taking values of a snapshot.
If you can demonstrate a case where session 1 is holding a transaction open on a completed UPDATE statement and session 2 with a SELECT FOR UPDATE is not waiting for session 1 to commit I am pretty sure you will be demonstrated a bug.
"conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted)."
David J.