ERROR: DISCARD ALL cannot run inside a transaction block when connection closed uncleanly

[drdrsh]

Describe the bug

I am working on a test bench to test some edge cases against pgcat. Opening a transaction and then closing the client connection causes pgcat to issue ROLLBACK; DISCARD ALL, however this behavior triggers ERROR: DISCARD ALL cannot run inside a transaction block error on the server. Might be something wrong with my setup or an issue with pgcat

To Reproduce
Put the following 3 files in the same directory and run docker compose up

docker-compose.yml

version: '3'
services:
  pg:
    image: postgres:12-bullseye
    environment:
      POSTGRES_HOST_AUTH_METHOD: trust
      POSTGRES_DB: main_db
      POSTGRES_USER: main_user

  pgcat:
    build: https://github.com/levkk/pgcat.git#main
    restart: always
    command: ["pgcat", "/etc/pgcat/pgcat.toml"]
    links:
      - pg
    volumes:
      - "./config.toml:/etc/pgcat/pgcat.toml"

  app:
    image: ruby
    links:
      - pgcat
    volumes:
      - "./entrypoint.rb:/app/entrypoint.rb"
    command: bash -c "gem install pg && ruby /app/entrypoint.rb"

entrypoint.rb

require 'pg'
$stdout.sync = true

def poorly_behaved_client
    conn = PG::connect("postgres://main_user:@pgcat:5432/main_db")
    conn.async_exec("BEGIN")
    conn.async_exec("SELECT 1")
    conn.close
rescue Exception => e
    puts "Encountered #{e}"
end

sleep 5
loop { poorly_behaved_client }

config.toml

[general]
host = "0.0.0.0"
port = 5432
pool_size = 5
pool_mode = "transaction"
connect_timeout = 5000
healthcheck_timeout = 1000000
ban_time = 1 # Seconds
[user]
name = "main_user"
password = ""
[shards]
[shards.0]
servers = [[ "pg", 5432, "primary" ]]
database = "main_db"
[query_router]
default_role = "primary"
query_parser_enabled = false
primary_reads_enabled = false
sharding_function = "pg_bigint_hash"

Expected behavior
No server errors.

[levkk]

@drdrsh Hey, tried to fix it in #65, hope it helps. I couldn't reproduce it locally, as is tradition with Postgres connection abnormalities :)

Worst case we can just close the connection instead of re-using it, but that takes us back to Pgbouncer connection thrashing which we don't want, so if you come up with the root cause and solution, it would be awesome.

[drdrsh]

That fix worked. I am not able to reproduce this anymore.
I still do not understand how it is possible that the DISCARD ALL ends up running in a transaction.

[drdrsh]

@levkk I think I understand what happened here. Docs for PQexec state that

The command string can include multiple SQL commands (separated by semicolons). Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions.

This behavior can be demonstrated in ruby by doing the following

require 'pg'
conn = PG::connect("some_url")
conn.async_exec("BEGIN")
conn.async_exec("ROLLBACK;DISCARD ALL")

This will fail with ERROR: DISCARD ALL cannot run inside a transaction block (PG::ActiveSqlTransaction)

[levkk]

Very nice find! Thank you.