Joins of data-modifying CTE with the target table
Hi list,
This popped up yesterday during a discussion at the Boston PostgreSQL group
meetup, and Jesper Pedersen had advised that I post it here.
Imagine this setup:
CREATE TABLE IF NOT EXISTS mytable (id BIGSERIAL PRIMARY KEY, value TEXT
NOT NULL);
WITHinsert_cte AS
(
INSERT
INTOmytable (value)
VALUES ('test')
RETURNING
*
)
SELECT mytable.*
FROMinsert_cte
JOINmytable
USING (id);
This query will return nothing, even though people would expect it to
return the newly inserted record.
This is just a minimally reproducible example, in which you can easily work
around the problem just by getting rid of the join to mytable. But during
my consulting career, I've seen people try putting together more complex
queries using the same pattern, and this always comes as a surprise.
I get why it's not working (because the statement is not allowed to see the
tuples with its own cmin), but I was wondering if it was worth it at least
to spell it out explicitly in the documentation.
Right now the documentation says:
https://www.postgresql.org/docs/15/queries-with.html#QUERIES-WITH-MODIFYING
RETURNING data is the only way to communicate changes between different
> WITH sub-statements and the main query
which I don't think is covering the JOIN issue (after all, I am using the
RETURNING clause to communicate with the main query).
Can we please add this example to the documentation? I can do the wording
if that's something worth adding.
Thank you!
Re: Joins of data-modifying CTE with the target table
To reiterate, I (I personally) get why it's not working, but the documentation is something that is intended for people who don't. Technically, anyone can deduce it from reading the documentation thoroughly enough, but do we need the documentation to be as terse as possible? To answer your question, by "explicitly" I mean mentioning this very pattern, where you join the returned id with the target table. People often try to use this pattern for queries like "add an item to the order in a CTE, select the order total in the main query", and often don't notice that the order total doesn't include the new item until it hits production. ср, 19 апр. 2023 г. в 11:46, Tom Lane : > Alex Bolenok writes: > > I get why it's not working (because the statement is not allowed to see > the > > tuples with its own cmin), but I was wondering if it was worth it at > least > > to spell it out explicitly in the documentation. > > What's not explicit about this? > > The sub-statements in WITH are executed concurrently with each other > and with the main query. Therefore, when using data-modifying > statements in WITH, the order in which the specified updates actually > happen is unpredictable. All the statements are executed with the same > snapshot (see Chapter 13), so they cannot “see” one another's effects > on the target tables. > > regards, tom lane >
