### 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
private final String[] indices;
    private final SearchSourceBuilder nextQuery;
    private final List<BucketExtractor> extractors;
    private final BitSet mask;
    private final int limit;
    private final boolean includeFrozen;
    CompositeAggCursor(
        SearchSourceBuilder nextQuery,
        List<BucketExtractor> exts,
        BitSet mask,
        int remainingLimit,
        boolean includeFrozen,
        String... indices
    ) {
        this.indices = indices;
        this.nextQuery = nextQuery;
        this.extractors = exts;
        this.mask = mask;
        this.limit = remainingLimit;
        this.includeFrozen = includeFrozen;
    }
    public CompositeAggCursor(StreamInput in) throws IOException {
        indices = in.readStringArray();
        nextQuery = new SearchSourceBuilder(in);
        limit = in.readVInt();
        extractors = in.readNamedWriteableList(BucketExtractor.class);
        mask = BitSet.valueOf(in.readByteArray());
        includeFrozen = in.readBoolean();
    }
    @Override