From: vlsi (@vlsi) <noreply+vlsi@github.com>
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
Subject: [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
Date: Sun, 23 Aug 2026 17:21:09 +0000
Message-ID: <gh-pgjdbc-pgjdbc-4394@github.com> (raw)
**Breaking changes.** With generated keys requested:
- An `EXPLAIN` without a `RETURNING` clause of its own no longer gets `RETURNING` appended. `execute` returns the plan through `getResultSet()` and `getGeneratedKeys()` is empty, where the plan rows came back as generated keys. `PreparedStatement.executeUpdate` throws `A result was returned when none was expected.` (SQLState `0100E`), and `Statement.executeUpdate` returns -1, where both returned 0. This is the second commit, and it can be dropped on its own.
- A `MERGE` whose source is a subquery, or whose actions are all `DO NOTHING`, now gets `RETURNING` appended. PostgreSQL 17 returns its rows as generated keys, and PostgreSQL 15 and 16 reject it with SQLState `42601`, as they already rejected a `MERGE` with a table source and an `UPDATE` action.
`Parser.parseJdbcSql` typed a statement by the first command keyword anywhere in it, and with generated keys requested it appends `RETURNING` to INSERT, UPDATE, DELETE, and WITH. So a later word decided the command:
```java
con.prepareStatement("drop table insert /* c */", Statement.RETURN_GENERATED_KEYS).execute();
// ERROR: syntax error at or near "RETURNING" (SQLState 42601)
```
The same happened to `listen insert /* channel */`, `copy t to stdout with (format csv)`, and `copy (insert into t values (1) returning a) to stdout`. In `notify update; select 1`, the `select 1` was typed UPDATE and got `RETURNING`.
The first commit takes the command type from the first word of a statement; WITH still takes it from its main statement. `EXPLAIN` with its options and `PREPARE name AS` are skipped, so the statement they wrap behaves as before. `MERGE` gets a command type of its own, because it used to get `RETURNING` only through an `UPDATE`, `DELETE`, or `INSERT` action that came before any `SELECT` in its text.
The second commit stops skipping `EXPLAIN`, which generates no keys. Per the [`Statement.execute(String, int)` javadoc](https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#execute-java.lang.String-int-), the driver ignores the flag when the statement "is not an INSERT statement, or an SQL statement able to return auto-generated keys".
The third commit is the original change of this PR: `parseInsertKeyword`, `parseBeginKeyword`, and `parseAtomicKeyword` required one character more than the keyword they compare. Every public `parse…Keyword` method now calls one private `parseKeyword`, which takes the length check from the keyword itself. The loop costs up to 0.7 ns per call more than the unrolled comparison: the JMH benchmarks `ParseJdbcSql` and `KeywordMatcher` in the same commit show no difference on a typical INSERT, and about 10% on JDK 21 for a WITH followed by 10000 words that miss SELECT only in the last character. On its own, the guard fix made `drop table insert` fail the way `drop table insert ` already did; after the first commit it changes nothing a driver user can observe.
**Scope.** #4367 changes the same code in `Parser.parseJdbcSql` and is to be rebased onto this PR: it types every statement of a multi-statement string, where only the first can be typed INSERT now, and it credits the word before `;` to the statement that word ends, where this PR drops the word. These stay as they are on master: a WITH whose main statement is `VALUES` or `TABLE` (`with x as (select 1) values (1)`) gets `RETURNING` and fails with `42601`; `Statement.executeUpdate(sql, RETURN_GENERATED_KEYS)` ignores a result set instead of throwing; an EXPLAIN with its own `RETURNING` still returns the plan as generated keys. Is the MERGE change on PostgreSQL 15 and 16 acceptable? The parser does not know the server version, and leaving MERGE untyped would drop the generated keys that `merge into t using s … when matched then update …` returns on PostgreSQL 17.