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

[Tatsuo Ishii]

It seems you need to prevent set_var() from load balancing.
Please consider to use black_function_lis/white_function_list
or /*NO LOAD BALANCE*/ comment.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general