Re: archive items not in correct section order

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