Professional Web Applications Themes

disabling OIDs? - PostgreSQL / PGSQL

The spam filtering package I use (dspam) had a section in their release notes recently which stated that disabling OIDs greatly increased speeds, and so they suggested that people do that on their tables. When creating new tables, you can disable OIDs with, CREATE TABLE foo (...) WITHOUT OIDS; And you can disable OIDs on existing tables by executing for each table, ALTER TABLE foo SET WITHOUT OIDS; and then running a vacuumdb (either with pg_vacuumdb or VACUUM YSE;) Does anyone know of any risks or potential downsides to doing this? Thanks! -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~ L. Friedman [email]netllama[/email] LlamaLand [url]http://netllama.linux-sxs.org[/url] ...

  1. #1

    Default disabling OIDs?

    The spam filtering package I use (dspam) had a section in their
    release notes recently which stated that disabling OIDs greatly
    increased speeds, and so they suggested that people do that on their
    tables.

    When creating new tables, you can disable OIDs with,
    CREATE TABLE foo (...) WITHOUT OIDS;
    And you can disable OIDs on existing tables by executing for each table,
    ALTER TABLE foo SET WITHOUT OIDS;
    and then running a vacuumdb (either with pg_vacuumdb or VACUUM YSE;)


    Does anyone know of any risks or potential downsides to doing this?

    Thanks!

    --
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
    L. Friedman [email]netllama[/email]
    LlamaLand [url]http://netllama.linux-sxs.org[/url]

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

    Lonni J Friedman Guest

  2. #2

    Default Re: disabling OIDs?

    Lonni J Friedman <netllama> writes:
    > The spam filtering package I use (dspam) had a section in their
    > release notes recently which stated that disabling OIDs greatly
    > increased speeds, and so they suggested that people do that on their
    > tables.
    "greatly increased"? I doubt it.

    Last I heard, dspam was not noted for any large amount of cluefulness
    WRT postgres. It was only recently that we managed to talk them out of
    their most egregious bits of mysql-centricity. Going to them for
    postgres tuning tips is about like coming to me for mysql tuning ...

    regards, tom lane

    ---------------------------(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

    Tom Lane Guest

  3. #3

    Default Re: disabling OIDs?

    On Sun, 12 Dec 2004 22:16:27 -0500, Tom Lane <tglsss.pgh.pa.us> wrote:
    > Lonni J Friedman <netllama> writes:
    > > The spam filtering package I use (dspam) had a section in their
    > > release notes recently which stated that disabling OIDs greatly
    > > increased speeds, and so they suggested that people do that on their
    > > tables.
    >
    > "greatly increased"? I doubt it.
    >
    > Last I heard, dspam was not noted for any large amount of cluefulness
    > WRT postgres. It was only recently that we managed to talk them out of
    > their most egregious bits of mysql-centricity. Going to them for
    > postgres tuning tips is about like coming to me for mysql tuning ...
    >
    OK, thanks. So is there any real benefit in doing this in a generic
    (non-dspam) sense, or is it just a hack that wouldn't be noticable?
    Any risks or potential problems down the line?


    --
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~
    L. Friedman [email]netllama[/email]
    LlamaLand [url]http://netllama.linux-sxs.org[/url]

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

    Lonni J Friedman Guest

  4. #4

    Default Re: disabling OIDs?

    On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
    > OK, thanks. So is there any real benefit in doing this in a generic
    > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
    > Any risks or potential problems down the line?
    It saves 4 bytes per row; depending on alignment and padding
    considerations, that may or may not equate to disk space savings. Other
    than the inability to use OIDs on the table, there is no real risks to
    doing this -- I'm planning to advocate making WITHOUT OIDS the default
    in PostgreSQL 8.1+. You can get this behavior in 8.0 by setting the
    default_with_oids config variable to "false".

    -Neil



    ---------------------------(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

    Neil Conway Guest

  5. #5

    Default Re: disabling OIDs?


    Lonni J Friedman <netllama> writes:
    > OK, thanks. So is there any real benefit in doing this in a generic
    > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
    > Any risks or potential problems down the line?
    OIDs increase the storage requirements so they do slow postgres somewhat.
    About (exactly?) the same impact as adding another integer column. That will
    have a bigger impact on a narrow table than wider tables.

    --
    greg


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Greg Stark Guest

  6. #6

    Default Re: disabling OIDs?

    > For what it's worth, OIDs are required if you ever want to use
    > updateable cursors with the ODBC driver. We discovered this the hard
    > way. Mark Dexter
    Mark Dexter Guest

  7. #7

    Default Re: disabling OIDs?


    "Mark Dexter" <MDEXTERdexterchaney.com> writes:
    > > For what it's worth, OIDs are required if you ever want to use
    > > updateable cursors with the ODBC driver. We discovered this the hard
    > > way. Mark Dexter
    That's unfortunate. Is it because it's difficult to track down the primary key
    of the table? Is it any easier to track down the primary key of the table in
    8.0? It would be much better if it checked the primary key and used that
    instead of OIDs.

    Though I'm unclear implementing "updateable cursors" in the client-end is
    really a good idea. I suppose it's nice if you understand the limitations
    inherent.

    --
    greg


    ---------------------------(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

    Greg Stark Guest

  8. #8

    Default Re: disabling OIDs?

    I don't know why they use OID's for cursors. But I do know that if you
    run a trace the SQL that creates the cursor uses OID's,
    so it doesn't work if the table is created without OID's. Also, if you
    want to have updateable cursors against views (i.e., a view with rules
    for INSERT, UPDATE, and DELETE), you must name the OID and CTID as
    columns in the view. Again, we learned this the hard way. Mark Dexter

    -----Original Message-----
    From: [email]gsstarkmit.edu[/email] [mailto:gsstarkmit.edu]
    Sent: Monday, December 13, 2004 12:06 PM
    To: Mark Dexter
    Cc: [email]pgsql-generalpostgresql.org[/email]; [email]gsstarkmit.edu[/email]
    Subject: Re: disabling OIDs?



    "Mark Dexter" <MDEXTERdexterchaney.com> writes:
    > > For what it's worth, OIDs are required if you ever want to use
    > > updateable cursors with the ODBC driver. We discovered this the
    > > hard way. Mark Dexter
    That's unfortunate. Is it because it's difficult to track down the
    primary key of the table? Is it any easier to track down the primary key
    of the table in 8.0? It would be much better if it checked the primary
    key and used that instead of OIDs.

    Though I'm unclear implementing "updateable cursors" in the client-end
    is really a good idea. I suppose it's nice if you understand the
    limitations inherent.

    --
    greg


    ---------------------------(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

    Mark Dexter Guest

  9. #9

    Default Re: SELECTing on age

    I'm attempting to select records from my postgresql database using php
    based on whether someone is at least 17 years old on the date of a
    particular visit.

    My sql is:

    $db_sql = "SELECT * from list WHERE ((visit_date - birth_date) >= 17)'"
    $db_result = db_exec($db_sql)
    $num = pg_num_rows($db_result);
    for($i = 0; $i < $num; $i++)
    {
    $data = pg_num_rows($db_result,$i)
    $visit_date = $data["visit_date"];
    $birth_date = $data["birth_date"];
    echo "Visit date[$visit_date] Birth date[$birth_date]";
    }

    The problem I'm having is that the the query is returning results for
    some people with ages < 17 (most of them are correct, just a couple of
    incorrect ones interspersed with the correct ones that are over 17)?

    For example, my output contains:

    Visit date[2004-07-14] Birth date[2004-02-19]
    and
    Visit date[2004-08-11] Birth date[2003-04-21]

    which are clearly people who are < 17.


    Any suggestions on how to track down this problem or rework the query so
    it always works correctly? If I reverse the query and look for people <
    17, I don't get any that are older than 17.

    Thanks,
    Bruce


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

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

    Kall, Bruce A. Guest

  10. #10

    Default Re: SELECTing on age

    On Mon, 2004-12-13 at 15:15, Kall, Bruce A. wrote:
    > I'm attempting to select records from my postgresql database using php
    > based on whether someone is at least 17 years old on the date of a
    > particular visit.
    >
    > My sql is:
    >
    > $db_sql = "SELECT * from list WHERE ((visit_date - birth_date) >= 17)'"
    > $db_result = db_exec($db_sql)
    > $num = pg_num_rows($db_result);
    > for($i = 0; $i < $num; $i++)
    > {
    > $data = pg_num_rows($db_result,$i)
    > $visit_date = $data["visit_date"];
    > $birth_date = $data["birth_date"];
    > echo "Visit date[$visit_date] Birth date[$birth_date]";
    > }
    >
    > The problem I'm having is that the the query is returning results for
    > some people with ages < 17 (most of them are correct, just a couple of
    > incorrect ones interspersed with the correct ones that are over 17)?
    >
    > For example, my output contains:
    >
    > Visit date[2004-07-14] Birth date[2004-02-19]
    > and
    > Visit date[2004-08-11] Birth date[2003-04-21]
    >
    > which are clearly people who are < 17.
    Check out what this query tells you:

    postgres=# select ('2004-07-31'::date-'2004-07-01'::date);
    ?column?
    ----------
    30


    Notice how the output of subtracting one date from another is an int for
    the number of days? A better way would be:

    select * from table1 where dt <now()-'17 years'::interval;

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

    Scott Marlowe Guest

  11. #11

    Default increasing max_connections on freebsd

    Hello...

    I want to increase my max_connections up to 128
    connections, but I got that I need to recompile my
    kernel. I'm newbie in postgresql and freebsd. How to
    increase max_connections and recompile freebsd kernel.

    Help Me please.....

    Thank's

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    [url]http://mail.[/url]

    ---------------------------(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

    Hengki Suhartoyo Guest

  12. #12

    Default Re: increasing max_connections on freebsd

    You might want to check some of these sites:

    [url]http://www.silverwraith.com/papers/freebsd-kernel.php[/url]
    [url]http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/[/url]
    kernelconfig.html


    Anyone have a clue why he would need to recompile his kernel for this,
    though?

    On Dec 14, 2004, at 5:46 AM, Hengki Suhartoyo wrote:
    > Hello...
    >
    > I want to increase my max_connections up to 128
    > connections, but I got that I need to recompile my
    > kernel. I'm newbie in postgresql and freebsd. How to
    > increase max_connections and recompile freebsd kernel.
    >
    > Help Me please.....
    >
    > Thank's
    >
    > __________________________________________________
    > Do You Yahoo!?
    > Tired of spam? Yahoo! Mail has the best spam protection around
    > [url]http://mail.[/url]
    >
    > ---------------------------(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
    >
    >
    -----------------------------------------------------------
    Frank D. Engel, Jr. <fde101fjrhome.net>

    $ ln -s /usr/share/kjvbible /usr/manual
    $ true | cat /usr/manual | grep "John 3:16"
    John 3:16 For God so loved the world, that he gave his only begotten
    Son, that whosoever believeth in him should not perish, but have
    everlasting life.
    $



    __________________________________________________ _________
    $0 Web Hosting with up to 120MB web space, 1000 MB Transfer
    10 Personalized POP and Web E-mail Accounts, and much more.
    Signup at [url]www.doteasy.com[/url]


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

    Frank D. Engel, Jr. Guest

  13. #13

    Default Re: increasing max_connections on freebsd

    On Tue, Dec 14, 2004 at 10:01:47AM -0500, Frank D. Engel, Jr. wrote:
    > On Dec 14, 2004, at 5:46 AM, Hengki Suhartoyo wrote:
    > >
    > >I want to increase my max_connections up to 128
    > >connections, but I got that I need to recompile my
    > >kernel. I'm newbie in postgresql and freebsd. How to
    > >increase max_connections and recompile freebsd kernel.
    >
    > You might want to check some of these sites:
    >
    > [url]http://www.silverwraith.com/papers/freebsd-kernel.php[/url]
    > [url]http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/[/url]
    > kernelconfig.html
    >
    > Anyone have a clue why he would need to recompile his kernel for this,
    > though?
    The error probably says something about not having enough shared
    memory or semaphores. On FreeBSD some IPC settings can be configured
    via sysctl but others might need to be built into the kernel. For
    example, on FreeBSD 4, trying to set kern.ipc.semmni or kern.ipc.semmns
    with sysctl fails, saying that the OID is read-only.

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

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Michael Fuhr Guest

  14. #14

    Default Re: increasing max_connections on freebsd

    On Tue, 14 Dec 2004, Michael Fuhr wrote:
    > On Tue, Dec 14, 2004 at 10:01:47AM -0500, Frank D. Engel, Jr. wrote:
    [snipp]
    > The error probably says something about not having enough shared
    > memory or semaphores. On FreeBSD some IPC settings can be configured
    > via sysctl but others might need to be built into the kernel. For
    > example, on FreeBSD 4, trying to set kern.ipc.semmni or kern.ipc.semmns
    > with sysctl fails, saying that the OID is read-only.
    At least on FreeBSD 5.x there is no need to build a customer kernel.
    The following can be set in /boot/loader.conf

    # defaults for FreeBSD are as follows
    # SEMMNI=10, # of semaphore identifiers
    # SEMMNS=60, # of semaphores in system
    # SEMUME=10, max # of undo entries per process
    # SEMMNU=30, # of undo structures in system
    kern.ipc.semmni=40
    kern.ipc.semmns=240
    kern.ipc.semume=40
    kern.ipc.semmnu=120

    these will be set on boot.

    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 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Christian Kratzer Guest

  15. #15

    Default Re: increasing max_connections on freebsd

    On Tue, Dec 14, 2004 at 06:34:05PM +0100, Christian Kratzer wrote:
    >
    > At least on FreeBSD 5.x there is no need to build a customer kernel.
    > The following can be set in /boot/loader.conf
    I forgot about /boot/loader.conf. If I get a chance I'll check if
    the settings you posted also work in FreeBSD 4. Thanks.

    --
    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: increasing max_connections on freebsd

    >>>>> "MF" == Michael Fuhr <mikefuhr.org> writes:

    MF> On Tue, Dec 14, 2004 at 06:34:05PM +0100, Christian Kratzer wrote:
    >>
    >> At least on FreeBSD 5.x there is no need to build a customer kernel.
    >> The following can be set in /boot/loader.conf
    MF> I forgot about /boot/loader.conf. If I get a chance I'll check if
    MF> the settings you posted also work in FreeBSD 4. Thanks.

    Yes, they do. You can also bump maxfiles if necessary via a sysctl.

    --
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Vivek Khera, Ph.D. Khera Communications, Inc.
    Internet: [email]kherakciLink.com[/email] Rockville, MD +1-301-869-4449 x806
    AIM: vivekkhera Y!: vivek_khera [url]http://www.khera.org/~vivek/[/url]

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

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

    Vivek Khera Guest

  17. #17

    Default Re: disabling OIDs?

    On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
    > OK, thanks. So is there any real benefit in doing this in a generic
    > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
    > Any risks or potential problems down the line?
    >
    >
    I'd just like to add that some 3rd party applications/interfaces make
    use of OIDs, as a convenient id to use if there is no primary key (or if
    the 3rd party software doesn't take the time to find the primary key).

    One might argue that those 3rd party applications/interfaces are broken,
    but you still might want to keep OIDs around in case you have a use for
    one of those pieces of software.

    Regards,
    Jeff


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

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

    Jeff Davis Guest

  18. #18

    Default Re: disabling OIDs?

    On Sat, Jan 01, 2005 at 06:35:30PM -0800, Jeff Davis wrote:
    > On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
    > > OK, thanks. So is there any real benefit in doing this in a generic
    > > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
    > > Any risks or potential problems down the line?
    > >
    > I'd just like to add that some 3rd party applications/interfaces make
    > use of OIDs, as a convenient id to use if there is no primary key (or if
    > the 3rd party software doesn't take the time to find the primary key).
    >
    > One might argue that those 3rd party applications/interfaces are broken,
    > but you still might want to keep OIDs around in case you have a use for
    > one of those pieces of software.
    Yep, especially since an OID is not a unique value and so can't
    possibly be a primary key and generally isn't indexed either. Even
    Access asks you to identify the primary key...
    --
    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]

    iD8DBQFB1/YWY5Twig3Ge+YRAhvpAJ9kXwBxh/1IVyBtICDpaLMSzVPElACeKP/G
    1PCg3kID3GVrf3NVhXFAmGI=
    =hMZs
    -----END PGP SIGNATURE-----

    Martijn van Oosterhout Guest

  19. #19

    Default Re: disabling OIDs?

    On Sunday 02 January 2005 08:24, Martijn van Oosterhout wrote:
    > On Sat, Jan 01, 2005 at 06:35:30PM -0800, Jeff Davis wrote:
    > > On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote:
    > > > OK, thanks. So is there any real benefit in doing this in a generic
    > > > (non-dspam) sense, or is it just a hack that wouldn't be noticable?
    > > > Any risks or potential problems down the line?
    > >
    > > I'd just like to add that some 3rd party applications/interfaces make
    > > use of OIDs, as a convenient id to use if there is no primary key (or if
    > > the 3rd party software doesn't take the time to find the primary key).
    > >
    > > One might argue that those 3rd party applications/interfaces are broken,
    > > but you still might want to keep OIDs around in case you have a use for
    > > one of those pieces of software.
    >
    > Yep, especially since an OID is not a unique value and so can't
    > possibly be a primary key and generally isn't indexed either. Even
    > Access asks you to identify the primary key...
    Of course some 3rd party apps are nice and they look for a primary key first,
    then a unique index, then look for an oid. Furthermore the really clueful
    ones will check # of affected rows = 1 when modifying by oid, so its pretty
    safe.

    --
    Robert Treat
    Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Robert Treat Guest

Similar Threads

  1. Setting MAXDSIZ, MAXSSIZ, DFLDSIZ via kernel OIDs?
    By O. Hartmann in forum FreeBSD
    Replies: 1
    Last Post: February 24th, 08:07 PM
  2. Disabling Components?
    By mdawg999 in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 16th, 08:09 PM
  3. Strange OIDs returned for IBM 3584 v2c trap variables
    By John Ramsden in forum PERL Miscellaneous
    Replies: 2
    Last Post: September 4th, 03:33 PM
  4. disabling autoplay on C:\...
    By mould in forum Windows XP/2000/ME
    Replies: 0
    Last Post: July 10th, 09:51 AM
  5. Disabling Profile
    By John in forum Windows Setup, Administration & Security
    Replies: 0
    Last Post: July 8th, 05:27 PM

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