### 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/Querier.java
class LocalAggregationSorterListener extends ActionListener.Delegating<Page, Page> {
        
        private final AggSortingQueue data;
        private final AtomicInteger counter = new AtomicInteger();
        private volatile Schema schema;
        
        private static final int MAXIMUM_SIZE = MultiBucketConsumerService.DEFAULT_MAX_BUCKETS;
        private final boolean noLimit;
        LocalAggregationSorterListener(ActionListener<Page> listener, List<Tuple<Integer, Comparator>> sortingColumns, int limit) {
            super(listener);
            int size = MAXIMUM_SIZE;
            if (limit < 0) {
                noLimit = true;
            } else {
                noLimit = false;
                if (limit > MAXIMUM_SIZE) {
                    throw new PlanningException("The maximum LIMIT for aggregate sorting is [{}], received [{}]", MAXIMUM_SIZE, limit);