Message-ID: <26034d51-1ca3-498b-83e6-d2f3d3c643c7@postgrespro.ru>
Date: Wed, 28 Feb 2024 22:29:05 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [Bugg hash join and parallel worker]
Content-Language: en-US
To: "GADACHA Rachid (Acoss)" <rachid.gadacha@acoss.fr>,
 "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
References: 
 <PAYP264MB3368AF4D63704E370B204C39FF582@PAYP264MB3368.FRAP264.PROD.OUTLOOK.COM>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <PAYP264MB3368AF4D63704E370B204C39FF582@PAYP264MB3368.FRAP264.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/26034d51-1ca3-498b-83e6-d2f3d3c643c7%40postgrespro.ru>
Precedence: bulk

On 28/2/2024 21:50, GADACHA Rachid (Acoss) wrote:
> Hello,
> 
> We encounter an error when we join 2 subqueries with large tables like 
> below.
> 
> *The query*
> 
> 
> SELECT  count(*)  FROM (select anod_siret_decl,anod_mpd,anod_frac
> 
>               from dsn_anomalies.dsn_ano_camp_ctr c
> 
>                        join dsn_anomalies.dsn_ano_rel_camp darc on 
> c.camp_it = darc.camp_it
> 
>                        join dsn_anomalies.dsn_ano_anomalie on 
> darc.ano_it = dsn_ano_anomalie.ano_it
> 
>                        join dsn_anomalies.dsn_ano_decl d on c.anod_it = 
> d.anod_it
> 
>                        join dsn_anomalies.dsn_ano_statut_anomalie
> 
>                             on 
> dsn_anomalies.dsn_ano_statut_anomalie.sta_it =
> 
>                                (select 
> dsn_anomalies.dsn_ano_statut_anomalie.sta_it
> 
>                                 from dsn_anomalies.dsn_ano_statut_anomalie
> 
>                                 where 
> dsn_anomalies.dsn_ano_statut_anomalie.ano_it =
> 
>                                       dsn_anomalies.dsn_ano_anomalie.ano_it
> 
>                                 order by 
> dsn_anomalies.dsn_ano_statut_anomalie.sta_ts_crea desc
> 
>                                 limit 1)
> 
>               where c.camp_cd_ctx_exec = '7_FLUX_DSNDI'
> 
>                 and sta_cd_statut = 'DEPOT') a
> 
>                  join (select anod_siret_decl,camp_dt_mois_ctrl,anod_frac
> 
>                        from dsn_anomalies.dsn_ano_camp_ctr c
> 
>                                 join dsn_anomalies.dsn_ano_decl d
> 
>                                      on c.anod_it = d.anod_it
> 
>                        where c.camp_cd_ctx_exec = 
> '10_DSNDADI_EXIGIBILITE') b
> 
>                       on (a.anod_siret_decl, a.anod_mpd, a.anod_frac) =
> 
>                          (b.anod_siret_decl, b.camp_dt_mois_ctrl, 
> b.anod_frac);
> 
> *The error*
> 
> *ERROR:  invalid DSA memory alloc request size 1811939328*
> 
> *CONTEXT:  parallel worker*
> 
> **
> 
> *The work arround*
> 
> *enable_parallel_hash=off*
> 
> **
> 
> Context
> 
> *Version postgresql :* PostgreSQL 13.2 on x86_64-koji-linux-gnu, 
> compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
> 
> *OS VERSION :* CentOS release 6.10 (Final)

Yeah, we already had the report likewise yours.
Could you provide some reproduction to see what had happened? Or, at 
least, a coredump? backtrace?

-- 
regards,
Andrei Lepikhov
Postgres Professional