pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed[Bugg hash join and parallel worker]
7+ messages / 3 participants
[nested] [flat]
* [Bugg hash join and parallel worker]
@ 2024-02-28 14:50 GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 15:29 ` Re: [Bugg hash join and parallel worker] Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-28 16:29 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: GADACHA Rachid (Acoss) @ 2024-02-28 14:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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)
Best Regards
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Bugg hash join and parallel worker]
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
@ 2024-02-28 15:29 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
1 sibling, 0 replies; 7+ messages in thread
From: Andrei Lepikhov @ 2024-02-28 15:29 UTC (permalink / raw)
To: GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Bugg hash join and parallel worker]
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
@ 2024-02-28 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:33 ` RE: [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-02-28 16:29 UTC (permalink / raw)
To: GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [Bugg hash join and parallel worker]
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:29 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-28 16:33 ` GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:43 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
2024-02-29 09:03 ` Re: [Bugg hash join and parallel worker] Andrei Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 2 replies; 7+ messages in thread
From: GADACHA Rachid (Acoss) @ 2024-02-28 16:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Bugg hash join and parallel worker]
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:29 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:33 ` RE: [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
@ 2024-02-28 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-29 13:20 ` RE: [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-02-28 16:43 UTC (permalink / raw)
To: GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [Bugg hash join and parallel worker]
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:29 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:33 ` RE: [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:43 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-29 13:20 ` GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
0 siblings, 0 replies; 7+ messages in thread
From: GADACHA Rachid (Acoss) @ 2024-02-29 13:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Bugg hash join and parallel worker]
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:29 ` Re: [Bugg hash join and parallel worker] Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:33 ` RE: [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
@ 2024-02-29 09:03 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
1 sibling, 0 replies; 7+ messages in thread
From: Andrei Lepikhov @ 2024-02-29 09:03 UTC (permalink / raw)
To: GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-02-29 13:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-28 14:50 [Bugg hash join and parallel worker] GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 15:29 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2024-02-28 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-28 16:33 ` GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-28 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-29 13:20 ` GADACHA Rachid (Acoss) <rachid.gadacha@acoss.fr>
2024-02-29 09:03 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox