Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kZvZd-0005Jw-IF for pgsql-bugs@arkaria.postgresql.org; Tue, 03 Nov 2020 12:38:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kZvZc-0008Dt-Hf for pgsql-bugs@arkaria.postgresql.org; Tue, 03 Nov 2020 12:38:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kZvZc-0008Dm-37 for pgsql-bugs@lists.postgresql.org; Tue, 03 Nov 2020 12:38:04 +0000 Received: from mta97aws110.mxout.inova.com.br ([3.223.97.110] helo=mxhero01.a.inova.com.br) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kZvZW-00038K-RI for pgsql-bugs@lists.postgresql.org; Tue, 03 Nov 2020 12:38:03 +0000 Received: from ip-10-0-4-78.ec2.internal (localhost [127.0.0.1]) by mxhero01.a.inova.com.br (Postfix) with ESMTP id 2CFCA1CE36E for ; Tue, 3 Nov 2020 12:37:57 +0000 (UTC) Received: from corpmoutpolicyd01.a.inova.com.br (puppetmaster.a.inova.com.br [10.0.1.88]) by mxhero01.a.inova.com.br (Postfix) with ESMTP; Tue, 3 Nov 2020 12:37:57 +0000 (UTC) Received: from corpmoutpolicyd01.a.inova.com.br (localhost [127.0.0.1]) by corpmoutpolicyd01.a.inova.com.br (Postfix) with ESMTP id 1735D220C16; Tue, 3 Nov 2020 10:37:53 -0200 (BRST) Received: from corpmout01.a.inova.com.br (ip-10-0-1-125.ec2.internal [10.0.1.125]) by corpmoutpolicyd01.a.inova.com.br (Postfix) with ESMTP id 6176CE0634; Tue, 3 Nov 2020 10:37:38 -0200 (BRST) Received: from corpmbox02.a.inova.com.br (ip-10-0-4-158.ec2.internal [10.0.4.158]) by corpmout01.a.inova.com.br (Postfix) with ESMTP; Tue, 3 Nov 2020 09:37:34 -0300 (-03) Date: Tue, 3 Nov 2020 09:37:33 -0300 (BRT) From: luis.roberto@siscobra.com.br To: Tom Lane Cc: pgsql-bugs , "alan.formagi" Message-ID: <1779929796.39206557.1604407053112.JavaMail.zimbra@siscobra.com.br> In-Reply-To: <136656.1604092024@sss.pgh.pa.us> References: <622580997.37108180.1604080457319.JavaMail.zimbra@siscobra.com.br> <102089.1604082122@sss.pgh.pa.us> <541187747.37582276.1604086719237.JavaMail.zimbra@siscobra.com.br> <107029.1604087483@sss.pgh.pa.us> <1673261949.37800156.1604090102715.JavaMail.zimbra@siscobra.com.br> <1807748588.37862034.1604091799640.JavaMail.zimbra@siscobra.com.br> <136656.1604092024@sss.pgh.pa.us> Subject: Re: segfault with incremental sort MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_823b5072-f343-42b4-b660-7418175cb639" X-Originating-IP: [10.0.1.247] X-Mailer: Zimbra 8.8.15_GA_3968 (ZimbraWebClient - GC86 (Win)/8.8.15_GA_3968) Thread-Topic: segfault with incremental sort Thread-Index: mmZuks7bTXIer8qlCLWw2v2aHMTlCw== x-mxHero-Origin-Ip: /127.0.0.1:55750 X-mxHero-AttachmentBlock: rule=48;result=false List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_823b5072-f343-42b4-b660-7418175cb639 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hm ... suggestive, but not really enough to debug it. Can you build a self-contained test case? regards, tom lane Hi Tom! It took me some time to make it the same... I managed to simplify the error. It appears to be something related to a subplan with a distinct clause and another subplan... drop table main,secondary; create table main ( id bigint generated by default as identity primary key, id2 int not null, type smallint default 0, name text not null ); insert into main (id2,name,type) select (id%100)+1,md5(id::text),case when (id%100) > 0 then 0 else 1 end from generate_series(1,3401305) a(id); create index on main (id2); create table secondary ( id bigint, id2 smallint, name text, primary key (id,id2) ); insert into secondary (id,id2,name) select m.id,a.seq,md5(m.id::text) from main m, generate_series(1,16) a(seq); analyze main,secondary; explain analyze select m.id2, m.id, s.description FROM main m LEFT JOIN ( SELECT DISTINCT m.id, CASE WHEN m.id2 = 15 AND (SELECT name FROM secondary x WHERE x.id = s2.id AND x.id2 = 10) = md5(123::text) THEN 'description' WHEN m.id2 = 15 THEN (SELECT name FROM secondary x WHERE x.id = s2.id AND x.id2 = 5) END AS description FROM main m JOIN secondary s2 ON m.id = s2.id WHERE m.id2 = 15 and type = 0) s ON s.id = m.id WHERE m.id2 IN (15) and type = 0 --=_823b5072-f343-42b4-b660-7418175cb639 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable


Hm ... suggestive, but not really enough to debug it.

Can y= ou build a self-contained test case?

     &= nbsp;           &nbs= p;      regards, tom lane

Hi Tom!

It took me some time to make it the same... I managed to simplify the erro= r. It appears to be something related to a subplan with a distinct clause a= nd another subplan...

drop table main,secondary;
 
create table main (
&nb= sp; id bigint generated by default as identity primary key,
  id2 int not null,
  type smallint default 0,
  name text not null
);
insert into main (id2,name,type)
select (id%100)+1,md5(id::text),case when (id%100) &= gt; 0 then 0 else 1 end
fr= om generate_series(1,3401305) a(id);

create ind= ex on main (id2);

create table secondary (
  id bigint,
=
  id2 smallint,
  name text,
  primary key (id,id2)
);

insert into second= ary (id,id2,name)
select m= .id,a.seq,md5(m.id::text)
= = from main m,
  &nbs= p;  generate_series(1,16) a(seq);

analyze = main,secondary;
explain analyze <= /div>
select m.id2,
      &n= bsp;m.id,
 = ;      s.description
  FROM main m
       LEFT JOIN ( SELECT DIST= INCT
  &nb= sp;                     &= nbsp; m.id,
&nb= sp;                     &= nbsp;   CASE 
                  &nb= sp;       =09  WHEN m.id2 =3D 15 AND (SELECT name FROM= secondary x WHERE x.id =3D s2.id AND x.id2 =3D 10) =3D md5(123::text) = ; THEN 'description'
                   =       =09  WHEN m.id2 =3D 15 THEN (SELECT name FROM s= econdary x WHERE x.id =3D s2.id AND x.id2 =3D 5)
           =                END AS description<= /span>
    &= nbsp;   =09=09     FROM main m
        =09=09&nb= sp;         JOIN secondary s2 ON m.id =3D s2.id<= /div>
      &= nbsp; = =09=09    WHERE m.id2 =3D 15
        =09=09  &= nbsp;  and type =3D 0) s ON s.id =3D m.id
   WHERE m.id2 IN (15)  = ;
  &nbs= p;  and type =3D 0

<= /div>
--=_823b5072-f343-42b4-b660-7418175cb639--