Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Calculate agglevelsup correctly when Aggref contains a CTE.

  1. BUG #19055: Server crash at ExecInterpExpr

    PG Bug reporting form <noreply@postgresql.org> — 2025-09-17T14:34:23Z

    The following bug has been logged on the website:

Bug reference:      19055
Logged by:          BugForge
Email address:      dllggyx@outlook.com
PostgreSQL version: 17.6
Operating system:   Ubuntu 20.04 x86-64, docker image postgres:17.6
Description:        

PoC:
SELECT FROM ( SELECT generate_series ( 1 , '31' ) x ) GROUP BY ( x ) WINDOW
w AS ( ORDER BY ( WITH x AS ( WITH x AS ( SELECT sum ( x ) ) SELECT DISTINCT
* FROM x ) ( SELECT ( count ( ( SELECT x FROM x ) ) ) ) ) )

docker log:
#0 0xbd810a (ExecInterpExpr+0x142a)
#1 0xce2c89 (ExecSubPlan+0x889)
#2 0xbdb3d6 (ExecInterpExpr+0x46f6)
#3 0xc4f44e (ExecAgg+0x114e)
#4 0xce1372 (ExecSort+0x8e2)
#5 0xd0739e (begin_partition+0x3fe)
#6 0xcf71f6 (ExecWindowAgg+0xbb6)
#7 0xc016ac (standard_ExecutorRun+0x59c)
#8 0x133404d (PortalRunSelect+0x2dd)
#9 0x133321d (PortalRun+0x51d)
#10 0x132f1de (exec_simple_query+0x146e)
#11 0x1328627 (PostgresMain+0x2c57)
#12 0x13192e4 (BackendMain+0xe4)
#13 0x10a26c3 (postmaster_child_launch+0x193)
#14 0x10adb91 (ServerLoop+0x4821)
#15 0x10a76ec (PostmasterMain+0x241c)
#16 0xd5c2b8 (main+0x458)
#17 0x7592b5dde083 (__libc_start_main+0xf3)
#18 0x4a9c6e (_start+0x2e)

  2. Re: BUG #19055: Server crash at ExecInterpExpr

    Vik Fearing <vik@postgresfriends.org> — 2025-09-17T17:57:14Z

    On 17/09/2025 16:34, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      19055
> Logged by:          BugForge
> Email address:      dllggyx@outlook.com
> PostgreSQL version: 17.6
> Operating system:   Ubuntu 20.04 x86-64, docker image postgres:17.6
> Description:
>
> PoC:
> SELECT FROM ( SELECT generate_series ( 1 , '31' ) x ) GROUP BY ( x ) WINDOW
> w AS ( ORDER BY ( WITH x AS ( WITH x AS ( SELECT sum ( x ) ) SELECT DISTINCT
> * FROM x ) ( SELECT ( count ( ( SELECT x FROM x ) ) ) ) ) )


This query seems to crash the server at least back to 16.  The only 
simplification of it that I could manage was to replace generate_series 
with SELECT 1.  I could also replace all of the x's with different names 
without effect.

-- 

Vik Fearing

  3. Re: BUG #19055: Server crash at ExecInterpExpr

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-09-17T18:02:09Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> PoC:
> SELECT FROM ( SELECT generate_series ( 1 , '31' ) x ) GROUP BY ( x ) WINDOW
> w AS ( ORDER BY ( WITH x AS ( WITH x AS ( SELECT sum ( x ) ) SELECT DISTINCT
> * FROM x ) ( SELECT ( count ( ( SELECT x FROM x ) ) ) ) ) )

Interesting example.  De-obfuscating a little bit, we have

SELECT 1 FROM ( SELECT generate_series ( 1 , '31' ) gs ) ss
  GROUP BY ( gs )
  WINDOW w AS ( ORDER BY (
    WITH x1 AS -- MATERIALIZED
      ( WITH x2 AS ( SELECT sum ( gs ) )
        SELECT DISTINCT * FROM x2 )
    SELECT ( count ( ( SELECT gs FROM x1 ) ) )
  ) );

If you stick in MATERIALIZED where I show, then instead of an
executor assertion failure you get
	ERROR:  could not find CTE "x1"
which is also what happens in branches pre-dating default inlining
of CTEs.

The problem appears to be that the count() aggregate is assigned the
wrong agglevelsup: it's labeled with agglevelsup = 1, implying that
it belongs to the outer query level (which is where its "gs" input
comes from).  Then when we try to pull it up to the outer level,
the contained reference to the x1 CTE becomes dangling --- the planner
can't find any x1 in that level.  Or, if we don't say MATERIALIZED,
the planner tries to inline x1 and just botches things entirely.
I suspect it's getting confused about which level "sum(gs)"
belongs to, but I didn't bother running down the details.

In any case, this is the parser's fault.  Because the count()
references x1, it should not be given an agglevelsup higher than
where x1 is.  The attached seems to fix it.  I need to think
of a test case with less extraneous crud, though...

Thanks for the report!

			regards, tom lane