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

Lazaro Ruben Garcia Martinez lgarciam at vnz.uci.cu
Thu Jan 30 23:38:19 JST 2014


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."

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.

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);

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


More information about the pgpool-general mailing list