Connection through Pgpool not working

[bigunmd]

What language are you using?.

Python3.9

What version are you using?

0.3.2

What database are you using?

PostgreSQL

What dataframe are you using?

Pandas

Can you describe your bug?

Connection through pgpool to postgresql instance results with "RuntimeError: connection closed". Direct connection works as intended.

What are the steps to reproduce the behavior?

Example from "Getting started" section of the connectorx package and docker-compose with pgpool

version: '3.8'
services:
  pgprimary:
    image: bitnami/postgresql:13.1.0
    ports:
      - 5432
    volumes:
      - pgprimary_data:/bitnami/postgresql
    environment:
      - POSTGRESQL_REPLICATION_MODE=master
      - POSTGRESQL_REPLICATION_USER=postgres
      - POSTGRESQL_REPLICATION_PASSWORD=postgres
      - POSTGRESQL_PASSWORD=postgres
      - POSTGRESQL_DATABASE=test_db
  pgreplica:
    image: bitnami/postgresql:13.1.0
    ports:
      - 5432
    depends_on:
      - pgprimary
    environment:
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=postgres
      - POSTGRESQL_REPLICATION_PASSWORD=postgres
      - POSTGRESQL_MASTER_HOST=pgprimary
      - POSTGRESQL_PASSWORD=postgres
  pgpool:
    image: bitnami/pgpool:4.2.1
    ports:
      - 5432:5432
    depends_on:
      - pgprimary
    environment:
      - PGPOOL_BACKEND_NODES=0:pgprimary:5432:4:primary:ALWAYS_PRIMARY,1:pgreplica:5432:6:replica
      - PGPOOL_POSTGRES_USERNAME=postgres
      - PGPOOL_POSTGRES_PASSWORD=postgres
      - PGPOOL_ADMIN_USERNAME=admin
      - PGPOOL_ADMIN_PASSWORD=postgres
      - PGPOOL_ENABLE_LOAD_BALANCING=yes
      - PGPOOL_ENABLE_STATEMENT_LOAD_BALANCING=yes
      - PGPOOL_NUM_INIT_CHILDREN=10
      - PGPOOL_MAX_POOL=1
      - PGPOOL_SR_CHECK_USER=postgres
      - PGPOOL_SR_CHECK_PASSWORD=postgres
volumes:
  pgprimary_data:

Database setup if the error only happens on specific data or data type

Example query / code

import connectorx as cx

cx.read_sql("postgresql://postgres:postgres@127.0.0.1:5432/postgres?sslmode=disable", "select * from information_schema.tables")

What is the error?

Traceback (most recent call last):
File "/home/mikhail/sandbox/py/test.py", line 3, in
cx.read_sql("postgresql://postgres:postgres@127.0.0.1:5432/postgres?sslmode=disable", "select * from information_schema.tables")
File "/home/mikhail/sandbox/py/.venv/lib/python3.9/site-packages/connectorx/init.py", line 264, in read_sql
result = _read_sql(
RuntimeError: connection closed

[wangxiaoying]

Seems like your database name is test_db instead of postgres. Can you try changing your connection string to postgresql://postgres:postgres@127.0.0.1:5432/test_db?sslmode=disable?

[bigunmd]

That's not the case, just a typo in the local stand yaml....

We have a Pgpool based cluster with data on a production server and we can access it with connectorx via normal route to the one of the replicas or master, but going through Pgpool is messed up. Feels like the driver laying behind connectorx is not waiting for query response and due to Pgpool rerouting mechanism connection closes before query result is acknowledged by the client...