Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B81E79FB804 for ; Sat, 4 Aug 2007 17:03:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 48720-10 for ; Sat, 4 Aug 2007 17:03:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by postgresql.org (Postfix) with ESMTP id 8C08E9FB825 for ; Sat, 4 Aug 2007 17:03:04 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d32so2184781pye for ; Sat, 04 Aug 2007 13:03:03 -0700 (PDT) Received: by 10.64.208.20 with SMTP id f20mr6851255qbg.1186257782513; Sat, 04 Aug 2007 13:03:02 -0700 (PDT) Received: by 10.65.185.15 with HTTP; Sat, 4 Aug 2007 13:03:02 -0700 (PDT) Message-ID: <881035ea0708041303h4ea256b3q4b40c5291f8cd3b7@mail.gmail.com> Date: Sat, 4 Aug 2007 22:03:02 +0200 From: "Sven Clement" To: "Tom Lane" Subject: Re: Performance problems with large telemetric datasets on 7.4.2 Cc: "Andrew Kroeger" , "Mark Lewis" , pgsql-performance@postgresql.org In-Reply-To: <4770.1186180449@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_87339_32424833.1186257782473" References: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com> <1186150836.19184.194.camel@archimedes> <881035ea0708030857s621af020r4d3d7b994e2ca9a3@mail.gmail.com> <46B39A77.8050105@sprocks.gotdns.com> <4770.1186180449@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/31 X-Sequence-Number: 26161 ------=_Part_87339_32424833.1186257782473 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi everybody, The bigint problem was probably a typo because I had to type the entire definitions, as the server is on a vpn and I don't had access with my machine where I wrote the mail, and the 7.4.2 was surely a typo... ;) I apology... OK so beginning on Monday I will test the config on a 8.2.x to verify the performance issues, but I also found some disturbing info's on the net, that the index may be corrupted because of the big difference between an index entry which is deleted and the new value inserted afterwards, which should not be an issue with a btree, but do you guys know something more about it, sorry I'm really good in SQL but in Postgre I'm still a beginner. What the version belongs, so I know that it's not the actual bug fix, but as it is used in a running prod system and as my employer began considering the migration a year ago and so they froze the version waiting for an major update. Thanks really to everybody here who already helped me a lot... Thanks!!! Sven Clement 2007/8/4, Tom Lane : > > Andrew Kroeger writes: > > With the table definitions you posted, one of the first things I noticed > > was that the default value for an integer column was a bigint value. I > > did some quick 32-bit math and found that the smallest legal 32-bit > > integer value is -2147483648, not -2147483684 (notice the last 2 numbers > > are transposed). > > Oooh, good catch, but 7.4 seems to notice the overflow all right: > > regression=# create temp table foo(f1 int default -2147483684::bigint); > CREATE TABLE > regression=# insert into foo default values; > ERROR: integer out of range > regression=# select version(); > version > ---------------------------------------------------------------- > PostgreSQL 7.4.17 on hppa-hp-hpux10.20, compiled by GCC 2.95.3 > (1 row) > > So I think we can conclude that the OP never actually uses this default. > > regards, tom lane > -- DSIGN.LU Sven Clement +352 621 63 21 18 sven@dsign.lu www.dsign.lu ------=_Part_87339_32424833.1186257782473 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hi everybody,
 
The bigint problem was probably a typo because I had to type the entire definitions, as the server is on a vpn and I don't had access with my machine where I wrote the mail, and the 7.4.2 was surely a typo... ;) I apology...
 
OK so beginning on Monday I will test the config on a 8.2.x to verify the performance issues, but I also found some disturbing info's on the net, that the index may be corrupted because of the big difference between an index entry which is deleted and the new value inserted afterwards, which should not be an issue with a btree, but do you guys know something more about it, sorry I'm really good in SQL but in Postgre I'm still a beginner.
 
What the version belongs, so I know that it's not the actual bug fix, but as it is used in a running prod system and as my employer began considering the migration a year ago and so they froze the version waiting for an major update.
 
Thanks really to everybody here who already helped me a lot... Thanks!!!
Sven Clement

 
2007/8/4, Tom Lane <tgl@sss.pgh.pa.us>:
Andrew Kroeger <andrew@sprocks.gotdns.com> writes:
> With the table definitions you posted, one of the first things I noticed
> was that the default value for an integer column was a bigint value.  I
> did some quick 32-bit math and found that the smallest legal 32-bit
> integer value is -2147483648, not -2147483684 (notice the last 2 numbers
> are transposed).

Oooh, good catch, but 7.4 seems to notice the overflow all right:

regression=# create temp table foo(f1 int default -2147483684::bigint);
CREATE TABLE
regression=# insert into foo default values;
ERROR:  integer out of range
regression=# select version();
                           version
----------------------------------------------------------------
PostgreSQL 7.4.17 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

So I think we can conclude that the OP never actually uses this default.

                       regards, tom lane



--
DSIGN.LU
Sven Clement
+352 621 63 21 18
sven@dsign.lu

www.dsign.lu ------=_Part_87339_32424833.1186257782473--