pg.ddx.io  psycopg/psycopg GitHub issues and pull requests  
help / color / mirror / Atom feed
[psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
16+ messages / 3 participants
[nested] [flat]

* [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-09-04 19:01 "jerch (@jerch)" <noreply+jerch@github.com>
  0 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-09-04 19:01 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

This is a continuation of #1143. I was able to get this work in the python version (example for int4range):

```python
class Int4RangeBinaryDumper(RangeBinaryDumper):
    oid = _oids.INT4RANGE_OID
    inner_oid = _oids.INT4_OID    # would have to be defined on all range classes using fixed dumping

    def dump(self, obj: Range[Any]) -> Buffer | None:
        if (item := self._get_item(obj)) is not None:
            #dump = self._tx.get_dumper(item, self._adapt_format).dump
            dump = self._tx.get_dumper_by_oid(self.inner_oid, Format.BINARY).dump
        else:
            dump = fail_dump

        return dump_range_binary(obj, dump)

```
(`dump` can be handled on the super class, it is here just to show the difference...)

If you like the idea, I can create another MR for it.

A few things would have to be discussed for this:
- Should it be only applied for range dumpers from `set_types` or for all?
- Also for text format?
- Apply it to all range dumper classes, or just those that have the current ambiguity (int4range vs. int8range)?

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-09-04 19:02 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-09-04 19:02 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@jerch renamed this.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-15 19:32 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-15 19:32 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@dvarrazzo To illustrate the idea further I have created #1186. There is also a c version for the range dumping, which gives a huge perf bonus (~3 times faster).

If you like the idea and the direction it goes, I can work on a cleaner solution, prolly OID based.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-17 14:28 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-17 14:28 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@dvarrazzo Tests are now passing 😅  - so it would be nice if you find some time to have a look.

I did not implement a full `CDumper` with a `cdump` function for range types yet, as this looks pretty involved for their "dynamics", instead the `dump` function in python resorts to the c function for now. A full `CDumper.cdump` impl would be ideal to also make use of the inplace bytearray writing (not sure yet if it is worth the hassle, as this is already ~3 times faster). Just let me know, if you would want a more complete impl (also the code currently resides in the wrong place 😅).

And if you wonder, why I mixed `get_dumper_by_oid` idea with a c dump impl in the first place - because `get_dumper_by_oid` is cdef'ed in cython, so not directly available from python.

From range dumper side the changes are as follows - if the dumper has an `_inner_oid` attribute set, this gets used for binary value dumping, otherwise the old logic kicks in.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-17 14:46 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-17 14:46 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@jerch renamed this.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-17 18:39 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-17 18:39 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Update - now with binary cdumpers for range types 😸 

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-17 18:54 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-17 18:54 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

This changes the following usage pattern:
```python
with cur.copy(b'COPY "range_test" (r) FROM STDIN (FORMAT BINARY)') as copy:
    copy.set_types(['int8range'])  # int8 expected
    for left, right in range_data:
        # before - explicit with Int8()
        copy.write_row([Range(Int8(10), Int8(20))])
        # after - auto int8 type
        copy.write_row([Range(10, 20)])
```
Which overall runs ~2.5 times faster with full db roundtrip (the value dumping alone is ~4 times faster):
```text
# old
bench_range_db: 0.06474 ±0.00527 (cv: 0.081, repeat: 10)
# new
bench_range_db: 0.02713 ±0.00249 (cv: 0.092, repeat: 10)
```

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-18 10:54 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-18 10:54 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Lol this turns more and more into a c impl for range types, which I did not intend in the first place. Ofc I wonder now, if the text dumpers and the loaders also should have a c variant just for the speed benefit?  👀 

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-18 11:46 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-10-18 11:46 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

I also wonder how much these objects are used and if squeezing performance is really a benefit in real case scenarios. Yes, if you create queries dumpling or loading a gazillion of them you will see a big difference, but are these queries realistic? Range objects are pretty exotic, I assume there will be only few columns of them in only few queries.

Don't fall into a sunk cost fallacy. These nested dumpers are complex beasts: tests should cover all the corner cases, but you are bound to find many errors the hard way.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-18 11:47 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-10-18 11:47 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(Also, I apologise for not having reviewed this yet, but I am busy with finally releasing 3.2.11 right now, and then I need to look at all these Pool issues for which someone has kindly provided a complex repro)

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-18 12:18 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-18 12:18 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@dvarrazzo Just looked at the text dumper - quite a regexp'ed beast, so a cython variant will prolly not show much of a benefit. Also my original issue using the wrong internal dumper does not exist with the text format.

I agree with you, that range types are rarely used. Still we use them quite alot, as they tend to perform better for range or contain queries than with separated start/end columns (no clue if there is a general pro/contra sentiment out there regarding range fields).

And plz dont worry for not looking at it yet, it is still pretty early code and quite the playground mess still. Also no explicit tests yet 😅  I'd just need your early feedback, whether I am completely on the wrong track, or if you'd consider something like that to be added later on.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-21 11:58 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-21 11:58 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

The following is now implemented for range types:
- binary dumpers --> huge benefit being ~4x faster
- text dumpers --> small benefit being ~2x faster
- binary loaders --> tiny benefit being ~1.3x faster

For the text dumpers it was clear, that it cannot benefit alot, as additional scans are needed for proper escaping. This is done with regexp in python, which is known to be relatively fast.

The binary loaders suffer from the ctor call into python to create the range object, which dominates the value loading. Trying to move the range construction to cython looks rather futile to me, as the range class is quite spicy in its setup. So I think there is not more to gain here (beside micro-opts in the cython code).

Only missing piece would be the text loaders, but they are even worse in regexp usage than the text dumpers, so I am currently not keen to try a cython replacement with lengthy manual content evaluation.

Next step would be to add extensive tests. I also found a few incongruent code paths between binary and text, which might need careful evaluation which one is the right way to handle things.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2025-10-21 12:26 ` "jerch (@jerch)" <noreply+jerch@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: jerch (@jerch) @ 2025-10-21 12:26 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

In the loader logic I stumbled over another question - the cload function gets a char pointer for the data and a length. But the real length needed to do the extraction at the current position is encoded within cload itself. How is that meant to be handled, if the provided data is too short? I currently raise a DataError, but also saw, that other cload functions basically dont care at the that level. This there some boundary check at higher level in place, e.g. to guarantee, that cload always gets enough bytes to chew on?

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2026-11-10 16:12 ` "HawkOnPK (@HawkOnPK)" <noreply+HawkOnPK@github.com>
  14 siblings, 0 replies; 16+ messages in thread

From: HawkOnPK (@HawkOnPK) @ 2026-11-10 16:12 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Another, simpler way to hit this, without COPY or `set_types`: a plain query parameter of the typed `Int4Range` class, in AUTO or binary format.

```python
import psycopg
from psycopg.types.range import Int4Range, Range

with psycopg.connect(DSN) as conn:
    conn.execute("select %t::int4range", [Int4Range(1, 5)])  # ok (text)
    conn.execute("select %s::int4range", [Range(1, 5)])      # ok (generic Range, oid upgraded)
    conn.execute("select %s::int4range", [Int4Range(1, 5)])  # ProtocolViolation
    conn.execute("select %b::int4range", [Int4Range(1, 5)])  # ProtocolViolation
```

```
psycopg.errors.ProtocolViolation: insufficient data left in message
CONTEXT:  unnamed portal parameter $1
```

`Int4RangeBinaryDumper` dumps its bounds with the dumper of the Python value, so `1` and `5` go out as int2 while the oid says int4range:

```python
>>> bytes(Int4RangeBinaryDumper(Int4Range, conn).dump(Int4Range(1, 5)))
b'\x02\x00\x00\x00\x02\x00\x01\x00\x00\x00\x02\x00\x05'
```

Since `Int4RangeBinaryDumper` is the last dumper registered for `Int4Range`, it is also the one chosen for `%s`, so the typed class fails by default, while the generic `Range` works. Taking the bound dumper from the range's element oid, as proposed here, would fix it. Until then, registering `Int4RangeDumper` (text) last for `Int4Range` works around it.

psycopg 3.3.8 (binary implementation), PostgreSQL 17, Python 3.14, Windows.


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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2026-11-11 17:15 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  14 siblings, 0 replies; 16+ messages in thread

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

Hello! This ticket got sort of sprawling. However:

1) there is a clear bug, as @HawkOnPK shows
2) the C dumpers are being tested in #1186 

so I am trying to close this bug, basically implementing this idea, and if that's merged I'll close this ticket. The C implementation can keep on living and we will work on it when it's its time.

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

* Re: [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting
@ 2026-11-11 22:48 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  14 siblings, 0 replies; 16+ messages in thread

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

@dvarrazzo closed this.

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


end of thread, other threads:[~2026-11-11 22:48 UTC | newest]

Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-04 19:01 [psycopg/psycopg] issue #1157: proposal: retrieve range value dumper from inner_oid setting "jerch (@jerch)" <noreply+jerch@github.com>
2025-09-04 19:02 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-15 19:32 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-17 14:28 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-17 14:46 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-17 18:39 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-17 18:54 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-18 10:54 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-18 11:46 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2025-10-18 11:47 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2025-10-18 12:18 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-21 11:58 ` "jerch (@jerch)" <noreply+jerch@github.com>
2025-10-21 12:26 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-11-10 16:12 ` "HawkOnPK (@HawkOnPK)" <noreply+HawkOnPK@github.com>
2026-11-11 17:15 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-11 22:48 ` "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