On 28/08/2018 21:51, Tom Lane wrote:
> Hm, could I trouble you for a self-contained test case? I tried to
> flesh it out as attached, but I'm not seeing any error with this.
> So there must be some other moving part ...
>
> regards, tom lane
>
> <snip>
Tom, the materialized view in question was dependent on another
materialized view (pointlessly). I've changed the underlying view to a
simple one and the problem message "archive items not in correct section
order" doesn't appear.
Simply nesting the materialized views doesn't cause the issue though so
I'm no further forward, alas:
drop schema rating cascade;
drop schema r cascade;
create schema rating;
create table rating.cy (f1 int, cid int, ye int);
create schema r;
set search_path = r;
create table c(id int);
create table f(id int, cid int, ye int, ytext text);
create table i(gid int);
create table o(id int);
create table p(id int);
create table wb(cid int, fid int, prop float8);
create materialized view rating.d AS
SELECT c.id as cid,
f.id as fid,
COALESCE(( SELECT sum(
CASE
WHEN i.gid = 3 THEN 1
ELSE 0
END)::double precision / count(p.id)::double precision AS c
FROM o,
p,
i
WHERE o.id = c.id AND f.id = p.id AND o.id = i.gid)) AS fp
FROM rating.cy, c, f
WHERE c.id = f.id AND f.id = cy.cid AND f.ye = cy.ye
GROUP BY c.id, f.id;
CREATE materialized VIEW rating.b AS
SELECT
d.cid,
f.ytext,
min(coalesce(
(select
case
when wb.prop >= 0.333 then 4
when wb.prop >= 0.25 then 3
when wb.prop >= 0.15 then 2
when wb.prop >= 0.1 then 1
else 0
end
FROM r.wb
where
wb.cid = d.cid and
wb.fid = f.id), 0)) as score
FROM
rating.cy, rating.d, f
WHERE
d.cid = f.cid AND f.cid = cy.cid AND f.ye = cy.ye
GROUP BY
1, 2
LIMIT 1;
Tim Clarke
