Thread: disabling OIDs?

disabling OIDs?

From
Lonni J Friedman
Date:
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 ANALYSE;)


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

Thanks!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

Re: disabling OIDs?

From
Tom Lane
Date:
Lonni J Friedman <netllama@gmail.com> 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

Re: disabling OIDs?

From
Lonni J Friedman
Date:
On Sun, 12 Dec 2004 22:16:27 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Lonni J Friedman <netllama@gmail.com> 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                                    netllama@gmail.com
LlamaLand                       http://netllama.linux-sxs.org

Re: disabling OIDs?

From
Neil Conway
Date:
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



Re: disabling OIDs?

From
Greg Stark
Date:
Lonni J Friedman <netllama@gmail.com> 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

Re: disabling OIDs?

From
"Mark Dexter"
Date:

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

Re: disabling OIDs?

From
Greg Stark
Date:
"Mark Dexter" <MDEXTER@dexterchaney.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

Re: disabling OIDs?

From
"Mark Dexter"
Date:
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: gsstark@mit.edu [mailto:gsstark@mit.edu]
Sent: Monday, December 13, 2004 12:06 PM
To: Mark Dexter
Cc: pgsql-general@postgresql.org; gsstark@mit.edu
Subject: Re: disabling OIDs?



"Mark Dexter" <MDEXTER@dexterchaney.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


Re: SELECTing on age

From
"Kall, Bruce A."
Date:
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


Re: SELECTing on age

From
Scott Marlowe
Date:
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;

increasing max_connections on freebsd

From
Hengki Suhartoyo
Date:
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
http://mail.yahoo.com

Re: increasing max_connections on freebsd

From
"Frank D. Engel, Jr."
Date:
You might want to check some of these sites:

http://www.silverwraith.com/papers/freebsd-kernel.php
http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/
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
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.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 www.doteasy.com


Re: increasing max_connections on freebsd

From
Michael Fuhr
Date:
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:
>
> http://www.silverwraith.com/papers/freebsd-kernel.php
> http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/
> 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
http://www.fuhr.org/~mfuhr/

Re: increasing max_connections on freebsd

From
Christian Kratzer
Date:
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                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136

Re: increasing max_connections on freebsd

From
Michael Fuhr
Date:
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
http://www.fuhr.org/~mfuhr/

Re: increasing max_connections on freebsd

From
Vivek Khera
Date:
>>>>> "MF" == Michael Fuhr <mike@fuhr.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: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

Re: disabling OIDs?

From
Jeff Davis
Date:
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


Re: disabling OIDs?

From
Martijn van Oosterhout
Date:
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   <kleptog@svana.org>   http://svana.org/kleptog/
> 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.

Attachment

Re: disabling OIDs?

From
Robert Treat
Date:
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