REST catalog: ClickHouse-written metadata files are never referenced by the catalog, so `DROP TABLE` with `data_lake_delete_data_on_drop = 1` leaves them behind and the table cannot be re-created with an explicit engine

[Selfeer]

Description

On an Iceberg REST catalog, ClickHouse writes a metadata JSON file of its own, metadata/vN-<uuid>.metadata.json, in two situations: when it creates a table with an explicit ENGINE = IcebergS3(...) inside the catalog database, and on every commit (INSERT, ALTER, TRUNCATE). In both cases it then asks the catalog to create or commit the table through the REST API. That API cannot take a client-written file: the catalog server writes its own 0000N-<uuid>.metadata.json and points the table at that. The file ClickHouse wrote is left in the table's metadata/ directory, referenced by nothing. On the explicit-engine path it even records a different table-uuid than the table the catalog registered.

PR #2305 makes this visible to users in two ways. DROP TABLE ... SETTINGS data_lake_delete_data_on_drop = 1 asks the catalog to purge; the server deletes everything its metadata tree references and leaves the ClickHouse files, so a purged table's directory is never empty. And the PR's new check in IcebergMetadata::createInitial sees those leftover *.metadata.json files, assumes they come from a keep-data drop, and refuses to create the table again with TABLE_ALREADY_EXISTS, even though the user purged.

We found it in the drop with purge and explicit engine create scenarios of the suite.

Steps to reproduce

Environment : 26.6.2.20001.altinityantalya (PR #2305 build), release; ice-rest-catalog 0.16.0; MinIO bucket warehouse; database lake created with the same CREATE DATABASE statement.

Part A: purge leaves ClickHouse's file behind

1. Create an engine-less table and insert one row.

CREATE TABLE lake.`ns1.t` (id Int64);
INSERT INTO lake.`ns1.t` VALUES (1) SETTINGS allow_insert_into_iceberg = 1;

2. List the metadata directory.

mc ls minio/warehouse/data/ns1/t/metadata/

Observed: three files. Two written by the catalog server, one by ClickHouse during the INSERT.

00000-8e1f2e64-b6cb-4335-ad79-6732d11dbc52.metadata.json   server, at CREATE
00001-c681bc1d-49f3-4e3e-a3d7-de8968dc0335.metadata.json   server, at the INSERT commit (catalog's metadata-location)
v1-dd8b961b-7389-4fc8-97ab-125abc7cb31e.metadata.json      ClickHouse, during the INSERT; referenced by nothing

The server log confirms who wrote the third one:

<Debug> IcebergStorageSink: Writing new metadata file s3://warehouse/data/ns1/t/metadata/v1-dd8b961b-....metadata.json
<Debug> IcebergStorageSink: Metadata file s3://warehouse/data/ns1/t/metadata/v1-dd8b961b-....metadata.json written

3. Drop the table with purge and list again.

DROP TABLE lake.`ns1.t` SETTINGS data_lake_delete_data_on_drop = 1;

mc ls minio/warehouse/data/ns1/t/metadata/

Expected: the directory is empty (six objects before the drop, zero after).

Actual: the catalog entry is gone and the server log says Dropped table ns1.t from DataLakeCatalog (purge=true), but one object remains:

v1-dd8b961b-7389-4fc8-97ab-125abc7cb31e.metadata.json

Part B: the leftover blocks re-creation

4. Re-create the same table with an explicit engine at its original location.

CREATE TABLE lake.`ns1.t` (id Int64)
ENGINE = IcebergS3('http://minio:9000/warehouse/data/ns1/t/', 'admin', 'password');

Expected: the table is created; the location was purged in step 3.

Actual:

Code: 57. DB::Exception: The catalog has no table ns1.t registered, but Iceberg metadata files are already present at data/ns1/t/, so creating the table there would clash with them. This is usually left behind by a previous `DROP TABLE` without `data_lake_delete_data_on_drop`, which keeps the data and metadata in place: remove the leftover files, or create the table at a different location. (TABLE_ALREADY_EXISTS)

Part C: the explicit-engine create itself leaves an orphan

5. Create a table with an explicit engine in a fresh namespace and list its metadata directory.

CREATE TABLE lake.`ns2.t` (id Int64)
ENGINE = IcebergS3('http://minio:9000/warehouse/data/ns2/t/', 'admin', 'password');

mc ls minio/warehouse/data/ns2/t/metadata/

Expected: one initial metadata file, the one the catalog points at.

Actual: two files. The catalog points at the server's; ClickHouse's carries a different table-uuid.

v1-3697eb89-f2cd-4ae0-8fba-69bdf64b6c4d.metadata.json      ClickHouse (createInitial), table-uuid 3697eb89-...
00000-e60f0d6a-eacf-42af-a9aa-c3e1024e0c13.metadata.json   catalog server, its own table-uuid; this is the catalog's metadata-location

Related to: #2305
Build used: https://altinity-build-artifacts.s3.amazonaws.com/PRs/2305/6ec36d21729a28815041df233317bd454908c5a0/build_amd_release/clickhouse-common-static_26.6.2.20001.altinityantalya_amd64.deb