CTE behavior: Citus vs pg

[begriffs]

Communication goals (e.g. detailed howto vs orientation)

Alert people in case they rely on pg semantics for their CTEs.

Good locations for content in docs structure

Add a CTE section to https://docs.citusdata.com/en/v7.3/develop/reference_sql.html

How does this work? (devs)

As pointed out in citusdata/citus#2153:

• We execute CTEs one-by-one, in order of appearance
• CTEs can see the result of other modifying CTEs
• Each CTE may work on different snapshots (read committed semantics)

We can address the third point by using repeatable mode as mentioned in citusdata/citus#1804. A challenge is that repeatable read needs to be set at the start of a transaction block and the CTE might appear mid-transaction block.

[marcocitus]

We can address the third point by using repeatable mode as mentioned in citusdata/citus#1804. A challenge is that repeatable read needs to be set at the start of a transaction block and the CTE might appear mid-transaction block.

Just a note that this isn't actually implemented yet. Setting repeatable read mode on the coordinator does not propagate to the workers.

[jonels-msft]

@marcocitus how is our CTE support in 9.0? Any changes from what's listed above?

[marcocitus]

No changes

[JelteF]

In citus 9.2 CTEs can now be inlined, i.e. they act the same as a subquery. This happens automatically except when:

1. you use WITH MATERIALIZED
2. you use the CTE more than once
3. either the CTE or the query it is used in is a modifying query
4. you use a volatile function, like random()

[onderkalaci]

fyi, I'll open a separate issue on CTE pushdown (CTE inlining) in Citus 9.2, and give more detail information