Re: linux bug and lost rows

Jaime Silvela <JSilvela(at)Bear(dot)com> writes:
> A long time ago I wrote to the list about a problem I was having with
> COPY losing rows from an import file: the number of imported rows was
> not equal to the number of rows in the file, and two consecutive imports
> from the same file would get different row counts. Several people tried
> to reproduce it unsuccessfully. Reference:
> http://archives.postgresql.org/pgsql-general/2006-07/msg00925.php

> More recently, as I was practicing a database upgrade to 8.2.3, I
> captured an "unexpected data beyond EOF" in the log, which led to
> missing tables in the upgraded db. I opened a thread, and it turned out
> someone had previously had the same problem, and it was due to the Linux
> kernel version: 2.6.5-7.244
> Reference: http://archives.postgresql.org/pgsql-general/2007-03/msg01543.php

> Now my server has been upgraded to 2.6.5-7.282, and I'm happy to report
> that BOTH problems have disappeared. The first problem, that of lost
> rows for COPY, tended to present itself for large import files, nearing
> 1GB, but I was never able to get reproducible results. As I understand,
> the Linux bug responsible for the "unexpected data beyond EOF" had to do
> with faulty disk reads. Probably this was also affecting the COPY
> command, only failing silently?

Your COPY problems were all on PG 8.1.x, right? The "unexpected data
beyond EOF" check was added in 8.2.0 specifically because we realized we
were getting bit by a Linux kernel bug. In 8.1, manifestations of that
same bug would have just led to silent data loss. The cases that we
identified before all seemed to involve concurrent insertions by
different backends, but I don't think anyone has hard proof that it
couldn't happen for successive insertions by a single backend. So yeah,
it now seems highly likely that that bug explains the COPY problem.

Out of sheer conservatism, I didn't backpatch the "unexpected data
beyond EOF" check into pre-8.2 stable branches, but I wonder if we
shouldn't do that now.

regards, tom lane