pg.ddx.io  GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
[pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
14+ messages / 2 participants
[nested] [flat]

* [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  0 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

**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.


^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "minor".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "minor".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "bug".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "java".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "bug".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-23 17:21  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-23 17:21 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "java".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
@ 2026-08-23 17:37  "codecov[bot] (bot) (@codecov[bot])" <noreply+codecov[bot]@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: codecov[bot] (bot) (@codecov[bot]) @ 2026-08-23 17:37 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

## [Codecov](https://app.codecov.io/gh/pgjdbc/pgjdbc/pull/4394?dropdown=coverage&src=pr&el=h1&utm_med...) Report
:x: Patch coverage is `97.29730%` with `1 line` in your changes missing coverage. Please review.
:white_check_mark: Project coverage is 72.43%. Comparing base ([`dca5b05`](https://app.codecov.io/gh/pgjdbc/pgjdbc/commit/dca5b058748b0428ee048749ea759476c584152b?dropdown=cov...)) to head ([`9157f80`](https://app.codecov.io/gh/pgjdbc/pgjdbc/commit/9157f807b9919e01cbcbfec90bb3fa8bdc7876c8?dropdown=cov...)).
:warning: Report is 1 commits behind head on master.

<details><summary>Additional details and impacted files</summary>



```diff
@@             Coverage Diff              @@
##             master    #4394      +/-   ##
============================================
+ Coverage     72.34%   72.43%   +0.09%     
+ Complexity     5485     5467      -18     
============================================
  Files           244      244              
  Lines         22195    22190       -5     
  Branches       3731     3724       -7     
============================================
+ Hits          16056    16074      +18     
+ Misses         4470     4464       -6     
+ Partials       1669     1652      -17     
```
</details>
<details><summary> :rocket: New features to boost your workflow: </summary>

- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
</details>

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-24 07:14  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-24 07:14 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "merge-after-review-window".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-08-24 07:14  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-08-24 07:14 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

I consider this change straightforward and low-risk. Unless there are objections, I plan to merge it after 2026-08-31.

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character
@ 2026-09-10 04:50  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-09-10 04:50 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi removed label "merge-after-review-window".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
@ 2026-11-05 09:10  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-11-05 09:10 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi renamed this.

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
@ 2026-11-05 09:10  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-11-05 09:10 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

@vlsi applied label "release/breaking-change".

^ permalink  raw  [nested|flat] 14+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement
@ 2026-11-05 09:28  "vlsi (@vlsi)" <noreply+vlsi@github.com>
  12 siblings, 0 replies; 14+ messages in thread

From: vlsi (@vlsi) @ 2026-11-05 09:28 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

After more analysis I widened the scope of this PR so that it is coherent from the user's side: on its own, the guard fix made `drop table insert` fail with `42601` when generated keys are requested, so the parser now takes the command type from the first word of a statement. EXPLAIN and some MERGE forms behave differently with generated keys requested (see the description), so the earlier "low-risk" note no longer applies.

I see no performance degradation from moving the matchers to `parseKeyword` (third commit): a typical statement parses in the same time, and only a synthetic worst case is about 10% slower on JDK 21. JMH results against the parent commit, Apple M1 Max, `-f 3 -wi 5 -i 5`, measured while other jobs ran on the machine.

`Parser.parseJdbcSql`, µs per call:

| Input | JDK 8 (8.0.402) | JDK 21 (21.0.9) |
| --- | --- | --- |
| `insert into t(a, b, c) values (?, ?, ?)` | 0.26 → 0.28, within noise | 0.33 → 0.34, within noise |
| `with x as (select 1)` + 10000 × `selecz` | ≈285 → ≈290, within noise | 222 → 245 |
| 10000 × `insera;` | 590 → 775 ± 190, noise exceeds the difference | 400 → 397 |

One matcher, ns per call, unrolled → `parseKeyword`:

| Word | JDK 8 | JDK 21 |
| --- | --- | --- |
| `select` | 3.0 → 3.7 | 1.69 → 1.90 |
| `selecz` | 2.9 → 3.1 | 1.55 → 1.74 |
| `zelect` | 2.2 → 2.4 | 0.76 → 0.81 |

The WITH tail is the input with the most matcher calls per character: each word is tested against UPDATE, DELETE, INSERT, SELECT, and VALUES, and `selecz` matches `select` up to its last character. A typical statement makes a few calls and shows no difference. Moving `keyword.length()` into a local variable changes nothing on either JDK.


^ permalink  raw  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2026-11-05 09:28 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-23 17:21 [pgjdbc/pgjdbc] PR #4394: fix(core): take the command type from the first word of a statement "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:21 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:21 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:21 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:21 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:21 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:21 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-23 17:37 ` "codecov[bot] (bot) (@codecov[bot])" <noreply+codecov[bot]@github.com>
2026-08-24 07:14 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-08-24 07:14 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-09-10 04:50 ` Re: [pgjdbc/pgjdbc] PR #4394: fix(core): stop three keyword matchers demanding an extra character "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-11-05 09:10 ` "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-11-05 09:10 ` "vlsi (@vlsi)" <noreply+vlsi@github.com>
2026-11-05 09:28 ` "vlsi (@vlsi)" <noreply+vlsi@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox