Re: Queries getting older values (autocommit enabled)

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(dot)valcarcel(at)gmail(dot)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,
> Eudald
>
> El vie., 24 abr. 2020 a las 18:15, Tom Lane (<tgl(at)sss(dot)pgh(dot)pa(dot)us>)
> escribió:
>
>> =?UTF-8?Q?Eudald_Valc=C3=A0rcel_Lacasa?= <eudald(dot)valcarcel(at)gmail(dot)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
>>
>