Re: Is replacing transactions with CTE a good idea?

On Mon, 5 Apr 2021 at 14:18, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> 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.
>
>
I think we are in agreement. My point was that WITH queries don't change
the isolation semantics.

I was pretty sure we didn't do a SELECT FOR UPDATE which would imply a lock.

Dave Cramer
www.postgres.rocks