sql/parser: memory leak of parsed SQL comments

[mgartner]

We've discovered a memory leak in the parser that can retain memory for parsed SQL comments for as long as a session remains open.

Here's a reproduction that continually executes SQL statements that contain comments:

package main

import (
	"context"
	"fmt"
	"github.com/jackc/pgx/v4"
	rand2 "math/rand/v2"
	"os"
	"os/signal"
	"syscall"
)

const url = "postgresql://[email protected]:26257/defaultdb?sslmode=disable"

func main() {
	ctx := context.Background()

	ch := make(chan os.Signal, 1)
	signal.Notify(ch, syscall.SIGINT)

	conn, err := pgx.Connect(context.Background(), url)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(ctx)

LOOP:
	for i := 0; true; i++ {
		d := rand2.Int()
		q := fmt.Sprintf("SELECT /* This is a query for the integer: %d. */ %d", d, d)
		if i%1000 == 0 {
			select {
			case <-ch:
				fmt.Println("Stopping queries...")
				break LOOP
			default:
				fmt.Println(q)
			}
		}
		if _, err := conn.Exec(ctx, q); err != nil {
			fmt.Fprintf(os.Stderr, "error running query: %v\n", err)
			os.Exit(1)
		}
	}

	fmt.Println("Shutting down...")
}

Running this against v23.2.7 we can observe that memory continues to climb past 1GB after a couple of minutes:

After applying a simply patch to remove the leak, memory plateaus just above 600MB:

Jira issue: CRDB-40562

[blathers-crl]

Hi @mgartner, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[mgartner]

The best workaround is to regularly close and re-open connections, e.g., with a "max age" connection pool setting in the client.

[mgartner]

Some follow-up work: #127713