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