SET search_path in batch causes "relation does not exist"

[ymmt2005]

Hello,
Thank you for the super powerful driver!

I'm developing a multi-tenant application that creates a schema for each tenant.
Each schema has a users table as follows.

CREATE SCHEMA t_123;
CREATE TABLE t_123.users  (
    user_id TEXT PRIMARY KEY,
    display_name TEXT NOT NULL,
    primary_email TEXT NOT NULL,
);

I wanted to write an efficient query for this table, so tried to use pgx.Batch as follows.
Note the SET search_path TO in the batch queue.

	var users []*User
	b := &pgx.Batch{}
	b.Queue("SET search_path TO " + tenantId)
	b.Queue("SELECT user_id, display_name FROM users").Query(...)
	err := tx.SendBatch(ctx, b).Close()

The code worked fine if it is run sequentially, but when I tried to run tests for the function
in parallel using testing.T.Parallel() and pgxpool.Pool, I got a bunch of following errors.

ERROR: relation "users" does not exist (SQLSTATE 42P01)

If I change the default query execution mode to pgx.QueryExecModeExec, the error disappears.
QueryExecModeCacheStatement, QueryExecModeCacheDescribe, and QueryExecModeDescribeExec did not work.

Since all schemas have exactly the same tables, I wonder why QueryExecModeCacheDescribe did not fix it.
Any help is appreciated.

[jackc]

The query exec modes that fail all use the extended protocol to prepare / describe the queries before they are executed. The batch system minimizes the number of round trips by sending all prepares / describes at once and then sending all queries at once.

So in your example the equivalent of PREPARE SELECT user_id, display_name FROM users is executed before the SET search_path TO .

A similar problem occurs when creating and then using temporary tables in the same batch.

[ymmt2005]

@jackc Thank you very much.

So the query was targeting the public schema and failed to find the table.
Understood, but I still don't understand why QueryExecModeExec worked though
it also uses the extended protocol IIUC.

[jackc]

QueryExecModeExec does use the extended protocol, but it does not describe the query before it is executed. Instead of asking PostgreSQL what types the parameters are, it tells PostgreSQL the type the arguments are encoded as. In the vast majority of cases, this works fine. However, there are cases such as when the underlying type is json/jsonb where this means pgx will not know that it needs to encode as JSON.

[ymmt2005]

I see. I understood the behavior as follows with QueryExecModeExec.

1. Suppose we are sending a batch of statements that starts with SET searth_path TO xxx.
2. pgx sends parse message to prepare SET search_path TO xxx
3. pgx sends bind message to execute SET search_path TO xxx. Hereafter, the search path is set to xxx.
4. pgx sends parse message to prepare the next statement in the batch.
5. pgx sends bind message to execute the above statement.
6. Repeat 4 & 5 until all statements are executed.

I guess other execution modes do the parse phase for all statements in the batch first and then execute them, which caused my error.

Thank you!