### 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> expected = new HashMap<>();
        expected.put(IS_PARTIAL_NAME, false);
        expected.put(IS_RUNNING_NAME, false);
        expected.put(COLUMNS_NAME, singletonList(columnInfo(mode, "1", "integer", JDBCType.INTEGER, 11)));
        expected.put(ROWS_NAME, singletonList(singletonList(1)));
        assertAsyncResponse(expected, runSql(builder, mode));
    }
    public void testAsyncTextWait() throws IOException {
        RequestObjectBuilder builder = query("SELECT 1").waitForCompletionTimeout("1d").keepOnCompletion(false);
        Map<String, String> contentMap = new HashMap<>() {
            {
                put("txt", "       1       \n---------------\n1              \n");
                put("csv", "1\r\n1\r\n");
                put("tsv", "1\n1\n");
            }
        };
        for (String format : contentMap.keySet()) {
            Response response = runSqlAsTextWithFormat(builder, format);
            assertEquals(contentMap.get(format), responseBody(response));
            assertTrue(hasText(response.getHeader(HEADER_NAME_ASYNC_ID)));
            assertEquals("false", response.getHeader(HEADER_NAME_ASYNC_PARTIAL));
            assertEquals("false", response.getHeader(HEADER_NAME_ASYNC_RUNNING));
        }
    }
    @AwaitsFix(bugUrl = "https:github.com/elastic/elasticsearch/issues/80089")
    public void testAsyncTextPaginated() throws IOException, InterruptedException {
        final Map<String, String> acceptMap = new HashMap<>() {
            {
                put("txt", "text/plain");
                put("csv", "text/csv");