I'm using Postgrseql 7.4.8. In January, I reported a psql bug. The
problem was that an INSERT issued through psql would cause a
crash. There was no problem with other operations I tried, or with the
same INSERT submitted through JDBC. The discussion thread begins here:
http://archives.postgresql.org/pgsql-bugs/2006-01/msg00071.php
There was no resolution to this problem -- a bad psql build was
suggested and I couldn't disprove it.
The problem has occurred again, and I've found a buffer overflow in
psql that explains it. Here is code from src/bin/psql/common.c, from
the PrintQueryResults function:
case PGRES_COMMAND_OK:
{
char buf[10];
success = true;
sprintf(buf, "%u", (unsigned int) PQoidValue(results));
In 8.1.5, the sprintf is replaced by an snprintf, resulting in a less
serious form of the bug.
I believe that we end up in this code after an INSERT is processed
through psql. If PQoidValue returns 1000000000 (1 billion) or higher,
which requires 10 characters, then we overflow buf due to the terminal
zero.
Looking at my databases, I find that the problem occurs exactly in the
databases with OIDs above 1 billion for newly inserted rows. (Because
the psql crash occurs in processing results, the INSERT succeeds, and
I can examine the OIDs of the inserted rows.)
My January email indicates that we had been loading data for three
months, so OIDs could conceivably have gotten as high as 1 billion (if
I understand OIDs correctly). The problem occurred again at about the
same time -- three months into a test.
Changing the 10 to 11, INSERTs through psql no longer cause psql to
crash.
I have two questions:
1) Is one of the postgresql developers willing to get this fix into
the next release? (We're patching our own 7.4.8 build.)
2) If no one else has hit this, then it suggests I might be in
uncharted territory with OIDs getting this high. Do I need to
review my vacuuming strategy? (I can summarize my vacuuming
strategy for anyone interested.)
Jack Orenstein