### 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/qa/server/src/main/java/org/elasticsearch/xpack/sql/qa/rest/BaseRestSqlTestCase.java
}
        public RequestObjectBuilder mode(Object m) {
            String modeString = m.toString();
            request.append(field(MODE_NAME, modeString));
            if (isQuery) {
                Mode mode = (m instanceof Mode) ? (Mode) m : Mode.fromString(modeString);
                if (Mode.isDedicatedClient(mode)) {
                    version(Version.CURRENT.toString());
                }
            }
            return this;
        }
        public RequestObjectBuilder fetchSize(Integer fetchSize) {
            request.append(field(FETCH_SIZE_NAME, fetchSize));
            return this;
        }
        public RequestObjectBuilder timeZone(String timeZone) {
            request.append(field(TIME_ZONE_NAME, timeZone));
            return this;
        }
        public RequestObjectBuilder catalog(String catalog) {
            request.append(field(CATALOG_NAME, catalog));
            return this;
        }
        public RequestObjectBuilder clientId(String clientId) {
            request.append(field(CLIENT_ID_NAME, clientId));
            return this;
        }
        public RequestObjectBuilder filter(String filter) {
            request.append(field(FILTER_NAME, filter));