Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMLU1-00000001ynE-10cN for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Oct 2026 08:27:37 +0000 Received: from localhost ([127.0.0.1]:58890 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMLU0-00000003W7b-3BQQ for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Oct 2026 08:27:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:56002) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMLT8-00000003RTw-1QLN for pgsql-hackers@lists.postgresql.org; Thu, 29 Oct 2026 08:26:42 +0000 Received: from mail-wr1-x434.google.com ([2a00:1450:4864:20::434]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xMLT6-00000001YeM-0uLz for pgsql-hackers@lists.postgresql.org; Thu, 29 Oct 2026 08:26:42 +0000 Received: by mail-wr1-x434.google.com with SMTP id ffacd0b85a97d-48df0fa43c5so227532f8f.0 for ; Thu, 29 Oct 2026 01:26:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1793262399; x=1793867199; darn=lists.postgresql.org; h=content-transfer-encoding:content-type:in-reply-to:from :content-language:references:cc:to:subject:user-agent:mime-version :date:message-id:from:to:cc:subject:date:message-id:reply-to :content-type; bh=Z+plHPpiYrU2AY/3sNT2S0iEJzGFVbIjSJrQlX3O/Ck=; b=VClSgXcRIRaJ8eapPN0z5l7FegpzVUUufDreE3Hqr0Fuz2xay4+hefAAXhrLQCAB0s QjojafXdSR+WPaVmueNb2V0CfW7lxcRSEsW6GxMe/T7XVh4AvBVMLdHs5B5VMW9lK2gU clDmoRVYfXVFZZHKaMY466NOi8NMPFBDf/h5z/TtSZDfOaPyV6JF0x51/lGwhYyRTEpb cvpgd4luIC/vIxgJzLew/ffYVLFLM43c5b+n+1MtGRU/BOGpmt0zyNkuUYY/rUTsJ+Bt Bkej3XbxnNpJipq7fxtppB8k0Tm/qEy58TANW3PLaNWXcbc1zvyOHOwNIQDlx3l3eNim y9Yg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1793262399; x=1793867199; h=content-transfer-encoding:content-type:in-reply-to:from :content-language:references:cc:to:subject:user-agent:mime-version :date:message-id:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to:content-type; bh=Z+plHPpiYrU2AY/3sNT2S0iEJzGFVbIjSJrQlX3O/Ck=; b=k2d6UFpNEfLJyZHF1EdtIYg9IVqIa2m3p2BHeEkG+FWNY0z51myFcahW6yS4W60apN NdHyFo5uwZrBRqqa6yjtQy/5QrElt2a9sylnXPvF7H8ibG3UQjK1TIvDuwYQMGXNZbO7 +nV0zL9zB0eQp/VaesgcHL8SwsDhomSxMVEDsWvUrm81RXODDmg1JG19SLg03ekABmZg ASSF86UF+Q0aVpTiILLhNuhPP7AHruqO5/X1b1TSvVMXIUW7tS14nBitX/MppT69vTV7 4qUrBVug9kOffpvKzVTGWYYdSHTY9Au/xir2JMzC5N9lxrnFDtaRa2peBm6vjCX5jtW9 Qchw== X-Forwarded-Encrypted: i=1; AKwUvBx/gJqUltYtUVzuCFyLrAYJb1nNe+jt2xVeGRywO4RCRpB19XPbzgpX4jmiXl7jvVpdru3cvm0jcVqQkyo0@lists.postgresql.org X-Gm-Message-State: AFq9FYIdNTBmhubGSys1dwySVbZO6j4ZDGNLCgriZGRltSHs8f2yVYwj RijraFhyOOyCBS8a5iYy5LLu+MCVzCAu4KgGFKgtq5cBF7s4JbJuD+5X+pHYIOAC5rE= X-Gm-Gg: AYBFou3+IyZYScGq7by0DbDil/Qjnx0RbLQ3/rWqcNjhOmLnX5l3S9Gic6w75mLyjP+ a063Xa2OaGDqvCtgRWYvUhyn32XH3kZ4DOn9+QLF2DmZx/q2n5adedPgRQ/SmXFLoSv3YRdx8O9 hurMxlYwxy5hfB8doduYX5vZh9C888bsEm+VBPx43g4Jy28tiQge1lA2O68isZUUHtaWvX7tP9Q l50p4coEI4hDpThvf0f71iz2sp+Xx2/uOVaT8eopP2cmfLovjSuZwErPcCThhira6GHbPoNJC+g 1wbk2aqMZr91t8BWrc7h8zwfVCjUj6Vfpf8EiEpFAW9OEXkEjl1N+916Ph44wzWVHIrGRVSkvD5 zdoOSPr9a23qexTjLJ8WlomEgrmaEHWA4VRqib6ag8zj8tey6kNrj++P2PR47yvTJ8ezgqf0lOX 3hBzilY1HmQi80k8NLsKIZbp4viEH1iJtMYvgracqstSB5tXQBL5HHHNwbrIXbg2tDflnoKG60I T9lYqmeInRbFEkxNjT0RLFRidtgPnCXc+w3/C3b/5TKtzs5XKEFIKrdRVw2vS2PQwlSAk2eFNd7 2vsSrKyVXYcDAlZZc552zbNldCTfD1VSKQ/iUhA2tfkNx+t7gomd1rYkOjPq6vC1EjesaQChQ1J LXY4KPPtokDe3m8yL5MjUbHEhab4jr+w82fva+n3baCgRcS2LNCXv/0CCmnQ2 X-Received: by 2002:a05:6000:25fb:b0:48d:fbe0:62e1 with SMTP id ffacd0b85a97d-48e64d028afmr4571397f8f.27.1793262398948; Thu, 29 Oct 2026 01:26:38 -0700 (PDT) Received: from ?IPV6:2003:c1:5f13:bb8b:a67a:b3a2:4056:97bc? (p200300062199b98ba67ab3a2405697bc.dip0.t-ipconnect.de. [2003:6:2199:b98b:a67a:b3a2:4056:97bc]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-48e3ebd4ba7sm33127757f8f.33.2026.10.29.01.26.37 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 29 Oct 2026 01:26:38 -0700 (PDT) Message-ID: <837d0e7e-29d1-4aea-9bf0-47269ef473fb@gmail.com> Date: Thu, 29 Oct 2026 09:26:37 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Assert failure in parallel query due to SubPlan double visit for hash join To: Lukas Fittl , PostgreSQL Hackers Cc: Andres Freund References: Content-Language: en-US From: David Geier In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Lukas! > Whilst stress testing the next version of the stack-based > instrumentation patch [0], I ran into what appears to be an ancient > bug in how we walk plan state for SubPlan nodes in parallel query > setup and instrumentation handling. Good find. > Specifically, the planstate_tree_walker callbacks in execParallel.c > assume that a given PlanState is only walked once, but that doesn't > hold true for hash joins since (I think, not tested) Postgres 10. We > create two separate references to the same subplan PlanState, one via > hashclauses and the other via hashkeys, each having their own > SubPlanState, but going to the same subplan PlanState. And > planstate_walk_subplans() descends into the subtree once per > reference. Yes. I've confirmed this and put some details below. > I believe this is an issue since b8d7f053c5c (Postgres 10, CCing > Andres as the author of that one) changed how ExecInitHashJoin calls > ExecInitExpr, creating the second SubPlanState. More digging also > found the very old a64846f3ada that talked about a seemingly related > problem, but that must have happened before parallel query was in the > picture. (gdb) p *planstate $17 = { type = T_HashJoinState, plan = 0x5a9ef8df0040, state = 0x5a9ef8ddfed0, ... (gdb) p ((SubPlanState *)planstate->subPlan->elements[0])->planstate $15 = (PlanState *) 0x5a9ef8de01e0 (gdb) p ((SubPlanState *)planstate->subPlan->elements[1])->planstate $16 = (PlanState *) 0x5a9ef8de01e0 This happens in ExecInitSubPlanExpr() which adds the sort PlanState twice to the subPlan list. > We can see this cause an assertion failure on master with the > following reproducer: > > CREATE TABLE big (a int, b int); > INSERT INTO big SELECT g, g FROM generate_series(1, 10000) g; > CREATE TABLE small (x int PRIMARY KEY, y int); > INSERT INTO small SELECT g, g FROM generate_series(1, 1000) g; > CREATE TABLE other (k int PRIMARY KEY, f int); > INSERT INTO other SELECT g, g FROM generate_series(1, 1000) g; > ANALYZE big, small, other; > ALTER TABLE other SET (parallel_workers = 0); > > SET parallel_setup_cost = 0; > SET parallel_tuple_cost = 0; > SET min_parallel_table_scan_size = 0; > SET enable_nestloop = off; > SET enable_mergejoin = off; > SET enable_parallel_hash = off; > SET parallel_leader_participation = off; -- to show loop count more clearly > > EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) > SELECT COUNT(*) FROM big b JOIN small s ON s.x = > CASE WHEN (b.a, b.b) NOT IN (SELECT o.k, o.f FROM other o ORDER BY o.f) > THEN 1 ELSE 2 END; > > TRAP: failed Assert("toc->toc_entry[i].key != key"), File: > "shm_toc.c", Line: 190 I can reproduce the assertion. > Besides the assertion failure it also causes problems for > instrumentation. In this example we get an incorrect loop count (the > subplan is really only run in the two workers, so the loops=16 below > should be 4), and the Sort node is missing the Sort Method reporting: > > QUERY PLAN > -------------------------------------------------------------- > Finalize Aggregate (actual rows=1.00 loops=1) > -> Gather (actual rows=2.00 loops=1) > Workers Planned: 2 > Workers Launched: 2 > -> Partial Aggregate (actual rows=1.00 loops=2) > -> Hash Join (actual rows=5000.00 loops=2) > Hash Cond: (CASE WHEN (NOT (ANY ((b.a = (hashed > SubPlan any_1).col1) AND (b.b = (hashed SubPlan any_1).col2)))) THEN 1 > ELSE 2 END = s.x) > -> Parallel Seq Scan on big b (actual > rows=5000.00 loops=2) > -> Hash (actual rows=1000.00 loops=2) > Buckets: 1024 Batches: 1 Memory Usage: 44kB > -> Seq Scan on small s (actual > rows=1000.00 loops=2) > SubPlan any_1 > -> Sort (actual rows=1000.00 loops=16) > Sort Key: o.f > -> Seq Scan on other o (actual > rows=1000.00 loops=16) > > The assertion failure happens because ExecParallelInitializeDSM visits > that Sort twice and tries to shm_toc_insert() the same plan_node_id > key. The instrumentation issue happens because (1) for loops, we > accumulate the worker instrumentation twice in > ExecParallelReportInstrumentation and then again in > ExecParallelRetrieveInstrumentation for the nodes underneath the > shared SubPlan, (2) for "Sort Method" the leader and workers end up > pointing at different (duplicate) SharedSortInfo slots and the leader > looks at the empty one. > > To fix this, the least convoluted way I could think of is to invent a > new mechanism to collect a deduplicated list of plan states, and then > call the existing logic on each list member. This also allows some > refactoring to remove logic for counting how many nodes we've visited. While I like generally like your fix and the resulting code simplifications, I'm wondering if it's the right fix. It seems to me that the PlanState graph structure is broken for the case you encountered. While it's generally possible that a PlanState is referenced more than once (e.g. multiple CteScanStates reference the same CteScanState::cteplanstate), it's weird that the same PlanState references another PlanState more than once. Beyond that, I'm wondering how much other code is broken or can break if we don't change this structurally in the graph. You found already another one in ExecShutdownNode(). A simple alternative fix would to ensure inExecInitSubPlanExpr() that the subPlan list is always unique. > With the attached patch, the instrumentation now shows correctly, and > the added regression test no longer fails with an assertion failure. > > ... > SubPlan any_1 > -> Sort (actual rows=1000.00 loops=4) > Sort Key: o.f > Worker 0: Sort Method: quicksort Memory: 48kB > Worker 1: Sort Method: quicksort Memory: 48kB > -> Seq Scan on other o (actual > rows=1000.00 loops=4) > > We could alternatively deal with this in the existing walkers, but > then we'd have to modify the walker context in four different places > to carry the identities of already visited plan states. Really? Can't you check based on state you have already decide if it's already initialized and if so bail? For example in ExecSortInitializeDSM(): if (node->shared_info != NULL) return; > I also assume > that changing how ExecInitHashJoin works is off the table, but I admit > that logic goes a bit over my head. It's these two calls that cause the sort PlanState to be added twice: hjstate->hj_OuterHash = ExecBuildHash32Expr(hjstate->js.ps.ps_ResultTupleDesc, hjstate->js.ps.resultops, outer_hashfuncid, node->hashcollations, node->hashkeys, hash_strict, &hjstate->js.ps, 0); and hjstate->hashclauses = ExecInitQual(node->hashclauses, (PlanState *) hjstate); Both, the hash clauses and the qual, reference the sub-plan. How would you avoid this? Also, it's too easy for other plan nodes to do the same. Hence, i think it's better to fix it structurally. > Note that technically the ExecShutdownNode_walker suffers from the > same problem, but it doesn't appear to be a practical problem with > in-core code today, and it's a bit more complicated to fix with the > current nested InstrStartNode assumptions (that I intend to fix > separately in the stack-based instrumentation series). Can you give some more details why it's not a problem in-core and what the problem with InstrStart/StopNode() is? Any node sitting below the Sort node, which doesn't like having its shutdown function called twice, might cause problems. Especially, CustomScanStates that we don't have full control over could be problematic. Seems to me that it would be good to fix it all in one go. But maybe I'm not seeing the complications clearly enough. -- David Geier