Skip site navigation (1) Skip section navigation (2)

Re: Range Type Support

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: "Jonathan S(dot) Katz" <jonathan(dot)katz(at)excoventures(dot)com>
Cc: psycopg(at)postgresql(dot)org
Subject: Re: Range Type Support
Date: 2012-09-18 18:08:42
Message-ID: CA+mi_8aYJoG-f3c+aJLX9+kM+6RSysyU9_KnBdYz67S7srDawQ@mail.gmail.com (view raw or flat)
Thread:
Lists: psycopg
On Tue, Sep 18, 2012 at 6:20 PM, Jonathan S. Katz
<jonathan(dot)katz(at)excoventures(dot)com> wrote:
> On Sep 18, 2012, at 12:07 PM, Daniele Varrazzo wrote:
>> On Tue, Sep 18, 2012 at 5:57 PM, Jonathan S. Katz
>> <jonathan(dot)katz(at)excoventures(dot)com> wrote:
>>> Hi,
>>>
>>> I am using range types in an application I am writing and am planning on writing some mapping to have support with built-in Python ranges, and some simple extensions for dealing with date ranges.  I am going to write this on my own regardless, but is anyone working on this for psycopg2?  If not, I would be willing to contribute the code I am working on.
>>
>> What is a "built-in Python range"? You mean some Python object to be
>> written with the same semantics of a postgres range? (subtype,
>> handling of boundaries, empty singleton).
>
> Good point, my mistake in terminology.  I was thinking of the "range" function for integers, but that outputs an array of numbers.  This works well for small data sets but if your range type in the DB is something like [1, 10000000] we could have a bad day.

You are being misled by the common "range" term. In Python, a range is
an object primarily made to be iterated, in Postgres is primarily for
membership/intersection test. A python range (in python 2 xrange()
would do, in python 3 there's really a range type) is only equivalent
to a postgres int range with left bound included. OTOH there is no
step in postgres range. They are definitely two different beasts.


> I would like to propose having a special class to handle the ranges with the same semantics of the Postgres ranges.  I would like to have a base class that would handle the range mechanics that could then be inherited, thus mimicking how range types can be extended.  Additionally, this would allow us to support continuous ranges (e.g. time, numeric/float/decimal).

Yes, that would be the thing to adapt to. I don't know how much it
would be useful in Python but I see no more useful thing to adapt to.


> My concern is I don't want to deviate too far from the standard way of doing things in Python (lists as ranges) for the sake of other libs (i.e. ORMs) interfacing with psycopg2, so I can make strong arguments on both sides.

I don't see any standard python object to adapt to without leaving
half of the model away.


Ah, postgres ranges can also be open left/right/both. So I'd have:

- Basic class with all attributes and implementation, and subclasses
specifying a type as you mentioned.

- The basic class should have the following attributes, which are a
direct mapping to the postgres ones and are required to inspect the
object:
  - lower/upper attributes
  - lower_inc/upper_inc attributes
  - lower_inf/upper_inf attributes
  - isempty attibute

- Possibly it should support an "in" operator and an & operator. But
then, should we mimic all the operators?
<http://www.postgresql.org/docs/9.2/static/functions-range.html>
Probably not.

- Parsing interval should resort to the base type parser, so when
constructing a concrete range type a typecaster should be probably
specified.

- If a new range type is created in postgres, it would be nice to have
something like register_range() to introspect pg_range and use the
right typecaster (rngsubtype) for parsing.

- test and docs

Having the range iterable seems tempting but the Postgres model
doesn't have it (even for discrete ranges, it mandates a
canonicalization function, not a step delta) so the two types wouldn't
map automatically (not possible to introspect the catalog to get such
a step)

What do you think?


-- Daniele


In response to

Responses

psycopg by date

Next:From: Daniele VarrazzoDate: 2012-09-18 18:13:01
Subject: Re: Problem with Zope 2.13.15, python 2.6.6 psycopg2-2.4.5, pg 9.0.3
Previous:From: Jonathan S. KatzDate: 2012-09-18 17:20:59
Subject: Re: Range Type Support

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group