Re: Is replacing transactions with CTE a good idea?

On Fri, Apr 9, 2021 at 11:05:34PM +0800, Glen Huang wrote:
> 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.

Yes, isolation only controls whether a new snapshot is computed
_between_ queries in a multi-statement transaction. Single queries
always use a single snapshot, except for maintenance commands like
VACUUM.

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

You mean multiple queries in a single CTE, yes, they are like repeatable
read.

> @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?

Uh, the SELECT manual page explains that non-SELECT queries in a CTE do
behave unusually:

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.

and the quoted paragraph suggests that your query should not work.
However, you are not referencing the foo table directly, but via
referential integrity check, which I guess does work.

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