Re: Is replacing transactions with CTE a good idea?

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?

--
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.