### 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
AggregationBuilder aggregation = source.aggregations()
            .getAggregatorFactories()
            .stream()
            .filter(a -> Objects.equals(a.getName(), Aggs.ROOT_GROUP_NAME))
            .findFirst()
            .orElse(null);
        Check.isTrue(aggregation instanceof CompositeAggregationBuilder, "Unexpected aggregation builder " + aggregation);
        return (CompositeAggregationBuilder) aggregation;
    }
    static boolean couldProducePartialPages(CompositeAggregationBuilder aggregation) {
        for (var agg : aggregation.getPipelineAggregations()) {
            if (agg instanceof BucketSelectorPipelineAggregationBuilder) {
                return true;
            }
        }
        return false;
    }
    static CompositeAggregation getComposite(SearchResponse response) {
        Aggregation agg = response.getAggregations().get(Aggs.ROOT_GROUP_NAME);
        Check.isTrue(agg instanceof CompositeAggregation, "Unrecognized root group found; " + agg);
        return (CompositeAggregation) agg;
    }
    private static void updateCompositeAfterKey(SearchResponse r, SearchSourceBuilder search) {
        updateSourceAfterKey(getComposite(r).afterKey(), search);
    }
    private static void updateSourceAfterKey(Map<String, Object> afterKey, SearchSourceBuilder search) {
        AggregationBuilder aggBuilder = search.aggregations().getAggregatorFactories().iterator().next();
        
        if (aggBuilder instanceof CompositeAggregationBuilder comp) {
            comp.aggregateAfter(afterKey);