Re: Obvious data mismatch in View2 which basically SELECT * from View1 - Mailing list pgsql-general

Dear list,

Recently I am getting feedback, data in my analytic report is not
repeatable. From time to time they get different data for the same time
span.
(but IIRC previously it was OK). Therefore I started debuging the View
chain for that report, during which I bumped into this issue/phenomenon.

In a over -simplified version:

CREATE VIEW2 AS SELECT * FROM VIEW1;
SELECT  col1 FROM VIEW2 WHERE cond1=True;
SELECT  col1 FROM VIEW1 WHERE cond1=True;

Now col1 from both views looks different. I don't know where to start to
solve this problem.

The actual situation is a bit more than that, the following is the
actual query:


     -- trying to audit utlog weighed stat
     with t as (
     select '2020-07-01 00:00:00'::timestamp t0, '2020--07-02
0:0:0'::timestamp t1
     )
     --select * from t;
     select *
     -- from utlog.cache_stats_per_shift_per_reason_weighed_stats
     -- from utlog.stats_per_shift_filtered_per_reason
     from utlog.stats_per_shift_filtered                     (let's call
it #View2 for short)
     -- from utlog.stats_per_shift_filtered_b0206      (let's call it
#View1 for short)
     -- from utlog.stats_per_shift
     cross join t
     where wline = 'F02'  and wts >= t.t0 and wts < t.t1 and wsft ='D'
     limit 100
     ;