### 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/internalClusterTest/java/org/elasticsearch/xpack/sql/action/SqlSearchPageTimeoutIT.java
assertTrue(response.hasCursor());
        assertEquals(0, getNumberOfSearchContexts());
        Thread.sleep(1000);
        
        response = new SqlQueryRequestBuilder(client(), SqlQueryAction.INSTANCE).cursor(response.cursor()).get();
        assertEquals(1, response.size());
    }
    private void setupTestIndex() {
        assertAcked(client().admin().indices().prepareCreate("test").get());
        client().prepareBulk()
            .add(new IndexRequest("test").id("1").source("field", "bar"))
            .add(new IndexRequest("test").id("2").source("field", "baz"))
            .setRefreshPolicy(WriteRequest.RefreshPolicy.IMMEDIATE)
            .get();
        ensureYellow("test");
    }
    private long getNumberOfSearchContexts() {
        return client().admin()
            .indices()
            .prepareStats("test")
            .clear()
            .setSearch(true)
            .get()
            .getIndex("test")
            .getTotal()
            .getSearch()
            .getOpenContexts();
    }
}