Re: Performance problems with large telemetric datasets on 7.4.2 - Mailing list pgsql-performance

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