From: Andrew Kroeger <andrew@sprocks.gotdns.com>
To: Sven Clement <sven@dsign.lu>
Cc: Mark Lewis <mark.lewis@mir3.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems with large telemetric datasets on 7.4.2
Date: Fri, 03 Aug 2007 16:13:27 -0500
Message-ID: <46B39A77.8050105@sprocks.gotdns.com> (raw)
In-Reply-To: <881035ea0708030857s621af020r4d3d7b994e2ca9a3@mail.gmail.com>
References: <881035ea0708030652r37b25a86g61e88e1f5e95eab2@mail.gmail.com>
<1186150836.19184.194.camel@archimedes>
<881035ea0708030857s621af020r4d3d7b994e2ca9a3@mail.gmail.com>
Sven Clement wrote:
> Table: "public.tmdata"
...
> id | integer | default -2147483684::bigint
...
> Table: "public.tmdataintervalsec"
...
> id | integer | default -2147483684::bigint
Not that this directly addresses the performance issues you described,
but I have already seen 2 recommendations that you upgrade...
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).
I checked your previous post and saw that you are currently running PG
7.4.2/7.4.7 (subject says 7.4.2, but you indicate 7.4.7 in the body of
your original post). I did a quick check on my 8.1.9 box using the same
bigint default value for an integer column and received an "integer out
of range" error when I attempted to use the default value.
I don't know the exact workings of your system, but you'll need to watch
out for any cases where the default value for the id columns was used.
If that default value was used (and was allowed by your PG version) you
will probably have values in the id column that are not what you'd
expect. I don't know how a bigint would be coerced into an integer, but
it would probably truncate in some form which would give you positive
values in the id column where you expected the smallest 32-bit integer
value (i.e. -2147483648).
I don't know if this was ever actually an issue (if you never rely on
the default value for the id column -- maybe version 7.4.7 would
generate the same error if you did), but if it was, you need to look at
a couple of things before upgrading (whether to a more recent 7.4.X or
8.2.4):
1. If you do rely on the default clause for the id column, you may
encounter the "integer out of range" errors with your existing codebase.
2. You may have values in the id column that are supposed to represent
the smallest 32-bit integer that may in fact be positive integers.
You will probably want to investigate these potential issues and perform
any necessary schema changes and data cleanup before attempting any upgrade.
Again, I'm not sure if this was ever an issue or if this issue has any
effects on your database. I don't have any PG machines running anything
prior to 8.1.X, so I can't really test these. I just saw the bigint
value as a default for an integer column and it caught my eye.
Hope this might help you avoid some problems when upgrading.
Andrew
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@sprocks.gotdns.com, sven@dsign.lu, mark.lewis@mir3.com
Subject: Re: Performance problems with large telemetric datasets on 7.4.2
In-Reply-To: <46B39A77.8050105@sprocks.gotdns.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox