PITR Recovery and out-of-sync indexes - Mailing list pgsql-general

We are running a production server off of a new database that was
synchronized using PITR recovery. We found that many of the btree
indexes were out of sync with the underlying data after bringing the
new server out of recovery mode, but the data itself appeared to be
okay.

Both servers have identical Intel processors and both are running 64-
bit PostgreSQL 8.2.4. The original server is running 64-bit openSUSE
10.2 (Linux 2.6.18.2-34-default #1 SMP Mon Jul 16 01:16:32 GMT 2007
x86_64 x86_64 x86_64 GNU/Linux) and the new server is running Mac OS
X Leopard Server.

The first tip-off that things were amiss was this error in the log:
[2007-10-02 01:12:27 MDT] postgres@ssprod host:192.168.0.54(53976)
ERROR:  duplicate key violates unique constraint
"fed_product__unique__data_feed_id_prod_id_from_src_idx"
[2007-10-02 01:12:27 MDT] postgres@ssprod host:192.168.0.54(53976)
STATEMENT:  UPDATE FED_PRODUCT SET FEEDS_TO_MERCHANT_PRODUCT_ID =
5108509 WHERE (PRODUCT_ID = decode
('0000C0A80036000007D22F00000001155F68741EFE1555FB','hex') AND
DATA_FEED_ID = decode
('0000C0A80012000007D718000000010442E7CCFC929764DE','hex'))

This update threw a duplicate key error that should have been
triggered when the row was inserted. Looking at the row and the
application logs, I verified the conflicting row was inserted in the
new database after it was brought out of recovery mode. (I included
the fed_product's table definition below).

I performed a query qualifying using equals for the data_feed_id and
product_id_from_source to find the original row and the new bogus row
and no rows were returned. I updated the query to qualify using like
'xxxxx%' instead of equals forcing a sequential scan and two, albeit
conflicting, rows were returned.

I ran a query to delete any newly inserted bogus rows (there were 85
in all) and reindexed the fed_product table. Subsequent searches and
inserts against this table work as expected.

I ran queries against other tables and many indexes were returning
zero rows for rows that exist. I have now reindexed the complete
database and everything seems okay.

In the Continuous Archiving Point-In-Time Recovery section of the
docs, one of the caveats listed is:
"Operations on hash indexes are not presently WAL-logged, so replay
will not update these indexes. The recommended workaround is to
manually REINDEX each such index after completing a recovery operation"

Is it possible there are issues with btree indexes being maintained
properly as well? Any other ideas?

Brian Wipf
Clickspace Interactive Inc.
<brian@clickspace.com>

Table "public.fed_product"
  data_feed_id                    | bytea                        | not null
  date_created                    | timestamp without time zone    | not null
  date_modified                    | timestamp without time zone    |
  feeds_to_merchant_product_id    | integer                        |
  feeds_to_product_id                | integer                        |
  product_id                        | bytea                        | not null
  product_id_from_source            | character varying(512)        | not null
Indexes:
     "fed_product_pk" PRIMARY KEY, btree (product_id)
     "fed_product__unique__data_feed_id_prod_id_from_src_idx" UNIQUE,
btree (data_feed_id, product_id_from_source)
     "fed_product__additional_1__idx" btree (product_id_from_source)
     "fed_product__additional_4__idx" btree
(feeds_to_merchant_product_id)
     "fed_product__data_feed_id_fk_idx" btree (data_feed_id)