Use Common Table Expressions for pgSettings

[haininghu]

Feature description

The current implementation sets per-request settings by opening a transaction and issuing set_config before executing the actual query.
This forces every query, even read-only ones, to run inside a transaction, wich carries unnescessary overhead.
A WITH query (CTE) could be an alternative to inject the set_config call inline, avoiding the need to open a transaction for settings-only purposes.

Current solution:

BEGIN;
SELECT set_config ...;
SELECT ...;
COMMIT;

Proposal:

WITH _settings AS (SELECT set_config(...))
SELECT ... FROM ..., _settings WHERE ...;

Motivating example

This approach reduces avoidable commands like BEGIN/COMMIT, eliminating unnecessary overhead. Encapsulating everything within a single query would also allow statement-level load balancing (for example, using pgpool2).

Supporting development

I [tick all that apply]:

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[benjie]

I’m not certain we can rely on this, I don’t think Postgres guarantees the order of operations in this case? It’s possible something may execute before the setting is set?

[benjie]

I’m pretty sure this would also break role when it is determining which operations you can execute (that would happen before the set_config executes), and even which RLS policies to apply, and maybe make search_path unreliable.

[haininghu]

In my understanding, since set_config is volatile, execution before the actual query is guaranteed.