Transactions are getting failed when connection pooler is enabled, The response is coming from the connection pooler cache instead of DB

Hi All

We have issue with postgres operator pod when application is connected to connection pooler and from connection pooler to Database.

The transaction status response is coming from the connection pooler cache instead of the database. So due to this the transaction status is not updating from the database. Due to this our application is not getting the real time status from the connection pooler and transaction is getting failed.

Postgress operator version : 1.8.2
postgresql version : 14

Yaml file configuration:

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
name: acid-minimal-cluster
namespace: default
spec:
#nodeName: worker-1
teamId: "acid"
nodeAffinity:
requiredDuringSchedulingIgnoredDuringExecution:
nodeSelectorTerms:
- matchExpressions:
- key: node-type
operator: In
values:
- worker
volume:
size: 20Gi
numberOfInstances: 3
users:
zalando: # database owner
- superuser
- createdb
foo_user: [] # role for application foo
databases:
foo: zalando # dbname: owner
preparedDatabases:
bar: {}
postgresql:
version: "14"
enableLogicalBackup: true
logicalBackupSchedule: "40 07 * * *"
enableMasterLoadBalancer: true
enableReplicaLoadBalancer: true
enableConnectionPooler: true # enable/disable connection pooler deployment
enableReplicaConnectionPooler: true # set to enable connectionPooler for replica service
allowedSourceRanges: # load balancers' source ranges for both master and replica services

• 127.0.0.1/32
  connectionPooler:
  numberOfInstances: 2

Start by reproducing the transaction-status problem with the supplied PostgreSQL operator YAML, using PostgreSQL 14 and connection pooling enabled. Compare the status returned through the connection pooler with the database status; done means the application receives the current transaction status rather than a cached response.