Thread: 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 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
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
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
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
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
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" <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
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
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
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;
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
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
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/
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
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/
>>>>> "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/
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
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
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