PostgreSQL: guide with `WITH` Clause Not Returning Expected Results After Multiple Updates
I'm integrating two systems and I'm trying to configure I'm working with an scenario with a query that uses a `WITH` clause to perform multiple updates in a single transaction, but it's not returning the expected results. I'm using PostgreSQL 14.1 and have the following query structure: ```sql WITH updated_rows AS ( UPDATE my_table SET column_a = 'new_value' WHERE column_b = 'condition' RETURNING * ) SELECT * FROM updated_rows; ``` When I run the above query, I expect to see the rows that were updated, but instead, I receive an empty result set. I've verified that there are indeed rows matching the update condition, and the update runs successfully without any errors. I tried adding a `RETURNING` clause directly to the main `UPDATE` statement, but that didn't help either. I've also attempted to isolate the scenario by running the `UPDATE` statement standalone and confirmed it works as intended. Additionally, I've reviewed the transaction isolation level, and it seems to be set to `READ COMMITTED`, which should allow me to see the updates immediately. Could the scenario be related to the use of `WITH` in this case? Is there a specific reason why the `SELECT` is not returning the updated rows? Any insights or alternative approaches would be greatly appreciated! This issue appeared after updating to Sql 3.10. Am I missing something obvious? I'm developing on Debian with Sql.