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@xxxxxxxxxx> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
