[pgpool-general: 120] Re: black_function_list seems does not works

Lazaro Rubén García Martinez lgarciam at vnz.uci.cu
Mon Dec 26 11:07:01 JST 2011


Tatsuo thank you very much for your help, The problem was solved rewriting the store procedure and removing the temporary table in it.

Regards.

-----Mensaje original-----
De: Tatsuo Ishii [mailto:ishii at postgresql.org] 
Enviado el: domingo, 25 de diciembre de 2011 06:28:PM
Para: Lazaro Rubén García Martinez
CC: pgpool-general at pgpool.net
Asunto: Re: [pgpool-general: 116] black_function_list seems does not works

> Tatsuo, thank you very much for your answer:
> 
> Then, I need send the statements separately?

Yes.

> If I execute the query like this:
> 
> $query = "/*No LOAD BALANCE*/BEGIN; SELECT * from fun_comun_obtener_datos_contacto_persona('w', '$idUsuario', $tipoDocumento); FETCH ALL IN w;";
> 
> This query is sent to the primary server?

Yes.

> Regards, and happy new year.

A happy new year!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> -----Mensaje original-----
> De: Tatsuo Ishii [mailto:ishii at postgresql.org] 
> Enviado el: domingo, 25 de diciembre de 2011 07:00:AM
> Para: Lazaro Rubén García Martinez
> CC: pgpool-general at pgpool.net
> Asunto: Re: [pgpool-general: 116] black_function_list seems does not works
> 
> Pgpool does not support multi statements. You should send query like this:
> 
> BEGIN;
> SELECT * from fun_comun_obtener_datos_contacto_persona('w', '647f9d6a-d994-4e2a-b74e-b6408bb0c782', 1);
> FETCH ALL IN w;
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
>> Hello everyone in the list, I am having a problem with streaming replication and pgpool. In my work there is an application in php. The app runs a query with a store procedure. In the body of the store procedure, a temporary table is created, for this reason this query should be sent to the primary server only. But, the query go to the standby server and throws an error. This store procedure is in black_function_list like a regular expression, but I do not know why the query goes to standby node.
>> 
>> The query sends by php to pgpool is this:
>> 
>> $query = "BEGIN; SELECT * from fun_comun_obtener_datos_contacto_persona('w', '$idUsuario', $tipoDocumento); FETCH ALL IN w;";
>> 
>> This is the configuration of pgpool.conf file:
>> 
>> black_function_list = 'currval,lastval,nextval,setval,.*insertar.*,.*eliminar.*,.*modificar.*,.*crear.*,fun_aprobar_tramite,fun_cita.*,.*cancelar.*,fun_asignar_rol,.*contacto.*' 
>> 
>> The pgpool and postgresql log files are like these:
>> 
>> PostgreSQL log file:
>> 
>> 308 2011-12-23 11:57:03 VET LOG:  statement: BEGIN; SELECT * from fun_comun_obtener_datos_contacto_persona('w', '647f9d6a-d994-4e2a-b74e-b6408bb0c782', 1); FETCH ALL IN w;
>> 308 2011-12-23 11:57:03 VET ERROR:  cannot execute CREATE TABLE in a read-only transaction
>> 308 2011-12-23 11:57:03 VET CONTEXT:  SQL statement "CREATE TEMPORARY TABLE tbl_tmp_datos_contacto(
>> 	      pk_tipo_contacto integer,
>> 	      valor_contacto text
>> 	    ) ON COMMIT DROP"
>> 	PL/pgSQL function "fun_comun_obtener_datos_contacto_persona" line 9 at SQL statement
>> 
>> 
>> Pgpool log file:
>> 
>> 2011-12-23 11:57:03 LOG:   pid 1194: connection received: host=10.8.8.8 port=50767
>> 2011-12-23 11:57:03 ERROR: pid 1194: read_kind_from_backend: 1 th kind E does not match with master or majority connection kind D
>> 2011-12-23 11:57:03 ERROR: pid 1194: kind mismatch among backends. Possible last query was: "BEGIN; SELECT * from fun_comun_obtener_datos_contacto_persona('w', '647f9d6a-d994-4e2a-b74e-b6408bb0c782', 1); FETCH ALL IN w;" kind details are: 0[D] 1[E: cannot execute CREATE TABLE in a read-only transaction]
>> 2011-12-23 11:57:03 LOG:   pid 1194: do_child: exits with status 1 due to error
>> 
>> 
>> I tested this , including /*NO LOAD BALANCE*/ before the query, but the problem persist:
>> 
>> $query = "BEGIN; /*No LOAD BALANCE*/SELECT * from fun_comun_obtener_datos_contacto_persona('w', '$idUsuario', $tipoDocumento); FETCH ALL IN w;";
>> 
>> Could someone in the list, help me to solve this problem?
>> Could be this a pgpool bug?
>> 
>> I am using pgpool-II 3.1.1 with postgresql-9.0.6
>> 
>> Thank you very much for your time.
>> Regards.
>> 
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list