[pgpool-general: 115] black_function_list seems does not works

Lazaro Rubén García Martinez lgarciam at vnz.uci.cu
Sat Dec 24 06:43:39 JST 2011


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.


More information about the pgpool-general mailing list