Question 247023 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using set_config in CTE

I want to be able to call set_config to set a configuration parameter within the same transaction/scope as an INSERT statement. There is a function triggered AFTER the insert which reads from this configuration parameter.

I need to accomplish this as a single statement so CTEs seems like a good idea:

WITH 
  idx AS (SELECT set_config('audit.trace_id', '123', true)), 
  rows AS (INSERT INTO "person" ("name") values ('Mary') RETURNING *) 
SELECT * FROM rows

However, it seems audit.trace_id is not available to the trigger function. So then I thought I’d structure it like below, which should create a dependency:

WITH 
  idx AS (SELECT set_config('audit.trace_id', '123', true)) 
INSERT INTO "person" ("name") values ('Mary') RETURNING *

But still, the value in audit.trace_id is still not set within scope of the triggered function. I believe this is because Postgre avoids running the SELECT as the result isn’t required further into the CTE.

If I select out from both, like below, then audit.trace_id is successfully set when the trigger function runs:

WITH 
  idx AS (SELECT set_config('audit.trace_id', '123', true) as __trace_id),
  row as (INSERT INTO "person" ("name") values ('Mary') RETURNING *)
SELCET * FROM idx, row

Question

Is ‘audit.trace_id’ guaranteed to be set by the time the trigger executes on the person table? Or is there a possible race condition here?

EDIT:

What about this? It seems to work fine:

INSERT INTO "person" ("name") values ('Mary') RETURNING *, set_config('audit.trace_id', '123', true)
Tags:

2

Answers


  1. 0

    In PostgreSQL, within a Common Table Expression (CTE), the queries are not guaranteed to be executed in the order they are written, they are optimized by the PostgreSQL engine and might be executed in parallel or in a different order that might not be expected.
    However, data-modifying statements in WITH are executed exactly once, and always to completion, which does mean that they will always fully complete before the main query starts to run.

    Given your constraints and the problems you are facing, it seems that the final solution you proposed:

    INSERT INTO "person" ("name") values ('Mary') RETURNING *, set_config('audit.trace_id', '123', true)

    would be the most reliable way to ensure the configuration parameter 'audit.trace_id' is set before the trigger function executes, as it sets the configuration parameter during the INSERT statement’s RETURNING clause, essentially making it a part of the data modification statement, ensuring it is executed within the same transaction and before any AFTER INSERT triggers.

    Login or Signup to reply.
  2. 0

    PostgreSQL executes the CTE idx only if it is referenced in the main statement. That’s why your third attempt was successful, unlike the first two.

    The placeholder parameter will certainly be set by the time an AFTER INSERT trigger executes, because the CTE has to be executed before the INSERT.

    There cannot be any race conditions, because the execution plan is deterministic, and no parallel processing is taking place. set_config is marked PARALLEL UNSAFE, so a statement containing it will never be executed using parallel query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search