Professional Web Applications Themes

OID Usage - PostgreSQL / PGSQL

Hi ... I am using postgresql 7.4 on a pontencial large DB system, and I am quite happy of the performance of this database, as for now. Only one thing worrys me, and I like to get some pease to my mind about this. I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK constaints, but I use OID in one special situation. When I insert a single row into a table, I like my low level code to be kompatible with mysql ( mysql_insert_id ), and fetch the row that I just inserted. This I ...

  1. #1

    Default OID Usage

    Hi ...

    I am using postgresql 7.4 on a pontencial large DB system, and I am
    quite happy of the performance of this database, as for now. Only one
    thing worrys me, and I like to get some pease to my mind about this.

    I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
    constaints, but I use OID in one special situation. When I insert a
    single row into a table, I like my low level code to be kompatible with
    mysql ( mysql_insert_id ), and fetch the row that I just inserted. This
    I do by using the PGoidValue function, and then select the row by the
    oid. This works quite nice .... but when a table get large, it become a
    big search (seq scan) so I have added an index on oid's on the table
    where I use this trick, and this have helper :-)

    This is the ONLY use I have for the oid's, and I don't use them for
    anything else !

    Now, are there any danger in using this method ? And if there is, how
    can I do this trick without knowing the layout of the table I insert into ?

    Regards

    /BL


    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Bo Lorentsen Guest

  2. #2

    Default Re: OID Usage

    Michael Glaesemann wrote:
    > You can use currval() to get the sequence value that was pulled from
    > your insert. You can check the doentation for usage, as well as
    > searching the archives for discussions of using OIDs as part of your
    > database logic.
    I know this, but i like not to know anything about the metadata of the
    table i use. Basicly using the same functionality, as given in mysql in
    the mysql_insert_id, as I use the same low level code for both DB's
    (until my boss give in totally to PG :-)).

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Bo Lorentsen Guest

  3. #3

    Default Re: OID Usage

    Hi,

    On Fri, 14 Jan 2005, Bo Lorentsen wrote:
    > Michael Glaesemann wrote:
    >
    >> You can use currval() to get the sequence value that was pulled from your
    >> insert. You can check the doentation for usage, as well as searching
    >> the archives for discussions of using OIDs as part of your database logic.
    >
    > I know this, but i like not to know anything about the metadata of the table
    > i use. Basicly using the same functionality, as given in mysql in the
    > mysql_insert_id, as I use the same low level code for both DB's (until my
    > boss give in totally to PG :-)).
    why should your application not want to know about the metadata of it's
    own tables ? That sounds quite strange when you think about it.

    If you name your sequences in a generic way you can alway construct the
    name of the sequence from the name of the table and the id column.

    We use this in our php framework

    function insert_id()
    {
    global $pg_conn;
    if(isset($pg_conn)) {
    $query = sprintf("SELECT currval('%s_%s_seq') AS id",$this->table,$this->id_column);
    $result = pg_query($pg_conn,$query);
    $row = pg_fetch_assoc($result);
    return strval($row["id"]);
    } else {
    return 0;
    }
    }

    Greetings
    Christian

    --
    Christian Kratzer [email]ckcksoft.de[/email]
    CK Software GmbH [url]http://www.cksoft.de/[/url]
    Phone: +49 7452 889 135 Fax: +49 7452 889 136

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Christian Kratzer Guest

  4. #4

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 10:32:18AM +0100, Christian Kratzer wrote:
    > $query = sprintf("SELECT currval('%s_%s_seq') AS
    > id",$this->table,$this->id_column);
    PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
    returns the sequence name for a particular column so you don't have
    to construct it. This is useful when a table or column has been
    renamed, in which case the above will probably break.

    CREATE TABLE foo (fooid serial);
    ALTER TABLE foo RENAME TO bar;
    ALTER TABLE bar RENAME fooid TO barid;
    \d bar
    Table "public.bar"
    Column | Type | Modifiers
    --------+---------+--------------------------------------------------------
    barid | integer | not null default nextval('public.foo_fooid_seq'::text)

    SELECT pg_get_serial_sequence('bar', 'barid');
    pg_get_serial_sequence
    ------------------------
    public.foo_fooid_seq
    (1 row)

    INSERT INTO bar VALUES (DEFAULT);
    SELECT currval(pg_get_serial_sequence('bar', 'barid'));
    currval
    ---------
    1
    (1 row)

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Michael Fuhr Guest

  5. #5

    Default Re: OID Usage

    Christian Kratzer wrote:
    > why should your application not want to know about the metadata of it's
    > own tables ? That sounds quite strange when you think about it.
    Well, the ideer is to be compatible with mysql at the same level in the
    code. This works nicely, as I have descriped, but I am concerned if
    there is any strains attached to this method.

    It is all found in the : [url]http://lue.dk/prj/dbc/index.html[/url]
    > If you name your sequences in a generic way you can alway construct
    > the name of the sequence from the name of the table and the id column.
    >
    > We use this in our php framework
    >
    > function insert_id()
    > {
    > global $pg_conn;
    > if(isset($pg_conn)) {
    > $query = sprintf("SELECT currval('%s_%s_seq')
    > AS id",$this->table,$this->id_column);
    > $result = pg_query($pg_conn,$query);
    > $row = pg_fetch_assoc($result);
    > return strval($row["id"]);
    > } else {
    > return 0;
    > }
    > }
    Thanks, but this demands you to have the table and id_column name in
    your hand, and I don't right now.

    Also ... the "currval" function are specifik to postgresql, and there
    are nothing like it in mysql that can make any garanti for getting row
    for newly inserted data. You can access autoincrement values in mysql,
    but no garanties are given about its value (someone else have inserted a
    new in the same table).

    But thanks for your interrest., anyway.

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Bo Lorentsen Guest

  6. #6

    Default Re: OID Usage

    Michael Fuhr wrote:
    >PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
    >returns the sequence name for a particular column so you don't have
    >to construct it. This is useful when a table or column has been
    >renamed, in which case the above will probably break.
    >
    >
    Quite nice but not what I need, as I still need to know the id column name.

    But thanks anyway.

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Bo Lorentsen Guest

  7. #7

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 11:39:54AM +0100, Bo Lorentsen wrote:
    > Thanks, but this demands you to have the table and id_column name in
    > your hand, and I don't right now.
    You can create a function to get the sequence name attached to a table.
    Of course, you should take into account the fact that there could be
    more than one (two serial fields in a table are rare but not
    impossible), but if your tables have only one sequence you should be OK.
    Something with

    select relname, relkind
    from pg_depend join pg_class on (oid = objid)
    where pg_depend.refobjid = 'foo'::regclass
    and relkind = 'S';

    (only lightly tested). Then you can use that to construct your argument
    to the nextval() function.
    > Also ... the "currval" function are specifik to postgresql, and there
    > are nothing like it in mysql that can make any garanti for getting row
    > for newly inserted data. You can access autoincrement values in mysql,
    > but no garanties are given about its value (someone else have inserted a
    > new in the same table).
    This doesn't happen with sequences on Postgres. The value you get is
    guaranteed to be the one the sequence generated for you.

    --
    Alvaro Herrera (<alvherre[]dcc.uchile.cl>)
    "Those who use electric razors are infidels destined to burn in hell while
    we drink from rivers of beer, download free vids and mingle with naked
    well shaved babes." ([url]http://slashdot.org/comments.pl?sid=44793&cid=4647152[/url])

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Alvaro Herrera Guest

  8. #8

    Default Re: OID Usage

    Bo Lorentsen <blnetgroup.dk> writes:
    > I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
    > constaints, but I use OID in one special situation. When I insert a
    > single row into a table, I like my low level code to be kompatible with
    > mysql ( mysql_insert_id ), and fetch the row that I just inserted. This
    > I do by using the PGoidValue function, and then select the row by the
    > oid. This works quite nice .... but when a table get large, it become a
    > big search (seq scan) so I have added an index on oid's on the table
    > where I use this trick, and this have helper :-)
    The thing you have to worry about is the possibility of duplicate OIDs
    once your DB has been running long enough for the OID counter to wrap
    around (2^32 OIDs). You should make sure that index is specifically
    declared as UNIQUE, so that any attempt to insert a duplicate OID will
    fail. That might be enough for you, or you might want to add logic to
    your application to retry automatically after such a failure.

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Tom Lane Guest

  9. #9

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 11:47:25AM +0100, Bo Lorentsen wrote:
    > Michael Fuhr wrote:
    >
    > >PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
    > >returns the sequence name for a particular column so you don't have
    > >to construct it. This is useful when a table or column has been
    > >renamed, in which case the above will probably break.
    > >
    > Quite nice but not what I need, as I still need to know the id column name.
    You could query the system catalogs for the table's primary key,
    either on the client side or in a server-side function. The
    pg_attrdef table even has the default value's nextval() expression
    with the sequence name, which could be converted into a currval()
    call.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Michael Fuhr Guest

  10. #10

    Default Re: OID Usage

    Michael Fuhr wrote:
    >You could query the system catalogs for the table's primary key,
    >either on the client side or in a server-side function. The
    >pg_attrdef table even has the default value's nextval() expression
    >with the sequence name, which could be converted into a currval()
    >call.
    >
    >
    This is not a bad ideer, I will take a look in the "pg_attrdef" table to
    see what I can find, and some good caching may help alot too :-)

    But, does this mean that the oid sollution I have decriped (and
    implimentet) have some unknown problems, or will oid's become obsolete
    in the near future ?

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Bo Lorentsen Guest

  11. #11

    Default Re: OID Usage

    Alvaro Herrera wrote:
    >You can create a function to get the sequence name attached to a table.
    >Of course, you should take into account the fact that there could be
    >more than one (two serial fields in a table are rare but not
    >impossible), but if your tables have only one sequence you should be OK.
    >
    >
    Are there a way to find and test if it is a primary key ?
    >Something with
    >
    >select relname, relkind
    >from pg_depend join pg_class on (oid = objid)
    >where pg_depend.refobjid = 'foo'::regclass
    > and relkind = 'S';
    >
    >
    Hmm, need to play more around using the "pg_" system tables.

    Are they all well doentet, or need I some guessing ?
    >(only lightly tested). Then you can use that to construct your argument
    >to the nextval() function.
    >
    >
    :-)
    >This doesn't happen with sequences on Postgres. The value you get is
    >guaranteed to be the one the sequence generated for you.
    >
    >
    I know, and this is one of the reasons for not using MySQL :-)

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Bo Lorentsen Guest

  12. #12

    Default Re: OID Usage

    Tom Lane wrote:
    >The thing you have to worry about is the possibility of duplicate OIDs
    >once your DB has been running long enough for the OID counter to wrap
    >around (2^32 OIDs). You should make sure that index is specifically
    >declared as UNIQUE, so that any attempt to insert a duplicate OID will
    >fail. That might be enough for you, or you might want to add logic to
    >your application to retry automatically after such a failure.
    >
    >
    Ahh, yes ... this was what I thought may have be the problem, Not that
    2^32 is a small number, but as time goes by on a busy system, this will
    happened one day.

    Unique index is a good plan, it will make an error but no data will be
    harmed then !

    How does PG itself handle a search on an duplicated oid, without a index
    .... return two rows ?

    Will there be a future substitute for PGoidValue that is more reliable,
    like a rowid ?

    Thanks anyway !

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Bo Lorentsen Guest

  13. #13

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:
    >
    > But, does this mean that the oid sollution I have decriped (and
    > implimentet) have some unknown problems, or will oid's become obsolete
    > in the near future ?
    The PostgreSQL doentation discourages the use of OIDs for primary
    keys. For example, the "Object Identifier Types" section in the
    "Data Types" chapter says:

    The oid type is currently implemented as an unsigned four-byte
    integer. Therefore, it is not large enough to provide database-wide
    uniqueness in large databases, or even in large individual tables.
    So, using a user-created table's OID column as a primary key is
    discouraged. OIDs are best used only for references to system
    tables.

    The "System Columns" section of the "Data Definition" chapter says:

    OIDs are 32-bit quantities and are assigned from a single
    cluster-wide counter. In a large or long-lived database, it is
    possible for the counter to wrap around. Hence, it is bad practice
    to assume that OIDs are unique, unless you take steps to ensure that
    they are unique.

    The CREATE TABLE doentation in the Reference part says:

    Once the counter wraps around, uniqueness of OIDs can no longer be
    assumed, which considerably reduces their usefulness. Specifying
    WITHOUT OIDS also reduces the space required to store the table on
    disk by 4 bytes per row of the table, thereby improving performance.

    The 8.0 Release Notes say the following under "Deprecated Features":

    By default, tables in PostgreSQL 8.0 and earlier are created with
    OIDs. In the next release, this will _not_ be the case: to create a
    table that contains OIDs, the WITH OIDS clause must be specified or
    the default_with_oids configuration parameter must be enabled.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Michael Fuhr Guest

  14. #14

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:
    > But, does this mean that the oid sollution I have decriped (and
    > implimentet) have some unknown problems, or will oid's become obsolete
    > in the near future ?
    It means using OIDs as you described has very well known problems and
    they will break on you eventually. You can mitigate the damage by
    creating a UNIQUE index on the oid column but you'd better be sure your
    application can handle the side-effects.

    OIDs won't become obsolete, but they'll probably no longer be enabled
    by default at some stage.

    Hope this helps,
    --
    Martijn van Oosterhout <kleptogsvana.org> [url]http://svana.org/kleptog/[/url]
    > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
    > tool for doing 5% of the work and then sitting around waiting for someone
    > else to do the other 95% so you can sue them.
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.0.6 (GNU/Linux)
    Comment: For info see [url]http://www.gnupg.org[/url]

    iD8DBQFB6Bn9Y5Twig3Ge+YRAieNAJ9fPTXeQ8z+iH9MqRw5vW PUTD85FQCeN9vr
    38anlOmRBcKl9TQcT0kek2M=
    =OOuw
    -----END PGP SIGNATURE-----

    Martijn van Oosterhout Guest

  15. #15

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:
    > Alvaro Herrera wrote:
    >
    > >You can create a function to get the sequence name attached to a table.
    > >Of course, you should take into account the fact that there could be
    > >more than one (two serial fields in a table are rare but not
    > >impossible), but if your tables have only one sequence you should be OK.
    > >
    > Are there a way to find and test if it is a primary key ?
    pg_index has an indisprimary column.
    > Hmm, need to play more around using the "pg_" system tables.
    >
    > Are they all well doentet, or need I some guessing ?
    See the "System Catalogs" chapter in the doentation.

    If you run "psql -E" you'll see the queries that psql executes for
    commands like "\d foo". Those commands query the system catalogs.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Michael Fuhr Guest

  16. #16

    Default Re: OID Usage

    On Fri, Jan 14, 2005 at 12:20:50PM -0700, Michael Fuhr wrote:
    > On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:
    > > Alvaro Herrera wrote:
    > >
    > > >You can create a function to get the sequence name attached to a table.
    > > >Of course, you should take into account the fact that there could be
    > > >more than one (two serial fields in a table are rare but not
    > > >impossible), but if your tables have only one sequence you should be OK.
    > > >
    > > Are there a way to find and test if it is a primary key ?
    >
    > pg_index has an indisprimary column.
    Yeah, though things get hairy that way because you have to peek at
    pg_attribute to match the objsubid in pg_depend; and self-join pg_class
    to get to the index itself. Not sure if it all can be done in a single
    query.
    > If you run "psql -E" you'll see the queries that psql executes for
    > commands like "\d foo". Those commands query the system catalogs.
    Sadly, there's hardly anything there that uses pg_depend.

    --
    "I dream about dreams about dreams", sang the nightingale
    under the pale moon (Sandman)

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Alvaro Herrera Guest

  17. #17

    Default Re: OID Usage

    Michael Fuhr wrote:
    >The PostgreSQL doentation discourages the use of OIDs for primary
    >keys. For example, the "Object Identifier Types" section in the
    >"Data Types" chapter says:
    >
    >
    ....

    Thanks for taking you the time to snip this together, I think I will try
    to find a way to find the propper primary key (using pg_* tables), and
    if this uses the "nextval", I may be able to retrive the currently
    inserted row by using currval.
    >The 8.0 Release Notes say the following under "Deprecated Features":
    >
    >
    Why have this not happend before ? The "PGoidValue need to be depricated
    too. And why is it not substitutet with something else ?

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

    Bo Lorentsen Guest

  18. #18

    Default Re: OID Usage

    Martijn van Oosterhout wrote:
    >It means using OIDs as you described has very well known problems and
    >they will break on you eventually. You can mitigate the damage by
    >creating a UNIQUE index on the oid column but you'd better be sure your
    >application can handle the side-effects.
    >
    >
    Ok, Tom told me about the same :-( But why are oid's still in PG, that
    are they good for ? Will there be a real unique row id, like there is in
    Oracle, or will this be keept as an internal value only ?
    >OIDs won't become obsolete, but they'll probably no longer be enabled
    >by default at some stage.
    >
    >
    Is this because some old application's using oid's in somewhat small
    dataset ?
    >Hope this helps,
    >
    >
    It did thanks.

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Bo Lorentsen Guest

  19. #19

    Default Re: OID Usage

    Michael Fuhr wrote:
    >See the "System Catalogs" chapter in the doentation.
    >
    >
    Ok, I think I will compile all the given information in this thread, to
    make a new and more non oid'ish solution, as the dataset I manage are
    going to grow quite a lot :-)
    >If you run "psql -E" you'll see the queries that psql executes for
    >commands like "\d foo". Those commands query the system catalogs.
    >
    >
    >
    This may be very usefull, thanks.

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Bo Lorentsen Guest

  20. #20

    Default Re: OID Usage

    Alvaro Herrera wrote:
    >Yeah, though things get hairy that way because you have to peek at
    >pg_attribute to match the objsubid in pg_depend; and self-join pg_class
    >to get to the index itself. Not sure if it all can be done in a single
    >query.
    >
    >
    Sounds like my task, to make an oid free insert/select, is going to be
    very interesting :-)

    /BL

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Bo Lorentsen Guest

Page 1 of 3 123 LastLast

Similar Threads

  1. CPU usage is 99%
    By Twinlife in forum Macromedia Contribute Connection Administrtion
    Replies: 3
    Last Post: January 31st, 05:31 PM
  2. Hi cpu usage ?
    By Zack Whittaker \(R2 Mentor\) in forum Windows Vista
    Replies: 10
    Last Post: May 13th, 06:05 AM
  3. CPU usage
    By ROB in forum MySQL
    Replies: 2
    Last Post: March 7th, 06:27 PM
  4. FCS CPU Usage 100%
    By video conferencing in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: October 12th, 09:04 PM
  5. pod::usage example help
    By Paul Kraus in forum PERL Beginners
    Replies: 2
    Last Post: January 10th, 05:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139