ClickHouse / ClickHouse/clickhousectl
Align Postgres slow-query duration schemas with fractional responses
- Dominant language
- Rust
- Stars
- 74
- Forks
- 5
- Avg merge
- 2d 11h
- Merged PRs (30d)
- 196
Description
# OpenAPI declares Postgres slow-query aggregate durations as integers but the API returns fractions
**Blocked on:** Cloud API/OpenAPI owners confirming the aggregate numeric contract and correcting the response schemas. The client tolerance fix in #758 can proceed independently.
## Problem
The deployed Postgres slow-query list and detail endpoints return fractional microsecond aggregate values. The current ClickHouse Cloud OpenAPI declares those fields as integers, so strict generated clients can reject successful HTTP 200 responses unless they add out-of-contract tolerance.
This is the upstream schema remainder from ClickHousectl [#758](https://github.com/ClickHouse/clickhousectl/issues/758). The CLI stack now preserves fractional values, but the public schema captured after that fix remains incompatible with live responses.
Snapshot under review: OpenAPI 3.1.2 retrieved 2026-09-08T19:50:35Z, SHA-256 `c4811d18b26701aa61794b1d6ea1fa1b375493776bdac2c2033383849ba047d9`. The repository snapshot at commit `4c17bc55d89108a0a73579b0655baf8189d97207` has the same SHA.
## Affected operations and schema
- `GET /v1/organizations/{organizationId}/postgres/{postgresId}/slowQueryPatterns` — `slowQueryPatternsGetList` ([operation](https://github.com/ClickHouse/clickhousectl/blob/4c17bc55d89108a0a73579b0655baf8189d97207/crates/clickhouse-cloud-api/clickhouse_cloud_openapi.json#L14699-L14718))
- `GET /v1/organizations/{organizationId}/postgres/{postgresId}/slowQueryPatterns/{queryId}` — `slowQueryPatternGet` ([operation](https://github.com/ClickHouse/clickhousectl/blob/4c17bc55d89108a0a73579b0655baf8189d97207/crates/clickhouse-cloud-api/clickhouse_cloud_openapi.json#L14929-L14948)); its `aggregate` reuses `PostgresSlowQueryPattern` ([schema ref](https://github.com/ClickHouse/clickhousectl/blob/4c17bc55d89108a0a73579b0655baf8189d97207/crates/clickhouse-cloud-api/clickhouse_cloud_openapi.json#L33481-L33490)).
The exact observed pointers are:
- `#/components/schemas/PostgresSlowQueryPattern/properties/avgDurationUs`
- `#/components/schemas/PostgresSlowQueryPattern/properties/p95DurationUs`
- `#/components/schemas/PostgresSlowQueryPattern/properties/p99DurationUs`
All remain `type: integer` in the latest snapshot ([duration fields](https://github.com/ClickHouse/clickhousectl/blob/4c17bc55d89108a0a73579b0655baf8189d97207/crates/clickhouse-cloud-api/clickhouse_cloud_openapi.json#L33197-L33220)).
## Direct HTTPS evidence
A fresh read-only direct HTTPS capture on 2026-09-08 at approximately 19:53Z returned HTTP 200. Query text and all service, organization, database, user, application, and query identifiers were excluded from the evidence.
The first list result contained:
```json
{
"avgDurationUs": 1210.2717391304348,
"p95DurationUs": 1220.7,
"p99DurationUs": 1859.259999999998
}
```
The detail result's `aggregate` contained:
```json
{
"avgDurationUs": 1207.566758747698,
"p99DurationUs": 2439.639999999985
}
```
In those responses, `totalDurationUs`, `maxDurationUs`, and `p50DurationUs` happened to be integral. That does not prove they are always integral, but this issue should not assert that they are fractional without evidence.
## Reproduction
The following read-only list request emits only numeric duration fields with a fractional part; it does not print SQL or identifiers. Use a window known to contain slow-query patterns, with exact-millisecond UTC timestamps accepted by the current endpoint.
```bash
export CLICKHOUSE_CLOUD_API='https://api.clickhouse.cloud/v1'
export CLICKHOUSE_CLOUD_API_KEY_ID='...'
export CLICKHOUSE_CLOUD_API_KEY_SECRET='...'
export ORG_ID='...'
export POSTGRES_ID='...'
export FROM_DATE='2026-09-08T16:30:00.000Z'
export TO_DATE='2026-09-08T17:35:00.000Z'
curl --fail-with-body --silent --show-error \
--user "$CLICKHOUSE_CLOUD_API_KEY_ID:$CLICKHOUSE_CLOUD_API_KEY_SECRET" \
--get \
--data-urlencode "from_date=$FROM_DATE" \
--data-urlencode "to_date=$TO_DATE" \
--data-urlencode 'limit=1' \
"$CLICKHOUSE_CLOUD_API/organizations/$ORG_ID/postgres/$POSTGRES_ID/slowQueryPatterns" \
| jq '{status,fractions:[.result[0] | to_entries[] | select(.key|test("DurationUs$")) | select((.value|type)=="number") | select(.value != (.value|floor)) | {field:.key,value:.value}]}'
```
The detail endpoint can be checked without printing identifying fields by supplying the required lookup values as environment variables and filtering to `aggregate`:
```bash
export QUERY_ID='...'
export DB_NAME='...'
export DB_USER='...'
export DB_OPERATION='SELECT'
curl --fail-with-body --silent --show-error \
--user "$CLICKHOUSE_CLOUD_API_KEY_ID:$CLICKHOUSE_CLOUD_API_KEY_SECRET" \
--get \
--data-urlencode "db_name=$DB_NAME" \
--data-urlencode "db_user=$DB_USER" \
--data-urlencode "db_operation=$DB_OPERATION" \
"$CLICKHOUSE_CLOUD_API/organizations/$ORG_ID/postgres/$POSTGRES_ID/slowQueryPatterns/$QUERY_ID" \
| jq '{status,fractions:[.result.aggregate | to_entries[] | select(.key|test("DurationUs$")) | select((.value|type)=="number") | select(.value != (.value|floor)) | {field:.key,value:.value}]}'
```
Both requests are read-only; no cleanup is required.
## Expected resolution
- Change the confirmed fractional aggregate fields `avgDurationUs`, `p95DurationUs`, and `p99DurationUs` from `integer` to `number`.
- Confirm the calculation and serialization contract for `p50DurationUs`; update it too if fractional percentiles are possible.
- Keep `totalDurationUs` and `maxDurationUs` integral if the service guarantees that contract.
- Add representative fractional list and detail examples so generated-client tests exercise the deployed values.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start in crates/clickhouse-cloud-api/clickhouse_cloud_openapi.json, reading the slowQueryPatternsGetList and slowQueryPatternGet operations and the PostgresSlowQueryPattern schema. Confirm the aggregate numeric contract with the Cloud API/OpenAPI owners, then update the confirmed schemas and add fractional list and detail examples so generated-client tests cover deployed responses.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- openapi, rust
- Domain
- api, backend-api-design
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100