What happens when a CTE query is interrupted I have the following query.

WITH d_rows AS (DELETE FROM t_a RETURNING * )  INSERT INTO t_b SELECT * FROM d_rows; 


What will happen if i cancel this query (Ctrl+c in psql)? Will the delete run? Does it run already? Does it run in batches? I see the t_b is growing while the query is running. This is important to know since this is about a lot of data.
A CTE isn't any different to any other statement in this regard - it runs within a transaction. If no transaction is already open then one is opened for the statement and committed only at the successful completion of the whole statement.

Thus, if you interrupt the CTE, all effects will roll back and nothing will happen1. If you interrupt it just at the moment it is committing then it might commit and all take effect, but either all or none of the CTE will take effect. Individual CTE sub-terms cannot take effect by themselves.

So ... the delete might run, but if so, its effects will not be committed and will be rolled back when the query is cancelled. Exactly how it's executed does not matter. PostgreSQL could choose to batch it, execute it row-by-row, or execute the whole DELETE and feed all its results into the INSERT. It doesn't matter, because the effects will be the same.



1 Well, strictly a whole lot of table bloat could be created, but there are no effects logically visible to other transactions.