BUG #2863: vacuum failing, weird record in table...

From: "Robert Locke" <rob(at)mobius(dot)ph>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2863: vacuum failing, weird record in table...
Date: 2006-12-23 13:10:53
Message-ID: 200612231310.kBNDArrk067958@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 2863
Logged by: Robert Locke
Email address: rob(at)mobius(dot)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?

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2006-12-24 06:50:15 Re: [BUGS] BUG #2846: inconsistent and confusing handling of
Previous Message Sergey 2006-12-23 12:46:17 BUG #2862: ERROR: failed to build any 7-way joins