[pgpool-general: 639] Forwarding session settings from slave to master

[Kouber Saparev]

Hello,

Is there some special parsing check for the SET command in pgpool?

I am asking, because when I use SET in a stored procedure (in EXECUTE
context), the setting is lost, once the connection is forwarded to the
master (because of a DML statement), when using pgpool in load-balancing
mode. If I set the setting explicitly with the SET command, then the
behaviour is correct - the value is forwarded to the master.

Here's the test environment. I am using custom_variable_classes.

The master is on 192.168.10.21, the slave - 192.168.10.20.

CREATE TABLE xxx (
  user_sid int default CURRENT_SETTING('session.user_sid')::int,
  server inet default INET_SERVER_ADDR(),
  pid int default PG_BACKEND_PID(),
  datetime timestamp not null default NOW()
);

CREATE FUNCTION set_var(text, int) RETURNS void AS $$
BEGIN
  EXECUTE 'SET session.' || QUOTE_IDENT($1) || ' TO ' || $2;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;



So, I connect to pgpool through the psql client, and then:

db=# SELECT set_var('user_sid', 666);
 set_var
---------
 
(1 row)

db=# SELECT CURRENT_SETTING('session.user_sid'), INET_SERVER_ADDR();
 current_setting | inet_server_addr
-----------------+------------------
 666             | 192.168.10.20
(1 row)

db=# INSERT INTO xxx (user_sid) VALUES (default) RETURNING user_sid, server;
 user_sid |    server    
----------+---------------
        0 | 192.168.10.21
(1 row)

INSERT 0 1

db=# SELECT CURRENT_SETTING('session.user_sid'), INET_SERVER_ADDR();
 current_setting | inet_server_addr
-----------------+------------------
 666             | 192.168.10.20
(1 row)



However, if I set it with the SET command, then the value is forwarded:

db=# SET session.user_sid TO -1;
SET

b4=# SELECT CURRENT_SETTING('session.user_sid'), INET_SERVER_ADDR();
 current_setting | inet_server_addr
-----------------+------------------
 -1              | 192.168.10.20
(1 row)

db=# INSERT INTO xxx (user_sid) VALUES (default) RETURNING user_sid, server;
 user_sid |    server    
----------+---------------
       -1 | 192.168.10.21
(1 row)

INSERT 0 1



Why is that difference?


Regards,
--
Kouber Saparev