### 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
sourceBuilder,
            cfg.requestTimeout(),
            query.shouldIncludeFrozen(),
            Strings.commaDelimitedListToStringArray(index)
        );
        @SuppressWarnings("rawtypes")
        List<Tuple<Integer, Comparator>> sortingColumns = query.sortingColumns();
        listener = sortingColumns.isEmpty() ? listener : new LocalAggregationSorterListener(listener, sortingColumns, query.limit());
        if (cfg.task() != null && cfg.task().isCancelled()) {
            listener.onFailure(new TaskCancelledException("cancelled"));
        } else if (query.isAggsOnly()) {
            ActionListener<SearchResponse> l;
            if (query.aggs().useImplicitGroupBy()) {
                l = new ImplicitGroupActionListener(listener, client, cfg, output, query, search);
            } else {
                l = new CompositeActionListener(listener, client, cfg, output, query, search);
            }
            client.search(search, l);
        } else {
            searchWithPointInTime(search, new SearchHitActionListener(listener, client, cfg, output, query, sourceBuilder));
        }
    }
    private void searchWithPointInTime(SearchRequest search, ActionListener<SearchResponse> listener) {
        final OpenPointInTimeRequest openPitRequest = new OpenPointInTimeRequest(search.indices()).indicesOptions(search.indicesOptions())
            .keepAlive(cfg.pageTimeout());
        client.execute(OpenPointInTimeAction.INSTANCE, openPitRequest, wrap(openPointInTimeResponse -> {
            String pitId = openPointInTimeResponse.getPointInTimeId();
            search.indices(Strings.EMPTY_ARRAY);
            search.source().pointInTimeBuilder(new PointInTimeBuilder(pitId));
            ActionListener<SearchResponse> closePitOnErrorListener = wrap(searchResponse -> {