github ClickHouse/pg_clickhouse v0.10.0
Release v0.10.0
on GitHub

6 hours ago

⚡ Improvements

  • Added the clickhouse_server_version(server) function, which reports the ClickHouse server version ("major.minor.patch") for a foreign server (#293).
  • Added pushdown for subqueries the planner cannot flatten into joins (SubPlans): correlated and uncorrelated scalar aggregate subqueries, EXISTS, and single-column equality IN/NOT IN, in WHERE and HAVING. Covers TPC-H Q2, Q11, Q15, Q17, Q20, and Q22 shapes. NOT IN preserves PostgreSQL's NULL semantics under ClickHouse's two-valued IN, deparsing with compensating guards when the columns involved are nullable (#315). Requires ClickHouse 25.8 or later; older servers still evaluate the subquery locally (#289).
  • Added transform_null_in 0 to the default value of the pg_clickhouse.session_settings parameter, so that a ClickHouse server profile cannot silently change the IN semantics the pushdown rules rely on (#315).
  • Added or corrected pushdown for IN-family operations on arrays. Previous behavior was missing or incorrect, either structurally or due to behavioral differences in the ClickHouse implementation of the operation. Corrected the structurally incorrect pushdown (#315) and handled ClickHouse behavioral changes using special case statements where needed (#317).
  • Added pushdown for more aggregate functions (#290):
  • Mapped the following PostgreSQL ordered set aggregate functions to push down to corresponding ClickHouse parametric aggregate functions (#291):
    • percentile_cont(double[]) → quantiles()
    • percentile_disc(double) → quantileExactLow()
    • percentile_disc(double[]) → quantilesExactLow()
  • Added pushdown for encode(bytea, fmt) with a constant format:
    • encode(bytea, 'hex') → lower(hex())
    • encode(bytea, 'base64') → base64Encode() wrapped to reproduce PostgreSQL's MIME line break every 76 characters.
    • encode(bytea, 'base64url') (PostgreSQL 19+) → base64URLEncode().
  • Extended interval arithmetic pushdown to date and timestamp operands and to interval subtraction, beyond the previous timestamptz + interval. Deparses to ClickHouse INTERVAL <n> <unit> terms, one per nonzero month, day, and second component, replacing nested addMonths/addDays/addSeconds calls that mishandled day arithmetic on dates and drifted across DST boundaries (#301).
  • The binary driver now flushes an insert block once it buffers 64MiB, bounding memory for large COPY FROM and INSERT SELECT commands (#303).
  • The binary driver now supports inserting into Array(Nullable(T)) columns (#316).
  • Added pushdown for the three-argument forms of ltrim, rtrim, and btrim (#307).
  • Added clickhouse_query(server, sql), a set-returning function that runs a query against a configured foreign server and returns its rows typed by the caller's column definition list (#309).
  • Added clickhouse_perform(server, sql), a procedure that runs a statement against a configured foreign server and discards any result, for statements such as DDL that return no results (#329).
  • Deprecated clickhouse_raw_query(), which will be removed in next release. Use clickhouse_query(server, sql) or CALL clickhouse_perform(server, sql), which use a foreign server rather than a connection string (#329).
  • Any ClickHouse column now reads into text or another string type, rendered by the output function of the PostgreSQL type it maps to, rather than failing with could not cast value (#329).
  • Added pushdown support for partial aggregates under partition-wise aggregation, so a query over a partitioned table mixing local and foreign partitions computes the foreign partition's aggregate on ClickHouse instead of fetching its rows. Covers decomposable aggregates (count, sum, min, max, bool_and/bool_or, bit_and/bit_or), plus avg over integers and avg/var_pop/var_samp/stddev_pop/stddev_samp over floats. Aggregates with an internal transition state (anything over numeric, avg(bigint), avg(interval)) still fall back to local aggregation. Requires enable_partitionwise_aggregate (#298).
  • Disabled remote MIN/MAX optimization by raising MinMaxAggPath cost rather than understating foreign scan cost (#310).
  • Added mapping to push down the re2 v0.4 @~ operator to ClickHouse as the match() function (#318).
  • Binary driver errors now name the column and type involved, for example cannot encode integer into ClickHouse Array(Array(Int32)) (column "c2") in place of unexpected PG/CH type pair for column 0 (#326).
  • Improved array element coercion in the binary driver. Converting Array(Int32) to bigint[], or quantilesExactLow() results into double precision[] no longer fails with could not cast value from integer[] to bigint[] (#326).
  • Added support for the third argument to array_position() when it's a positive constant value, pushing it down to a call to arraySlice() to start the search from the specified index. Thanks to Minh Vu for the PR (#334)!
  • The HTTP driver now uses ClickHouse's Native format, sharing encoding and decoding with binary driver. Only the deprecated clickhouse_raw_query() function retains TabSeparated behavior; it will likely be removed in favor of clickhouse_query() and clickhouse_perform() in a future release. (#328)
  • Deprecated the fetch_size setting, now ignored. With the switch to binary encoding, the HTTP driver always streams results the same as the binary driver. Setting fetch_size triggers a warning and will be removed in a future release. (#328)

🐞 Bug Fixes

  • Fixed crashes when a single query ran more than one foreign scan on the binary (native-protocol) driver at once, such as a correlated subquery or a nested-loop join over foreign tables, colliding in the single connection. Each concurrently active scan now gets its own connection (#296).
  • Fixed a use-after-free of a foreign scan's batch memory context on rescan that could corrupt memory and hang (#296).
  • Fixed subsecond precision lost inserting timestamps over HTTP (#300).
  • Fixed the binary driver to use pg_clickhouse.session_settings with inserts.
  • Fixed undefined behavior: out-of-bounds reads in key/value iteration, and the binary driver's simple query path; CollapsingMergeTree validation; missing rejection of non-Const/null units in date_trunc/date_part pushdown; NULL handling in record conversion; unchecked curl_easy_escape failures; and unbounded DateTime64 scale lookups (#313).
  • Fixed incorrect results from pushed-down = ANY/<> ALL expressions and IN expressions over constant lists and when a NULL could reach the comparison, compensating for ClickHouse evaluating IN under two-valued logic vs. PostgreSQL three-value NULL logic (#315, #317).
  • Fixed <> ANY(array) deparsing to ClickHouse SQL that computes <> ALL, which was incorrect even with no NULLs involved: In Postgres, 1 <> ANY('{1,5}') is TRUE. For now, do not push down (#315).
  • Fixed the pushdown of a CASE arg WHEN .. expression without checking its branches when the tested expression was itself unshippable (#315).
  • Fixed an error that incorrectly selected an invalid relation OID when selecting the user to execute the remote query. Thanks to Kostia R for the PR (#319)!
  • Fixed the omission of typmod behavior in the binary driver causing incorrect values. For example, padding will now be preserved in char(x) columns (#330).
  • Fixed date_part('dow', ...) and EXTRACT(DOW FROM ...) pushdown to use PostgreSQL's Sunday (0) through Saturday (6) numbering, rather than ClickHouse's default Monday through Sunday scheme. Thanks to Minh Vu for the PR (#331)!
  • Fixed the encoding of bytea array elements to use the same ClickHouse binary-literal serializer previously for scalar values, preventing PostgreSQL IN lists from producing invalid or mismatched FixedString comparisons. Thanks to @jxom for the PR (#333)!
  • Fixed array_position() pushdown to return NULL, rather than zero, when ClickHouse does not find an element. Thanks to Minh Vu for the PR (#334)!
  • Fixed array_length() pushdown to preserve empty-array and requested dimension semantics. Thanks to Minh Vu for the PR (#336)!
  • Taught json_extract_path*() and jsonb_extract_path*() not to push down when the path array contains any NULL elements. Thanks to Minh Vu for the PR (#335)!
  • Fixed loss of subsecond precision in to_timestamp(float8) by mapping it to the ClickHouse toDateTime64() function instead of fromUnixTimestamp(). Thanks to Minh Vu for the PR (#338).

📚 Documentation

  • Separated the list of custom ordered set aggregate functions provided by pg_clickhouse (currently quantile() and quantileExact()) from the Postgres ordered set aggregate functions, putting them under their own header, "Custom Ordered Set Aggregates".
  • Fixed the links to the ClickHouse docs for the functions to which ltrim and rtrim push down.
  • Documented that minimum required version of ClickHouse is 23.3, not 23.0.
  • Added a section on managing mixed local and foreign partitions under the new "Partitioned Tables" header, as well as the example script doc/offload-partition.sql to demonstrate migrating data from a Postgres table to a ClickHouse foreign table #298.
  • Updated discussion of COPY to document its efficient streaming behavior via the same code path as INSERT.
  • Noted the need to preserve certain settings in the pg_clickhouse.session_settings GUC when overriding the default value.
  • Documented that columns in Postgres that map to unexpected types in ClickHouse are not converted to text and passed to the input function for the Postgres type.
  • Added "IN and NULL Semantics" section.
Check out latest releases or
releases around ClickHouse/pg_clickhouse v0.10.0

Don't miss a new pg_clickhouse release

NewReleases is sending notifications on new releases.

Get notifications