[pgpool-hackers: 2023] question about Pgpool's state machine

[Sergey Kim]

Dear Pgpool Community,


Nowadays we perform very deep tests of the Pgpool 3.6.1 with PostgreSQL 9.6. Probably you see the activeness of the "supp_k" account in your bug tracker. The tests are performed because the pgpool was choosen as the basis of the HA solution which was developed in order to provide performance, scalability and reliability for our product.

We have one question which we cann't answer without understanding how the Pgpool's state machine works. We could reverse enjineer the source code but your expert's opinion will be much more appreciated.

The majority of the program logic which was implemented in our product runs on the top of the JBoss AS. The JBoss AS has internal jdbc connection pools which reuse database connections. Due to some unknown reasons sometimes we see that connections returned into that pools (datasources) have not committed transactions (explicit). It can happen because the program logic fails and compensation schemas are not implemented (e.g. try-catch-finally) or probably we face Hibernate errors. But anyway sometimes at happens.

In order to fight not closed explicit transactions we configured datasource validation queries as "ROLLBACK" and the validation is performed in background. This solution helps us to close that uncommited or not rollback-ed connections. And still it does validation of connections.  This is the way we go now...  If there are not committed active transactions then we observer "IDLE IN TRANSACTION" connection states and for instance  such cases impact Autovacuuming.

But unfortunately we see the PostgreSQL generates log messages "WARNING:  there is no transaction in progress". The same warning is observed in Pgpool logs. So technically the warning is correct and absolutely logical. We not worry when it comes to PostgreSQL database, because the PostgreSQL is still ok and operates healthy. Warning are warnings as for now!
But we wonder how these "inconsistent" demands to rollback the none existing transactions can impact he Pgpool's behaviour. In several cases we think we could observer it hangs - certain connections hang I mean. But we are not sure we can reproduce the case.

So we would like to ask for your expert opinion on the question and possible impact on the Pgpool.


Thank you!


Sergey Kim / HA architect
ODIN Ingram Micro Cloud
skim at odin.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20170201/6be556ae/attachment.html>