pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedQueries getting older values (autocommit enabled)
8+ messages / 3 participants
[nested] [flat]
* Queries getting older values (autocommit enabled)
@ 2020-04-24 16:03 Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:08 ` Re: Queries getting older values (autocommit enabled) David G. Johnston <david.g.johnston@gmail.com>
2020-04-24 16:15 ` Re: Queries getting older values (autocommit enabled) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Eudald Valcàrcel Lacasa @ 2020-04-24 16:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
@ 2020-04-24 16:08 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2020-04-24 16:08 UTC (permalink / raw)
To: Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
@ 2020-04-24 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-24 16:30 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2020-04-24 16:15 UTC (permalink / raw)
To: Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:15 ` Re: Queries getting older values (autocommit enabled) Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-24 16:30 ` Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:06 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eudald Valcàrcel Lacasa @ 2020-04-24 16:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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ó:
> =?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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:15 ` Re: Queries getting older values (autocommit enabled) Tom Lane <tgl@sss.pgh.pa.us>
2020-04-24 16:30 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
@ 2020-04-25 19:06 ` Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:36 ` Re: Queries getting older values (autocommit enabled) David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eudald Valcàrcel Lacasa @ 2020-04-25 19:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:15 ` Re: Queries getting older values (autocommit enabled) Tom Lane <tgl@sss.pgh.pa.us>
2020-04-24 16:30 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:06 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
@ 2020-04-25 19:36 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-25 19:44 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2020-04-25 19:36 UTC (permalink / raw)
To: Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:15 ` Re: Queries getting older values (autocommit enabled) Tom Lane <tgl@sss.pgh.pa.us>
2020-04-24 16:30 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:06 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:36 ` Re: Queries getting older values (autocommit enabled) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-25 19:44 ` Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 20:09 ` Re: Queries getting older values (autocommit enabled) David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eudald Valcàrcel Lacasa @ 2020-04-25 19:44 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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: . 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.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queries getting older values (autocommit enabled)
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:15 ` Re: Queries getting older values (autocommit enabled) Tom Lane <tgl@sss.pgh.pa.us>
2020-04-24 16:30 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:06 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:36 ` Re: Queries getting older values (autocommit enabled) David G. Johnston <david.g.johnston@gmail.com>
2020-04-25 19:44 ` Re: Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
@ 2020-04-25 20:09 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2020-04-25 20:09 UTC (permalink / raw)
To: Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2020-04-25 20:09 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-24 16:03 Queries getting older values (autocommit enabled) Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-24 16:08 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-24 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-24 16:30 ` Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:06 ` Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 19:36 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-25 19:44 ` Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com>
2020-04-25 20:09 ` David G. Johnston <david.g.johnston@gmail.com>
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