[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

"GADACHA Rachid (Acoss)" <rachid.gadacha@acoss.fr> writes:

We encounter an error when we join 2 subqueries with large tables like below.
...
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

13.2 is extremely old. Can you reproduce this on the current minor
release (13.14)?

regards, tom lane

Hello,
I can't reproduce the scenario, because we don't have the current minor release (13.14).

Best Regards

-----Message d'origine-----
De : Tom Lane <tgl@sss.pgh.pa.us>
Envoyé : mercredi 28 février 2024 17:30
À : GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
Cc : pgsql-bugs@lists.postgresql.org
Objet : Re: [Bugg hash join and parallel worker]

"GADACHA Rachid (Acoss)" <rachid.gadacha@acoss.fr> writes:

We encounter an error when we join 2 subqueries with large tables like below.
...
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

13.2 is extremely old. Can you reproduce this on the current minor release (13.14)?

regards, tom lane

"GADACHA Rachid (Acoss)" <rachid.gadacha@acoss.fr> writes:

I can't reproduce the scenario, because we don't have the current minor release (13.14).

If you're unwilling to update to a new minor release, how do you
think we could help you? If the bug's still there (which it might
not be) any fix would appear in a future minor release. There's
nothing we can do now about the behavior of 13.2.

regards, tom lane

On 28/2/2024 23:33, GADACHA Rachid (Acoss) wrote:

Hello,
I can't reproduce the scenario, because we don't have the current minor release (13.14).

Could you provide us with a core dump and/or backtrace at the moment of
the error on your current system?

--
regards,
Andrei Lepikhov
Postgres Professional

Thank you for this feedback, so I'll see if we can install a newer version.

Best Regards

-----Message d'origine-----
De : Tom Lane <tgl@sss.pgh.pa.us>
Envoyé : mercredi 28 février 2024 17:44
À : GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
Cc : pgsql-bugs@lists.postgresql.org
Objet : Re: [Bugg hash join and parallel worker]

"GADACHA Rachid (Acoss)" <rachid.gadacha@acoss.fr> writes:

I can't reproduce the scenario, because we don't have the current minor release (13.14).

If you're unwilling to update to a new minor release, how do you think we could help you? If the bug's still there (which it might not be) any fix would appear in a future minor release. There's nothing we can do now about the behavior of 13.2.

regards, tom lane