Re: Is replacing transactions with CTE a good idea?

On Sun, Apr 4, 2021 at 10:02:20AM -0400, Dave Cramer wrote:
> On Sun, 4 Apr 2021 at 09:12, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > OK, that makes sense, but I think it is wrong minded to think that this
> > absolves one of taking isolation into account.
> >
> > When you make the first read you will still have to deal with all of the
> > isolation issues 
>
> I have no idea what you are saying above.  Why is a SELECT-only CTE not
> the same as a repeatable-read SELECT-only multi-statement transaction?
> Are you saying that a SELECT in a CTE doesn't do SELECT FOR UPDATE? 
>
>
> No, but where is this documented ?

Well, every query runs with a single snapshot, even WITH queries. We do
document how non-SELECT WITH visibility is handled:

https://www.postgresql.org/docs/13/sql-select.html

The primary query and the WITH queries are all (notionally) executed at
the same time. This implies that the effects of a data-modifying
statement in WITH cannot be seen from other parts of the query, other
than by reading its RETURNING output. If two such data-modifying
statements attempt to modify the same row, the results are unspecified.

A key property of WITH queries is that they are normally evaluated only
once per execution of the primary query, even if the primary query
refers to them more than once. In particular, data-modifying statements
are guaranteed to be executed once and only once, regardless of whether
the primary query reads all or any of their output.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.