### 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/RestSqlTestCase.java
Map<String, Object> asyncStatus = null;
                    long millis = 1;
                    for (boolean isRunning = true; isRunning; Thread.sleep(millis *= 2)) {
                        asyncStatus = toMap(client().performRequest(request), null);
                        isRunning = (boolean) asyncStatus.get(IS_RUNNING_NAME);
                    }
                    assertEquals(200, (int) asyncStatus.get("completion_status"));
                    assertEquals(asyncStatus.get(IS_RUNNING_NAME), asyncStatus.get(IS_PARTIAL_NAME));
                    assertEquals(asyncId, asyncStatus.get(ID_NAME));
                }
                
                Request request = new Request("GET", SQL_ASYNC_REST_ENDPOINT + asyncId);
                if (pollForCompletion == false) {
                    request.addParameter(WAIT_FOR_COMPLETION_TIMEOUT_NAME, "1d");
                }
                if (randomBoolean()) {
                    request.addParameter(URL_PARAM_FORMAT, format);
                    if (format.equals("csv")) {
                        csvDelimiter = ';';
                        request.addParameter(URL_PARAM_DELIMITER, URLEncoder.encode(String.valueOf(csvDelimiter), StandardCharsets.UTF_8));
                    }
                } else {
                    request.setOptions(request.getOptions().toBuilder().addHeader("Accept", acceptMap.get(format)));
                }
                response = client().performRequest(request);
                assertEquals(200, response.getStatusLine().getStatusCode());
                assertEquals(asyncId, response.getHeader(HEADER_NAME_ASYNC_ID));
                assertEquals("false", response.getHeader(HEADER_NAME_ASYNC_PARTIAL));
                assertEquals("false", response.getHeader(HEADER_NAME_ASYNC_RUNNING));
            }