Is it safe to mark a postgres function as parallel safe if its uses `set local`

user_151461 0
Viewed 225

I'm using something like this as part of a query in my application in order to authenticate users

set local jwt.claims.sub to 'ffad81a1-cc4e-4370-b4bc-1453975a4e8d';

With the above is it safe to access that value in another procedure that is marked as parallel safe

I'm executing a transaction which first makes the set local call, then performs a select query. The function that I want to mark as parallel safe is actually used as part of my row level security logic, so it executes during the select query and all within the same transaction that started with the set local call.

The documentation states

... should be labeled as parallel restricted if they access temporary tables, client connection state, cursors, prepared statements, or miscellaneous backend-local state which the system cannot synchronize in parallel mode (e.g., setseed cannot be executed other than by the group leader because a change made by another process would not be reflected in the leader).

https://www.postgresql.org/docs/12/sql-createfunction.html

I'm wondering if this use of set local is considered as client connection state? Hopefully not as parallel safe is making a dramatic difference for some functions and would love to enable it

postgresql
1 Answers

A function that uses SET or SET LOCAL should be marked PARALLEL RESTRICTED, otherwise the worker processes that work on the query don't agree on the setting, which can lead to trouble.

You could refactor your code and run SET LOCAL outside the function, before you run the SQL statement that uses the function. As long as they run in the same transaction, it shouldn't make a difference.

Alternatively, you can use

ALTER FUNCTION ... SET parameter = value;

In that case, the changed parameter value is only in effect during function execution and does not leak out of the function, so it should be safe to use PARALLEL SAFE.

user_64643080
Related