disabling OIDs?

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

"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/

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