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