postgres 9.2, range types, and exclude constraints

[Chris Withers]

Hi All (Mike?),

Has any work been done to get support for postgres 9.2's range types,
associated operators and exclude constraints supported in sqlalchemy?

If not, I'd be interested in giving it a shot. Where would I look for
similar examples to start from?

The postgres docs are here:

http://www.postgresql.org/docs/9.2/static/rangetypes.html

I'm particularly interested in implementing:

- a type for tsrange columns, looks like psycopg2 supports this already,
how would I just use their psycopg2._range.Range object as the python
representation of the column value?

- the operators listed in the docs above

- the necessary to build exclude constraints programaticaly as part of
of a declarative mixin (think a "temporal" mixin that gives you a
valid_on column and associated exclude constaint based on the other
non-primary-key columns)

Any help/examples (or best of all an email telling me it's all been done
already!) gratefully received...

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Michael Bayer]

On May 15, 2013, at 3:13 AM, Chris Withers <ch...@simplistix.co.uk> wrote:

> Hi All (Mike?),
>
> Has any work been done to get support for postgres 9.2's range types, associated operators and exclude constraints supported in sqlalchemy?
>
> If not, I'd be interested in giving it a shot. Where would I look for similar examples to start from?
>
> The postgres docs are here:
>
> http://www.postgresql.org/docs/9.2/static/rangetypes.html
>
> I'm particularly interested in implementing:
>
> - a type for tsrange columns, looks like psycopg2 supports this already, how would I just use their psycopg2._range.Range object as the python representation of the column value?
>
> - the operators listed in the docs above
>
> - the necessary to build exclude constraints programaticaly as part of of a declarative mixin (think a "temporal" mixin that gives you a valid_on column and associated exclude constaint based on the other non-primary-key columns)
>
> Any help/examples (or best of all an email telling me it's all been done already!) gratefully received...

I'd recommend looking at the ARRAY and HSTORE implementations, that should reveal most of what you need to know. Particularly HSTORE you'll see how we take advantage of psycopg2's native support, when available.

[Chris Withers]

On 15/05/2013 16:06, Michael Bayer wrote:
>
>> http://www.postgresql.org/docs/9.2/static/rangetypes.html
>>
>> I'm particularly interested in implementing:
>>
>> - a type for tsrange columns, looks like psycopg2 supports this already, how would I just use their psycopg2._range.Range object as the python representation of the column value?

Okay, basic types now done, these look good?

https://github.com/cjw296/sqlalchemy/commits/pg-ranges

>> - the operators listed in the docs above

These are next on my list:

http://www.postgresql.org/docs/devel/static/functions-range.html

I'm guessing the operators that are used for other types (==, <>, etc)
will "just work"?

Where's the correct place to put further operator definitions (&&, <@
and @> in particular) and where can I find some examples of unit testing
similar?

>> - the necessary to build exclude constraints programaticaly as part of of a declarative mixin (think a "temporal" mixin that gives you a valid_on column and associated exclude constaint based on the other non-primary-key columns)

I'll leave this for later.

[Chris Withers]

On 19/05/2013 09:02, Chris Withers wrote:
> These are next on my list:
>
> http://www.postgresql.org/docs/devel/static/functions-range.html
>
> I'm guessing the operators that are used for other types (==, <>, etc)
> will "just work"?
>
> Where's the correct place to put further operator definitions (&&, <@
> and @> in particular) and where can I find some examples of unit testing
> similar?

For the benefit of the archives:

http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators

Wow, Mike, this stuff has a *really* nice feel to it :-)

I'm undecided on the "range functions" stuff from the postgres docs
above. My guess is generic functions may be enough for this, if not,
where should I put them? Is there somewhere in the postgres dialect they
should go or do they just go in sqlalchemy.sql.functions but only work
in postgres?

[Michael Bayer]

On May 19, 2013, at 10:32 AM, Chris Withers <ch...@simplistix.co.uk> wrote:

> On 19/05/2013 09:02, Chris Withers wrote:
>> These are next on my list:
>>
>> http://www.postgresql.org/docs/devel/static/functions-range.html
>>
>> I'm guessing the operators that are used for other types (==, <>, etc)
>> will "just work"?
>>
>> Where's the correct place to put further operator definitions (&&, <@
>> and @> in particular) and where can I find some examples of unit testing
>> similar?
>
> For the benefit of the archives:
>
> http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators
>
> Wow, Mike, this stuff has a *really* nice feel to it :-)
>
> I'm undecided on the "range functions" stuff from the postgres docs above. My guess is generic functions may be enough for this, if not, where should I put them? Is there somewhere in the postgres dialect they should go or do they just go in sqlalchemy.sql.functions but only work in postgres?

I'm just reading this thread after I see you've figured out the operator stuff, yeah it came out really great didn't it.

If your range stuff has enough verbosity then you could even put it into it's own module in dialects/postgresql/. That might not be a terrible idea, it would then look a lot like HSTORE, and we could get json in it's own file there too at some point. Maybe even the array stuff should be in array.py too. This whole idea of "the types and compiler and dialect are all in one .py file" is actually due to history, now that each database has it's own package we might want to start spreading out.

[Chris Withers]

On 19/05/2013 16:07, Michael Bayer wrote:
>
> On May 19, 2013, at 10:32 AM, Chris Withers <ch...@simplistix.co.uk> wrote:
>
>> On 19/05/2013 09:02, Chris Withers wrote:
>>> These are next on my list:
>>>
>>> http://www.postgresql.org/docs/devel/static/functions-range.html
>>>
>>> I'm guessing the operators that are used for other types (==, <>, etc)
>>> will "just work"?
>>>
>>> Where's the correct place to put further operator definitions (&&, <@
>>> and @> in particular) and where can I find some examples of unit testing
>>> similar?
>>
>> For the benefit of the archives:
>>
>> http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#types-operators
>>
>> Wow, Mike, this stuff has a *really* nice feel to it :-)
>>
>> I'm undecided on the "range functions" stuff from the postgres docs above. My guess is generic functions may be enough for this, if not, where should I put them? Is there somewhere in the postgres dialect they should go or do they just go in sqlalchemy.sql.functions but only work in postgres?
>
> I'm just reading this thread after I see you've figured out the operator stuff, yeah it came out really great didn't it.
>
> If your range stuff has enough verbosity then you could even put it into it's own module in dialects/postgresql/.

Already is:

https://github.com/cjw296/sqlalchemy/blob/pg-ranges/lib/sqlalchemy/dialects/postgresql/ranges.py

Is it legit for functions to end up in sqlalchemy.dialects.postgresql?
(lower(anyrange) and friends...)

Do we have any dialect-specific and_-like operator functions? I'm
punting on them for now, but I can see we might need python functions
for "42 <@ some_int4range_col" and the like. If they're needed, where
should they go?

Also going to be looking for a home for the ExcludeConstraint ddl
construct, will shovel into sqlalchemy.dialects.postgresql unless you
tell me otherwise :-)

And finally, once I've got this stuff functional I'm going to play with
it for a while and check it feels right; however, if you've got a
release coming up any time soon that you'll include this in, please poke
me and I'll get the docs written and a proper pull request submitted!
(don't want to be using my own hand-rolled sqlalchemy dists or checkouts
any longer than I need to ;-))

cheers,