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.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'
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.
https://www.postgresql.org/docs/12/sql-select.html
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ó:
Show quoted text
=?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 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ó:
Show quoted text
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ó:=?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
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: . 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?
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ó:
Show quoted text
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: . 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?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)."
https://www.postgresql.org/docs/12/explicit-locking.html#LOCKING-ROWS
David J.