Обсуждение: Mysterious table that exists but doesn't exist

live=# select * from someschema.tracked_deductibles;

ERROR:  relation "someschema.tracked_deductibles" does not exist

LINE 1: select * from someschema.tracked_deductibles;

........

live=#\d

                                      List of relations

   Schema   |                            Name                             |   Type   | Owner

------------+-------------------------------------------------------------+----------+-------

<snip>

 someschema | tracked_deductibles                                         | table    | live

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 4/16/2013 1:48 PM, Dale Fukami wrote:
> Tried the select with the quotes around each of the schema and table
> and I get the same result ("someschema"."tracked_deductibles"). I'm
> pretty certain they're both lowercase. Would the select from pg_tables
> show if they were mixed case by accident?
>

yes, it would.      I was assuming "someschema" was a made-up name to
hide some business secrets.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Dale Fukami <dale.fukami@gmail.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

Dale Fukami <dale.fukami@gmail.com> writes:
> On Tue, Apr 16, 2013 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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?

> My apologies: PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC
> gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
> This is the only version we've had since we've set up streaming replication.

> We believe we've found the moment of corruption. It appears that the db was
> shutdown then restarted and didn't quite catch up to the master and then
> was shut down again. We use this standby to take snapshots on a regular
> basis but it looks like this one didn't quite get into a consistent state
> before the next one started. Logs pasted at the end of this email.

Hm ... there was a fix in 9.0.12 that might be relevant to this:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=5840e3181b7e6c784fdb3aff708c4dcc2dfe551d
Whether that explains it or not, 9.0.5 is getting long in the tooth;
you really need to think about an update.  Especially in view of
CVE-2013-1899.

> 1) We were quite lucky I think to have discovered this issue. I think the
> only other way we'd have noticed would have been if we'd failed over to it
> and our app stopped working. I'm worried now that we'll end up in a similar
> situation and won't have known it for many weeks. At some point we lose the
> ability to PITR based on how many backups are kept. Is there a way to be
> more confident in our standby machines?

I think you should update your PG version and then resync your standbys
(ie, apply a fresh base backup) at the next convenient maintenance
window.

> Just to clear up the confusion that I had caused John. I had obscured the
> previous schema name by restoring from snapshots and altering the schema
> name itself. So, the queries and output are an exact copy/paste from my
> terminal. I get the same results on the Live standby when using the actual
> Live schema name but, obviously, it shows the Live schema name rather than
> 'someschema'.

Well, in that case there's the question of whether you'd duplicated the
standby's state accurately ...

            regards, tom lane