### 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/SearchHitCursor.java
return () -> new SearchHitRowSet(extractors, mask, nextQuery.size(), limit, response);
    }
    static void handle(
        Client client,
        SearchResponse response,
        SearchSourceBuilder source,
        Supplier<SearchHitRowSet> makeRowSet,
        ActionListener<Page> listener,
        boolean includeFrozen
    ) {
        if (log.isTraceEnabled()) {
            logSearchResponse(response, log);
        }
        SearchHit[] hits = response.getHits().getHits();
        SearchHitRowSet rowSet = makeRowSet.get();
        if (rowSet.hasRemaining() == false) {
            closePointInTime(
                client,
                response.pointInTimeId(),
                ActionListener.wrap(r -> listener.onResponse(Page.last(rowSet)), listener::onFailure)
            );
        } else {
            source.pointInTimeBuilder(new PointInTimeBuilder(response.pointInTimeId()));
            updateSearchAfter(hits, source);
            SearchHitCursor nextCursor = new SearchHitCursor(
                source,
                rowSet.extractors(),
                rowSet.mask(),
                rowSet.getRemainingLimit(),
                includeFrozen