pg.ddx.io  psycopg/psycopg GitHub issues and pull requests  
help / color / mirror / Atom feed
[psycopg/psycopg] issue #1499: executemany() commits different rows depending on the libpq version, and psycopg keeps stale prepared statements after a repeated DROP or DISCARD ALL
3+ messages / 2 participants
[nested] [flat]

* [psycopg/psycopg] issue #1499: executemany() commits different rows depending on the libpq version, and psycopg keeps stale prepared statements after a repeated DROP or DISCARD ALL
@ 2026-11-17 17:07  "turbolytics (@turbolytics)" <noreply+turbolytics@github.com>
  0 siblings, 0 replies; 3+ messages in thread

From: turbolytics (@turbolytics) @ 2026-11-17 17:07 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

I audited what psycopg guarantees when something fails, and whether every way of doing the same thing gives the same result. Two findings affect everyday code. Four narrower ones follow. Everything below is on `master` (cf390ed), with Postgres 17. The libpq 13 run uses the 3.3.9 release, which has the same `executemany()` code.

### 1. `executemany()` commits different rows depending on the libpq version

In autocommit, one row of the batch fails. With libpq 14 or newer, the server commits no row. With libpq 13, it commits the rows before the failure. It is the same psycopg version and the same code.

```python
import psycopg

with psycopg.connect(DSN, autocommit=True) as conn:
    conn.execute("drop table if exists t")
    conn.execute("create table t (v int primary key)")
    try:
        conn.cursor().executemany("insert into t values (%s)", [(1,), (2,), (2,), (3,)])
    except psycopg.errors.UniqueViolation:
        pass
    print(psycopg.pq.version(), conn.execute("select v from t order by v").fetchall())

# 180003 []                 libpq 18.3 (macOS)
# 130023 [(1,), (2,)]       libpq 13.23 (Rocky Linux 8, the pure-Python implementation)
```

**Cause.** `executemany()` uses pipeline mode only when `Pipeline.is_supported()`, which needs libpq 14. Otherwise it runs a loop ([cursor.py#L128-L146](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg/psycopg/cur...)). In pipeline mode, the server wraps the batch in one implicit transaction, even in autocommit ([pipeline.rst#L233-L235](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/docs/advanced/pipel...)). The loop follows the autocommit rule that statements are not atomic ([transactions.rst#L144-L150](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/docs/basic/transact...)). The docs say that `executemany()` uses pipeline mode with libpq 14 or newer ([cursors.rst#L156-L158](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/docs/api/cursors.rs...)). They present it as a performance change. They do not say that it changes which rows an autocommit batch commits.

RHEL 8 and its rebuilds ship libpq 13, and the pure-Python implementation uses the system libpq. Is the difference intended? If so, I can send a docs PR.

### 2. psycopg keeps stale prepared statements after a repeated `DROP` or `DISCARD ALL`

psycopg discards its prepared statements after `DROP`, `ALTER`, `DISCARD ALL` or `DEALLOCATE ALL` (#1307, #1409). It checks a statement only the first time it sees it. If the same statement ran once before anything was prepared, as a test fixture or a migration does, psycopg does not check the next run.

```python
import psycopg

conn = psycopg.connect(DSN, autocommit=True)

def reset(column_type):
    conn.execute("drop table if exists t")
    conn.execute(f"create table t (v {column_type})")

reset("text")                    # the DROP runs before anything is prepared
for _ in range(6):               # past prepare_threshold, so psycopg prepares the insert
    conn.execute("insert into t (v) values (%s)", ["x"])
reset("int")                     # the same DROP: psycopg keeps the prepared insert
conn.execute("insert into t (v) values (%s)", ["42"])
# DatatypeMismatch: column "v" is of type integer but expression is of type text
```

With `DISCARD ALL`, which #1307 names as the escape hatch, psycopg keeps using statements that the server has deallocated:

```python
conn = psycopg.connect(DSN, autocommit=True)
conn.execute("discard all")      # once, before anything is prepared
for _ in range(6):
    conn.execute("select %s::text", ["x"])
conn.execute("discard all")
conn.execute("select %s::text", ["x"])
# InvalidSqlStatementName: prepared statement "_pg3_0" does not exist
```

With `prepare_threshold=None`, both examples work. `ALTER`, `DEALLOCATE ALL`, transactions and pipeline mode behave the same way.

**Cause.** `_should_discard()` runs from `validate()`. The cursor calls `validate()` only when `maybe_add_to_cache()` returns a key, which happens only for a query that is in neither `_counts` nor `_names` ([_preparing.py#L140-L158](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg/psycopg/_pr...), [_cursor_base.py#L302-L315](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg/psycopg/_cu...)). The first `DROP` ran while nothing was prepared, so it cleared nothing and stayed in `_counts`. psycopg has already counted the statement, so it checks no later run.

### 3. The C implementation loads very large intervals as wrong values

```python
conn.execute("select interval '12000000 years'").fetchone()[0]
# C:      datetime.timedelta(days=85032704)
# Python: DataError: can't parse interval ... Python int too large to convert to C int
```

`'178000000 years'` loads as 545,490,560 days, and `'2000000000 days 6000000 years'` loads as a negative interval. Text and binary formats behave the same way.

**Cause.** The C loaders add `365 * years` into a 32-bit `int` ([datetime.pyx#L886](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg_c/psycopg_c...), [#L916](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg_c/psycopg_c...), [#L998-L1003](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg_c/psycopg_c...)). The sum wraps. When the result fits a `timedelta`, the loader raises no error. A 64-bit sum, or a `DataError` as in the Python implementation, would fix it.

### Smaller

4. With `standard_conforming_strings = off`, Postgres stores a `bytes` parameter sent with `%t`, or with COPY in text format, with an extra backslash: `b'\x00\xff'` becomes `b'\\x00ff'`. `BytesDumper.dump()` returns literal-escaped output ([string.py#L136](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg/psycopg/typ...)). Postgres 19 removed the setting (#1432), so this affects Postgres 18 and earlier only.
5. `-0.0` sent through `ClientCursor` or `sql.Literal` is stored as `0`. The quoted form is ` -0.0` ([numeric.py#L75](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg/psycopg/typ...)), which Postgres reads as a `numeric`, and `numeric` has no negative zero. #1480 fixed the `--` comment. The sign is still lost. Bound parameters keep it.
6. The C implementation sends `Decimal("-NaN")` as the text `-NaN`, which Postgres rejects with `%s`, `%t` and COPY text. With the C implementation, `ClientCursor` and `sql.Literal` produce an unquoted `-NaN`, and Postgres reports `column "nan" does not exist`. The Python implementation sends `NaN` ([numeric.py#L429](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg/psycopg/typ...), [numeric.pyx#L622](https://github.com/psycopg/psycopg/blob/cf390ed00b24b8d5f2d7294f298ff02fbfb731ea/psycopg_c/psycopg_c...)).

### How I found them

I used [Temper](https://github.com/turbolytics/temper) to audit each way of doing the same thing against the same guarantees. The same tests ran on three levels: the Python implementation, the C implementation, and the Python implementation on libpq 13. Temper helped identify these bugs. The table shows the result.

| Guarantee | Python, libpq 18 | C, libpq 18 | Python, libpq 13 |
|---|---|---|---|
| a failed batch commits nothing, in autocommit (4 `executemany` variants) | ✅ | ✅ | ❌ 1 |
| a failed batch commits nothing, in a transaction | ✅ | ✅ | ✅ |
| a prepared statement still works after the first `DROP`, `DISCARD ALL` or `DEALLOCATE ALL` | ✅ | ✅ | ✅ |
| a prepared statement still works after the same statement runs again | ❌ 2 | ❌ 2 | ❌ 2 |
| intervals load as the server's value or raise (text, binary) | ✅ | ❌ 3 | ✅ |
| `bytes` keep their value with `standard_conforming_strings = off` (text paths) | ❌ 4 | ❌ 4 | ❌ 4 |
| numbers keep their value: `ClientCursor`, `sql.Literal` | ❌ 5 | ❌ 5, 6 | ❌ 5 |
| numbers keep their value: `%t`, COPY text | ✅ | ❌ 6 | ✅ |
| numbers keep their value: `%b`, COPY binary | ✅ | ✅ | ✅ |
| text, bytes, dates and times, lists and ranges keep their value (6 paths) | ✅ | ✅ | ✅ |
| numbers, dates and times, text and arrays load as the server's value | ✅ | ✅ | ✅ |
| every pool class returns a clean connection, replaces a killed one, and times out on time | ✅ | ✅ | ✅ |

✅ passes. ❌ fails, marked with the item number. The reference for each value is a literal written in SQL, never another psycopg path. In the `bytes` row, `ClientCursor` and `sql.Literal` fail only for lists of `bytes`.

The map is [`MATRIX.md`](https://github.com/turbolytics/psycopg/blob/ed19b61b9f063cd8fedea4a4d44da46441a94b14/MATRIX.md), built from [these tests](https://github.com/turbolytics/psycopg/blob/ed19b61b9f063cd8fedea4a4d44da46441a94b14/tests/test_temp...), [these tests](https://github.com/turbolytics/psycopg/blob/ed19b61b9f063cd8fedea4a4d44da46441a94b14/tests/test_temp...) and [these declarations](https://github.com/turbolytics/psycopg/tree/ed19b61b9f063cd8fedea4a4d44da46441a94b14/temper).

I can send a PR for 2, 3, 5 and 6, with regression tests in the existing test files. For 1 and 4, I would like to know which behavior you want first. I'm happy to split these into separate issues.

I used an AI assistant to run the tests and draft this issue. I verified each example myself.


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

* Re: [psycopg/psycopg] issue #1499: executemany() commits different rows depending on the libpq version, and psycopg keeps stale prepared statements after a repeated DROP or DISCARD ALL
@ 2026-11-17 21:26  "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  1 sibling, 0 replies; 3+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2026-11-17 21:26 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

slop

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

* Re: [psycopg/psycopg] issue #1499: executemany() commits different rows depending on the libpq version, and psycopg keeps stale prepared statements after a repeated DROP or DISCARD ALL
@ 2026-11-17 21:26  "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  1 sibling, 0 replies; 3+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2026-11-17 21:26 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@dvarrazzo closed this.

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


end of thread, other threads:[~2026-11-17 21:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-17 17:07 [psycopg/psycopg] issue #1499: executemany() commits different rows depending on the libpq version, and psycopg keeps stale prepared statements after a repeated DROP or DISCARD ALL "turbolytics (@turbolytics)" <noreply+turbolytics@github.com>
2026-11-17 21:26 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-17 21:26 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@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