[pgpool-hackers: 4292] Occasional 005.jdbc test failure

[Tatsuo Ishii]

We occasionaly see 005.jdbc test failure. Typical error is something
like this:

2023-02-22 08:51:47.704: PostgreSQL JDBC Driver pid 12420: LOG:  DB node id: 0 backend pid: 12488 statement: Parse: COMMIT
2023-02-22 08:51:47.705: PostgreSQL JDBC Driver pid 12420: LOG:  pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 12488 statement: "COMMIT" message: "prepared statement "S_1" already exists"
2023-02-22 08:51:47.705: PostgreSQL JDBC Driver pid 12420: LOG:  Parse: Error or notice message from backend: : DB node id: 0 backend pid: 12488 statement: "COMMIT" message: "prepared statement "S_1" already exists"

I found possible cause of the error two seconds ago:

2023-02-22 08:51:45.241: PostgreSQL JDBC Driver pid 12420: LOG:  Sync message from frontend.
2023-02-22 08:51:45.241: PostgreSQL JDBC Driver pid 12420: LOG:  Terminate message from frontend.
2023-02-22 08:51:45.241: PostgreSQL JDBC Driver pid 12420: LOG:  DB node id: 0 backend pid: 12488 statement: DISCARD ALL
2023-02-22 08:51:45.242: PostgreSQL JDBC Driver pid 12420: LOG:  pool_send_and_wait: Error or notice message from backend: : DB node id: 0 backend pid: 12488 statement: "DISCARD ALL" message: "DISCARD ALL cannot be executed within a pipeline"

"DISCARD ALL" was generated by pgpool (reset_query_list) to discard
some objects including prepared statements created in the
session. Since DISCARD ALL failed, the prepared statement S_1 was not
removed. Thus the next session failed because S_1 already existed.

Question is why "DISCARD ALL cannot be executed within a pipeline"
error?

So I consult PostgreSQL document:
https://www.postgresql.org/docs/15/protocol-flow.html#PROTOCOL-FLOW-PIPELINING

"However, there are a few DDL commands (such as CREATE DATABASE) that
cannot be executed inside a transaction block. If one of these is
executed in a pipeline, it will fail unless it is the first command in
the pipeline."

"DISCARD ALL" is on the same boat as CREATE DATABASE (i.e. cannot be
executed inside a transaction block). It seems we have to wait for
pipeline being closed before issuing DISCARD ALL. How? Our PostgreSQL
doc says:

"When using this method, completion of the pipeline must be determined
by counting ReadyForQuery messages and waiting for that to reach the
number of Syncs sent."

This means that we should not issue DISCARD ALL before receiving at
least one ReadyForQuery from backend.

Waiting for ReadyForQuery is not a big deal. But pgpool cannot
unconditionaly wait for ReadyForQuery. What if ReadyForQuery was
already sent? I am going to study this issue.

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