### 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
SearchSourceBuilder source,
        Supplier<CompositeAggRowSet> makeRowSet,
        BiFunction<SearchSourceBuilder, CompositeAggRowSet, CompositeAggCursor> makeCursor,
        Runnable retry,
        ActionListener<Page> listener,
        boolean couldProducePartialPages
    ) {
        if (log.isTraceEnabled()) {
            logSearchResponse(response, log);
        }
        
        if (couldProducePartialPages && shouldRetryDueToEmptyPage(response)) {
            updateCompositeAfterKey(response, source);
            retry.run();
            return;
        }
        CompositeAggRowSet rowSet = makeRowSet.get();
        Map<String, Object> afterKey = rowSet.afterKey();
        if (afterKey != null) {
            updateSourceAfterKey(afterKey, source);
        }
        Cursor next = rowSet.remainingData() == 0 ? Cursor.EMPTY : makeCursor.apply(source, rowSet);
        listener.onResponse(new Page(rowSet, next));
    }
    private static boolean shouldRetryDueToEmptyPage(SearchResponse response) {
        CompositeAggregation composite = getComposite(response);
        
        return composite.getBuckets().isEmpty() && composite.afterKey() != null && composite.afterKey().isEmpty() == false;
    }
    static CompositeAggregationBuilder getCompositeBuilder(SearchSourceBuilder source) {