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

On 9/15/20 10:40 PM, Ben wrote:
> 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.

Try the queries in a serializable read only transaction.  That should any 
possible changes in the underlying data.

START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
SELECT  col1 FROM VIEW2 WHERE cond1=True;
SELECT  col1 FROM VIEW1 WHERE cond1=True;
COMMIT;

-- 
Angular momentum makes the world go 'round.