Thread: hstores in pl/python

It would be cool to be able to transparently use hstores as Python
dictionaries and vice versa. It would be easy enough with hstore as a
core type, but with hstore as an addon it's not that easy.

There was talk about including hstore in core, is there still chance for
that to happen in 9.1? I'd like to include hstore<->dict handling, but
with hstore out-of-core the only half-sane way I see is:* hack PL/Python's makefile to add -Icontrib/hstore (yuck!)*
createan extension module for Python that knows how to handle
 
hstores that would live next to plpython.so* install it in $libdir on make install* when PL/Python receives or is asked
tocreate an hstore, load the
 
extension module and use it to parse the value (ugly, probably slow)* the module would also have to make sure hstore.so
isloaded in the
 
database, which in itself is not pretty, as it would refer to
hstore_in/out symbols

I wrote a module that can be used with current PL/Python to simplify
hstore handling (https://github.com/wulczer/pyhstore), but it suffers
from most of the aforementioned problems, and on top of that you get
hstore->text->dict instead of just hstore->dict, which sucks.

Cheers,
Jan

Hello

this is little bit offtopic, sorry.

I am thinking, so we need a standard associative array support in core
- like Perl, Python or Javascript. So, I don't think, so migration of
hstore to core is good idea.

Regards

Pavel Stehule

2010/12/13 Jan Urbański <wulczer@wulczer.org>:
> It would be cool to be able to transparently use hstores as Python
> dictionaries and vice versa. It would be easy enough with hstore as a
> core type, but with hstore as an addon it's not that easy.
>
> There was talk about including hstore in core, is there still chance for
> that to happen in 9.1? I'd like to include hstore<->dict handling, but
> with hstore out-of-core the only half-sane way I see is:
>  * hack PL/Python's makefile to add -Icontrib/hstore (yuck!)
>  * create an extension module for Python that knows how to handle
> hstores that would live next to plpython.so
>  * install it in $libdir on make install
>  * when PL/Python receives or is asked to create an hstore, load the
> extension module and use it to parse the value (ugly, probably slow)
>  * the module would also have to make sure hstore.so is loaded in the
> database, which in itself is not pretty, as it would refer to
> hstore_in/out symbols
>
> I wrote a module that can be used with current PL/Python to simplify
> hstore handling (https://github.com/wulczer/pyhstore), but it suffers
> from most of the aforementioned problems, and on top of that you get
> hstore->text->dict instead of just hstore->dict, which sucks.
>
> Cheers,
> Jan
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
> Hey Pavel,
>
> 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> Hello
>>
>> this is little bit offtopic, sorry.
>>
>> I am thinking, so we need a standard associative array support in core
>> - like Perl, Python or Javascript. So, I don't think, so migration of
>> hstore to core is good idea.
>
> Could you tell why in-core associative array support would be better
> than in-core hstore support ?
> We enjoying to use hstore and thinking that it is implemented great.
>>

Because hstore is PostgreSQL specific type. More well known syntax is
better. More - who know, what is hstore? But everybody know, what is
associative array or hash.

Pavel

>> Regards
>>
>> Pavel Stehule
>>
>> 2010/12/13 Jan Urbański <wulczer@wulczer.org>:
>> > It would be cool to be able to transparently use hstores as Python
>> > dictionaries and vice versa. It would be easy enough with hstore as a
>> > core type, but with hstore as an addon it's not that easy.
>> >
>> > There was talk about including hstore in core, is there still chance for
>> > that to happen in 9.1? I'd like to include hstore<->dict handling, but
>> > with hstore out-of-core the only half-sane way I see is:
>> >  * hack PL/Python's makefile to add -Icontrib/hstore (yuck!)
>> >  * create an extension module for Python that knows how to handle
>> > hstores that would live next to plpython.so
>> >  * install it in $libdir on make install
>> >  * when PL/Python receives or is asked to create an hstore, load the
>> > extension module and use it to parse the value (ugly, probably slow)
>> >  * the module would also have to make sure hstore.so is loaded in the
>> > database, which in itself is not pretty, as it would refer to
>> > hstore_in/out symbols
>> >
>> > I wrote a module that can be used with current PL/Python to simplify
>> > hstore handling (https://github.com/wulczer/pyhstore), but it suffers
>> > from most of the aforementioned problems, and on top of that you get
>> > hstore->text->dict instead of just hstore->dict, which sucks.
>> >
>> > Cheers,
>> > Jan
>> >
>> > --
>> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-hackers
>> >
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
>
> --
> // Dmitriy.
>
>
>

On Monday 13 December 2010 15:27:48 Pavel Stehule wrote:
> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
> > Hey Pavel,
> > 
> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
> > 
> >> Hello
> >> 
> >> this is little bit offtopic, sorry.
> >> 
> >> I am thinking, so we need a standard associative array support in core
> >> - like Perl, Python or Javascript. So, I don't think, so migration of
> >> hstore to core is good idea.
> > 
> > Could you tell why in-core associative array support would be better
> > than in-core hstore support ?
> > We enjoying to use hstore and thinking that it is implemented great.
> 
> Because hstore is PostgreSQL specific type. More well known syntax is
> better. More - who know, what is hstore? But everybody know, what is
> associative array or hash.
So youre disturbed by the name?

Andres

2010/12/13 Andres Freund <andres@anarazel.de>:
> On Monday 13 December 2010 15:27:48 Pavel Stehule wrote:
>> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> > Hey Pavel,
>> >
>> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >
>> >> Hello
>> >>
>> >> this is little bit offtopic, sorry.
>> >>
>> >> I am thinking, so we need a standard associative array support in core
>> >> - like Perl, Python or Javascript. So, I don't think, so migration of
>> >> hstore to core is good idea.
>> >
>> > Could you tell why in-core associative array support would be better
>> > than in-core hstore support ?
>> > We enjoying to use hstore and thinking that it is implemented great.
>>
>> Because hstore is PostgreSQL specific type. More well known syntax is
>> better. More - who know, what is hstore? But everybody know, what is
>> associative array or hash.
> So youre disturbed by the name?
>

name and interface - hstore is designed as external module - a
internal class can be designed different.

Pavel

> Andres
>

On Monday 13 December 2010 16:01:35 Pavel Stehule wrote:
> 2010/12/13 Andres Freund <andres@anarazel.de>:
> > On Monday 13 December 2010 15:27:48 Pavel Stehule wrote:
> >> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
> >> > Hey Pavel,
> >> > 
> >> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
> >> > 
> >> >> Hello
> >> >> 
> >> >> this is little bit offtopic, sorry.
> >> >> 
> >> >> I am thinking, so we need a standard associative array support in
> >> >> core - like Perl, Python or Javascript. So, I don't think, so
> >> >> migration of hstore to core is good idea.
> >> > 
> >> > Could you tell why in-core associative array support would be better
> >> > than in-core hstore support ?
> >> > We enjoying to use hstore and thinking that it is implemented great.
> >> 
> >> Because hstore is PostgreSQL specific type. More well known syntax is
> >> better. More - who know, what is hstore? But everybody know, what is
> >> associative array or hash.
> > 
> > So youre disturbed by the name?
> 
> name and interface - hstore is designed as external module - a
> internal class can be designed different.
Could you actually name such a difference rather than pointing to some airily 
hint of one? That would make it way much easier to see where you want to go.

Andres

2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
> Interesting argument.


> I can ask, how many people knows what is tsvector or tsquery ?
> Or how many people knows what is polygon or path ?

TSearch isn't good example. There are not a common interface for fulltext.


> The answer is: everyone who need or using it.
>
> Hstore is a proven and well designed solution. And in fact I am
> surprising why it does not in core yet?!

Hstore is designed as external module. I am think, so when we can
modify parser when some functionality is internal, then a
implementation can be more effective and without some surprising for
user.

Pavel

>
> 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> > Hey Pavel,
>> >
>> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >>
>> >> Hello
>> >>
>> >> this is little bit offtopic, sorry.
>> >>
>> >> I am thinking, so we need a standard associative array support in core
>> >> - like Perl, Python or Javascript. So, I don't think, so migration of
>> >> hstore to core is good idea.
>> >
>> > Could you tell why in-core associative array support would be better
>> > than in-core hstore support ?
>> > We enjoying to use hstore and thinking that it is implemented great.
>> >>
>>
>> Because hstore is PostgreSQL specific type. More well known syntax is
>> better. More - who know, what is hstore? But everybody know, what is
>> associative array or hash.
>>
>> Pavel
>>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >>
>> >> 2010/12/13 Jan Urbański <wulczer@wulczer.org>:
>> >> > It would be cool to be able to transparently use hstores as Python
>> >> > dictionaries and vice versa. It would be easy enough with hstore as a
>> >> > core type, but with hstore as an addon it's not that easy.
>> >> >
>> >> > There was talk about including hstore in core, is there still chance
>> >> > for
>> >> > that to happen in 9.1? I'd like to include hstore<->dict handling,
>> >> > but
>> >> > with hstore out-of-core the only half-sane way I see is:
>> >> >  * hack PL/Python's makefile to add -Icontrib/hstore (yuck!)
>> >> >  * create an extension module for Python that knows how to handle
>> >> > hstores that would live next to plpython.so
>> >> >  * install it in $libdir on make install
>> >> >  * when PL/Python receives or is asked to create an hstore, load the
>> >> > extension module and use it to parse the value (ugly, probably slow)
>> >> >  * the module would also have to make sure hstore.so is loaded in the
>> >> > database, which in itself is not pretty, as it would refer to
>> >> > hstore_in/out symbols
>> >> >
>> >> > I wrote a module that can be used with current PL/Python to simplify
>> >> > hstore handling (https://github.com/wulczer/pyhstore), but it suffers
>> >> > from most of the aforementioned problems, and on top of that you get
>> >> > hstore->text->dict instead of just hstore->dict, which sucks.
>> >> >
>> >> > Cheers,
>> >> > Jan
>> >> >
>> >> > --
>> >> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> >> > To make changes to your subscription:
>> >> > http://www.postgresql.org/mailpref/pgsql-hackers
>> >> >
>> >>
>> >> --
>> >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-hackers
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

>>
>> name and interface - hstore is designed as external module - a
>> internal class can be designed different.
> Could you actually name such a difference rather than pointing to some airily
> hint of one? That would make it way much easier to see where you want to go.

My idea is:

somevar['key'] = value
value = somevar['key'];

or with constructor

somevar = ARRAY[key1 => value1, key2 => value2, .. ]

or some similar.

Regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:
>> Could you actually name such a difference rather than pointing to some airily
>> hint of one? That would make it way much easier to see where you want to go.

> My idea is:

> somevar['key'] = value
> value = somevar['key'];

> or with constructor

> somevar = ARRAY[key1 => value1, key2 => value2, .. ]

> or some similar.

We don't normally invent specialized syntax for a specific datatype.
Not even if it's in core.
        regards, tom lane

My most serious pro about hstore in core is a better dump/restore
support. Also, since we have so much better hstore and people started
to use it in their projects, it'd be great  to have built-in feature in 
PostgreSQL, which  mimic key-value or document-oriented database.


Oleg

On Mon, 13 Dec 2010, Jan Urbaski wrote:

> It would be cool to be able to transparently use hstores as Python
> dictionaries and vice versa. It would be easy enough with hstore as a
> core type, but with hstore as an addon it's not that easy.
>
> There was talk about including hstore in core, is there still chance for
> that to happen in 9.1? I'd like to include hstore<->dict handling, but
> with hstore out-of-core the only half-sane way I see is:
> * hack PL/Python's makefile to add -Icontrib/hstore (yuck!)
> * create an extension module for Python that knows how to handle
> hstores that would live next to plpython.so
> * install it in $libdir on make install
> * when PL/Python receives or is asked to create an hstore, load the
> extension module and use it to parse the value (ugly, probably slow)
> * the module would also have to make sure hstore.so is loaded in the
> database, which in itself is not pretty, as it would refer to
> hstore_in/out symbols
>
> I wrote a module that can be used with current PL/Python to simplify
> hstore handling (https://github.com/wulczer/pyhstore), but it suffers
> from most of the aforementioned problems, and on top of that you get
> hstore->text->dict instead of just hstore->dict, which sucks.
>
> Cheers,
> Jan
>
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
> There are a lot of operators and functions to work with hstore.
> Does it worth it to implement similar things only to make it
> possible using operator [] ?

yes

>
> 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> >>
>> >> name and interface - hstore is designed as external module - a
>> >> internal class can be designed different.
>> > Could you actually name such a difference rather than pointing to some
>> > airily
>> > hint of one? That would make it way much easier to see where you want to
>> > go.
>>
>> My idea is:
>>
>> somevar['key'] = value
>> value = somevar['key'];
>
> What type of <value> is? Can it be assoc. array ?
> Is it possible to indexing assoc. array by position ?
> Any many many other questions can be there. So,
> I don't think that assoc. arrays has common interface.
> Its still specialized type.

It's question. Minimally it can be a any known (defined) type -
composite type too. It would be nice if we can store data in native
format with constraints. Now Hstore can store only text - (note: It's
terrible hard to write this as external module, so Hstore does maximum
what is possible).

> But, Pavel, I feel you idea. You want to make the syntax
> clear in particular...

I like a possibility to define own types in pg. But sometimes, and
associative arrays is it, created interface is "too specific" - like
Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore
respects it in design. So when we could to move hstore functionality
to core, we can extend a parser, and we can create some general usable
API. It can be big plus for stored procedures programming. This is
just my opinion - when Hstore will be in core, then we will not have a
native associative array ever, so from my perspective is better Hstore
as contrib module.

Regards

Pavel Stehule

>
>>
>> or with constructor
>>
>> somevar = ARRAY[key1 => value1, key2 => value2, .. ]
>>
>> or some similar.
>>
>> Regards
>>
>> Pavel Stehule
>
>
>
> --
> // Dmitriy.
>
>
>

2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>
>
> 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> > There are a lot of operators and functions to work with hstore.
>> > Does it worth it to implement similar things only to make it
>> > possible using operator [] ?
>>
>> yes
>>
>> >
>> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >>
>> >> >>
>> >> >> name and interface - hstore is designed as external module - a
>> >> >> internal class can be designed different.
>> >> > Could you actually name such a difference rather than pointing to
>> >> > some
>> >> > airily
>> >> > hint of one? That would make it way much easier to see where you want
>> >> > to
>> >> > go.
>> >>
>> >> My idea is:
>> >>
>> >> somevar['key'] = value
>> >> value = somevar['key'];
>> >
>> > What type of <value> is? Can it be assoc. array ?
>> > Is it possible to indexing assoc. array by position ?
>> > Any many many other questions can be there. So,
>> > I don't think that assoc. arrays has common interface.
>> > Its still specialized type.
>>
>> It's question. Minimally it can be a any known (defined) type -
>> composite type too. It would be nice if we can store data in native
>> format with constraints. Now Hstore can store only text - (note: It's
>> terrible hard to write this as external module, so Hstore does maximum
>> what is possible).
>>
>> > But, Pavel, I feel you idea. You want to make the syntax
>> > clear in particular...
>>
>> I like a possibility to define own types in pg. But sometimes, and
>> associative arrays is it, created interface is "too specific" - like
>> Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore
>> respects it in design. So when we could to move hstore functionality
>> to core, we can extend a parser, and we can create some general usable
>> API. It can be big plus for stored procedures programming. This is
>> just my opinion - when Hstore will be in core, then we will not have a
>> native associative array ever, so from my perspective is better Hstore
>> as contrib module.
>
> In my opinion, hstore is defined and implemented well. Its complete in most
> cases. Therefore hstore is mature enough to be in core.
>
> On the other hand associative arrays should be implemented from scratch.
> Very well. Let it be. But how integration hstore in core today can interfere
> with implementation of support for associative arrays in future ? Is it will
> a big problem ?

I think so it can be a problem. Any second implemented feature will
have a handicap, because there will be a similar and realised feature.
Maybe I am too pessimist, but  there are very minimal probability to
common existence two similar features in core like hstore or
associative arrays.  And because associative arrays are more general
than hstore, I prefer a associative arrays. Hstore works well and a
moving it to core doesn't carry a new value. It's not comparable with
TSearch2. What I know, contrib modules are not problem for DBA now and
Hstore hasn't a "complex" installation like TSearch2 had. More - there
are not a security issues that had to be solved with TSearch2.

Why we need a Hstore in core? Why Hstore need be in core?

Back to plpython. There is possibility to call a external library
without linking. So Hstore must not be in core - and PL/Python can
call it.

Regards

Pavel Stehule


>>
>> Regards
>>
>> Pavel Stehule
>>
>> >
>> >>
>> >> or with constructor
>> >>
>> >> somevar = ARRAY[key1 => value1, key2 => value2, .. ]
>> >>
>> >> or some similar.
>> >>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

On Dec 13, 2010, at 8:06 AM, Oleg Bartunov wrote:

> My most serious pro about hstore in core is a better dump/restore
> support. Also, since we have so much better hstore and people started
> to use it in their projects, it'd be great  to have built-in feature in PostgreSQL, which  mimic key-value or
document-orienteddatabase. 

I thought the JSON data type was supposed to cover this. hstore would remain in contrib.

Davd

2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>
>
> 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> >
>> >
>> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >>
>> >> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> >> > There are a lot of operators and functions to work with hstore.
>> >> > Does it worth it to implement similar things only to make it
>> >> > possible using operator [] ?
>> >>
>> >> yes
>> >>
>> >> >
>> >> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >> >>
>> >> >> >>
>> >> >> >> name and interface - hstore is designed as external module - a
>> >> >> >> internal class can be designed different.
>> >> >> > Could you actually name such a difference rather than pointing to
>> >> >> > some
>> >> >> > airily
>> >> >> > hint of one? That would make it way much easier to see where you
>> >> >> > want
>> >> >> > to
>> >> >> > go.
>> >> >>
>> >> >> My idea is:
>> >> >>
>> >> >> somevar['key'] = value
>> >> >> value = somevar['key'];
>> >> >
>> >> > What type of <value> is? Can it be assoc. array ?
>> >> > Is it possible to indexing assoc. array by position ?
>> >> > Any many many other questions can be there. So,
>> >> > I don't think that assoc. arrays has common interface.
>> >> > Its still specialized type.
>> >>
>> >> It's question. Minimally it can be a any known (defined) type -
>> >> composite type too. It would be nice if we can store data in native
>> >> format with constraints. Now Hstore can store only text - (note: It's
>> >> terrible hard to write this as external module, so Hstore does maximum
>> >> what is possible).
>> >>
>> >> > But, Pavel, I feel you idea. You want to make the syntax
>> >> > clear in particular...
>> >>
>> >> I like a possibility to define own types in pg. But sometimes, and
>> >> associative arrays is it, created interface is "too specific" - like
>> >> Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore
>> >> respects it in design. So when we could to move hstore functionality
>> >> to core, we can extend a parser, and we can create some general usable
>> >> API. It can be big plus for stored procedures programming. This is
>> >> just my opinion - when Hstore will be in core, then we will not have a
>> >> native associative array ever, so from my perspective is better Hstore
>> >> as contrib module.
>> >
>> > In my opinion, hstore is defined and implemented well. Its complete in
>> > most
>> > cases. Therefore hstore is mature enough to be in core.
>> >
>> > On the other hand associative arrays should be implemented from scratch.
>> > Very well. Let it be. But how integration hstore in core today can
>> > interfere
>> > with implementation of support for associative arrays in future ? Is it
>> > will
>> > a big problem ?
>>
>> I think so it can be a problem. Any second implemented feature will
>> have a handicap, because there will be a similar and realised feature.
>> Maybe I am too pessimist, but  there are very minimal probability to
>> common existence two similar features in core like hstore or
>> associative arrays.  And because associative arrays are more general
>> than hstore, I prefer a associative arrays.
>
> Okay. According to
> http://www.postgresql.org/docs/9.0/static/arrays.html
> PostreSQL array - collection of values of the same type -- built-in or
> user-defined. Assoc. arrays (maps) are generalized arrays by definition.
> So, maps in PostgreSQL should become a generalizations of an currently
> existing arrays.
> Furthermore, if we speak about generalization, map keys must be arbitrary
> typed. And yes, ordering operator must exists for a key type and so on...
> Otherwise it will be specialized type just for fancy operator[] with
> text argument user "friendly", rather than map.
>
>> Hstore works well and a
>> moving it to core doesn't carry a new value. It's not comparable with
>> TSearch2. What I know, contrib modules are not problem for DBA now and
>> Hstore hasn't a "complex" installation like TSearch2 had. More - there
>> are not a security issues that had to be solved with TSearch2.
>>
>> Why we need a Hstore in core? Why Hstore need be in core?
>
> Well, okay. Could you explain by what formal criterion types become
> built-in ?

No I can't. Please, don't understand to me wrong. Usually I am not
against to enhancing a core features. Just I see a significant risk,
so PostgreSQL will not have a associative arrays ever, so I am talking
about it. If I remember well, then in core are very old types from
academic era and types that are necessary for ansi sql conformance.
All others are controversial - there was a big war about XML, there is
still very unsure JSON. TSearch2 is very specific. Very handy type
like "citext" isn't in core. Significant argument for implementation a
type in core is request on parser support.

This is analogy to intarray contrib module. It's same. I am sure, so
you don't want to use a arrays as was implemented in intarray module.

>>
>> Back to plpython. There is possibility to call a external library
>> without linking. So Hstore must not be in core - and PL/Python can
>> call it.
>
> BTW. Keys of maps in Python can be differently typed.
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>> >>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >>
>> >> >
>> >> >>
>> >> >> or with constructor
>> >> >>
>> >> >> somevar = ARRAY[key1 => value1, key2 => value2, .. ]
>> >> >>
>> >> >> or some similar.
>> >> >>
>> >> >> Regards
>> >> >>
>> >> >> Pavel Stehule
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > // Dmitriy.
>> >> >
>> >> >
>> >> >
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>
>
> 2010/12/14 Pavel Stehule <pavel.stehule@gmail.com>
>>
>> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> >
>> >
>> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >>
>> >> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> >> >
>> >> >
>> >> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >> >>
>> >> >> 2010/12/13 Dmitriy Igrishin <dmitigr@gmail.com>:
>> >> >> > There are a lot of operators and functions to work with hstore.
>> >> >> > Does it worth it to implement similar things only to make it
>> >> >> > possible using operator [] ?
>> >> >>
>> >> >> yes
>> >> >>
>> >> >> >
>> >> >> > 2010/12/13 Pavel Stehule <pavel.stehule@gmail.com>
>> >> >> >>
>> >> >> >> >>
>> >> >> >> >> name and interface - hstore is designed as external module - a
>> >> >> >> >> internal class can be designed different.
>> >> >> >> > Could you actually name such a difference rather than pointing
>> >> >> >> > to
>> >> >> >> > some
>> >> >> >> > airily
>> >> >> >> > hint of one? That would make it way much easier to see where
>> >> >> >> > you
>> >> >> >> > want
>> >> >> >> > to
>> >> >> >> > go.
>> >> >> >>
>> >> >> >> My idea is:
>> >> >> >>
>> >> >> >> somevar['key'] = value
>> >> >> >> value = somevar['key'];
>> >> >> >
>> >> >> > What type of <value> is? Can it be assoc. array ?
>> >> >> > Is it possible to indexing assoc. array by position ?
>> >> >> > Any many many other questions can be there. So,
>> >> >> > I don't think that assoc. arrays has common interface.
>> >> >> > Its still specialized type.
>> >> >>
>> >> >> It's question. Minimally it can be a any known (defined) type -
>> >> >> composite type too. It would be nice if we can store data in native
>> >> >> format with constraints. Now Hstore can store only text - (note:
>> >> >> It's
>> >> >> terrible hard to write this as external module, so Hstore does
>> >> >> maximum
>> >> >> what is possible).
>> >> >>
>> >> >> > But, Pavel, I feel you idea. You want to make the syntax
>> >> >> > clear in particular...
>> >> >>
>> >> >> I like a possibility to define own types in pg. But sometimes, and
>> >> >> associative arrays is it, created interface is "too specific" - like
>> >> >> Hstore is it. PostgreSQL doesn't allow to extend a parser - and
>> >> >> Hstore
>> >> >> respects it in design. So when we could to move hstore functionality
>> >> >> to core, we can extend a parser, and we can create some general
>> >> >> usable
>> >> >> API. It can be big plus for stored procedures programming. This is
>> >> >> just my opinion - when Hstore will be in core, then we will not have
>> >> >> a
>> >> >> native associative array ever, so from my perspective is better
>> >> >> Hstore
>> >> >> as contrib module.
>> >> >
>> >> > In my opinion, hstore is defined and implemented well. Its complete
>> >> > in
>> >> > most
>> >> > cases. Therefore hstore is mature enough to be in core.
>> >> >
>> >> > On the other hand associative arrays should be implemented from
>> >> > scratch.
>> >> > Very well. Let it be. But how integration hstore in core today can
>> >> > interfere
>> >> > with implementation of support for associative arrays in future ? Is
>> >> > it
>> >> > will
>> >> > a big problem ?
>> >>
>> >> I think so it can be a problem. Any second implemented feature will
>> >> have a handicap, because there will be a similar and realised feature.
>> >> Maybe I am too pessimist, but  there are very minimal probability to
>> >> common existence two similar features in core like hstore or
>> >> associative arrays.  And because associative arrays are more general
>> >> than hstore, I prefer a associative arrays.
>> >
>> > Okay. According to
>> > http://www.postgresql.org/docs/9.0/static/arrays.html
>> > PostreSQL array - collection of values of the same type -- built-in or
>> > user-defined. Assoc. arrays (maps) are generalized arrays by definition.
>> > So, maps in PostgreSQL should become a generalizations of an currently
>> > existing arrays.
>> > Furthermore, if we speak about generalization, map keys must be
>> > arbitrary
>> > typed. And yes, ordering operator must exists for a key type and so
>> > on...
>> > Otherwise it will be specialized type just for fancy operator[] with
>> > text argument user "friendly", rather than map.
>> >
>> >> Hstore works well and a
>> >> moving it to core doesn't carry a new value. It's not comparable with
>> >> TSearch2. What I know, contrib modules are not problem for DBA now and
>> >> Hstore hasn't a "complex" installation like TSearch2 had. More - there
>> >> are not a security issues that had to be solved with TSearch2.
>> >>
>> >> Why we need a Hstore in core? Why Hstore need be in core?
>> >
>> > Well, okay. Could you explain by what formal criterion types become
>> > built-in ?
>>
>> No I can't. Please, don't understand to me wrong. Usually I am not
>> against to enhancing a core features. Just I see a significant risk,
>> so PostgreSQL will not have a associative arrays ever, so I am talking
>> about it. If I remember well, then in core are very old types from
>> academic era and types that are necessary for ansi sql conformance.
>> All others are controversial - there was a big war about XML, there is
>> still very unsure JSON. TSearch2 is very specific. Very handy type
>> like "citext" isn't in core. Significant argument for implementation a
>> type in core is request on parser support.
>
> I believe that truth is born in the debate and I understand you.
> Restraint of developers and careful considering of each idea before its
> acceptance does PostgreSQL outstanding. Thanks you, hackers !
> I want you understand me too. I considering hstore as a data type.
> It neither an array nor a map. Yes, it was created to store semi-
> structured data in key => value pairs. But it is a type. Hence,
> PostgreSQL allows to create array of hstore -- hstore[].
> As I mentioned above, assoc. array (map) is a generalization of array.
> So, I would not mess map (array) with hstore (data type). In fact,
> if PostgreSQL will have a support for maps it should allow to create a
> map hstore, i.e. pairs of <TYPE> (key) => hstore (value).
> Obviously that map support should be in core if we speak about
> generalization of arrays. And even if hstore remain as contrib it
> should be possible to create mentioned map of hstores as well as
> array of hstores, because, again, hstore is a type by definition.
> I like the idea with maps in PostgreSQL if it will be truly
> generalization of existing arrays. It would be great!
> Responding to you question about why hstore should be in core,
> summary:
> 1. Because it proven, well designed, stable, complete;
> 2. Because there are many users of hstore;
> 3. As mentioned by Oleg Bartunov it brings better dump/restore
>     support.

only point 3 is argument for moving to core. And I am not sure. I can
speak, why not "citext" ?

>
>> This is analogy to intarray contrib module. It's same. I am sure, so
>> you don't want to use a arrays as was implemented in intarray module.
>
> Agree. Furthermore, when we first learned about hstore we are scared
> that it is a contib module (considering by us as a third party, not proven
> solution) rather than a built-in type. But after some experience we
> found it very powerful, useful and stable. It is truly useful data type.
> This is yet another argument why I want to see hstore as a built-in type.
>>

what will be changed for you, when Hstore will be in core?

Pavel


>> >>
>> >> Back to plpython. There is possibility to call a external library
>> >> without linking. So Hstore must not be in core - and PL/Python can
>> >> call it.
>> >
>> > BTW. Keys of maps in Python can be differently typed.
>> >>
>> >> Regards
>> >>
>> >> Pavel Stehule
>> >>
>> >>
>> >> >>
>> >> >> Regards
>> >> >>
>> >> >> Pavel Stehule
>> >> >>
>> >> >> >
>> >> >> >>
>> >> >> >> or with constructor
>> >> >> >>
>> >> >> >> somevar = ARRAY[key1 => value1, key2 => value2, .. ]
>> >> >> >>
>> >> >> >> or some similar.
>> >> >> >>
>> >> >> >> Regards
>> >> >> >>
>> >> >> >> Pavel Stehule
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > // Dmitriy.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > // Dmitriy.
>> >> >
>> >> >
>> >> >
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>
>
>
> --
> // Dmitriy.
>
>
>

On Mon, Dec 13, 2010 at 2:50 AM, Jan Urbański <wulczer@wulczer.org> wrote:
> It would be cool to be able to transparently use hstores as Python
> dictionaries and vice versa. It would be easy enough with hstore as a
> core type, but with hstore as an addon it's not that easy.
>
> There was talk about including hstore in core, is there still chance for
> that to happen in 9.1? I'd like to include hstore<->dict handling, but
> with hstore out-of-core the only half-sane way I see is:
>  * hack PL/Python's makefile to add -Icontrib/hstore (yuck!)
>  * create an extension module for Python that knows how to handle
> hstores that would live next to plpython.so
>  * install it in $libdir on make install
>  * when PL/Python receives or is asked to create an hstore, load the
> extension module and use it to parse the value (ugly, probably slow)
>  * the module would also have to make sure hstore.so is loaded in the
> database, which in itself is not pretty, as it would refer to
> hstore_in/out symbols
>
> I wrote a module that can be used with current PL/Python to simplify
> hstore handling (https://github.com/wulczer/pyhstore), but it suffers
> from most of the aforementioned problems, and on top of that you get
> hstore->text->dict instead of just hstore->dict, which sucks.

Can we arrange to pg_dlopen() the hstore module instead of linking
against it directly?  Seems like that might let you use it when
available without making it a hard requirement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> Can we arrange to pg_dlopen() the hstore module instead of linking
> against it directly?  Seems like that might let you use it when
> available without making it a hard requirement.

That doesn't deal with the issues of (a) what is a reasonable fallback
when the module's not there, and (b) how do you identify which type OID
is really hstore?  ("The one named hstore" is the wrong answer.)
Both of these problems are trivial for an in-core datatype, and not at
all trivial if it's an add-on.
        regards, tom lane

On Mon, Dec 13, 2010 at 9:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Can we arrange to pg_dlopen() the hstore module instead of linking
>> against it directly?  Seems like that might let you use it when
>> available without making it a hard requirement.
>
> That doesn't deal with the issues of (a) what is a reasonable fallback
> when the module's not there,

Well, if you were passed an hstore argument, and hstore can't be
loaded, wouldn't throwing an error be fairly reasonable?

> and (b) how do you identify which type OID
> is really hstore?  ("The one named hstore" is the wrong answer.)

Ugggh.  This issue of needing to identify things by OID keeps coming
up, and it bugs the heck out of me.  As an internal identifier, OIDs
are great, but the fact that they leak out and people need to care
about them is really not good.

I'm not super-eager to suck hstore into core.  As contrib modules go,
it's one of the better candidates, being time tested and popular.  But
I'd really like to think that standalone modules are a viable way to
distribute software, and that issues like this have a better solution
than "pull everything into core".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Dec 13, 2010 at 9:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> That doesn't deal with the issues of (a) what is a reasonable fallback
>> when the module's not there,

> Well, if you were passed an hstore argument, and hstore can't be
> loaded, wouldn't throwing an error be fairly reasonable?

Obviously that case won't arise if hstore isn't installed.  However,
you might want to make use of hstore in other ways, like translating
a hash *to* hstore.  No doubt you can always lobotomize your ideas
to the point where no such case can occur, but that doesn't seem
like a pleasant restriction in general.

> I'm not super-eager to suck hstore into core.  As contrib modules go,
> it's one of the better candidates, being time tested and popular.  But
> I'd really like to think that standalone modules are a viable way to
> distribute software, and that issues like this have a better solution
> than "pull everything into core".

I agree with that in general, but we do not have a very viable solution
for letting independent extensions interact.

It seems like what we need at this point is a detailed, non-arm-waving
design for what Jan would do in pl/python if hstore were in core.  Then
we can look at it and see exactly what we'd lose from keeping hstore out
of core and then decide whether it's worth pulling in.

We should also consider the JSON alternative that was muttered about
upthread.  There's more than one way to hash a hash ...
        regards, tom lane

On Mon, Dec 13, 2010 at 10:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I agree with that in general, but we do not have a very viable solution
> for letting independent extensions interact.

Can we create one?

> It seems like what we need at this point is a detailed, non-arm-waving
> design for what Jan would do in pl/python if hstore were in core.  Then
> we can look at it and see exactly what we'd lose from keeping hstore out
> of core and then decide whether it's worth pulling in.

Sure.

> We should also consider the JSON alternative that was muttered about
> upthread.  There's more than one way to hash a hash ...

Well, that's the thing.  If we decree that Python dictionaries map
onto hstore, does that mean they DON'T map onto json, or Pavel's
hand-wavy proposal for associative arrays?  Because from 10,000 feet
it sure isn't obvious why hstore would be preferable to either of the
other two, except that it already exists and the early bird gets the
worm.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Dec 13, 2010, at 7:19 PM, Robert Haas wrote:
> If we decree that Python dictionaries map
> onto hstore, does that mean they DON'T map onto json, or Pavel's
> hand-wavy proposal for associative arrays?  Because from 10,000 feet
> it sure isn't obvious why hstore would be preferable to either of the
> other two, except that it already exists and the early bird gets the
> worm.

I'll mention that psycopg2, the most widely Python DBI implementation for PostgreSQL, has a built-in mapping of hstore
todict, so signs are definitely pointing towards a hstore == dict standardization.  It also suffers from the problem
thatit needs to sniff the hstore OID, which is somewhat annoying, especially in a web environment where the sniff has
tohappen repeatedly. 

--
-- Christophe Pettus  xof@thebuild.com

2010/12/14 Robert Haas <robertmhaas@gmail.com>:
> On Mon, Dec 13, 2010 at 9:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> Can we arrange to pg_dlopen() the hstore module instead of linking
>>> against it directly?  Seems like that might let you use it when
>>> available without making it a hard requirement.
>>
>> That doesn't deal with the issues of (a) what is a reasonable fallback
>> when the module's not there,
>
> Well, if you were passed an hstore argument, and hstore can't be
> loaded, wouldn't throwing an error be fairly reasonable?
>
>> and (b) how do you identify which type OID
>> is really hstore?  ("The one named hstore" is the wrong answer.)

we can search a OID function descriptor. If you know a name and
parameter's type, then isn't a problem detect function. It can be
based on  FuncnameGetCandidates.

Regards

Pavel Stehule

>
> Ugggh.  This issue of needing to identify things by OID keeps coming
> up, and it bugs the heck out of me.  As an internal identifier, OIDs
> are great, but the fact that they leak out and people need to care
> about them is really not good.
>
> I'm not super-eager to suck hstore into core.  As contrib modules go,
> it's one of the better candidates, being time tested and popular.  But
> I'd really like to think that standalone modules are a viable way to
> distribute software, and that issues like this have a better solution
> than "pull everything into core".
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule <pavel.stehule@gmail.com> writes:
>> On Mon, Dec 13, 2010 at 9:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> and (b) how do you identify which type OID
>>> is really hstore?  ("The one named hstore" is the wrong answer.)

> we can search a OID function descriptor. If you know a name and
> parameter's type, then isn't a problem detect function. It can be
> based on  FuncnameGetCandidates.

You haven't noticed the circularity in that argument?
        regards, tom lane

2010/12/14 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>>> On Mon, Dec 13, 2010 at 9:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> and (b) how do you identify which type OID
>>>> is really hstore?  ("The one named hstore" is the wrong answer.)

COM architecture uses a uuid, but Microsoft leaves this idea.

can we identify installed extension? not now. Then if there is hstore
extension, then type hstore should be correct.

Has a extension some descriptor? like org/postgresql/contrib/hstore ??

Regards

Pavel

>
>> we can search a OID function descriptor. If you know a name and
>> parameter's type, then isn't a problem detect function. It can be
>> based on  FuncnameGetCandidates.
>
> You haven't noticed the circularity in that argument?
>

sorry - I didn't understand well.


>                        regards, tom lane
>

> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Dec 13, 2010 at 9:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It seems like what we need at this point is a detailed, non-arm-waving
> design for what Jan would do in pl/python if hstore were in core.   Then
> we can look at it and see exactly what we'd lose from keeping hstore out
> of core and then decide whether it's worth pulling in.

A function with a hstore parameter called x would get a Python dictionary as its input. A function said to be returning
ahstore could return a dictionary and if it would have only string keys/values, it would be changed into a hstore (and
ifnot, throw an ERROR). See the README for pyhstore and take out pyhstore.parse/serialize. 

There is already type conversion infrastructure in plpython, in the form of two functions with a switch that takes the
input/outputtype's OID. It'd be adding a branch to the switches and taking the code from my pyhstore module to parse
thehstore to and fro. 

Then there's the compatibility argument. Hstores used to be passed as strings, so it will break user code. I hate
behaviour-changingGUCs as much as anyone, but it seems the only option... 

> We should also consider the JSON alternative that was muttered about
> upthread.   There's more than one way to hash a hash ...

JSON, when it gets there, can work the same, no problem. JSON params are made available as Python dicts (albeit nested)
andvice versa. 

How about going the other way around? Hstore would produce hstore_plpython.so apart from hstore.so, if compiling with
--with-python.Loading hstore_plpython would register parser functions for hstores in plpython. Additionally this could
leadto hstore_plperl in the future etc. 

We would need to design some infrastructure for using such hooks in plpython (and in embedded PLs in general) but then
wesidestep the whole issue. 

Also, this would mean that loading an extension module changes the behaviour of already defined functions. Maybe the
parserscould simply go into hstore.so? That is, compile and link in hstore_plpython.o if building --with-python? Not
surehow the packagers will like it (would need 2 hstore packages, right?). 

Cheers,
Jan

On mån, 2010-12-13 at 10:55 -0500, Tom Lane wrote:
> We don't normally invent specialized syntax for a specific datatype.
> Not even if it's in core.

I think the idea would be to make associative arrays a kind of
second-order object like arrays, instead of a data type.

On mån, 2010-12-13 at 08:50 +0100, Jan Urbański wrote:
> It would be cool to be able to transparently use hstores as Python
> dictionaries and vice versa. It would be easy enough with hstore as a
> core type, but with hstore as an addon it's not that easy.

I have been thinking about this class of problems for a while.  I think
the proper fix is to have a user-definable mapping between types and
languages.  It would be another pair of input/output functions,
essentially.

Peter Eisentraut <peter_e@gmx.net> writes:
> On mån, 2010-12-13 at 10:55 -0500, Tom Lane wrote:
>> We don't normally invent specialized syntax for a specific datatype.
>> Not even if it's in core.

> I think the idea would be to make associative arrays a kind of
> second-order object like arrays, instead of a data type.

I haven't actually figured out what the benefit would be, other than
buzzword compliance and a chance to invent some random nonstandard
syntax.  If the element values all have to be the same type, you've
basically got hstore.  If they are allowed to be different types,
what have you got but a record?  Surely SQL can do composite types
already.
        regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:
> On mån, 2010-12-13 at 08:50 +0100, Jan Urbański wrote:
>> It would be cool to be able to transparently use hstores as Python
>> dictionaries and vice versa. It would be easy enough with hstore as a
>> core type, but with hstore as an addon it's not that easy.

> I have been thinking about this class of problems for a while.  I think
> the proper fix is to have a user-definable mapping between types and
> languages.  It would be another pair of input/output functions,
> essentially.

Interesting thought, but it still leaves you needing to solve the
problem of interconnecting two optional addons ...
        regards, tom lane

On Dec 13, 2010, at 11:37 PM, Jan Urbański wrote:

> A function with a hstore parameter called x would get a Python dictionary as its input. A function said to be
returninga hstore could return a dictionary and if it would have only string keys/values, it would be changed into a
hstore(and if not, throw an ERROR). See the README for pyhstore and take out pyhstore.parse/serialize. 

It doesn't turn a returned dictionary into a RECORD? That's what PL/Perl does, FBOFW.

> There is already type conversion infrastructure in plpython, in the form of two functions with a switch that takes
theinput/output type's OID. It'd be adding a branch to the switches and taking the code from my pyhstore module to
parsethe hstore to and fro. 
>
> Then there's the compatibility argument. Hstores used to be passed as strings, so it will break user code. I hate
behaviour-changingGUCs as much as anyone, but it seems the only option... 

Can you overload the stringification of a dictionary to return the hstore string representation?

> How about going the other way around? Hstore would produce hstore_plpython.so apart from hstore.so, if compiling with
--with-python.Loading hstore_plpython would register parser functions for hstores in plpython. Additionally this could
leadto hstore_plperl in the future etc. 
>
> We would need to design some infrastructure for using such hooks in plpython (and in embedded PLs in general) but
thenwe sidestep the whole issue. 

It would be better if there was some core support for the hash/ditionary/hstore/json/whatever data type, so that you
didn'thave to write a parser. 

Best,

David

On Tue, Dec 14, 2010 at 11:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> On mån, 2010-12-13 at 10:55 -0500, Tom Lane wrote:
>>> We don't normally invent specialized syntax for a specific datatype.
>>> Not even if it's in core.
>
>> I think the idea would be to make associative arrays a kind of
>> second-order object like arrays, instead of a data type.
>
> I haven't actually figured out what the benefit would be, other than
> buzzword compliance and a chance to invent some random nonstandard
> syntax.  If the element values all have to be the same type, you've
> basically got hstore.

Not exactly, because in hstore all the element values have to be,
specifically, text.  Having hstores of other kinds of objects would,
presumably, be useful.

> If they are allowed to be different types,
> what have you got but a record?  Surely SQL can do composite types
> already.

I think I mostly agree with this.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Dec 14, 2010 at 11:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If the element values all have to be the same type, you've
>> basically got hstore.

> Not exactly, because in hstore all the element values have to be,
> specifically, text.  Having hstores of other kinds of objects would,
> presumably, be useful.

Maybe, but I'm sure they'd have far less application than hstore.
There's a reason why that's based on text and not some other type ...
        regards, tom lane

On 12/14/2010 12:06 PM, Robert Haas wrote:
>> I haven't actually figured out what the benefit would be, other than
>> buzzword compliance and a chance to invent some random nonstandard
>> syntax.  If the element values all have to be the same type, you've
>> basically got hstore.
> Not exactly, because in hstore all the element values have to be,
> specifically, text.  Having hstores of other kinds of objects would,
> presumably, be useful.
>

I love hstore, and I've used it a lot, but I don't think there's much 
future in doing this. This is part of what JSON would buy us, isn't it?

cheers

andrew

2010/12/14 Andrew Dunstan <andrew@dunslane.net>:
> On 12/14/2010 12:06 PM, Robert Haas wrote:
>>>
>>> I haven't actually figured out what the benefit would be, other than
>>> buzzword compliance and a chance to invent some random nonstandard
>>> syntax.  If the element values all have to be the same type, you've
>>> basically got hstore.
>>
>> Not exactly, because in hstore all the element values have to be,
>> specifically, text.  Having hstores of other kinds of objects would,
>> presumably, be useful.
>>
>
> I love hstore, and I've used it a lot, but I don't think there's much future
> in doing this. This is part of what JSON would buy us, isn't it?

Well, JSON would give you numbers and booleans, but that's a pretty
small subset of all the types in the universe.  I think the main thing
JSON would give you is hierarchical structure.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2010/12/14 Tom Lane <tgl@sss.pgh.pa.us>:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Dec 14, 2010 at 11:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> If the element values all have to be the same type, you've
>>> basically got hstore.
>
>> Not exactly, because in hstore all the element values have to be,
>> specifically, text.  Having hstores of other kinds of objects would,
>> presumably, be useful.
>
> Maybe, but I'm sure they'd have far less application than hstore.
> There's a reason why that's based on text and not some other type ...
>

I don't think. For example - numeric array indexed with string is
often use case. Now you have to use a PLperl hashs.

Pavel

>                        regards, tom lane
>

2010/12/14 Tom Lane <tgl@sss.pgh.pa.us>:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Dec 14, 2010 at 11:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> If the element values all have to be the same type, you've
>>> basically got hstore.
>
>> Not exactly, because in hstore all the element values have to be,
>> specifically, text.  Having hstores of other kinds of objects would,
>> presumably, be useful.
>
> Maybe, but I'm sure they'd have far less application than hstore.
> There's a reason why that's based on text and not some other type ...

Sure.  You can smash anything to a string, and it's often a very
practical way to go, though not always.

I am not necessarily expressing any interest in building such a
facility, just pointing one way that it might hypothetically have an
advantage over hstore.  Whether it's worth pursuing is another
question.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 14 Dec 2010, Robert Haas wrote:

> On Tue, Dec 14, 2010 at 11:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Peter Eisentraut <peter_e@gmx.net> writes:
>>> On m?n, 2010-12-13 at 10:55 -0500, Tom Lane wrote:
>>>> We don't normally invent specialized syntax for a specific datatype.
>>>> Not even if it's in core.
>>
>>> I think the idea would be to make associative arrays a kind of
>>> second-order object like arrays, instead of a data type.
>>
>> I haven't actually figured out what the benefit would be, other than
>> buzzword compliance and a chance to invent some random nonstandard
>> syntax.  If the element values all have to be the same type, you've
>> basically got hstore.
>
> Not exactly, because in hstore all the element values have to be,
> specifically, text.  Having hstores of other kinds of objects would,
> presumably, be useful.

agree, we already thought about this, but then others got exited to
remove hstore limitations. We, probably, could revive our ideas, so
better now to decide if hstore will be "1st class citizen" in postgres.

>
>> If they are allowed to be different types,
>> what have you got but a record?  Surely SQL can do composite types
>> already.
>
> I think I mostly agree with this.
>
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Tue, 14 Dec 2010, Andrew Dunstan wrote:

>
>
> On 12/14/2010 12:06 PM, Robert Haas wrote:
>>> I haven't actually figured out what the benefit would be, other than
>>> buzzword compliance and a chance to invent some random nonstandard
>>> syntax.  If the element values all have to be the same type, you've
>>> basically got hstore.
>> Not exactly, because in hstore all the element values have to be,
>> specifically, text.  Having hstores of other kinds of objects would,
>> presumably, be useful.
>> 
>
> I love hstore, and I've used it a lot, but I don't think there's much future 
> in doing this. This is part of what JSON would buy us, isn't it?

Just wondering about JSON, are there anyone who signed already to work on JSON 
or it's just a theoretical discussions ? If so, I agree, having JSON 
properly implemented and simple wrapper for hstore just for compatibility, would
be very nice.

>
> cheers
>
> andrew
>
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Tue, Dec 14, 2010 at 12:25 PM, Oleg Bartunov <oleg@sai.msu.su> wrote:
> Just wondering about JSON, are there anyone who signed already to work on
> JSON or it's just a theoretical discussions ? If so, I agree, having JSON
> properly implemented and simple wrapper for hstore just for compatibility,
> would
> be very nice.

Three different people developed patches, and I think we don't really
have unanimity on which way to go with it.  I've kind of been thinking
we should wait for a broader consensus on which way to go with it...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 14 Dec 2010, Robert Haas wrote:

> On Tue, Dec 14, 2010 at 12:25 PM, Oleg Bartunov <oleg@sai.msu.su> wrote:
>> Just wondering about JSON, are there anyone who signed already to work on
>> JSON or it's just a theoretical discussions ? If so, I agree, having JSON
>> properly implemented and simple wrapper for hstore just for compatibility,
>> would
>> be very nice.
>
> Three different people developed patches, and I think we don't really
> have unanimity on which way to go with it.  I've kind of been thinking
> we should wait for a broader consensus on which way to go with it...

AFAIK, they have no index support, which I consider as a big project, so
I think in ideal situation it could be done for 9.2, or even for 9.3 if
there will be no support for developers. We need company, which said I need 
it, I pay for it.
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Robert Haas <robertmhaas@gmail.com> writes:
> 2010/12/14 Andrew Dunstan <andrew@dunslane.net>:
>> On 12/14/2010 12:06 PM, Robert Haas wrote:
>>> Not exactly, because in hstore all the element values have to be,
>>> specifically, text. �Having hstores of other kinds of objects would,
>>> presumably, be useful.

>> I love hstore, and I've used it a lot, but I don't think there's much future
>> in doing this. This is part of what JSON would buy us, isn't it?

> Well, JSON would give you numbers and booleans, but that's a pretty
> small subset of all the types in the universe.

Sure, but once you have those three, the set of remaining use-cases for
a generalized hstore has dropped from epsilon to epsilon cubed.  I don't
think there's much space left there for a useful type that doesn't make
the full jump to "record" (ie, allowing each value to be any SQL type).

Also, the more cases you support, the harder it is to write code that
processes the type, as we already saw in the other thread about
record-access functions.  It's not unlikely that something more flexible
than JSON would be less useful not more so, because of the ensuing usage
complexity.  (This is part of why hstore seems to be occupying a sweet
spot --- it may not cover everything you want to do, but "it's all text"
does simplify usage.)
        regards, tom lane

Oleg Bartunov <oleg@sai.msu.su> writes:
> On Tue, 14 Dec 2010, Robert Haas wrote:
>> On Tue, Dec 14, 2010 at 12:25 PM, Oleg Bartunov <oleg@sai.msu.su> wrote:
>>> Just wondering about JSON, are there anyone who signed already to work on
>>> JSON or it's just a theoretical discussions ?

>> Three different people developed patches, and I think we don't really
>> have unanimity on which way to go with it.  I've kind of been thinking
>> we should wait for a broader consensus on which way to go with it...

> AFAIK, they have no index support, which I consider as a big project, so
> I think in ideal situation it could be done for 9.2, or even for 9.3 if
> there will be no support for developers. We need company, which said I need 
> it, I pay for it.

For the sort of problems we're discussing here, whether there's index
support or not for JSON is practically irrelevant.  I agree we'd want
some nice indexing ability eventually, but it hardly seems like job #1.
        regards, tom lane

On Dec 14, 2010, at 9:31 AM, Robert Haas wrote:

> Three different people developed patches, and I think we don't really
> have unanimity on which way to go with it.  I've kind of been thinking
> we should wait for a broader consensus on which way to go with it...

There needs to be a discussion for that to happen, but it seems to have been dropped. Have the three developers who
workedon patches all given up? 

Best,

David

On 14/12/10 18:05, David E. Wheeler wrote:
> On Dec 13, 2010, at 11:37 PM, Jan Urbański wrote:
> 
>> A function said to be returning a hstore could return a dictionary and if it would have only string keys/values, it
wouldbe changed into a hstore (and if not, throw an ERROR).
 
> 
> It doesn't turn a returned dictionary into a RECORD? That's what PL/Perl does, FBOFW.

If the function is declared to return a hstore, it transforms the
dictionary to a hstore.

IOW: if the return type of a PL/Python function is "known", PL/Python
will try to convert the object returned by Python into a Postgres type,
according to some rules, that depend on the type. For instance, a if a
function is said to return booleans, PL/Python would take the return
value of the Python function invocation, cast it to a boolean using
Python casting rules and the return a Postgres boolean depending on the
result of the cast. If a type is "unknown", PL/Python just casts it to
string using Python rules and feeds it to the type's input function.

The whole point of this thread is how to make hstore a "known" type.

>> Then there's the compatibility argument. Hstores used to be passed as strings, so it will break user code. I hate
behaviour-changingGUCs as much as anyone, but it seems the only option...
 
> 
> Can you overload the stringification of a dictionary to return the hstore string representation?

Mmm, interesting thought. I don't particularily like it, because mucking
with the stringification of a built-in type is a big POLA violation (and
there would be other problems as well). And you still have to go through
the Python dict -> string -> hstore cycle, instead of cutting the string
step out.

>> How about going the other way around? Hstore would produce hstore_plpython.so apart from hstore.so, if compiling
with--with-python. Loading hstore_plpython would register parser functions for hstores in plpython. Additionally this
couldlead to hstore_plperl in the future etc.
 
>>
>> We would need to design some infrastructure for using such hooks in plpython (and in embedded PLs in general) but
thenwe sidestep the whole issue.
 
> 
> It would be better if there was some core support for the hash/ditionary/hstore/json/whatever data type, so that you
didn'thave to write a parser.
 

I'm not writing the parser, that's the point. You could provide a
pure-Python solution that would do the parsing, but that's fragile, slow
and ugly. The idea is: PL/Python notices that the function is supposed
to return a hstore. It takes the output of the Python call and uses
functions from hstore.so to construct the hstore and return it. Same
thing would happen with json.

Cheers,
Jan

On Dec 14, 2010, at 11:52 AM, Jan Urbański wrote:

> If the function is declared to return a hstore, it transforms the
> dictionary to a hstore.

Oh, right. Duh.

>> Can you overload the stringification of a dictionary to return the hstore string representation?
>
> Mmm, interesting thought. I don't particularily like it, because mucking
> with the stringification of a built-in type is a big POLA violation (and
> there would be other problems as well). And you still have to go through
> the Python dict -> string -> hstore cycle, instead of cutting the string
> step out.

Could you do it with a subclass of Dictionary? I'm thinking only of the params passed to the function here, not
returned.It doesn't matter what the return value stringifies as if you can use functions to do the transformation from
dictto hstore. 

>> It would be better if there was some core support for the hash/ditionary/hstore/json/whatever data type, so that you
didn'thave to write a parser. 
>
> I'm not writing the parser, that's the point. You could provide a
> pure-Python solution that would do the parsing, but that's fragile, slow
> and ugly. The idea is: PL/Python notices that the function is supposed
> to return a hstore. It takes the output of the Python call and uses
> functions from hstore.so to construct the hstore and return it. Same
> thing would happen with json.

Right, that sounds great. No reason why we couldn't support many of these hash-like things, eh? The question then is
justidentifying those types. That would be fantastic for PL/Perl, too. 

Best,

David

On 14/12/10 17:52, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> On mån, 2010-12-13 at 08:50 +0100, Jan Urbański wrote:
>>> It would be cool to be able to transparently use hstores as Python
>>> dictionaries and vice versa. It would be easy enough with hstore as a
>>> core type, but with hstore as an addon it's not that easy.
> 
>> I have been thinking about this class of problems for a while.  I think
>> the proper fix is to have a user-definable mapping between types and
>> languages.  It would be another pair of input/output functions,
>> essentially.
> 
> Interesting thought, but it still leaves you needing to solve the
> problem of interconnecting two optional addons ...

So I've been thinking about hot these two optional addons could
interact, and here's a sketchy plan:

1) the hstore contrib module when compiling with --with-python generates
a loadable hstore_plpython.so
2) hstore_plpython, when loaded, sets a rendezvous variable that points
to a structure containing two parser functions
3) plpython when converting PG datums to Python objects looks for the
hstore's rendezvous variable and if it finds it, it uses the parsers

Problem: what to do it hstore_plpython gets loaded, but hstore is not
yet loaded. hstore_plpython will want to DirectFunctionCall(hstore_in),
so loading hstore_plpython without loading hstore will result in an
ereport(ERROR, "undefined symbol hstore_in") with an errhint of "please
load hstore first". I could live with that, if no one has a better idea.

If that's OK, I'll go and code it. The rest is dressing on top, which I
would happily skip.

3a) optionally, there's a custom GUC for plpython called
plpython_hstore_as_dict that can be "always", "never" or
"when_available". "always" means that if the rendezvous variable is not
set, you get an ERROR when you pass in a hstore variable. "never" means
that regardless of hstore_plpython loading it gets ignored.
"when_available" is what I described above. The default is still to be
bikeshed. I think we can skip that, as someone loading hstore_plpython
is supposed to know that it will affect plpython functions, end of
story. Also: the hstore parsers would detect if the return value is a
dict and if not, would cast it to text and try to parse it. So if you
were hand-crafting your hstores in plpython before, it will still work,
thus making the compatibility argument moot.

Does that sound acceptable?

Jan

On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> how do you identify which type OID is really hstore?


How about an identification field on pg_type?

CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
-- Where the "identifier" is an arbitrary string.

Type information can be looked up by the PL, and the I/O functions can
be dynamically resolved using the identifier.

On Wed, Dec 15, 2010 at 12:19:53AM +0100, Jan Urbański wrote:
> Problem: what to do it hstore_plpython gets loaded, but hstore is not
> yet loaded. hstore_plpython will want to DirectFunctionCall(hstore_in),
> so loading hstore_plpython without loading hstore will result in an
> ereport(ERROR, "undefined symbol hstore_in") with an errhint of "please
> load hstore first". I could live with that, if no one has a better idea.

Correction: you won't get the helpful errhint, because the ERROR will be thrown when some does LOAD. And it still does
notsolve the problem of knowing whether it's a hstore that's been passed in to you.
 

OK, here's another master plan:

1) hstore_plplython, when loaded, looks for a type called "hstore". If you created a "hstore" type that does not come
fromhstore.so, and you still load hstore_plpython, you deserve a segfault. If there is no type "hstore", it throws an
ERROR.If it finds a type with that name, it creates a rendezvous variable with the name OID_plpython_parsers that
pointsto two functions. These functions use the looked up type's I/O funcs and transform things you pass to them from
andinto Python objects.
 

2) plpython, when receiving an object with a type with the name X, takes its OID, it the OID happens not to be one of
BOOLOID,FLOAT8OID etc, it does one last push of looking for a rendezvous variable OID_plpython_parsers and if it finds
one,uses its parsers. If it doesn't find it, it does what it did now (cast to text and pass it to the type's I/O
func).

That looks almost good to me. It's mildly annoying that you can't load hstore_plpython before hstore, but I could live
withthat.
 

Observe that this allows you to write a isbn_plpython module that would expose parsers for ISBN for python (or
json_plpython),as well as hstore_perl, isbn_tcl and so on. It piggybacks on the rendezvous variables mechanism, and
maybein the future you could get some kind of official support in the backend for this kind of things (ie. a hash table
inTopLevelContext keyed on the OIDs of the type and the language).
 

So I'm going to try this approach now.

Cheers,
Jan

Jan Urbański <wulczer@wulczer.org> writes:
> OK, here's another master plan:

> 1) hstore_plplython, when loaded, looks for a type called "hstore". If
you created a "hstore" type that does not come from hstore.so, and you
still load hstore_plpython, you deserve a segfault.

No, you don't.  I said upthread that relying on the name of the type was
a nonstarter, and it still is.  For one thing, this sketch ignores
search path issues.
        regards, tom lane

On Wed, Dec 15, 2010 at 9:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jan Urbański <wulczer@wulczer.org> writes:
>> OK, here's another master plan:
>
>> 1) hstore_plplython, when loaded, looks for a type called "hstore". If
> you created a "hstore" type that does not come from hstore.so, and you
> still load hstore_plpython, you deserve a segfault.
>
> No, you don't.  I said upthread that relying on the name of the type was
> a nonstarter, and it still is.  For one thing, this sketch ignores
> search path issues.

Well then we need a reliable way to identify a type.  What would satisfy you?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> Well then we need a reliable way to identify a type.  What would satisfy you?

Either (1) do nothing (reject this whole proposal) or (2) put hstore
in core where it will have a well-known OID.  While it would be nice to
have some more-workable way to interconnect independent extensions,
I feel no need to either design a solution to that on the spot, or to
accept half-baked approaches to it.
        regards, tom lane

On 15/12/10 15:38, Tom Lane wrote:
> Jan Urbański <wulczer@wulczer.org> writes:
>> OK, here's another master plan:
> 
>> 1) hstore_plplython, when loaded, looks for a type called "hstore". If
> you created a "hstore" type that does not come from hstore.so, and you
> still load hstore_plpython, you deserve a segfault.
> 
> No, you don't.  I said upthread that relying on the name of the type was
> a nonstarter, and it still is.  For one thing, this sketch ignores
> search path issues.

Hm. I wa assuming that if you have a type called "hstore" that's not the
contrib hstore type, but you do install hstore_plpython from contrib,
then you can't expect it to work.

So how about this: hstore when loaded sets a rendezvous variable that
points to its I/O routines, called "org.postgresql.types.hstore".

hstore_plpython looks for that rendezvous variable instead of looking up
the type from the catalogs, and then sets a RVV called
"org.postgresql.parsers.hstore.plpython". The problem now is how
plpython is supposed to know if the object it gets is the same hstore,
and not some other type called hstore.

What would fix it, is if the hstore module could somehow know what OID
did the system assign to it, and would publish its I/O routines *and*
its OID as "org.postgresql.types.hstore". hstore_plpython would then
look for "org.postgresql.types.hstore" and set up
"org.postgresql.parsers.OID.plpython" and plpython would look for
"org.postgresql.parsers.plpython.<WHATEVER-OID-GOT-PASSED>".

It almost looks like we need a unique identifier for the extension type
that's known beforehand by the type writer (which
"org.postgresql.types.hstore" would be)

Cheers,
Jan

On Wed, Dec 15, 2010 at 9:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Well then we need a reliable way to identify a type.  What would satisfy you?
>
> Either (1) do nothing (reject this whole proposal) or (2) put hstore
> in core where it will have a well-known OID.  While it would be nice to
> have some more-workable way to interconnect independent extensions,
> I feel no need to either design a solution to that on the spot, or to
> accept half-baked approaches to it.

I was asking what would satisfy you as regards a reliable way to
identify a type, not what you think we should do about this particular
proposal.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Dec15, 2010, at 02:14 , James William Pye wrote:
> On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> how do you identify which type OID is really hstore?
> 
> How about an identification field on pg_type?
> 
> CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> -- Where the "identifier" is an arbitrary string.

+1

I've wanted something like this a few times when dealing
with custom types within a client. A future protocol version
might even transmit these identifiers instead a the type's OID,
thereby removing the dependency on OID from clients entirely.

best regards,
Florian Pflug

Robert Haas <robertmhaas@gmail.com> writes:
> Well then we need a reliable way to identify a type.  What would satisfy you?

An oid ?

=# select objid    from pg_extension_objects('hstore')    where       class = 'pg_type'::regclass          and objdesc
~'(^|\.)hstore$';objid  
-------16387
(1 row)

You have 4 types in there so you want to be somewhat careful here…

=# select * from pg_extension_objects('hstore') where class = 'pg_type'::regclass; class  | classid | objid |
objdesc        
---------+---------+-------+----------------------pg_type |    1247 | 16387 | type utils.hstorepg_type |    1247 |
16392| type utils.hstore[]pg_type |    1247 | 16466 | type utils.ghstorepg_type |    1247 | 16469 | type
utils.ghstore[]
(4 rows)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Wed, Dec 15, 2010 at 10:00 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Well then we need a reliable way to identify a type.  What would satisfy you?
>
> An oid ?

Wrong probem.  What we need is a way to identify a type without
knowing in advance what its OID is.  In other words, we need to
distinguish between the hstore type that is shipped in contrib, and
some stupid DBA who types "CREATE DOMAIN hstore as text".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> I was asking what would satisfy you as regards a reliable way to
> identify a type, not what you think we should do about this particular
> proposal.

Okay: a preassigned OID is safe.  I haven't seen any other safe
proposals.  Relying on a non-reserved name is transparently unsafe.

[ thinks for awhile ... ]  You could imagine having the hstore module
set up a rendezvous variable containing the OIDs of its type, its
I/O functions, and anything else plpython might need to know.  Except
that the hstore C code doesn't know those OIDs either, at least not
when first loaded.  There's also the problem that you don't really want
plpython's behavior suddenly changing when hstore happens to get loaded
or first used.

Another possibility is that you make the user tell you the
fully-qualified name of the type:
plpython.use_hstore = 'public.hstore'

Such a GUC would also fix the backwards compatibility issues, since
in the absence of a setting you'd continue to use the old behavior.
But other than that configurability angle, this seems pretty ugly.
Also you'd have to think about protecting yourself against a bad
setting, ie the GUC specifies a type that's not hstore.  That might
not be a big problem though, as long as you aren't directly messing
with the type's representation but just calling its I/O functions.
        regards, tom lane

Robert Haas <robertmhaas@gmail.com> writes:
> Wrong probem.  What we need is a way to identify a type without
> knowing in advance what its OID is.  In other words, we need to
> distinguish between the hstore type that is shipped in contrib, and
> some stupid DBA who types "CREATE DOMAIN hstore as text".

Yeah, yeah. Now, what's wrong with the query I sent?

To ease discussion:

=# select objid    from pg_extension_objects('hstore')   where class = 'pg_type'::regclass and objdesc ~
'(^|\.)hstore$';objid
 
-------16387
(1 row)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On 15/12/10 16:11, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I was asking what would satisfy you as regards a reliable way to
>> identify a type, not what you think we should do about this particular
>> proposal.
> 
> Okay: a preassigned OID is safe.  I haven't seen any other safe
> proposals.  Relying on a non-reserved name is transparently unsafe.

We could preassign OIDs to contrib types, but that gives the
not-contrib-nor-core types the cold shoulder.

> Another possibility is that you make the user tell you the
> fully-qualified name of the type:
> 
>     plpython.use_hstore = 'public.hstore'
> 
> Such a GUC would also fix the backwards compatibility issues, since
> in the absence of a setting you'd continue to use the old behavior.

I just had an illumination. The search path problem is the main issue,
as (like you noticed), just calling I/O functions of a type should never
give you anything worse than an ERROR.

> But other than that configurability angle, this seems pretty ugly.
> Also you'd have to think about protecting yourself against a bad
> setting, ie the GUC specifies a type that's not hstore.  That might
> not be a big problem though, as long as you aren't directly messing
> with the type's representation but just calling its I/O functions.

So how about just adding a text column to pg_type and a IDENTIFIER
keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
pushing the argument to the extreme? Someone can change around bool and
text type oids, too... And then hstore_plpython looks up the well-known
identifier, sets up a RVV with the OID and everyone's happy.

Cheers,
Jan

On 15/12/10 16:25, Dmitriy Igrishin wrote:
> 2010/12/15 Jan Urbański <wulczer@wulczer.org>
>> So how about just adding a text column to pg_type and a IDENTIFIER
>> keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
>> pushing the argument to the extreme? Someone can change around bool and
>> text type oids, too... And then hstore_plpython looks up the well-known
>> identifier, sets up a RVV with the OID and everyone's happy.
>>
> How IDENTIFIER differs from name ? org.postgresql.hstore vs hstore ?

Hm, now that I think of it, the only real difference is that you don't
use search_path to look it up. So public.hstore is just as good an
identifier...

I could live with plpython_hstore_type = "public.hstore", I guess.
hstore_plpython would look at that GUC, look up the type, set up a RVV
containing the OID and plpython would use it.

Cheers,
Jan

On Wed, Dec 15, 2010 at 10:15 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Wrong probem.  What we need is a way to identify a type without
>> knowing in advance what its OID is.  In other words, we need to
>> distinguish between the hstore type that is shipped in contrib, and
>> some stupid DBA who types "CREATE DOMAIN hstore as text".
>
> Yeah, yeah. Now, what's wrong with the query I sent?
>
> To ease discussion:
>
> =# select objid
>     from pg_extension_objects('hstore')
>    where class = 'pg_type'::regclass and objdesc ~ '(^|\.)hstore$';
>  objid
> -------
>  16387
> (1 row)

OK, so I guess your point is that I should read the whole email before
replying.  :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
>> 2010/12/15 Florian Pflug <fgp@phlo.org>
>> On Dec15, 2010, at 02:14 , James William Pye wrote:
>> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> >> how do you identify which type OID is really hstore?
>> >
>> > How about an identification field on pg_type?
>> >
>> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
>> > -- Where the "identifier" is an arbitrary string.
>> 
>> I've wanted something like this a few times when dealing
>> with custom types within a client. A future protocol version
>> might even transmit these identifiers instead a the type's OID,
>> thereby removing the dependency on OID from clients entirely.
> 
> In some another tread I've proposed CREATE TYPE ... WITH OID...
Yeah, and I believe type identifiers are probably what you were
really looking for ;-)

> but it was rejected and was proposed to cache OIDs on client side.
> It is right approach, IMO.
Yes, but to cache OIDs you first have to find them. As long as their
name and schema are known, thats easy, but once they aren't you're
pretty much screwed.Since CREATE EXTENSION is going to let you
install an extension into any schema you want, not knowing the schema
is going to be pretty common, I believe. Type identifiers would solve
this, by providing an easy and unambiguous way to find specific types.

> But, IMO, comparing strings to determine type for each parameter
> is not very good idea because it is not so efficient as comparing
> integers, obviously.
That's maybe an argument against a possible future protocol version
that'd transfer type identifiers instead of OIDS. But not against
associating type identifiers with types in the first place, since
after your initial lookup you'd still be comparing OIDs.

best regards,
Florian Pflug

On Dec15, 2010, at 16:34 , Jan Urbański wrote:
> On 15/12/10 16:25, Dmitriy Igrishin wrote:
>> 2010/12/15 Jan Urbański <wulczer@wulczer.org>
>>> So how about just adding a text column to pg_type and a IDENTIFIER
>>> keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
>>> pushing the argument to the extreme? Someone can change around bool and
>>> text type oids, too... And then hstore_plpython looks up the well-known
>>> identifier, sets up a RVV with the OID and everyone's happy.
>>>
>> How IDENTIFIER differs from name ? org.postgresql.hstore vs hstore ?
>
> Hm, now that I think of it, the only real difference is that you don't
> use search_path to look it up. So public.hstore is just as good an
> identifier...

Not if CREATE EXTENSION allows you install hstore into an arbitrary schema.
For pl/python's purposes, requiring the DBA to set plpython_hstore_type
accordingly might work, but clients need to be able to reliably find hstore
too. For them, having to specify the schema of every non-core type your
database adapter might support isn't exactly ideal...

best regards,
Florian Pflug

On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
> 2010/12/15 Florian Pflug <fgp@phlo.org>
> On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
> >> 2010/12/15 Florian Pflug <fgp@phlo.org>
> >> On Dec15, 2010, at 02:14 , James William Pye wrote:
> >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> >> >> how do you identify which type OID is really hstore?
> >> >
> >> > How about an identification field on pg_type?
> >> >
> >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> >> > -- Where the "identifier" is an arbitrary string.
> >>
> >> I've wanted something like this a few times when dealing
> >> with custom types within a client. A future protocol version
> >> might even transmit these identifiers instead a the type's OID,
> >> thereby removing the dependency on OID from clients entirely.
> >
> > In some another tread I've proposed CREATE TYPE ... WITH OID...
> Yeah, and I believe type identifiers are probably what you were
> really looking for ;-)
> Indeed, but why OID cannot serve as identifier in this case ? Why to
> encode the code ? :-)
Because there are only 2^32 OIDs, so if people start picking them at
random, sooner or later there will be collisions.

> Type identifiers would solve
> this, by providing an easy and unambiguous way to find specific types.
> Agree with 1st assertion but disagree with 2nd. If I understand correctly,
> "identifier" is a second name for type (object), but Java-styled, right ?
> It probably does solve the problem if there are will be convention that
> types org.postgresql.* are reserved.
Yeah, that'd be the idea. If everyone uses reversed DNS-style names, and
everyone picks a name belonging to a DNS zone under his control, there
cannot be any collisions. At least for java packages, this seems to work
pretty nicely.

> But why not reserve name of type
> "hstore" and prevent the user to create type with this reserved name ?
> All this tells me one thing - to avoid conflicts of naming of specific types
> it is necessary to make them built-in.
None of these solutions scale well.

best regards,
Florian Pflug

On Wed, Dec 15, 2010 at 1:22 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
> millions, e.g. can be enough.

No, they can't.  PostgreSQL is already deployed without any such
restriction.  You can "reserve" those OIDs because they may already be
in use on any given system.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Dec 15, 2010 at 2:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Dec 15, 2010 at 1:22 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
>> millions, e.g. can be enough.
>
> No, they can't.  PostgreSQL is already deployed without any such
> restriction.  You can "reserve" those OIDs because they may already be
> in use on any given system.

Err, you CAN'T reserve these OIDs because blah blah.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

2010/12/15 Dmitriy Igrishin <dmitigr@gmail.com>:
>
>
> 2010/12/15 Florian Pflug <fgp@phlo.org>
>>
>> On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
>> > 2010/12/15 Florian Pflug <fgp@phlo.org>
>> > On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
>> > >> 2010/12/15 Florian Pflug <fgp@phlo.org>
>> > >> On Dec15, 2010, at 02:14 , James William Pye wrote:
>> > >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> > >> >> how do you identify which type OID is really hstore?
>> > >> >
>> > >> > How about an identification field on pg_type?
>> > >> >
>> > >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
>> > >> > -- Where the "identifier" is an arbitrary string.
>> > >>
>> > >> I've wanted something like this a few times when dealing
>> > >> with custom types within a client. A future protocol version
>> > >> might even transmit these identifiers instead a the type's OID,
>> > >> thereby removing the dependency on OID from clients entirely.
>> > >
>> > > In some another tread I've proposed CREATE TYPE ... WITH OID...
>> > Yeah, and I believe type identifiers are probably what you were
>> > really looking for ;-)
>> > Indeed, but why OID cannot serve as identifier in this case ? Why to
>> > encode the code ? :-)
>> Because there are only 2^32 OIDs, so if people start picking them at
>> random, sooner or later there will be collisions.
>
> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
> millions, e.g. can be enough.
>
>>
>> > Type identifiers would solve
>> > this, by providing an easy and unambiguous way to find specific types.
>> > Agree with 1st assertion but disagree with 2nd. If I understand
>> > correctly,
>> > "identifier" is a second name for type (object), but Java-styled, right
>> > ?
>> > It probably does solve the problem if there are will be convention that
>> > types org.postgresql.* are reserved.
>> Yeah, that'd be the idea. If everyone uses reversed DNS-style names, and
>> everyone picks a name belonging to a DNS zone under his control, there
>> cannot be any collisions. At least for java packages, this seems to work
>> pretty nicely.
>>
>> > But why not reserve name of type
>> > "hstore" and prevent the user to create type with this reserved name ?
>> > All this tells me one thing - to avoid conflicts of naming of specific
>> > types
>> > it is necessary to make them built-in.
>> None of these solutions scale well.
>
> Well, If there are will be identifiers for each type, e.g.
> org.postgresql.integer, why
> they need to be built-in ? For "historical reasons" ? :-)
> Let them also be in contribs...

some types are used in system tables, so without support of these
types, then you can't to add a new types. It's a egg-chicken problem

Pavel

>>
>> best regards,
>> Florian Pflug
>>
>>
>
>
>
> --
> // Dmitriy.
>
>
>

On tis, 2010-12-14 at 11:52 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > On mån, 2010-12-13 at 08:50 +0100, Jan Urbański wrote:
> >> It would be cool to be able to transparently use hstores as Python
> >> dictionaries and vice versa. It would be easy enough with hstore as a
> >> core type, but with hstore as an addon it's not that easy.
> 
> > I have been thinking about this class of problems for a while.  I think
> > the proper fix is to have a user-definable mapping between types and
> > languages.  It would be another pair of input/output functions,
> > essentially.
> 
> Interesting thought, but it still leaves you needing to solve the
> problem of interconnecting two optional addons ...

First you create the language and the type (in any order), and then you
create an additional SQL-level designation that connects the two.

In fact, the SQL standard contains something very similar for connecting
user-defined types to host languages.  So adapting that syntax a little,
it could work like this:

CREATE LANGUAGE plpython;

CREATE FUNCTION ...
...
CREATE TYPE hstore ...;

CREATE FUNCTION hstore_to_plpython(hstore) RETURNS internal ...
CREATE FUNCTION plpython_to_hstore(internal) RETURNS plpython ...

CREATE TRANSFORMS FOR hstore (TO plpython WITH hstore_to_plpython, FROM
plpython WITH plpython_to_hstore);


A shorter term solution that avoids creating a whole lot of SQL
infrastructure might be to write out the same transform specification
using a configuration variable, for example

plpython.transforms = 'hstore:public.hstore_to_plpython:public.plpython_to_hstore,...'

Florian Pflug <fgp@phlo.org> writes:
> Not if CREATE EXTENSION allows you install hstore into an arbitrary schema.

It also allows you to change it after the fact, and to easily track it
down. Here's an updated version of the query to find the hstore type OID
reliably once we have extensions in:

dim=# SELECT t.oid       FROM pg_extension_objects('hstore') o            JOIN pg_type t ON t.oid = o.objid
             AND o.classid = 'pg_type'::regclass       WHERE t.typname = 'hstore'; oid   
-------16393
(1 row)

For listing all the hstore objects interactively, use \dx hstore.

> For pl/python's purposes, requiring the DBA to set plpython_hstore_type
> accordingly might work, but clients need to be able to reliably find hstore
> too. For them, having to specify the schema of every non-core type your
> database adapter might support isn't exactly ideal...

Another reason why you will like the extension's patch :)

If you think you need the schema where the extension's objects are
living, there it is (for interactive use, just issue \dx):

=# SELECT n.nspname, e.extname    FROM pg_catalog.pg_extension e         LEFT JOIN pg_catalog.pg_depend d ON d.objid =
e.oid               AND d.refclassid = 'pg_catalog.pg_namespace'::regclass          LEFT JOIN pg_catalog.pg_namespace n
ONn.oid = d.refobjid    WHERE extname = 'hstore';nspname | extname  
---------+---------utils   | hstore
(1 row)


--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Dec15, 2010, at 21:28 , Dimitri Fontaine wrote:
> Florian Pflug <fgp@phlo.org> writes:
>> Not if CREATE EXTENSION allows you install hstore into an arbitrary schema.
>
> It also allows you to change it after the fact, and to easily track it
> down. Here's an updated version of the query to find the hstore type OID
> reliably once we have extensions in:
>
> <snipped SQL>
That's certainly cool, but having to use different methods to find a type
depending on how it has been installed isn't exactly ideal. And not every
user-defined type will be installed via CREATE EXTENSION.

Thus I still believe something like a type identifier that is independent from
it's name and schema would be nice to have.

In case you wonder if than means every object should have such a handle -
they should *not*. What makes types special is that they are used on
the protocol level, not only on the SQL level. Thus, handling them
frequently falls into the real of client libraries, not individual
client applications, making it more important to be able to handle them
in an application-agnostic way.

best regards,
Florian Pflug