### Description

Only "search hits" based queries (no aggregations) provide a consistent view on the data when paginating through the result set. With point-in-time, it is possible to support transactional semantics for aggregation queries as well.

This issue is somewhat blocked by https://github.com/elastic/elasticsearch/issues/75528 because a PIT can not immediately be closed if the aggregation result set contains less than `fetch_size` records. This could cause some increased resource utilization due to open PITs. Especially if the client does not close the SQL cursor.
x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/execution/search/CompositeAggCursor.java
@Override
    public void nextPage(SqlConfiguration cfg, Client client, ActionListener<Page> listener) {
        if (log.isTraceEnabled()) {
            log.trace("About to execute composite query {} on {}", StringUtils.toString(nextQuery), indices);
        }
        SearchRequest request = prepareRequest(nextQuery, cfg.requestTimeout(), includeFrozen, indices);
        client.search(request, new ActionListener.Delegating<>(listener) {
            @Override
            public void onResponse(SearchResponse response) {
                handle(
                    response,
                    request.source(),
                    makeRowSet(response),
                    makeCursor(),
                    () -> client.search(request, this),
                    delegate,
                    couldProducePartialPages(getCompositeBuilder(next()))
                );
            }
        });
    }
    protected Supplier<CompositeAggRowSet> makeRowSet(SearchResponse response) {
        CompositeAggregationBuilder aggregation = getCompositeBuilder(nextQuery);
        return () -> new CompositeAggRowSet(extractors, mask, response, aggregation.size(), limit, couldProducePartialPages(aggregation));
    }
    protected BiFunction<SearchSourceBuilder, CompositeAggRowSet, CompositeAggCursor> makeCursor() {
        return (q, r) -> new CompositeAggCursor(q, r.extractors(), r.mask(), r.remainingData(), includeFrozen, indices);
    }
    static void handle(
        SearchResponse response,