Re: archive items not in correct section order

[Tim Clarke]

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