Hi,
after a months I've found a time to make test-case for this bug, probably:
I've got my custom type and a table stores historical data of the main table.
The problem occurs at our production servers, version 9.2.6. We've found it after our backups wasn't
complete because of selection from "corrupted" table failed. The data are corrupted in slaves too
(we've internal streaming replication) so I expect the problem should be before writing on file
system (during work with WALs?, during auto-vacuum probably).
I's been looking for any information and found only these posts:
http://www.postgresql.org/message-id/20362.1359747327@sss.pgh.pa.us
http://www.postgresql.org/message-id/C62EC84B2D3CF847899CCF4B589CCF70B20AA08F@BBMBX.backbone.local
I think, post of Tom Lane describes our problem. I describe ours: I've custom type:
CREATE TYPE changeset AS (
colname varchar,
oldvalue varchar,
newvalue varchar
);
and the table which contains this type as an array:
CREATE TABLE foo_journal (
...
changes changeset[],
...
);
Not depend on the size of data (but bigger are better), after an update of record and after a such
time, it's not possible to read the data of column and error like this occurs:
ERROR: missing chunk number 0 for toast value 36009 in pg_toast_35984
I've make a test case which I'm attaching in this post. It's similar to our design, triggers are
used for logging changes of foo table. I think it doesn't depend on them but I've use it to
introduce to our db design. In attachment, there is our postgresql.conf too and a short ReadMe
describing the problem as similar to this post. I've tested the test case for a few times and around
the third round it's passed (select failed).
I hope, anybody focuses on it and tries to find where the problem or bug is. I'll answer to any
other question about it, some information are in the test case. I want to use my own custom type,
not to change to any other solution. I think it's probably a bug of postgres and hope anybody fix it.
Regards,
Jan Pecek
--
SW Architect and Web developer at MAFRA, a.s.
member of Czech and Slovak PostgreSQL Users Group, o.s.
