Обсуждение: archive items not in correct section order

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?

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

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