Thread: Store data in pg_toast for custom type fails (bug?)

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.

Honza <honzap@gmail.com> writes:
> after a months I've found a time to make test-case for this bug, probably:

Confirmed that this reproduces a problem on HEAD.  Will look into it,
thanks!
        regards, tom lane

I wrote:
> Honza <honzap@gmail.com> writes:
>> after a months I've found a time to make test-case for this bug, probably:

> Confirmed that this reproduces a problem on HEAD.  Will look into it,
> thanks!

I believe I understand what's going on here, and it's not quite as
exciting as it first appears.  The issue is that we are failing to
honor the "toasting goes only one level deep" rule in the specific
case of arrays of composite type.  So while it's definitely a nasty
bug, it affects only narrow use cases, and doesn't call into question
our whole vacuuming strategy or anything like that.

Specifically, what happens is that the example inserts data values
formed from
      array[ROW('description', OLD.description, NEW.description)::changeset]

in which the description fields might be toasted strings.  The ROW
expression calls heap_form_tuple, which leaves the descriptions toasted,
which is fine since per coding rule a tuple value can contain toasted
fields.  If we'd then inserted the composite value into another tuple
(eg, in preparation for storage), heap_form_tuple would have called
toast_flatten_tuple_attribute() which would have detoasted the nested
tuple's fields and all would be well.  But instead, we stuck it into an
array --- and while the array code knows it has to detoast toasted element
values, it does not know about invoking toast_flatten_tuple_attribute on
composite values that might contain nested fields.  So we end up with an
array of composite with some embedded toast pointers, and that goes out to
disk in that form.  After subsequent updates of the foo table, the
referenced toast entries get deleted, and now we have dangling pointers in
foo_journal.

So basically the array code has got to get taught about calling
toast_flatten_tuple_attribute() when necessary.
        regards, tom lane

On 03/28/2014 07:02 PM, Tom Lane wrote:
> I wrote:
>> Honza <honzap@gmail.com> writes:
>>> after a months I've found a time to make test-case for this bug, probably:
> 
>> Confirmed that this reproduces a problem on HEAD.  Will look into it,
>> thanks!
> 
> I believe I understand what's going on here, and it's not quite as
> exciting as it first appears.  The issue is that we are failing to
> honor the "toasting goes only one level deep" rule in the specific
> case of arrays of composite type.  So while it's definitely a nasty
> bug, it affects only narrow use cases, and doesn't call into question
> our whole vacuuming strategy or anything like that.

I would like to ask if there is anything new in this bug?

I've made a simple script which checks if daily backups are complete. For a week I've been deleting
a few records every day and hope the backup will be successfully done after that. The problem is
it's not possible to read all data from table during making backup using pg_dump too (not only
selecting data from table). I've found there is only one possibility to temporarily solve it and
have full backups - delete corrupted records.

Thanks for any news,

Jan

Honza <honzap@gmail.com> writes:
> On 03/28/2014 07:02 PM, Tom Lane wrote:
>> I believe I understand what's going on here, and it's not quite as
>> exciting as it first appears.  The issue is that we are failing to
>> honor the "toasting goes only one level deep" rule in the specific
>> case of arrays of composite type.  So while it's definitely a nasty
>> bug, it affects only narrow use cases, and doesn't call into question
>> our whole vacuuming strategy or anything like that.

> I would like to ask if there is anything new in this bug?

Still working on it --- haven't you been following the other thread?
http://www.postgresql.org/message-id/flat/29007.1396038881@sss.pgh.pa.us

We should have a fix in time for the next set of minor releases, whenever
that is (probably a month or two).  If you're feeling desperate for a
temporary solution, you could make use of one of the patches I already
posted, even though I now don't like those as a long-term answer.
        regards, tom lane

I wrote:
> I believe I understand what's going on here, and it's not quite as
> exciting as it first appears.  The issue is that we are failing to
> honor the "toasting goes only one level deep" rule in the specific
> case of arrays of composite type.  So while it's definitely a nasty
> bug, it affects only narrow use cases, and doesn't call into question
> our whole vacuuming strategy or anything like that.

I've committed a fix for this.  Thanks for the report!
        regards, tom lane