[pgpool-general: 2511] Re: black_function_list in replication mode

[Tatsuo Ishii]

> As the documentation says, a function specified in this property is not load balanced, nor replicated. I seems to me that this only works if you are using master slave mode, because in this mode, these selects queries are sent only to master servers, because in master slave mode, the replication responsability is for slony or streaming replication. The only way to solve this issue is activate replicate_select to true, but is not possible use the load balance functionality with this configuration, because all selects are replicated to all nodes. The other option is use streaming replication if you are using postgresql >= 9.0.
> 
> 
> "Specify a comma separated list of function names that do update the database. SELECTs using functions specified in this list are neither load balanced, nor replicated if in replication mode. In master slave mode, such SELECTs are sent to master(primary) only."

[Scratch head] Sorry, the description in the doc is wrong. Following
is the correction to the doc.

"Specify a comma separated list of function names that do update the
database. SELECTs using functions specified in this list are not load
balanced if in replication mode. In master slave mode, such SELECTs
are sent to master(primary) only."


> Regards.
> ________________________________________
> De: pgpool-general-bounces at pgpool.net [pgpool-general-bounces at pgpool.net] En nombre de Karsten Düsterloh [pgpool-general-ml at tal.de]
> Enviado el: jueves, 30 de enero de 2014 9:05
> Para: 'pgpool-general at pgpool.net'
> Asunto: [pgpool-general: 2508] black_function_list in replication mode
> 
> Hi!
> 
> We're using pgpool-II 3.3.2 with two backends in replication mode.
> We're using a self-defined function which causes INSERTs into the
> database, hence I blacklisted it to get it executed on all backends.
> 
>   white_function_list = ''
>   black_function_list = 'nextval,setval,lastval,currval,tal_clonerow'
> 
> But it doesn't work.

Works for me. SELECTs using functions in the black_function_list
should be sent to all nodes. What do you mean by "doesn't work"
exactly? Can you please show the query using "tal_clonerow"?

> Does changing black_function_list in replication mode require to pgpool
> to *restart*? The docs do not say, so I only made pgpool reload its config …
> 
> Furthermore, I tried to prefix the query:
> 
>   /*NO LOAD BALANCE*/select tal_clonerow('sometable',15579);

Have you tried /*REPLICATION*/ comment?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


> But to no avail either — if I understand correctly, this should even
> work without any blacklisting?!
> 
> 
> JFTR: This is the function in question:
> 
>> Schema              | public
>> Name                | tal_clonerow
>> Result data type    | text
>> Argument data types | regclass, bigint
>> Type                | normal
>> Volatility          | volatile
>> Owner               | admin
>> Language            | plpgsql
>> Source code         |
>>                     : declare iResult bigint; sResult text;
>>                     : begin
>>                     : if tal_refcheck($1,$2) then
>>                     :   execute 'select ''INSERT INTO "'||$1||'"("''||string_agg(column_name,''","'')||''") SELECT "''||string_agg(column_name,''","'')||''" FROM "'||$1||'" WHERE id='||$2||' returning id;'' from information_schema.columns where table_name='''||$1||''' and column_name!=''id'';' into sResult;
>>                     :   execute sResult into iResult;
>>                     :   return iResult;
>>                     : end if;
>>                     : return null;
>>                     : end;
> 
>> Schema              | public
>> Name                | tal_refcheck
>> Result data type    | boolean
>> Argument data types | regclass, integer
>> Type                | normal
>> Volatility          | volatile
>> Owner               | admin
>> Language            | plpgsql
>> Source code         |
>>                     : declare iResult int;
>>                     : begin
>>                     : if $1 is null and $2 is null then return null; elsif $1 is null or $2 is null then return false; end if;
>>                     : execute 'select id from '||$1||' where id='||$2 into iResult;
>>                     : return iResult is not null;
>>                     : end;
> 
> Karsten
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
> 
> Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
> http://www.antiterroristas.cu
> http://justiciaparaloscinco.wordpress.com
> 
> Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
> http://www.antiterroristas.cu
> http://justiciaparaloscinco.wordpress.com
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general