Make postgresql and PostgreSQL engine work against a ClickHouse instance - #110760

ClickHouse does not pad `Decimal` values with trailing zeros on output
(`1.5000` is printed as `1.5`), so use a fractional part ending in a
non-zero digit to keep the expected string unambiguous. Verified against
a local server: the `postgresql()` self-connect reads the row back
byte-for-byte.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Address review: `convertDataTypeToPostgresColumnTypeSpec` advertised every
integer type wider than a signed 64-bit `bigint` (`UInt64`, `Int128`,
`UInt128`, `Int256`, `UInt256`) and any type it did not enumerate (including
`Nullable`/`LowCardinality`-wrapped ones) as `VARCHAR`, and `FieldDescription`
hardcoded the type modifier to -1. A `Decimal(p, s)` therefore lost its
precision and scale over the wire.

Now the wide/unsigned integer types and `Decimal` are advertised as `numeric`
(OID 1700) with a real `atttypmod` encoded exactly as PostgreSQL does
(`((precision << 16) | scale) + 4`), and `RowDescription` serializes that
modifier. `Nullable`/`LowCardinality` are unwrapped first. This mirrors the
table-name path in the emulated `pg_attribute` (see `PostgreSQLHandler`), so a
direct PostgreSQL client reading a ClickHouse instance sees the correct
per-column types.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Address review: reading a self-connected ClickHouse `Array(...)` column back
through `postgresql(...)`/the `PostgreSQL` engine needs the values in the
PostgreSQL array-literal spelling (`{...}`, nested `{{...}}`, `NULL` for a null
element), because the reading side parses them with `pqxx::array_parser`.
ClickHouse's `serializeText` encloses arrays in `[...]` instead, which the
parser rejects.

Add a shared `writePostgreSQLArrayText` helper (used by both the
`PostgreSQLWire` output format and, in a later commit, the COPY-to-STDOUT path
of `PostgreSQLHandler`) that renders an array cell as a PostgreSQL literal,
double-quoting every scalar element (the parser strips the quotes before the
per-element parser runs, so quoting numbers is harmless and quoting text is
required) and escaping `"` and `\`.

`format_type` now decodes the PostgreSQL array type OIDs (`_int4` etc.) to
`<element>[]`, so that schema inference in `fetchPostgreSQLTableStructure`
recovers the array type instead of `text`.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…rrays

Address three review findings in the emulated `pg_catalog`/handler:

- `isTransactionControlQuery` matched a `BEGIN`/`COMMIT`/... prefix on the whole
  simple-query string, so a multi-statement query such as
  `BEGIN READ ONLY; SELECT 1` was swallowed as a no-op and the trailing
  statement was silently dropped. It now returns false when an internal
  semicolon remains after trimming, so such input falls through to normal
  processing instead.

- Relation OIDs were a truncated hash (`cityHash64(...) % 1e9`), which collides
  at realistic catalog sizes; a collision merges the column sets of two tables
  because `pg_class.oid` and `pg_attribute.attrelid` are joined during schema
  inference. Introduce a shared, unfiltered `pg_class_oids` view that assigns a
  dense, unique OID per `(database, table)` via
  `row_number() OVER (ORDER BY database, name)`, and take both `pg_class.oid` and
  `pg_attribute.attrelid` from it, so they always line up and never collide.

- `Array(...)` columns fell through to OID 25 (`text`) with `attndims = 0`, so
  `postgresql(..., 'arr_table')` inferred `String`. `pg_attribute` now advertises
  the PostgreSQL array-type OID of the element with `attndims` = the array
  nesting depth, and the COPY-to-STDOUT path streams array values in PostgreSQL
  literal form via `writePostgreSQLArrayText` so they can be read back.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

…L self-connect

`test_array_type_roundtrip` reads `Array(Int32)`, `Array(String)`,
`Array(Array(Int32))` and `Array(UInt64)` back through `postgresql(...)` and
checks both the inferred types (arrays, not `String`) and the values, including
nested and empty arrays.

`test_wire_types_for_wide_and_decimal` connects a PostgreSQL client and asserts
the `RowDescription` type OIDs: `Int64` is `bigint` (20) while `UInt64`,
`Int128` and `Decimal` are `numeric` (1700), not `varchar`.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

The TabSeparated output format backslash-escapes single quotes inside
String values, so the Map column read back as String comes out as
{\'k\':[1,2]}, not {'k':[1,2]}. Verified against a local server.

Two fixes to the binary COPY path:

- The pre-rendering of `Array(...)` columns into PostgreSQL array
  literals (`{...}`) now applies only to the text COPY formats
  (TSV/CSV), where libpq/pqxx expect that spelling. The binary format
  keeps the original array columns, so it serializes real array values
  instead of text literals wrapped into `String`.

- `toString(ASTCopyQuery::Formats::Binary)` returned "Binary", which is
  not the name of any ClickHouse format, so `COPY ... TO STDOUT WITH
  FORMAT binary` always failed with `UNKNOWN_FORMAT` before even
  reaching the data. It now maps to `RowBinary` - the same format the
  `COPY ... FROM STDIN` path already used (its now-redundant local
  format switch is replaced with a `toString` call).

…ulated `pg_attribute`

`system.columns.numeric_precision` / `numeric_scale` are only populated
for top-level numeric columns, so an `Array(Decimal(p, s))` column fell
back to `atttypmod = -1`: `format_type` rendered a bare `numeric[]` and
schema inference on the reading side collapsed the element type to
`Decimal(38, 19)`. Parse the precision and scale out of the type name
itself (skipping only the leading `Nullable(`/`LowCardinality(`/`Array(`
wrappers, the same prefix used for the base type and `attndims`), so the
element type round-trips exactly. A `Decimal` buried in a `Map`/`Tuple`
argument list is not picked up - such columns are exposed as text as
before.

…rrays

The three cases the review asked to cover: a constant array expression
selected over the wire (exercises the `ColumnConst` path of the array
serializer), a self-connect round-trip of `Array(Decimal(p, s))`
preserving the element precision and scale, and a
`COPY ... TO STDOUT WITH FORMAT binary` query with an array column
(the payload stays `RowBinary`, not a PostgreSQL text literal).

The integration test `test_copy_to_stdout_binary_is_rejected` failed
consistently (not flaky): a `psycopg2` client issuing
`COPY (...) TO STDOUT WITH FORMAT BINARY` saw
`server closed the connection unexpectedly` instead of the expected
`binary COPY format is not supported` error.

Root cause: `PostgreSQLHandler::processCopyQuery` rejected binary `COPY`
by throwing. The throw propagated through `processQuery` (which sends an
`ErrorResponse` and re-throws) up to `run`, which caught it as a
`Poco::Exception` and returned, closing the connection right after the
`ErrorResponse` and before the `ReadyForQuery` that completes the command
cycle. A `libpq`/`psycopg2` client still reading the command result then
hit EOF while waiting for `ReadyForQuery`, discarded the partial error,
and reported a lost connection. The plain `psql` REPL happened to surface
the message due to timing, which is why manual testing missed it.

An unsupported `COPY` option is an ordinary query error, not a fatal
connection error. Reject binary `COPY` by sending an `ErrorResponse` and
returning (marking the query as handled) instead of throwing, so the run
loop follows with `ReadyForQuery` and the connection stays open - the
canonical PostgreSQL "query failed" flow, which every client surfaces
correctly.

Validated end-to-end against a local server with a real `psycopg2`
`copy_expert` client: binary `COPY` (upper- and lower-case) is now
rejected with the clear message, the connection remains usable
afterwards, and the text/CSV `COPY` and self-connect `postgresql()` read
paths are unchanged.

CI report: https://s3.amazonaws.com/clickhouse-test-reports/json.html?PR=110760&sha=9782b7a5a9554b439a16047efae662c035ef306d&name_0=PR&name_1=Integration%20tests%20%28amd_asan_ubsan%2C%20flaky%29

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

… pg_attribute

When `postgresql()` or the `PostgreSQL` engine self-connects to a ClickHouse
instance, the emulated `pg_attribute` reported `attnotnull = 't'` for a
top-level array whose element type is `Nullable(...)` (for example
`Array(Nullable(Int32))`), because the column type does not start with
`Nullable(`. Schema inference in `fetchPostgreSQLTableStructure` therefore
inferred `Array(Int32)`, and `insertPostgreSQLValue` rewrote every `NULL`
element to the element type's default - so `[1, NULL, 3]` came back as
`[1, 0, 3]`, silently corrupting results.

Detect the nullable element from the leading chain of
`Nullable(` / `LowCardinality(` / `Array(` wrappers instead of only the very
start of the type name: a `Nullable(` can only appear in that chain right
before the innermost scalar, so its presence marks the value (an array
element, or the scalar itself) as nullable. `Map`/`Tuple` columns, which are
exposed as text, are unaffected because they never start with one of those
wrappers.

Added an integration test `test_array_of_nullable_roundtrip` covering
`Array(Nullable(Int32))` and `Array(Nullable(String))`.

…ire path

A direct PostgreSQL client running a `SELECT` over ClickHouse's PostgreSQL
wire protocol received type OID `1043` (`varchar`) for `DateTime` and
`DateTime64` columns, because `convertDataTypeToPostgresColumnTypeSpec` fell
through to the `VARCHAR` default for those types. The table-name path already
advertises them as `timestamp` in the emulated `pg_attribute`, so the wire
`RowDescription` was inconsistent and clients could not treat the values as
timestamps.

Map `DateTime` and `DateTime64` to PostgreSQL `timestamp` (OID `1114`, added
as `ColumnType::TIMESTAMP`). The value is still rendered as text - ClickHouse's
`YYYY-MM-DD hh:mm:ss[.ffffff]` form is exactly PostgreSQL's timestamp text
format - so a client such as psycopg2 parses it into a native datetime.

Added an integration test `test_wire_types_for_datetime` asserting the wire
OID is `1114` and the values parse back correctly.

The stateless test only *grants* `SELECT` on `system.tables` and
`system.columns` - it never queries them - but the style check
`various_checks.sh` only greps for the table names and then demands a
`database = currentDatabase()` condition somewhere in the file. Grant
`system.*` instead and name the tables in a comment (comment lines are
exempt from that grep), which keeps the test's intent and clears the
check. A red style check drops every other CI job.