Range type adaptation implemented

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: psycopg(at)postgresql(dot)org
Subject: Range type adaptation implemented
Date: 2012-09-24 08:56:14
Message-ID: CA+mi_8ahX2cQtv201k91VZYFrOjMR94U16ZhgLJZp7-4RMDtJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: psycopg

I have implemented adaptation between Postgres range types and Python.

Usage is documented at:
<http://initd.org/psycopg/docs/extras.html#range-data-types>

There is a basic Range class, completely working (it is not an
abstract class) but with not enough information for adaptation. A few
subclasses allow mapping to and from the db, for instance NumericRange
map to int4range, int8range and numrange. Adaptation of builtin range
types is supported out-of-the-box. Adaptation of user-defined ranges
is supported via the function register_range() that queries the
database, if required creates a new range type and registers all the
adapters. The usual chain of fallbacks is provided if the user doesn't
want to query the database etc.

The Range class doesn't re-implements the database range operator and
doesn't support canonicalization because we cannot emulate fully the
Postgres model. The funniest surprise I've had during the development
was an error creating the object TextRange('#', '$') in the database.
It should be possible as

>>> ord('#'), ord('$')
(35, 36)

but PG insisted that "range lower bound must be less than or equal to
range upper bound". Well, turns out that:

postgres=# select ascii('#') < ascii('$'), '#' < '$';
?column? | ?column?
----------+----------
t | f
(1 row)

Morale: it's between [hard and impossible) to canonicalize ranges the
way postgres does, hence it's better to leave only the database
responsible for it, otherwise we may end up with ranges valid for
postgres and not for python and viceversa.

On adaptation: numeric ranges are adapted using a syntax '[10,20)':
this allows to have a single range type for all numerical types. Other
ranges, such as the date range and in general user-defined types, are
adapted using a syntax such as daterange('2012-01-01', '2012-12-31',
'(]'), with the values adapted recursively. I would have preferred to
implement all the ranges in the second way, because it relies on
already defined adapters for the representation and returns typed
values instead of string literals. I've had to adapt the number ranges
as literals because there is no cast defined across postgres range
types:

postgres=# select '[10,20)'::int4range::numrange;
ERROR: cannot cast type int4range to numrange

nor AFAIK there is a constructor for an "untyped range" such as
range(lower, upper, bounds). So if what we have is Python numbers,
e.g. 10, 20, we cannot choose whether we want an int4range or an
int8range, and Postgres won't forgive the error with a cast. The
shortcoming of using a literal is that in case of ambiguity postgres
will refuse to guess, but this can be solved with a ::cast on the
placeholder.

OTOH the lack of a generic postgres range constructor function forces
us to have different Range subclasses for date, timestamp, timestamptz
and all the custom ranges, because in the sql representation we have
to pass the type name. I've thought about using a single Range with a
single adapter looking at the type of the bounds contained by the
python objects and making and using the proper PG type name, but this
wouldn't work for ranges containing no bound (unbound or empty) and in
that case it should resort to a literal. This is bad as a query would
end up having sometimes a daterange(to, from), sometimes just a
literal '(,)' or 'empty' so it may work all the time and fail
unexpectedly on specific values for cast problems in the database.

The above docs include the other changes implemented in these days:
customizable composite adaptation and json support. My devel has still
the features in separate branches to be merged together. Comments,
tests and docs proofreading are well accepted as usual. Thank you very
much.

-- Daniele

Responses

Browse psycopg by date

  From Date Subject
Next Message Karsten Hilbert 2012-09-24 09:45:42 Re: Range type adaptation implemented
Previous Message Tobias Oberstein 2012-09-22 18:57:23 Re: flexi adaption/casting scheme