jackc / jackc/pgx

`EXPLAIN (GENERIC_PLAN)` does not correctly handle 0 args

Open
#2,133 3 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Go
Stars
14.3k
Forks
1.1k
Avg merge
6d 9h
Merged PRs (30d)
11

Description

**Describe the bug**

When using `EXPLAIN (GENERIC_PLAN)` on a [PG16 database](https://www.postgresql.org/docs/16/sql-explain.html), pgx incorrectly tries to assign values from the args to `Query` to the query parameters. In this scenario it should probably error if _any_ args are provided instead.

**To Reproduce**

- Given the database schema (in a postgres 16 DB)
```sql
create table items (
item_id integer not null,
name text not null,

primary key (item_id)
);
```

- And the following go code:

```go
// main.go
package main

import (
"context"
"log"
"os"
"os/signal"

"github.com/jackc/pgx/v5"
)

func main() {
ctx, cancel := signal.NotifyContext(context.Background(), os.Interrupt)
defer cancel()

conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal("opening db connection: ", err)
}
defer func() { _ = conn.Close(ctx) }()

var plan string
if err := conn.QueryRow(
ctx,
`explain (generic_plan) select items.name from items where items.item_id = $1`,
// ! passes no args, as we want to understand the generic query plan
).Scan(&plan); err != nil {
log.Fatal("executing query: ", err)
}

fmt.Println(plan)
}
```

> I'll note this also applies to the latest patch of `pgx/v4`, where I first found this

- Running

```shell
go run -race ./main.go
```

- Gives

```
2024/09/27 19:33:00 executing query: expected 1 arguments, got 0
exit status 1
```

**Expected behavior**

I expected pgx to recognise that `explain (generic_plan)` does not require arguments to be provided, accept the query, and return the query plan provided by the database

PSQL correctly returns the query plan as expected

```
$ psql --no-psqlrc --tuples-only -c 'explain (generic_plan) select items.name from items where items.item_id = $1'
Index Scan using items_pkey on items (cost=0.15..8.17 rows=1 width=32)
Index Cond: (item_id = $1)
```

**Actual behavior**

pgx returns an error, as no arguments were provided to the query.

```
expected 1 arguments, got 0
```

**Version**

- Go: `go version go1.23.1 darwin/arm64`
- PostgreSQL: `PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit` (a freshly installed docker container running on an M1 Pro)
- pgx: `github.com/jackc/pgx/v5 v5.7.1`
- also present in `github.com/jackc/pgx/v4 v4.18.3`

**Additional context**

- [pq](https://github.com/lib/pq) correctly handles this behaviour, but I suspect this is more by luck than design as it [short-circuits to `simpleQuery`](https://github.com/lib/pq/blob/master/conn.go#L901-L905) if no args are provided

Contributor guide

Open the contributing guide

Research direction

Start at the conn.QueryRow entry point and reproduce the reported behavior against PostgreSQL 16 using the provided Go example. Trace how EXPLAIN (GENERIC_PLAN) handles its placeholders when no arguments are supplied; done means the query returns the generic plan as expected and argument handling remains correct.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
backend, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.