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

[liujinyang-highgo]

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:

1. create PROCEDURE will be executed on primary node.

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

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

call sub_proc1();

3. 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)

4. 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？

[tatsuo-ishii]

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