[pgpool-general: 7042] Re: PREPARE STATMENTS

[Bo Peng]

Hello,

On Tue, 26 May 2020 18:33:05 +0200
Franco Ricci <franco.ricc at gmail.com> wrote:

> Prepared statement are wide used to avoid SQL injection and to speed up DB response in case of SELECT that are always the same.
> Why is not possible balance  prepared statement?
> It could bu useful if I can use something like “black_query_pattern_list” but in white flavour…

To avoid EXECUTE error like "ERROR:  prepared statement ... does not exist",
pgpool doesn't load-balance "PREPARE/EXECUTE/DEALLOCATE".

- The current feature of load balance:

If you set "disable_load_balance_on_write = transaction" (default),
when a write query is issued inside an explicit truncation, 
subsequent queries will be sent to Primary only until the end
of this transaction in order to avoid the replication delay.

If pgpool load balances "PREPARE/EXECUTE/DEALLOCATE",
after issuing a WRITE query, "EXECUTE" will be sent to Primary
and it can cause "ERROR:  prepared statement ... does not exist".

Example:
----------------------------------------
BEGIN;
  PREPARE test AS SELECT 1;     -- Load balanced to standby
  EXECUTE test;                 -- Load balanced to standby
  UPDATE ...                    -- WRITE query to primary
  EXECUTE test;                 -- Don't be load balanced because a WRITE query was issued.
                                   "ERROR:  prepared statement test does not exist"
----------------------------------------

To load balance "PREPARE/EXECUTE/DEALLOCATE" and avoid an error,
pgpool needs to run "PREPARE" again on Primary after issuing a WRITE query.
However, currently pgpool doesn't have such a feature.

It may be considered in future development.

Regards,

> Franco
> 
> > On 26 May 2020, at 09:43, Bo Peng <pengbo at sraoss.co.jp> wrote:
> > 
> > hello,
> > 
> > On Fri, 22 May 2020 17:58:00 +0200
> > Franco Ricci <franco.ricc at gmail.com> wrote:
> > 
> >> I’s possible specify which server should execute a "prepared statement"?
> >> I noticed in pgpool 4.1 that ordinary query are routed on master or slave server according query action (SELECT, UPDATE etc.).
> >> If I create a PREPARE STATEMENT this query is routed always on master server and never executed on slave even if statements is only a SELECT query!
> >> 
> >> There is a way to programmatic set which is the server that has to server a prepared statement?
> > 
> > No.
> > Pgpool doesn't load balance "prepared statement".
> > "Prepared statement" should be sent to primary only.
> > 
> >> _______________________________________________
> >> pgpool-general mailing list
> >> pgpool-general at pgpool.net
> >> http://www.pgpool.net/mailman/listinfo/pgpool-general
> > 
> > 
> > -- 
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS, Inc. Japan
> 


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan