Feature request - CREATE TYPE ... WITH OID = oid_number.

Hey hackers@,

libpq execution function works with OIDs. In some cases it is
highly recommended specify OIDs of parameters according to
libpq documentation.
While developing a database application with libpq and if
application works with custom data types IMO reasonable to
provide developer extended type creation syntax, e.g.
CREATE TYPE my_type ... WITH OID = 12345;
Yes, it is possible to make dump of the database with oids,
but if developer prefer to hard code OIDs in the application it
would be more convenient for him to use syntax above.
Btw, there is already Oid lo_import_with_oid function in large
objects libpq's API which are very useful.

What do you think about it?

--
// Dmitriy.

On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> Hey hackers@,
>
> libpq execution function works with OIDs. In some cases it is
> highly recommended specify OIDs of parameters according to
> libpq documentation.
> While developing a database application with libpq and if
> application works with custom data types IMO reasonable to
> provide developer extended type creation syntax, e.g.
>   CREATE TYPE my_type ... WITH OID = 12345;
> Yes, it is possible to make dump of the database with oids,
> but if developer prefer to hard code OIDs in the application it
> would be more convenient for him to use syntax above.
> Btw, there is already Oid lo_import_with_oid function in large
> objects libpq's API which are very useful.

It's possible to do this in 9.0 in a slightly indirect way. See the
contrib/pg_upgrade folder. In particular, grep for
set_next_pg_type_oid.

merlin

On 12/07/2010 10:02 AM, Merlin Moncure wrote:
> On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin<dmitigr(at)gmail(dot)com> wrote:
>> Hey hackers@,
>>
>> libpq execution function works with OIDs. In some cases it is
>> highly recommended specify OIDs of parameters according to
>> libpq documentation.
>> While developing a database application with libpq and if
>> application works with custom data types IMO reasonable to
>> provide developer extended type creation syntax, e.g.
>> CREATE TYPE my_type ... WITH OID = 12345;
>> Yes, it is possible to make dump of the database with oids,
>> but if developer prefer to hard code OIDs in the application it
>> would be more convenient for him to use syntax above.
>> Btw, there is already Oid lo_import_with_oid function in large
>> objects libpq's API which are very useful.
> It's possible to do this in 9.0 in a slightly indirect way. See the
> contrib/pg_upgrade folder. In particular, grep for
> set_next_pg_type_oid.
>
>

This doesn't strike me as very good advice. Those things are not exposed
generally for good reason. The right way to do this surely is to have
the app look up and cache the OIDs it needs rather than hardcode the
values in the application.

cheers

andrew

On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 12/07/2010 10:02 AM, Merlin Moncure wrote:
>> On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin<dmitigr(at)gmail(dot)com>
>>  wrote:
>>>
>>> Hey hackers@,
>>>
>>> libpq execution function works with OIDs. In some cases it is
>>> highly recommended specify OIDs of parameters according to
>>> libpq documentation.
>>> While developing a database application with libpq and if
>>> application works with custom data types IMO reasonable to
>>> provide developer extended type creation syntax, e.g.
>>>   CREATE TYPE my_type ... WITH OID = 12345;
>>> Yes, it is possible to make dump of the database with oids,
>>> but if developer prefer to hard code OIDs in the application it
>>> would be more convenient for him to use syntax above.
>>> Btw, there is already Oid lo_import_with_oid function in large
>>> objects libpq's API which are very useful.
>>
>> It's possible to do this in 9.0 in a slightly indirect way.   See the
>> contrib/pg_upgrade folder.  In particular, grep for
>> set_next_pg_type_oid.
>>
>
> This doesn't strike me as very good advice. Those things are not exposed
> generally for good reason. The right way to do this surely is to have the
> app look up and cache the OIDs it needs rather than hardcode the values in
> the application.

Note he didn't provide reasons why he is asking for this power. Your
assertion is a coded variant of "don't use the binary protocol" which
I happen to think is not very good advice IF you know what you're
doing. We plan on using this feature to support binary transfer of
data between databases through the variadic dblink library we maintain
that uses binary format (but pre 9.0 it reverts to text in many
cases). This can be 2x or more faster than stock dblink in real world
cases.

merlin

(your advice is generally correct however) :-)

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> This doesn't strike me as very good advice. Those things are not exposed
>> generally for good reason. The right way to do this surely is to have the
>> app look up and cache the OIDs it needs rather than hardcode the values in
>> the application.

> Note he didn't provide reasons why he is asking for this power. Your
> assertion is a coded variant of "don't use the binary protocol" which
> I happen to think is not very good advice IF you know what you're
> doing.

Say what? He didn't say that, he said "don't assume that user-defined
types have hard-wired OIDs".

regards, tom lane

On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> This doesn't strike me as very good advice. Those things are not exposed
>>> generally for good reason. The right way to do this surely is to have the
>>> app look up and cache the OIDs it needs rather than hardcode the values in
>>> the application.
>
>> Note he didn't provide reasons why he is asking for this power.  Your
>> assertion is a coded variant of "don't use the binary protocol" which
>> I happen to think is not very good advice IF you know what you're
>> doing.
>
> Say what?  He didn't say that, he said "don't assume that user-defined
> types have hard-wired OIDs".

Well, you're right, strictly speaking. Of course, the OP is not
assuming it, he is enforcing it. And I still think this is a proxy
argument about binary protocol features.

merlin

(Andrew's advice is of course prudent, and should certainly by
typically taken before mine) :-D

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Say what? He didn't say that, he said "don't assume that user-defined
>> types have hard-wired OIDs".

> Well, you're right, strictly speaking. Of course, the OP is not
> assuming it, he is enforcing it.

No, he's wishing he could enforce it. Which will work, mostly, until
the day it doesn't because of a pre-existing collision. And then he'll
be up the creek with a lot of software that he can't fix readily. I
concur with Andrew's advice: don't go there in the first place. Use a
cache to mitigate the costs of looking up user-defined OIDs, and you
won't regret it later.

regards, tom lane

2010/12/7 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Say what?  He didn't say that, he said "don't assume that user-defined
>>> types have hard-wired OIDs".
>
>> Well, you're right, strictly speaking.  Of course, the OP is not
>> assuming it, he is enforcing it.
>
> No, he's wishing he could enforce it.  Which will work, mostly, until
> the day it doesn't because of a pre-existing collision.  And then he'll
> be up the creek with a lot of software that he can't fix readily.  I
> concur with Andrew's advice: don't go there in the first place.  Use a
> cache to mitigate the costs of looking up user-defined OIDs, and you
> won't regret it later.
>

I had to solve similar task, and probably I am not alone. Can pg
supports some cache and some API for "custom oid"? Now, a work with
custom types on C level is little bit unfriendly. There isn't a
problem with builtin types - these are well defined. I agree, so
direct access to oids for custom types isn't a good idea. But some
general API or pattern can be nice - mainly for client side.

regards

Pavel

>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Tue, Dec 7, 2010 at 12:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Say what?  He didn't say that, he said "don't assume that user-defined
>>> types have hard-wired OIDs".
>
>> Well, you're right, strictly speaking.  Of course, the OP is not
>> assuming it, he is enforcing it.
>
> No, he's wishing he could enforce it.  Which will work, mostly, until
> the day it doesn't because of a pre-existing collision.  And then he'll
> be up the creek with a lot of software that he can't fix readily.  I
> concur with Andrew's advice: don't go there in the first place.  Use a
> cache to mitigate the costs of looking up user-defined OIDs, and you
> won't regret it later.

That problem is theoretical as long as you are aware of it and defend
against it properly. Which I do. Also a client side maintained cache
doesn't do squat if you want to transfer data between databases in
binary, which I suppose is another thing I'm not to be doing (nor
would I advise others to do it without disclaiming the risks). Not to
mention, maintaining a cache is harder than it sounds and is not a
route I'd suggest taking either for the most part. We don't even
expose pg_types.h to libpq.

OK, I understand your and Andrew's point of views here. The safest
path is generally the best path. That said, I and other users are
trying to do things that really ought to be able to be done. Short of
replacing oids with type names in the wire format (probably
unworkable) or pushing some of the type library into the client
(ecpg), we are stuck with the status quo. We are doing very cool
things with the status quo.

merlin

The reasons are simple, particularly, I don't want to bloat SQL
with CAST or ::. Its not elegant and looks ugly. If I need to bind
e.g. int or short I don't want write ::integer or ::smallint in SQL,
because I can easily map int to integer via OID...
I don't clearly understand how set_next_pg_type_oid code
can helps me.
Also I don't understand why cache of OIDs can't be used to get
results via PQexecParams in a binary form ? I can do it.
Querying the db for OIDs seems to me a good idea. But:
1. Since the structure of pg_type system catalog can be changed
the developer must be able to determine a libpq version to be able
to implement cross libpq-version of the product (especially library).
So PQversion() should be there :-)
2. To avoid memory overheads (especially in WEB environments)
it would be nice if libpq will keep cache of types metadata as a static
structure per database and provide an API to work with (in this case I
totally agree with Pavel). At least, the API should support rereading
the cache. In this case 1. (PQversion) is not needed -- libpq care it
itself :-)

2010/12/7 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

> 2010/12/7 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> > Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> >> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> Say what? He didn't say that, he said "don't assume that user-defined
> >>> types have hard-wired OIDs".
> >
> >> Well, you're right, strictly speaking. Of course, the OP is not
> >> assuming it, he is enforcing it.
> >
> > No, he's wishing he could enforce it. Which will work, mostly, until
> > the day it doesn't because of a pre-existing collision. And then he'll
> > be up the creek with a lot of software that he can't fix readily. I
> > concur with Andrew's advice: don't go there in the first place. Use a
> > cache to mitigate the costs of looking up user-defined OIDs, and you
> > won't regret it later.
> >
>
> I had to solve similar task, and probably I am not alone. Can pg
> supports some cache and some API for "custom oid"? Now, a work with
> custom types on C level is little bit unfriendly. There isn't a
> problem with builtin types - these are well defined. I agree, so
> direct access to oids for custom types isn't a good idea. But some
> general API or pattern can be nice - mainly for client side.
>
> regards
>
> Pavel
>
> > regards, tom lane
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >
>

--
// Dmitriy.

Hey Merlin,

Do you mean that due to binary transfer it is possible
to determine the type of data transfered to the backend
and therefore there is no need to pass OIDs ?

2010/12/7 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)net>
> wrote:
> > On 12/07/2010 10:02 AM, Merlin Moncure wrote:
> >> On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin<dmitigr(at)gmail(dot)com>
> >> wrote:
> >>>
> >>> Hey hackers@,
> >>>
> >>> libpq execution function works with OIDs. In some cases it is
> >>> highly recommended specify OIDs of parameters according to
> >>> libpq documentation.
> >>> While developing a database application with libpq and if
> >>> application works with custom data types IMO reasonable to
> >>> provide developer extended type creation syntax, e.g.
> >>> CREATE TYPE my_type ... WITH OID = 12345;
> >>> Yes, it is possible to make dump of the database with oids,
> >>> but if developer prefer to hard code OIDs in the application it
> >>> would be more convenient for him to use syntax above.
> >>> Btw, there is already Oid lo_import_with_oid function in large
> >>> objects libpq's API which are very useful.
> >>
> >> It's possible to do this in 9.0 in a slightly indirect way. See the
> >> contrib/pg_upgrade folder. In particular, grep for
> >> set_next_pg_type_oid.
> >>
> >
> > This doesn't strike me as very good advice. Those things are not exposed
> > generally for good reason. The right way to do this surely is to have the
> > app look up and cache the OIDs it needs rather than hardcode the values
> in
> > the application.
>
> Note he didn't provide reasons why he is asking for this power. Your
> assertion is a coded variant of "don't use the binary protocol" which
> I happen to think is not very good advice IF you know what you're
> doing. We plan on using this feature to support binary transfer of
> data between databases through the variadic dblink library we maintain
> that uses binary format (but pre 9.0 it reverts to text in many
> cases). This can be 2x or more faster than stock dblink in real world
> cases.
>
> merlin
>
> (your advice is generally correct however) :-)
>

--
// Dmitriy.