Search Postgresql Archives

Re: Is replacing transactions with CTE a good idea?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Mon, 5 Apr 2021 at 14:18, Bruce Momjian <bruce@xxxxxxxxxx> wrote:
On Sun, Apr  4, 2021 at 10:02:20AM -0400, Dave Cramer wrote:
> On Sun, 4 Apr 2021 at 09:12, Bruce Momjian <bruce@xxxxxxxxxx> wrote:
>     > 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 ?

Well, every query runs with a single snapshot, even WITH queries.  We do
document how non-SELECT WITH visibility is handled:

        https://www.postgresql.org/docs/13/sql-select.html

        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.

        A key property of WITH queries is that they are normally evaluated only
        once per execution of the primary query, even if the primary query
        refers to them more than once. In particular, data-modifying statements
        are guaranteed to be executed once and only once, regardless of whether
        the primary query reads all or any of their output.


I think we are in agreement. My point was that WITH queries don't change the isolation semantics. 

I was pretty sure we didn't do a SELECT FOR UPDATE which would imply a lock.


Dave Cramer
www.postgres.rocks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux