Re: Common case not at all clear

On Thu, Jul 29, 2021 at 8:04 AM PG Doc comments form
<noreply(at)postgresql(dot)org> wrote:
> For all this documentation, it is completely unclear how to handle the most
> common, simple case. I.e.
>
> Select balance into :bal ...where key =123;
> Update set balance = :bal+100 where key = 100

I don't think that that's the most common or simple case.

> The discussion of read committed for Updates is misleading, I am pretty sure
> it will fail if the select is in a different statement, a common case.

That's true.

> For Oracle, I think that by default a Select will return values at the
> beginning of a transaction, Select For Update will return the read committed
> value, and Select For Update will wait until conflicting transactions
> complete.

I don't think that's true. I believe that the main difference between
READ COMMITTED in Oracle is conflict handling: If an UPDATE needs to
wait for another UPDATE, the entire statement will be rolled back
before it is retried. While Postgres does something...more
complicated.

Both systems use a snapshot per statement in READ COMMITTED. And so
any differences between the two systems here don't seem relevant.

> So the answer is that the first Select would be a Select For
> Update, which should be the normal pattern to be safe (with primary key
> access) and minimize deadlocks.
>
> Is that how PostgreSql works? Is that the generally recommended pattern?
> Impossible to tell from the docs as written. MVCC really relies on Select
> For Update to work for transactions, I think.

I suggest using a higher isolation level. Ideally SERIALIZABLE.

--
Peter Geoghegan