### 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
cursor = response.getHeader(HEADER_NAME_CURSOR);
            String body = responseBody(response);
            if (i == fetchCount) {
                assertNull(cursor);
                assertFalse(hasText(body));
            } else {
                String expected = expectedTextBody(format, fetchSize, i, csvDelimiter);
                assertEquals(expected, body);
                if (hasText(cursor) == false) { 
                    assertEquals(i, fetchCount - 1);
                    i++; 
                }
            }
            
            Request request = new Request("DELETE", SQL_ASYNC_DELETE_REST_ENDPOINT + asyncId);
            Map<String, Object> deleteStatus = toMap(client().performRequest(request), null);
            assertEquals(200, response.getStatusLine().getStatusCode());
            assertTrue((boolean) deleteStatus.get("acknowledged"));
        }
    }
    public void testCompressCursor() throws IOException {
        String doc = IntStream.range(0, 1000)
            .mapToObj(i -> String.format(Locale.ROOT, "\"field%d\": %d", i, i))
            .collect(Collectors.joining(","));
        index("{" + doc + "}");
        String mode = randomMode();
        Map<String, Object> resp = toMap(runSql(query("SHOW COLUMNS FROM " + indexPattern("test")).fetchSize(1).mode(mode)), mode);
        
        assertThat(resp.get("cursor").toString().length(), lessThan(5000));
    }