The set command in the stored procedure is invalid for subsequent query statements

If some set command in procedure, that will be valid only in session to primary node, but not valid for sessions to standby node.

example as below:

CREATE OR REPLACE PROCEDURE sub_proc1()
LANGUAGE plpgsql
AS $$
BEGIN
set enable_indexscan=off;
END;
$$;

call procedure will be executed on primary node, enable_indexscan is set to off on session to primary node.

call sub_proc1();

show command will be executed on standby node. the result of enable_indexscan is still default value(on), not changed;

enable_indexscan;

on

(1 row)

so subsequent select query will be routed to standby node, that means for those query enable_indexscan is still on .

is any solution for this？

So your question is how to route a SELECT to primary node? You can use an SQL comment like this:

test=# /*NO LOAD BALANCE*/select 1;
NOTICE:  DB node id: 0 statement: /*NO LOAD BALANCE*/select 1;
 ?column? 
----------
        1
(1 row)

Note that NOTICE: DB node id: 0 statement: /*NO LOAD BALANCE*/select 1; is shown because of notice_per_node_statement = on

Navigation Menu