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.
