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

Hey hackers@,<br /><br />libpq execution function works with OIDs. In some cases it is<br />highly recommended specify
OIDsof parameters according to<br />libpq documentation.<br />While developing a database application with libpq and
if<br/> application works with custom data types IMO reasonable to<br />provide developer extended type creation
syntax,e.g.<br />  CREATE TYPE my_type ... WITH OID = 12345;<br />Yes, it is possible to make dump of the database with
oids,<br/> but if developer prefer to hard code OIDs in the application it<br />would be more convenient for him to use
syntaxabove. <br />Btw, there is already Oid lo_import_with_oid function in large<br />objects libpq's API which are
veryuseful.<br /><br />What do you think about it?<br /><br />-- <br />// Dmitriy.<br /><br /><br /> 

On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin <dmitigr@gmail.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@gmail.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@dunslane.net> wrote:
> On 12/07/2010 10:02 AM, Merlin Moncure wrote:
>> On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin<dmitigr@gmail.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@gmail.com> writes:
> On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan <andrew@dunslane.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@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan <andrew@dunslane.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@gmail.com> writes:
> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.us>:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.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@postgresql.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@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane <tgl@sss.pgh.pa.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