Re: [Bugg hash join and parallel worker]

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