archive items not in correct section order

On 08/27/2018 03:59 AM, Tim Clarke wrote:

Last night for the first time our pg_dump backup threw this error for
the first time. Repeated runs this morning consistently throw it too. I
can see the error in the Postgres source
https://doxygen.postgresql.org/pg__backup__archiver_8c_source.html but I
can't find any specifics about the cause or cure?

What is the full pg_dump command you are using?

Environment: Centos 6.10 kernel 2.6.32-754.2.1.el6.i686

postgresql10.i686 10.5-1PGDG.rhel6 @pgdg10
postgresql10-contrib.i686 10.5-1PGDG.rhel6 @pgdg10
postgresql10-devel.i686 10.5-1PGDG.rhel6 @pgdg10
postgresql10-libs.i686 10.5-1PGDG.rhel6 @pgdg10
postgresql10-odbc.i686 10.03.0000-1PGDG.rhel6 @pgdg10
postgresql10-server.i686 10.5-1PGDG.rhel6 @pgdg10
--
Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420

--
Adrian Klaver
adrian.klaver@aklaver.com

Tim Clarke <tim.clarke@minerva-analytics.info> writes:

Last night for the first time our pg_dump backup threw this error for
the first time. Repeated runs this morning consistently throw it too.

That's not supposed to happen. Can you create a test case, by any chance?
Presumably, it's triggered by some database schema change you made since
the last successful dump.

regards, tom lane

On 27/08/18 15:22, Tom Lane wrote:

Tim Clarke <tim.clarke@minerva-analytics.info> writes:

Last night for the first time our pg_dump backup threw this error for
the first time. Repeated runs this morning consistently throw it too.

That's not supposed to happen. Can you create a test case, by any chance?
Presumably, it's triggered by some database schema change you made since
the last successful dump.

regards, tom lane

In answer to Adrian's question, we run:

pg_dump -U (user) -C (database)

then we pipe that out to gpg. pg_dump still throws the error without the
pipe.

I'm cutting down to find as brief a test case as possible at the moment;
current hot favourite is a materialised view that's a crosstab using the
functions from here https://www.postgresql.org/docs/10/static/tablefunc.html

--
Tim Clarke

On 27/08/18 15:22, Tom Lane wrote:

That's not supposed to happen. Can you create a test case, by any chance?
Presumably, it's triggered by some database schema change you made since
the last successful dump.

regards, tom lane

It wasn't the cross-tab/pivot, it was this materialized view:

CREATE materialized VIEW r.b AS
  SELECT
    c.id,
    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 = c.id and
        wb.fid = f.id), 0)) as score
  FROM
    rating.cy,
    c,
    f
  WHERE
    c.id = f.cid AND
    f.cid = cy.cid AND
    f.ye = cy.ye
  GROUP BY
    1, 2
  LIMIT 1;

I've trimmed it down to even just one row and it still causes the
warning "pg_dump: [archiver] WARNING: archive items not in correct
section order"

It yields this data:

 id        | ytext    | score
-----------+----------+-------
         5 | 1996     |     0

--

Tim Clarke

Tim Clarke <tim.clarke@minerva-analytics.info> writes:

On 27/08/18 15:22, Tom Lane wrote:

That's not supposed to happen. Can you create a test case, by any chance?

It wasn't the cross-tab/pivot, it was this materialized view:

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

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 wb(cid int, fid int, prop float8);

CREATE materialized VIEW r.b AS
SELECT
c.id,
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 = c.id and
wb.fid = f.id), 0)) as score
FROM
rating.cy,
c,
f
WHERE
c.id = f.cid AND
f.cid = cy.cid AND
f.ye = cy.ye
GROUP BY
1, 2
LIMIT 1;

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