In-place TRUNCATE leaves projection row groups behind: row_group_pkey collision on the next write

[OffgridwithJD]

TRUNCATE followed by INSERT in the same transaction fails on any columnar table that has a projection, and rolls the whole transaction back.

Found while adding arms for #892. It is not caused by #892 — it reproduces on e42c80d with that branch nowhere in sight.

Reproduction

CREATE TABLE t (id int, a int, b text) USING pgcolumnar;
INSERT INTO t SELECT g, g%5, 'x'||g FROM generate_series(1,100) g;
SELECT pgcolumnar.add_projection('t','pp',ARRAY['a','b'],ARRAY['a']);

BEGIN;
  TRUNCATE t;
  INSERT INTO t SELECT g, g%5, 'y'||g FROM generate_series(1,50) g;
COMMIT;

Observed on PostgreSQL 18.4, assert build, at e42c80d:

ERROR:  duplicate key value violates unique constraint "row_group_pkey"
DETAIL:  Key (storage_id, group_number)=(10000000004, 2) already exists.

The transaction rolls back, so the table is left as it was. It fails loudly rather than losing data, which is the good outcome — but TRUNCATE then reload in one transaction is the ordinary way to refresh a table, and it is unavailable on any table carrying a projection.

Three controls, which isolate it to the projection

case	result
A. same statements, no projection declared	succeeds, 50 rows
B. with a projection, all in one transaction	row_group_pkey violation, rolled back
C. with a projection, table created in an earlier transaction	row_group_pkey violation, rolled back
D. with a projection, TRUNCATE but no INSERT after it	succeeds

So it needs a projection, and it needs a write after the TRUNCATE in the same transaction. Whether the table pre-exists makes no difference.

Where I think it is, stated as a hypothesis rather than a finding

pgcolumnar_relation_set_new_filelocator already handles exactly this shape for the base relation, and src/columnar_tableam.c says so in its own words:

a transaction that writes, truncates and writes again COMMITS into storage nothing reads: the second insert reuses the stale state, flushes into the retired storage id [...] Before the delete above existed, the retired storage's catalog rows survived and the stale flush collided with them on the primary key, so the transaction ERRORed and rolled back.

That is this error, and the fix there was PgColumnarForgetWriteStateForRelation(RelationGetRelid(rel)). A projection has its own storage and its own write state, keyed separately, and I do not see an equivalent forget for it. That would explain why case A passes and case B does not, and why the symptom is the collision the comment describes rather than silent loss.

I have not confirmed that by instrumenting the write state, so treat the location as unverified. The reproduction and the four cases above are measured.

Note on severity

The comment quoted above records that the base-relation version of this bug was, at one point, silent data loss rather than an error — the collision was the only thing making it safe. If a future change deletes the retired projection rows without also forgetting the projection's write state, this becomes the silent version. That is the reason to fix it rather than document it.

[OffgridwithJD]

Correcting this issue. Two things I filed were wrong, and the real condition is narrower and more interesting than what I described.

What I got wrong

1. "Whether the table pre-exists makes no difference." That is the decisive condition, and I had it backwards. My case C claimed the table pre-existed in an earlier transaction, but the reproduction put every statement into a single psql -c, which psql sends as one implicit transaction. So the table did not pre-exist. A control that does not isolate what it claims to isolate is worse than no control, and this one produced a wrong sentence in a public issue.

2. The hypothesised cause. I guessed the projection's write state has no equivalent of PgColumnarForgetWriteStateForRelation. It does not need one: projection writers live inside the base relation's write state as writeState->projWriters, keyed by the base relid, so the existing forget already drops them. The cause is elsewhere.

The real condition, with the controls that isolate it

The table must be created in the same transaction as the TRUNCATE. All six arms in one message form each:

arm	result
CREATE separate, rest in one transaction	OK
CREATE+INSERT+add_projection together, TRUNCATE+INSERT separate	OK
everything together, explicit BEGIN/COMMIT	ERROR
everything together, no explicit transaction block	ERROR
everything together, no add_projection (control)	OK
everything together, no write after the TRUNCATE (control)	OK

So it needs all four: same transaction as the CREATE, a projection, a TRUNCATE, and a write after it.

Why that combination, measured

PostgreSQL truncates a relation created in the current transaction in place, because a rollback discards the whole relation anyway. Measured:

same transaction as CREATE:   before relfilenode=16567 storage=10000000000
                              after  relfilenode=16567 storage=10000000000
earlier transaction:          before relfilenode=16570 storage=10000000001
                              after  relfilenode=16573 storage=10000000002

The storage id is reused, so pgcolumnar_relation_set_new_filelocator never runs and nothing tears down the old catalog rows.

The base storage is still cleared. The projection's is not:

BASE storage      = 10000000000
PROJ storage      = 10000000001 (projection_id 1)
row_group before  : storage 10000000000 groups 1
row_group before  : storage 10000000001 groups 1
row_group AFTER tr: storage 10000000001 groups 1      <-- base cleared, projection kept
ERROR:  duplicate key value violates unique constraint "row_group_pkey"
DETAIL:  Key (storage_id, group_number)=(10000000001, 2) already exists.

The colliding storage id is the projection's own, not the base's. So: an in-place TRUNCATE clears the base storage's row groups and leaves the projection's storage untouched, and the next write to the projection collides with the row groups the truncate should have removed.

Minimal reproduction

BEGIN;
  CREATE TABLE z1 (id int, a int, b text) USING pgcolumnar;
  INSERT INTO z1 SELECT g, g%5, 'x'||g FROM generate_series(1,100) g;
  SELECT pgcolumnar.add_projection('z1','pp',ARRAY['a','b'],ARRAY['a']);
  TRUNCATE z1;
  INSERT INTO z1 SELECT g, g%5, 'y'||g FROM generate_series(1,50) g;
COMMIT;

on e42c80d, PostgreSQL 18.4, assert build.

Severity, restated

Narrower than I first said — it needs the table to be created in the same transaction, which is a load-and-reload or a test-fixture shape rather than routine operation. It still fails loudly and rolls back rather than losing data.

The reason to fix it rather than document it is unchanged and now better grounded: whatever clears the base storage on an in-place truncate does not know about projection storage. A future change that starts clearing those rows without also discarding the buffered projection writers turns this loud collision into silent loss, which is exactly the history columnar_tableam.c already records for the base relation.