postgresml / pgcat

PostgreSQL pooler with sharding, load balancing and failover support.
MIT License
3.13k stars 193 forks source link

Request to make a release with sqlparser bumped to the latest version #865

Open cdbridger-tracplus opened 1 week ago

cdbridger-tracplus commented 1 week ago

Hi, we are running into two issues where some of our queries are failing to be parsed by the query parser and then instead of sending the transaction to the primary as our config specifies, sends it to the read-replica.

# If the client doesn't specify, route traffic to
# this role by default.
#
# any: round-robin between primary and replicas,
# replica: round-robin between replicas only without touching the primary,
# primary: all queries go to the primary unless otherwise specified.
# We have used primary here as it appears if query parser succeeds the result is used - this is a fallback
default_role = "primary"

# Query parser. If enabled, we'll attempt to parse
# every incoming query to determine if it's a read or a write.
# If it's a read query, we'll direct it to a replica. Otherwise, if it's a write,
# we'll direct it to the primary.
query_parser_enabled = true

error we get:

Query parsing error: QueryRouterParserError("sql parser error: Expected ;, found: EOF") (client: { application_name: pgcat, username: postgres, pool_name: river })    

and then our replica throws an error as the query it fails to parse is an insert that should have been sent to the primary (at least this is our current working theory).

Exception: 25006: cannot execute COPY during recovery

We are hoping that bumping the parser to the latest version will fix this issue of failing to parse the insert (or if the bug of on failure it routes to the correct default is fixed, or ideally both!)