[pgpool-hackers: 4273] multi-statement + DEALLOCATE error

[Tatsuo Ishii]

While taking care of this:
https://www.pgpool.net/mantisbt/view.php?id=780

I found that pgppol raises a kind mismatch error in the following
condition:

- streaming replication mode
- load balance node is other than primary
- PREPARE is used in a multi-statement query

Step to reproduce:

1) create 2 node streaming replication cluster
   $ pgpool_setup

2) set load balance node to backend 1
   $ echo "backend_weight0 = 0" >> etc/pgpool.conf

3) start pgpool
   ./startall

4) issue following queries

   SELECT 1\;PREPARE foo;
   DEALLOCATE foo;

The error is caused by a bug in pool_where_to_send(). For DEALLOCATE
it sets the nodes to be sent to all backend if pgpool failed to find a
prepared statement previously received. For "SELECT 1\;PREPARE foo;",
pgpool ignores "PREPARE" part and just sends the multi-statement query
to primary. So primary actually has the prepared statement "foo" but
pgpool thinks that there's no prepared statement named "foo". And
pgpool sends DEALLOCATE to both primary and standby, then a kind
mismatch error raised. Fix is, just sending DEALLOCATE to primary node
in this case if pgpool is in streaming replication mode.  Same thing
can be said to EXECUTE too and should be fixed.

I also found another bug: in replication mode or SI mode, pgpool needs
to send multi-statement query to all backend because the
multi-statement query maybe a write query. However pgpool sends to
main node only in this case.

I am going to fix those issues.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp