Re: Is replacing transactions with CTE a good idea?

On Sun, 4 Apr 2021 at 09:12, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Sun, Apr 4, 2021 at 08:35:41AM -0400, Dave Cramer wrote:
> >
> >
> > On Thu, 1 Apr 2021 at 15:39, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > On Thu, Apr 1, 2021 at 11:24:48AM -0400, Dave Cramer wrote:
> > > CTE's don't change the isolation level. I'm not sure what you are
> getting
> > at
> > > here ?
> >
> > I think what he/she means here is that all queries in a CTE use a
> single
> > snapshot, meaning you don't see changes by commits that happen
> between
> > queries that are part of the same CTE. If you were running the
> queries
> > separately in read committed mode, you would see those changes, but
> you
> > would not see them in repeatable read or serializable transaction
> mode.
> >
> > 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 ?

Dave Cramer
www.postgres.rocks