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
     ;

The Result for #View2

     wts                 | wsft | wspan  | wstate | wline | rcodes
     --------------------+------+--------+--------+-------+-------
     2020-07-01 08:00:00 | D    |      0 | S00    | F02   | {PDCB}
     2020-07-01 09:50:01 | D    | 12.533 | S00    | F02   | {PDCB}
     2020-07-01 11:35:46 | D    | 12.217 | S00    | F02   | {CDSO}
     2020-07-01 13:22:58 | D    |   5.15 | S00    | F02   | {PDCB}
     2020-07-01 14:57:38 | D    |    6.8 | S00    | F02   | {PDCB}

     INDEX | COLUMN_NAME | DATA_TYPE
     ------+-------------+------------
     1     | wts         | timestamptz
     3     | wsft        | varchar
     4     | wspan       | float8
     5     | wstate      | varchar
     6     | wline       | varchar
     7     | rcodes      | text[]


Same query, the Result for #View1

     wts                 | wsft | wspan | wstate | wline | rcodes
     --------------------+------+-------+--------+-------+-------
     2020-07-01 08:00:00 | D    |     5 | S00    | F02   | {PDCB}
     2020-07-01 09:50:01 | D    |    13 | S00    | F02   | {PDCB}
     2020-07-01 11:35:46 | D    |    12 | S00    | F02   | {CDSO}
     2020-07-01 13:22:58 | D    |     5 | S00    | F02   | {PDCB}
     2020-07-01 14:57:38 | D    |     7 | S00    | F02   | {PDCB}

     INDEX | COLUMN_NAME | DATA_TYPE
     ------+-------------+------------
     1     | wts         | timestamptz
     3     | wsft        | varchar
     4     | wspan       | float8
     5     | wstate      | varchar
     6     | wline       | varchar
     7     | rcodes      | varchar[]

Reuslts in `wspan` column is inaccurate while both type are float8. Most 
weird thing is the 5 to 0 change. for Row 1.

The `_b0206`(#View1) is just a version of 
`stats_per_shift_filtered`(#View2) from past revisions.
I am sure the original CREATE statement for (#View2) is `CREATE VIEW ... 
AS SELECT * FROM ...._b0206`

Definition of View2 in SQLWorkbench/J generated schema:


     CREATE OR REPLACE VIEW utlog.stats_per_shift_filtered (#View2)
     (
     wts,
     wdate,
     wsft,
     wspan,
     wstate,
     wline,
     rcodes
     )
     AS
     SELECT stats_per_shift_filtered_u0206.wts,
         stats_per_shift_filtered_u0206.wsft::character varying AS wsft,
         stats_per_shift_filtered_u0206.wspan,
         stats_per_shift_filtered_u0206.wstate,
         stats_per_shift_filtered_u0206.wline,
         stats_per_shift_filtered_u0206.rcodes
     FROM utlog.stats_per_shift_filtered_u0206;  (as #View1 in this post)


It feels like the utlog.stats_per_shift_filtered_u0206 in 
utlog.stats_per_shift_filtered definition is a different object from 
utlog.stats_per_shift_filtered_u0206?

I am totally out of clues. Any help would be appreciated. Thanks.


Regards,

Ben