archiver register is not idempotent — fails with duplicate key on re-registration

[imtiazqa]

The archiver register command uses a plain SQL INSERT into coldfront.partition_config. If a table is already registered and the user attempts to re-register it with updated lifecycle values (e.g. changing hot-period or retention), the command fails with a unique constraint violation instead of updating the existing row. This makes it impossible to update registration parameters via the register command — users must use archiver set instead, which is not documented as the intended update path.

Test Data:

Table: public.orders
Initial registration: hot-period=1 month, retention=5 years
Re-registration: hot-period=2 months, retention=3 years

Steps to Reproduce:

Step 1 — Initial registration (succeeds)

./bin/archiver register --config cf.yaml
--table orders --schema public
--period monthly --hot-period "1 month" --retention "5 years"

Step 2 — Re-register with new values (fails)

./bin/archiver register --config cf.yaml
--table orders --schema public
--period monthly --hot-period "2 months" --retention "3 years"

Step 3 — Verify values unchanged

docker exec -e PGPASSWORD=coldfront coldfront-db-1 psql -U coldfront -d coldfront
-c "SELECT table_name, hot_period, retention_period FROM coldfront.partition_config WHERE table_name = 'orders';"

Expected Result:

Step 2: succeeds silently (upsert)

Step 3:

table_name | hot_period | retention_period
------------+------------+-----------------
orders | 2 mons | 3 years
(1 row)

Actual Result:

Step 2:

2026/07/17 12:10:16 register: write public.orders:
ERROR: duplicate key value violates unique constraint "partition_config_pkey" (SQLSTATE 23505)

Step 3: values unchanged

table_name | hot_period | retention_period
------------+------------+-----------------
orders | 1 mon | 5 years
(1 row)