Re: Table has 22 million records, but backup doesn't see them

________________________________
From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Radcon Entec <radconentec(at)yahoo(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, April 8, 2009 1:15:55 PM
Subject: Re: [GENERAL] Table has 22 million records, but backup doesn't see them

Radcon Entec wrote:
>
>  Here is the text that results from dumping my 22-million-row feedback table:
>  ...
>
> CREATE RULE feedback_partition_active AS ON INSERT TO feedback WHERE (new.charge > 7000) DO INSTEAD INSERT INTO feedback_active (charge, elapsed_time, tag_type, stack, tag_value, heating, status) VALUES (new.charge, new.elapsed_time, new.tag_type, new.stack, new.tag_value, new.heating, new.status);
>
> ...
>
> CREATE RULE feedback_partition_archived AS ON INSERT TO feedback WHERE (new.charge <= 7000) DO INSTEAD INSERT INTO feedback_archived_7000 (charge, elapsed_time, tag_type, stack, tag_value, heating, status) VALUES (new.charge, new.elapsed_time, new.tag_type, new.stack, new.tag_value, new.heating, new.status);
>

Are you certain that feedback actually contains any data or is it just the parent table and the real data is in the child tables? What is the output of "select count(*) from only feedback;" ?

Cheers,
Steve

Steve,

You are, of course, correct.  "select count(*) from only feedback" returns 0.  I have never used (or even seen) PostgreSQL rules before. 

When I run the query "select * from feedback where charge = 23017", I get 538 records.  Adding the word "only" gives me zero records, as expected, and querying the feedback_active table gets me my 538 records.  But the feedback table only has the INSERT rules you quoted above.  I clicked on the feedback table's Rules leaf and selected "New Rule", and saw that I can create SELECT, INSERT, UPDATE and DELETE rules.  But even though I don't have a SELECT rule explicitly defined, PostgreSQL appears to be smart enough to retrieve data from the correct actual table when I think I'm selecting from the feedback table.  Is that standard behavior? 

Of course, my next step will be to read the documentation.

Thank you yet again!

RobR