How to enable statement caching?

[imraan-go]

This is not a issue but more of a question, how to utilize the pgx statement caching mechanism with pgxpool ? Because all SQL statements are predefined, it will be very efficient to cache all available queries on start of new connection.
There are some details here: jackc/pgx#791

Any idea how to utilize it with pggen?

[jschaf]

This is not a issue but more of a question, how to utilize the pgx statement caching mechanism with pgxpool.

I think it's the same way you would enable statement caching without pggen. The client controls the pool, not pggen, so the client can use AfterConnect like the linked issue suggests.

Because all SQL statements are predefined, it will be very efficient to cache all available queries on start of new connection.

Definitely, but I think that decision should be left up to the client. pggen should never connect to postgres unless explicitly requested.

One thing I'd be willing to entertain is to generate a PrepareAll function that takes a genericConn and prepares every statement in a querier. That way, the client still controls the connection.

[imraan-go]

Definitely, but I think that decision should be left up to the client. pggen should never connect to postgres unless explicitly requested.

One thing I'd be willing to entertain is to generate a PrepareAll function that takes a genericConn and prepares every statement in a querier. That way, the client still controls the connection.

Yes, thats what exactly what I was trying to say. Automatically generate a function that we can hook up to "AfterConnect" to prepare all statements on the new connection.

[jschaf]

Here's a draft of the code I think pggen should generate. Does this work for your use-case?

// preparer is any Postgres connection transport that provides a way to prepare
// a statement, most commonly *pgx.Conn.
type preparer interface {
	Prepare(ctx context.Context, name, sql string) (sd *pgconn.StatementDescription, err error)
}

// PrepareAllQueries executes a PREPARE statement for all pggen generated SQL
// queries in the querier files. Typical usage is for the pgxpool.Config 
// AfterConnect callback.
// 
// pgx will use the prepared statement if available. Calling this is an optional
// optimization to avoid a network round-trip the first time pgx runs a query if
// pgx statement caching is enabled.
func PrepareAllQueries(ctx context.Context, p preparer) error {
	_, err := p.Prepare(ctx, customTypesSQL, customTypesSQL)
	if err != nil {
		return fmt.Errorf("prepare all queries 'customTypes': %w", err)
	}

	_, err = p.Prepare(ctx, customMyIntSQL, customMyIntSQL)
	if err != nil {
		return fmt.Errorf("prepare all queries 'customMyInt': %w", err)
	}
	
	return nil
}

[jschaf]

I implemented the sketch above. Happy to revisit as needed.

[imraan-go]

Thanks for your work. I'll check it tonight.