pg.ddx.io psycopg/psycopg GitHub issues and pull requests
help / color / mirror / Atom feedFrom: dvarrazzo (@dvarrazzo) <noreply+dvarrazzo@github.com>
To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>
Subject: [psycopg/psycopg] PR #1495: fix: dump typed ranges bounds using the subtype dumper
Date: Wed, 11 Nov 2026 22:47:40 +0000
Message-ID: <gh-psycopg-psycopg-1495@github.com> (raw)
Int4Range and the other builtin range and multirange subclasses chose the bounds dumper from the Python value, e.g. int2 for a small int in an int4range, which broke binary dumping. In binary, also convert naive or aware timestamps bounds as Postgres would do in text.
Close #1157.
inline patch 1: docs/news.rst, psycopg/psycopg/types/multirange.py, psycopg/psycopg/types/range.py and 2 more (+260/-26 lines) download inline-1.patch
diff --git a/docs/news.rst b/docs/news.rst
index 5f032a95a..a12b28a75 100644
--- a/docs/news.rst
+++ b/docs/news.rst
@@ -16,6 +16,8 @@ Psycopg 3.3.9 (unreleased)
- Use the C implementation of the wait functions on Windows too. It was
disabled because of an excessive CPU usage, fixed in Psycopg 3.2.11
(:ticket:`#645`).
+- Fix binary dumping of `!Int4Range` and other typed ranges and multiranges
+ (:ticket:`#1157`).
- Fix dumping of Decimal 0 with large exponent (part of :ticket:`#1414`).
- Close the connection if the query cancellation following a Ctrl-C or a
task cancellation is interrupted again, instead of leaving it stuck in
diff --git a/psycopg/psycopg/types/multirange.py b/psycopg/psycopg/types/multirange.py
index a3d741735..bb8f3b5e8 100644
--- a/psycopg/psycopg/types/multirange.py
+++ b/psycopg/psycopg/types/multirange.py
@@ -16,9 +16,9 @@
from .. import errors as e
from .. import postgres, sql
from ..pq import Format
-from ..abc import AdaptContext, Buffer, Dumper, DumperKey, QueryNoTemplate
+from ..abc import AdaptContext, Buffer, Dumper, DumperKey, DumpFunc, QueryNoTemplate
from .range import Range, T, dump_range_binary, dump_range_text, fail_dump
-from .range import load_range_binary, load_range_text
+from .range import load_range_binary, load_range_text, make_bound_dump
from .._oids import INVALID_OID, TEXT_OID
from ..adapt import PyFormat, RecursiveDumper, RecursiveLoader
from .._struct import pack_len, unpack_len
@@ -179,11 +179,20 @@ class TimestamptzMultirange(Multirange[datetime]):
class BaseMultirangeDumper(RecursiveDumper):
+ # Specified by the subclasses dumping builtin types.
+ subtype_oid = INVALID_OID
+
def __init__(self, cls: type, context: AdaptContext | None = None):
super().__init__(cls, context)
self.sub_dumper: Dumper | None = None
self._adapt_format = PyFormat.from_pq(self.format)
+ # If the class specifies the subtype, dump the bounds using its
+ # dumper (see BaseRangeDumper).
+ self._dump_bound: DumpFunc | None = None
+ if self.subtype_oid != INVALID_OID:
+ self._dump_bound = make_bound_dump(self._tx, self.subtype_oid, self.format)
+
def get_key(self, obj: Multirange[Any], format: PyFormat) -> DumperKey:
# If we are a subclass whose oid is specified we don't need upgrade
if self.cls is not Multirange:
@@ -224,6 +233,17 @@ def upgrade(self, obj: Multirange[Any], format: PyFormat) -> BaseMultirangeDumpe
return dumper
+ def _get_dump_bound(self, obj: Multirange[Any]) -> DumpFunc:
+ """
+ Return the function to dump the bounds of the ranges.
+ """
+ if self._dump_bound:
+ return self._dump_bound
+ elif (item := self._get_item(obj)) is not None:
+ return self._tx.get_dumper(item, self._adapt_format).dump
+ else:
+ return fail_dump
+
def _get_item(self, obj: Multirange[Any]) -> Any:
"""
Return a member representative of the multirange
@@ -254,11 +274,7 @@ def dump(self, obj: Multirange[Any]) -> Buffer | None:
if not obj:
return b"{}"
- if (item := self._get_item(obj)) is not None:
- dump = self._tx.get_dumper(item, self._adapt_format).dump
- else:
- dump = fail_dump
-
+ dump = self._get_dump_bound(obj)
out: list[Buffer] = [b"{"]
for r in obj:
out.append(dump_range_text(r, dump))
@@ -271,11 +287,7 @@ class MultirangeBinaryDumper(BaseMultirangeDumper):
format = Format.BINARY
def dump(self, obj: Multirange[Any]) -> Buffer | None:
- if (item := self._get_item(obj)) is not None:
- dump = self._tx.get_dumper(item, self._adapt_format).dump
- else:
- dump = fail_dump
-
+ dump = self._get_dump_bound(obj)
out: list[Buffer] = [pack_len(len(obj))]
for r in obj:
data = dump_range_binary(r, dump)
@@ -425,26 +437,32 @@ def _make_binary_loader(name: str, oid: int) -> type[MultirangeBinaryLoader[Any]
class Int4MultirangeDumper(MultirangeDumper):
oid = _oids.INT4MULTIRANGE_OID
+ subtype_oid = _oids.INT4_OID
class Int8MultirangeDumper(MultirangeDumper):
oid = _oids.INT8MULTIRANGE_OID
+ subtype_oid = _oids.INT8_OID
class NumericMultirangeDumper(MultirangeDumper):
oid = _oids.NUMMULTIRANGE_OID
+ subtype_oid = _oids.NUMERIC_OID
class DateMultirangeDumper(MultirangeDumper):
oid = _oids.DATEMULTIRANGE_OID
+ subtype_oid = _oids.DATE_OID
class TimestampMultirangeDumper(MultirangeDumper):
oid = _oids.TSMULTIRANGE_OID
+ subtype_oid = _oids.TIMESTAMP_OID
class TimestamptzMultirangeDumper(MultirangeDumper):
oid = _oids.TSTZMULTIRANGE_OID
+ subtype_oid = _oids.TIMESTAMPTZ_OID
# Binary dumpers for builtin multirange types wrappers
@@ -454,26 +472,32 @@ class TimestamptzMultirangeDumper(MultirangeDumper):
class Int4MultirangeBinaryDumper(MultirangeBinaryDumper):
oid = _oids.INT4MULTIRANGE_OID
+ subtype_oid = _oids.INT4_OID
class Int8MultirangeBinaryDumper(MultirangeBinaryDumper):
oid = _oids.INT8MULTIRANGE_OID
+ subtype_oid = _oids.INT8_OID
class NumericMultirangeBinaryDumper(MultirangeBinaryDumper):
oid = _oids.NUMMULTIRANGE_OID
+ subtype_oid = _oids.NUMERIC_OID
class DateMultirangeBinaryDumper(MultirangeBinaryDumper):
oid = _oids.DATEMULTIRANGE_OID
+ subtype_oid = _oids.DATE_OID
class TimestampMultirangeBinaryDumper(MultirangeBinaryDumper):
oid = _oids.TSMULTIRANGE_OID
+ subtype_oid = _oids.TIMESTAMP_OID
class TimestamptzMultirangeBinaryDumper(MultirangeBinaryDumper):
oid = _oids.TSTZMULTIRANGE_OID
+ subtype_oid = _oids.TIMESTAMPTZ_OID
# Text loaders for builtin multirange types
diff --git a/psycopg/psycopg/types/range.py b/psycopg/psycopg/types/range.py
index a1903c5f9..220d47eae 100644
--- a/psycopg/psycopg/types/range.py
+++ b/psycopg/psycopg/types/range.py
@@ -16,8 +16,9 @@
from .. import errors as e
from .. import postgres, sql
from ..pq import Format
+from .._tz import get_tzinfo
from ..abc import AdaptContext, Buffer, Dumper, DumperKey, DumpFunc, LoadFunc
-from ..abc import QueryNoTemplate
+from ..abc import QueryNoTemplate, Transformer
from .._oids import INVALID_OID, TEXT_OID
from ..adapt import PyFormat, RecursiveDumper, RecursiveLoader
from .._compat import TypeVar
@@ -282,11 +283,21 @@ class TimestamptzRange(Range[datetime]):
class BaseRangeDumper(RecursiveDumper):
+ # Specified by the subclasses dumping builtin types.
+ subtype_oid = INVALID_OID
+
def __init__(self, cls: type, context: AdaptContext | None = None):
super().__init__(cls, context)
self.sub_dumper: Dumper | None = None
self._adapt_format = PyFormat.from_pq(self.format)
+ # If the class specifies the subtype, dump the bounds using its dumper.
+ # Choosing the dumper from the value might pick a type of a different
+ # size (e.g. int2 for a small int).
+ self._dump_bound: DumpFunc | None = None
+ if self.subtype_oid != INVALID_OID:
+ self._dump_bound = make_bound_dump(self._tx, self.subtype_oid, self.format)
+
def get_key(self, obj: Range[Any], format: PyFormat) -> DumperKey:
# If we are a subclass whose oid is specified we don't need upgrade
if self.cls is not Range:
@@ -325,6 +336,17 @@ def upgrade(self, obj: Range[Any], format: PyFormat) -> BaseRangeDumper:
return dumper
+ def _get_dump_bound(self, obj: Range[Any]) -> DumpFunc:
+ """
+ Return the function to dump the bounds of the range.
+ """
+ if self._dump_bound:
+ return self._dump_bound
+ elif (item := self._get_item(obj)) is not None:
+ return self._tx.get_dumper(item, self._adapt_format).dump
+ else:
+ return fail_dump
+
def _get_item(self, obj: Range[Any]) -> Any:
"""
Return a member representative of the range
@@ -348,12 +370,7 @@ class RangeDumper(BaseRangeDumper):
"""
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
- else:
- dump = fail_dump
-
- return dump_range_text(obj, dump)
+ return dump_range_text(obj, self._get_dump_bound(obj))
def dump_range_text(obj: Range[Any], dump: DumpFunc) -> Buffer:
@@ -393,12 +410,7 @@ class RangeBinaryDumper(BaseRangeDumper):
format = Format.BINARY
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
- else:
- dump = fail_dump
-
- return dump_range_binary(obj, dump)
+ return dump_range_binary(obj, self._get_dump_bound(obj))
def dump_range_binary(obj: Range[Any], dump: DumpFunc) -> Buffer:
@@ -435,6 +447,39 @@ def dump_range_binary(obj: Range[Any], dump: DumpFunc) -> Buffer:
return out
+def make_bound_dump(tx: Transformer, subtype_oid: int, format: Format) -> DumpFunc:
+ """
+ Return the function to dump the bounds of a range with a known subtype.
+ """
+ dcls = tx.adapters.get_dumper_by_oid(subtype_oid, format)
+ dump = dcls(object, tx).dump
+ if format != Format.BINARY:
+ return dump
+
+ # In binary, a datetime can't be dumped as a different type and cast by
+ # the server: convert it as Postgres would do with its text representation.
+ if subtype_oid == _oids.TIMESTAMP_OID:
+
+ def dump_naive(item: datetime) -> Buffer | None:
+ if item.tzinfo is not None:
+ item = item.replace(tzinfo=None)
+ return dump(item)
+
+ return dump_naive
+
+ elif subtype_oid == _oids.TIMESTAMPTZ_OID:
+ conn = tx.connection
+
+ def dump_aware(item: datetime) -> Buffer | None:
+ if item.tzinfo is None:
+ item = item.replace(tzinfo=get_tzinfo(conn.pgconn if conn else None))
+ return dump(item)
+
+ return dump_aware
+
+ return dump
+
+
def fail_dump(obj: Any) -> Buffer:
raise e.InternalError("trying to dump a range element without information")
@@ -598,26 +643,32 @@ def _make_binary_loader(name: str, oid: int) -> type[RangeBinaryLoader[Any]]:
class Int4RangeDumper(RangeDumper):
oid = _oids.INT4RANGE_OID
+ subtype_oid = _oids.INT4_OID
class Int8RangeDumper(RangeDumper):
oid = _oids.INT8RANGE_OID
+ subtype_oid = _oids.INT8_OID
class NumericRangeDumper(RangeDumper):
oid = _oids.NUMRANGE_OID
+ subtype_oid = _oids.NUMERIC_OID
class DateRangeDumper(RangeDumper):
oid = _oids.DATERANGE_OID
+ subtype_oid = _oids.DATE_OID
class TimestampRangeDumper(RangeDumper):
oid = _oids.TSRANGE_OID
+ subtype_oid = _oids.TIMESTAMP_OID
class TimestamptzRangeDumper(RangeDumper):
oid = _oids.TSTZRANGE_OID
+ subtype_oid = _oids.TIMESTAMPTZ_OID
# Binary dumpers for builtin range types wrappers
@@ -627,26 +678,32 @@ class TimestamptzRangeDumper(RangeDumper):
class Int4RangeBinaryDumper(RangeBinaryDumper):
oid = _oids.INT4RANGE_OID
+ subtype_oid = _oids.INT4_OID
class Int8RangeBinaryDumper(RangeBinaryDumper):
oid = _oids.INT8RANGE_OID
+ subtype_oid = _oids.INT8_OID
class NumericRangeBinaryDumper(RangeBinaryDumper):
oid = _oids.NUMRANGE_OID
+ subtype_oid = _oids.NUMERIC_OID
class DateRangeBinaryDumper(RangeBinaryDumper):
oid = _oids.DATERANGE_OID
+ subtype_oid = _oids.DATE_OID
class TimestampRangeBinaryDumper(RangeBinaryDumper):
oid = _oids.TSRANGE_OID
+ subtype_oid = _oids.TIMESTAMP_OID
class TimestamptzRangeBinaryDumper(RangeBinaryDumper):
oid = _oids.TSTZRANGE_OID
+ subtype_oid = _oids.TIMESTAMPTZ_OID
# Text loaders for builtin range types
diff --git a/tests/types/test_multirange.py b/tests/types/test_multirange.py
index 8c17e630c..0283f493d 100644
--- a/tests/types/test_multirange.py
+++ b/tests/types/test_multirange.py
@@ -11,7 +11,7 @@
from psycopg.types.multirange import Multirange, MultirangeInfo, register_multirange
from ..utils import eur
-from .test_range import create_test_range
+from .test_range import create_test_range, type2sub, wrapper_samples
from ..test_adapt import StrNoneBinaryDumper, StrNoneDumper
pytestmark = [
@@ -159,6 +159,7 @@ def test_repr(self):
),
]
+# Please keep these two lists aligned.
mr_names = """
int4multirange int8multirange nummultirange
datemultirange tsmultirange tstzmultirange""".split()
@@ -256,6 +257,53 @@ def test_dump_builtin_multirange(conn, pgtype, ranges, fmt_in):
assert cur.fetchone()[0] is True
+@pytest.mark.parametrize("rname, min, max, bounds", wrapper_samples)
+@pytest.mark.parametrize("fmt_in", PyFormat)
+def test_dump_builtin_multirange_wrapper(conn, rname, min, max, bounds, fmt_in):
+ pgtype = rname.replace("range", "multirange")
+ sub = type2sub[rname]
+ wrapper = getattr(multirange, mr_classes[mr_names.index(pgtype)])
+ mr = wrapper([Range(min, max, bounds)])
+ cur = conn.execute(
+ f"select {pgtype}({rname}(%t::{sub}, %t::{sub}, %s)) = %{fmt_in.value}",
+ (min, max, bounds, mr),
+ )
+ assert cur.fetchone()[0] is True
+
+
+@pytest.mark.parametrize("fmt_in", PyFormat)
+def test_dump_tstzmultirange_naive_bounds(conn, fmt_in):
+ conn.execute("set timezone to 'Europe/Rome'")
+ mr = multirange.TimestamptzMultirange(
+ [Range(dt.datetime(2020, 1, 1), dt.datetime(2020, 7, 1))]
+ )
+ cur = conn.execute(
+ "select tstzmultirange(tstzrange('2020-01-01', '2020-07-01'))"
+ f" = %{fmt_in.value}",
+ (mr,),
+ )
+ assert cur.fetchone()[0] is True
+
+
+@pytest.mark.parametrize("fmt_in", PyFormat)
+def test_dump_tsmultirange_aware_bounds(conn, fmt_in):
+ conn.execute("set timezone to 'Europe/Rome'")
+ mr = multirange.TimestampMultirange(
+ [
+ Range(
+ dt.datetime(2020, 1, 1, tzinfo=tzinfo),
+ dt.datetime(2020, 7, 1, tzinfo=tzinfo),
+ )
+ ]
+ )
+ cur = conn.execute(
+ "select tsmultirange(tsrange('2020-01-01', '2020-07-01'))"
+ f" = %{fmt_in.value}",
+ (mr,),
+ )
+ assert cur.fetchone()[0] is True
+
+
@pytest.mark.parametrize("pgtype", mr_names)
@pytest.mark.parametrize("fmt_out", pq.Format)
def test_load_builtin_empty(conn, pgtype, fmt_out):
@@ -322,6 +370,26 @@ def test_copy_in(conn, min, max, bounds, format):
assert rec[0] == Multirange()
+@pytest.mark.parametrize("rname, min, max, bounds", wrapper_samples)
+@pytest.mark.parametrize("format", pq.Format)
+def test_copy_in_wrapper(conn, rname, min, max, bounds, format):
+ pgtype = rname.replace("range", "multirange")
+ sub = type2sub[rname]
+ wrapper = getattr(multirange, mr_classes[mr_names.index(pgtype)])
+ mr = wrapper([Range(min, max, bounds)])
+ cur = conn.cursor()
+ cur.execute(f"create table copymr (id serial primary key, mr {pgtype})")
+
+ with cur.copy(f"copy copymr (mr) from stdin (format {format.name})") as copy:
+ copy.write_row([mr])
+
+ cur.execute(
+ f"select mr = {pgtype}({rname}(%t::{sub}, %t::{sub}, %s)) from copymr",
+ (min, max, bounds),
+ )
+ assert cur.fetchone()[0] is True
+
+
@pytest.mark.parametrize("wrapper", mr_classes)
@pytest.mark.parametrize("format", pq.Format)
def test_copy_in_empty_wrappers(conn, wrapper, format):
diff --git a/tests/types/test_range.py b/tests/types/test_range.py
index cf5cb6baa..03a9f5d3a 100644
--- a/tests/types/test_range.py
+++ b/tests/types/test_range.py
@@ -51,6 +51,7 @@
),
]
+# Please keep these two lists aligned.
range_names = """
int4range int8range numrange daterange tsrange tstzrange
""".split()
@@ -137,6 +138,69 @@ def test_dump_builtin_range(conn, pgtype, min, max, bounds, fmt_in):
assert cur.fetchone()[0] is True
+wrapper_samples = [
+ ("int4range", 1, 5, "[)"),
+ ("int4range", 1, 100_000, "[)"),
+ ("int4range", None, 5, "(]"),
+ ("int4range", -(2**31), (2**31) - 1, "[)"),
+ ("int8range", 1, 5, "[)"),
+ ("int8range", 1, 2**40, "[)"),
+ ("int8range", -(2**63), (2**63) - 1, "[)"),
+ ("numrange", 1, 5, "[)"),
+ ("numrange", 1, Decimal("5.5"), "[]"),
+ ("numrange", Decimal("-1.5"), None, "()"),
+ ("daterange", dt.date(2000, 1, 1), dt.date(2020, 1, 1), "[)"),
+ ("daterange", dt.datetime(2000, 1, 1), dt.datetime(2020, 1, 1), "[)"),
+ (
+ "tsrange",
+ dt.datetime(2000, 1, 1, 00, 00),
+ dt.datetime(2020, 1, 1, 23, 59, 59, 999999),
+ "[]",
+ ),
+ (
+ "tstzrange",
+ dt.datetime(2000, 1, 1, 00, 00, tzinfo=tzinfo),
+ dt.datetime(2020, 1, 1, 23, 59, 59, 999999, tzinfo=tzinfo),
+ "()",
+ ),
+]
+
+
+@pytest.mark.parametrize("pgtype, min, max, bounds", wrapper_samples)
+@pytest.mark.parametrize("fmt_in", PyFormat)
+def test_dump_builtin_range_wrapper(conn, pgtype, min, max, bounds, fmt_in):
+ wrapper = getattr(range_module, range_classes[range_names.index(pgtype)])
+ r = wrapper(min, max, bounds)
+ sub = type2sub[pgtype]
+ cur = conn.execute(
+ f"select {pgtype}(%t::{sub}, %t::{sub}, %s) = %{fmt_in.value}",
+ (min, max, bounds, r),
+ )
+ assert cur.fetchone()[0] is True
+
+
+@pytest.mark.parametrize("fmt_in", PyFormat)
+def test_dump_tstzrange_naive_bounds(conn, fmt_in):
+ conn.execute("set timezone to 'Europe/Rome'")
+ r = range_module.TimestamptzRange(dt.datetime(2020, 1, 1), dt.datetime(2020, 7, 1))
+ cur = conn.execute(
+ f"select tstzrange('2020-01-01', '2020-07-01') = %{fmt_in.value}", (r,)
+ )
+ assert cur.fetchone()[0] is True
+
+
+@pytest.mark.parametrize("fmt_in", PyFormat)
+def test_dump_tsrange_aware_bounds(conn, fmt_in):
+ conn.execute("set timezone to 'Europe/Rome'")
+ r = range_module.TimestampRange(
+ dt.datetime(2020, 1, 1, tzinfo=tzinfo), dt.datetime(2020, 7, 1, tzinfo=tzinfo)
+ )
+ cur = conn.execute(
+ f"select tsrange('2020-01-01', '2020-07-01') = %{fmt_in.value}", (r,)
+ )
+ assert cur.fetchone()[0] is True
+
+
@pytest.mark.parametrize("pgtype", range_names)
@pytest.mark.parametrize("fmt_out", pq.Format)
def test_load_builtin_empty(conn, pgtype, fmt_out):
@@ -233,6 +297,25 @@ def test_copy_in_empty_wrappers(conn, bounds, wrapper, format):
assert rec[0] == r
+@pytest.mark.parametrize("pgtype, min, max, bounds", wrapper_samples)
+@pytest.mark.parametrize("format", pq.Format)
+def test_copy_in_wrapper(conn, pgtype, min, max, bounds, format):
+ wrapper = getattr(range_module, range_classes[range_names.index(pgtype)])
+ r = wrapper(min, max, bounds)
+ sub = type2sub[pgtype]
+ cur = conn.cursor()
+ cur.execute(f"create table copyrange (id serial primary key, r {pgtype})")
+
+ with cur.copy(f"copy copyrange (r) from stdin (format {format.name})") as copy:
+ copy.write_row([r])
+
+ cur.execute(
+ f"select r = {pgtype}(%t::{sub}, %t::{sub}, %s) from copyrange",
+ (min, max, bounds),
+ )
+ assert cur.fetchone()[0] is True
+
+
@pytest.mark.parametrize("bounds", "() empty".split())
@pytest.mark.parametrize("pgtype", range_names)
@pytest.mark.parametrize("format", pq.Format)
view thread (3+ messages) latest in thread
Message-ID: <gh-psycopg-psycopg-1495@github.com>
Permalink: ../gh-psycopg-psycopg-1495@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