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.

Search Discussions

  • Merlin Moncure at Dec 7, 2010 at 3:09 pm

    On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishin 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
  • Andrew Dunstan at Dec 7, 2010 at 3:45 pm

    On 12/07/2010 10:02 AM, Merlin Moncure wrote:
    On Tue, Dec 7, 2010 at 9:10 AM, Dmitriy Igrishinwrote:
    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
  • Merlin Moncure at Dec 7, 2010 at 4:41 pm

    On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan 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) :-)
  • Tom Lane at Dec 7, 2010 at 4:49 pm

    Merlin Moncure writes:
    On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan 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
  • Merlin Moncure at Dec 7, 2010 at 5:30 pm

    On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane wrote:
    Merlin Moncure <mmoncure@gmail.com> writes:
    On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan 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
  • Tom Lane at Dec 7, 2010 at 5:37 pm

    Merlin Moncure writes:
    On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane 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
  • Pavel Stehule at Dec 7, 2010 at 5:57 pm

    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 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
  • Dmitriy Igrishin at Dec 7, 2010 at 6:17 pm
    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>
    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 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


    --
    // Dmitriy.
  • Merlin Moncure at Dec 7, 2010 at 5:57 pm

    On Tue, Dec 7, 2010 at 12:37 PM, Tom Lane wrote:
    Merlin Moncure <mmoncure@gmail.com> writes:
    On Tue, Dec 7, 2010 at 11:49 AM, Tom Lane 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
  • Dmitriy Igrishin at Dec 8, 2010 at 8:51 am
    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>
    On Tue, Dec 7, 2010 at 10:45 AM, Andrew Dunstan 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) :-)


    --
    // Dmitriy.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedDec 7, '10 at 2:10p
activeDec 8, '10 at 8:51a
posts11
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase