Subject: sublink [exists (select xxx group by grouping sets ())] causes an assertion error

From: "赵庭海(庭章)" <[email-masked]>
Date: Fri, 22 Mar 2024 16:54:49 +0800

Hello postgres hackers:
I recently notice these sql can lead to a assertion error in pg14 and older version. Here is an example:
postgres=> CREATE TABLE t1 (a int); CREATE TABLE postgres=> INSERT INTO t1 VALUES (1); INSERT 0 1 postgres=> SELECT EXISTS ( SELECT * FROM t1 GROUP BY GROUPING SETS ((a), generate_series (1, 262144)) ) AS result; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.Here is breaktrace (release v14.11)
#0 0x00007fdc4d58d387 in raise () from /lib64/libc.so.6 #1 0x00007fdc4d58ea78 in abort () from /lib64/libc.so.6 #2 0x00000000009479aa in ExceptionalCondition (conditionName=conditionName@entry=0xb27bf0 "!lt->writing || lt->buffer == NULL", errorType=errorType@entry=0x99a00b "FailedAssertion", fileName=fileName@entry=0xb27989 "logtape.c", lineNumber=lineNumber@en try=1279) at assert.c:69 #3 0x000000000097715b in LogicalTapeSetBlocks (lts=<optimized out>) at logtape.c:1279 #4 0x00000000009793ab in tuplesort_free (state=0x1ee30e0) at tuplesort.c:1402 #5 0x000000000097f2b9 in tuplesort_end (state=0x1ee30e0) at tuplesort.c:1468 #6 0x00000000006cd3a1 in ExecEndAgg (node=0x1ecf2c8) at nodeAgg.c:4401 #7 0x00000000006be3b1 in ExecEndNode (node=<optimized out>) at execProcnode.c:733 #8 0x00000000006b8514 in ExecEndPlan (estate=0x1ecf050, planstate=<optimized out>) at execMain.c:1416 #9 standard_ExecutorEnd (queryDesc=0x1e18af0) at execMain.c:493 #10 0x0000000000673779 in PortalCleanup (portal=<optimized out>) at portalcmds.c:299 #11 0x0000000000972b34 in PortalDrop (portal=0x1e586c0, isTopCommit=<optimized out>) at portalmem.c:502 #12 0x0000000000834140 in exec_simple_query (query_string=0x1df5020 "SELECT\nEXISTS (\nSELECT\n* FROM t1\nGROUP BY\nGROUPING SETS ((a), generate_series (1, 26214400))\n) AS result;") at postgres.c:1223 #13 0x0000000000835e37 in PostgresMain (argc=argc@entry=1, argv=argv@entry=0x7fff746c4470, dbname=<optimized out>, username=<optimized out>) at postgres.c:4513 #14 0x00000000007acdcb in BackendRun (port=<optimized out>, port=<optimized out>) at postmaster.c:4540 #15 BackendStartup (port=<optimized out>) at postmaster.c:4262 #16 ServerLoop () at postmaster.c:1748 #17 0x00000000007adc45 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x1defb50) at postmaster.c:1420 #18 0x000000000050a544 in main (argc=3, argv=0x1defb50) at main.c:209
The reason could be that, there are mutiple phase in grouping sets in this exists sublink. In executing phase, once function ExecAgg return a valid tupleslot, ExecSubPlan won't call exector_node repeatedly, ineed there is no needed. It causes unexpected status in sort_out and sort_in, so they don't pass assertion checking in ExecEndAgg.
I haven’t thought of a good solution yet. Only in my opinion, it is unreasonable to add processing for other specific types of execution nodes in function ExecSubplan, but there is no way to know in ExecAgg whether it is executed again.
Best regards,
Tinghai Zhao