[PostgreSQL-Hackers] Feature request - CREATE TYPE ... WITH OID = oid_number.

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

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

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) :-)

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

regards, tom lane

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

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

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

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.stehule@gmail.com>

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

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@gmail.com>