Re: Is replacing transactions with CTE a good idea?

This discussion really questioned my understanding of concurrency in PostgreSQL, thanks a lot.

I gave the corresponding part of the doc some more read, and I’m now in the option that insolation level has no effect on CTEs, but please correct me if I’m wrong.

If notionally all queries execute at the same time, even if they are executed in read committed, they behave like repeatable read. This should also be true for serializable, since the anomalies that isolation level tries to address won’t occur in a CTE.

@Bruce
The gotchas you mentions are really interesting, I have a follow up question if you don’t mind:

CREATE foo(n int);
CREATE bar(n int REFERENCES foo(n));
WITH t AS (
INSERT INTO foo(n) VALUES(1)
)
INSERT INTO bar(n) VALUES(1);

Is the CTE guaranteed to success or it’s actually unspecified? I ran it a couple times without issues, but I can’t be sure. If it’s unspecified any idea how should I correct it?

> On Apr 6, 2021, at 2:41 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Mon, Apr 5, 2021 at 02:32:36PM -0400, Dave Cramer wrote:
>> On Mon, 5 Apr 2021 at 14:18, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> I think we are in agreement. My point was that WITH queries don't change the
>> isolation semantics.
>
> My point is that when you combine individual queries in a single WITH
> query, those queries run together with snaphot behavior as if they were
> in a repeatable-read multi-statement transaction.
>
> --
> 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.
>