Is logic blocks in stored procedure/server function run sequentially or run parallel like in CTE?-CodePudding

I had a problem that some of the CTE didn't run in the order I wanted, and I had no way to call one from the other.

WITH insert_cte AS (
     INSERT INTO some_table
     SELECT *
     FROM (...) AS some_values_from_first_relationship
)

UPDATE some_table
-- here I had no way to call insert_cte and values from first relationship were not updated 
SET <some_values_from_first_and_second_relation_ship>

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=c584581a91fbb1ca8f51c3b32c04283f

So I created server function via CREATE OR REPLACE FUNCTION/PROCEDURE and moved CTE to logic block BEGIN - END; like

<<main_label>>
BEGIN
      <<insert_cte_analogue>>
      BEGIN
         [insert_cte_logic]
      END;

      <<update_cte_analogue>>
      BEGIN
        [update_cte_logic]
      END;

END;

Will it run sequentially or I am going to run into the same problem as in the CTE?

CodePudding user response：

I apologize for the comment I left on your last question suggesting that you force the execution order by referencing the previous CTE. I use that frequently for setting FK values that rely on PKs generated in prior insert CTEs and force the order by referring to what comes back from RETURNING *.

I have never tried your use case, and the docs say it is not possible to update the same row twice within a single statement:

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.

CodePudding user response：

Okay, that worked. First the insert_cte_analogue logical block created rows, then the update_cte_analogue logical block updated this rows. I didn't need a commit between blocks. Everything went without errors. I think that logical blocks always will run sequentially.