BUG #2863: vacuum failing, weird record in table... - Mailing list pgsql-bugs

From Robert Locke
Subject BUG #2863: vacuum failing, weird record in table...
Date
Msg-id 200612231310.kBNDArrk067958@wwwmaster.postgresql.org
Whole thread Raw
List pgsql-bugs
The following bug has been logged online:

Bug reference:      2863
Logged by:          Robert Locke
Email address:      rob@mobius.ph
PostgreSQL version: 8.1.4
Operating system:   FreeBSD 6.1-RELEASE-p6
Description:        vacuum failing, weird record in table...
Details:

Our nightly vacuum has been failing for the last couple of days, and it
seems to consistently involve a particular table.  Below is the tail end of
the script's output:

INFO:  index "PREPAID_PINS_USED_BY_IDX" now contains 5320 row versions in 14
pages
DETAIL:  11 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  "PREPAID_PINS": removed 46 row versions in 9 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "PREPAID_PINS": found 46 removable, 5320 nonremovable row versions in
78 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.16 sec.
INFO:  analyzing "products.PREPAID_PINS"
INFO:  "PREPAID_PINS": scanned 78 of 78 pages, containing 5320 live rows and
0 dead rows; 3000 rows in sample, 5320 estimated total rows
vacuumdb: vacuuming of database "mobiusonline" failed: server closed the
connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

Errors were reported during vacuum.

In studying the table, I accidentally discovered a very strange row which
would caused an error when I would query it in a certain way:

# select * from products."PREPAID_PINS" where "USED_BY" = 0;
ERROR:  invalid memory alloc request size 4294967293

I could however select the unique "ID" of the row:

# select "ID" from products."PREPAID_PINS" where "USED_BY" = 0;
  ID
------
 5378
(1 row)

Which I then used to successfully query the entire row!

# select * from products."PREPAID_PINS" where "ID" = 5378;
  ID  | PRODUCT_ID | TRACK_ID | TRANSACTION_ID | EPIN | STATUS |
DATE_GENERATED    |      DATE_USED      | USED_BY
------+------------+----------+----------------+------+--------+------------
---------+---------------------+---------
 5378 |        157 | 1162009  | ^R               | 4^X    |      0 |
2000-01-01 00:00:00 | 2000-01-01 00:00:00 |       0
(1 row)

To add to the strangeness, I then dumped the table and found the offending
row, only to discover that the row as represented in the dump was different
from the row as queried above:

5378    157     1162009 ^R      \203\t \337^_   852025  2000-01-01 00:00:00
   2000-01-01 00:00:00     2418910

In particular, the "USED_BY" value is 2418910, and not 0.

I'm concerned that the table is somehow corrupted and am a bit worried as to
the overall integrity of the database.

Has anyone seen anything like this?  Could this indicate a hardware problem?
 Any advice?

pgsql-bugs by date:

Previous
From: "Sergey"
Date:
Subject: BUG #2862: ERROR: failed to build any 7-way joins
Next
From: Aaron Bingham
Date:
Subject: SIMILAR TO incorrect with alternation