Re: Multicolumn index corruption on 8.4 beta 2

Hi,

Josh Berkus wrote:
> It doesn't. But what I don't trust, and the *first* place I'd look for
> problems, is whether the OS flushes *all* dirty buffers to disk in the
> event the application gets killed.
>
> That's why I want more information on Floris' case. Was 8.4 killed or
> shut down with -m immediate? Or the os rebooted with 8.4 running?

The only reboots I have done on that server were with the "reboot"
system command, which should send a SIGTERM to all processes first
including PostgreSQL, before pulling the plug.

I do recall that during the execution of "vacuum full" the psql client
program once did report that it lost connection with the server, but was
able to reconnect. Maybe the server processes handling the connection
died then, but I am not sure of that, and it only happened once.

Anyway, the problem also occurs when there is no reboot or unexpected
event between the reindex and the query.

After a REINDEX it is able to find the row it was missing first, but
then other rows become missing.

All in the same psql session:

===
usenet=> SELECT count(*) FROM posts_index WHERE
poster='Yenc(at)power-post(dot)org (Yenc-PP-A&A)' AND groupid=300 AND
basefile='NIB8124849';
count
-------
0
(1 row)

usenet=> reindex index pgb_idx;
REINDEX
usenet=> SELECT count(*) FROM posts_index WHERE
poster='Yenc(at)power-post(dot)org (Yenc-PP-A&A)' AND groupid=300 AND
basefile='NIB8124849';
count
-------
1
(1 row)

usenet=> SELECT count(*) FROM posts_index WHERE
poster='Yenc(at)power-post(dot)org (Yenc-PP-A&A)' AND basefile='frx-fffe' AND
groupid=757;
count
-------
0
(1 row)

usenet=> set enable_indexscan=false;
SET
usenet=> set enable_bitmapscan=false;
SET
usenet=> SELECT count(*) FROM posts_index WHERE
poster='Yenc(at)power-post(dot)org (Yenc-PP-A&A)' AND basefile='frx-fffe' AND
groupid=757;
count
-------
1
(1 row)

===

Yours sincerely,

Floris Bos