Re: Mysterious table that exists but doesn't exist

Dale Fukami <dale(dot)fukami(at)gmail(dot)com> writes:
> I'm having a problem on a standby server (streaming replication) where a
> table seems to exist but is not queryable. Essentially a select statement
> (and drop/insert/etc) fails but \d and pg_tables show it exists. The table
> exists on the master (and is queryable) and replication is still working in
> that changes to the master table don't cause errors on the standby and
> changes to other tables are replicated and verified to be on the standby.

That's peculiar. The most likely theories seem to be
(1) corruption in the standby's pg_class indexes, such that an index
search for relname = 'tracked_deductibles' fails to find the row,
although it is found by seqscans; or
(2) some type of visibility issue causing SnapshotNow operations to
think the row is invalid, though it is valid to MVCC queries.

Either way, if it's working on the master, then you've had a replication
failure since the standby's files evidently don't match the master's.

What PG version is this (and which versions have been installed since
the replication was set up)? Have you had any system-level crashes on
the standby?

> 2) If there were corruption or something in the data for that particular
> table on the standby only, would replication report a failure (i.e., be
> unable to apply the binary changes) or would the binary changes still just
> sort of happen overtop the bad data?

Depends. It's not impossible that the corruption could magically
disappear, if there's a full-page update sent from the master that
overwrites whatever the problem is. Incremental updates on the same
page would not make things better, of course, and could easily make them
worse. It's unlikely that an incremental WAL replay operation would
notice a problem that evidently isn't being noticed by regular queries.

regards, tom lane