Projections are lost across pg_dump / restore

[jdatcmd]

Split out of #248, which fixed the same class for per-table options.

The gap

pgcolumnar.add_projection() declares a projection; the row lives in
pgcolumnar.projection. pg_dump does not carry it, so a restored table has no
projections and silently loses whatever read performance they were providing.
Nothing errors: the data is intact, the queries still answer, they are just
slower and nobody is told why.

Why the #248 fix does not extend to it

#248 registered pgcolumnar.options with pg_extension_config_dump, which works
because that table is keyed by regclass -- a name that survives the dump and
restore OID remap.

pgcolumnar.projection is keyed by storage_id, and carries proj_storage_id
as well:

storage_id        bigint   -- the table's base storage id
projection_id     integer
name              name
proj_storage_id   bigint   -- this projection's own storage id
sort_key          smallint[]
columns           smallint[]

Storage ids are assigned when a relation is created, so a restore generates new
ones. Registering this table for config_dump would restore rows pointing at
storage that does not exist, which is worse than losing them: corruption instead
of absence.

What it needs instead

Re-emitting the intent rather than the rows -- the restored database needs
pgcolumnar.add_projection(rel, name, ...) calls, not the old catalog contents.
pg_dump has no mechanism for an extension to contribute arbitrary SQL for a
table it does not own, so the plausible shapes are:

1. a documented pgcolumnar.dump_projections() returning the DDL to re-run,
   which puts the burden on the operator but is honest and cheap;
2. storing projection definitions in a regclass-keyed table so config_dump can
   carry them, with storage ids resolved at first use rather than at declaration;
3. accepting the loss and documenting it in docs/limitations.md.

Option 2 is the only one that makes a plain pg_dump | psql correct, and it is a
schema change. Worth deciding before 1.0 rather than after, since it changes the
catalog.

Test

test/pg_dump_roundtrip.sh already has the shape: pin the current behaviour as
an assertion so a fix turns it red, exactly as #249 did for options.

[ChronicallyJD]

Phase 0 is up (PR #267): pinned the loss in pg_dump_roundtrip.sh (asserts projections are dropped by both the plain and custom round trip, red the day a fix lands) and documented it in limitations.md / administration.md, next to the physical-backup-preserves-them contrast.

On the fix itself, grounding your three options in the code, since it changes which is cheap:

add_projection is eager -- it assigns proj_storage_id and back-fills the projection storage from the base table at declaration (columnar_projection.c:249), and pgcolumnar.projection is read by nine modules (customscan for scan selection, write_state for the insert fan-out, vacuum for alignment, ...). So the three shapes cost very differently:

• Option 3 (document + accept): done here, as the honest floor.
• Option 1 (pgcolumnar.dump_projections(rel) returning the add_projection DDL): no schema change, no lifecycle change -- it just reads the existing catalog and formats DDL. Cheap and honest; the burden is the operator running it after a restore. I can put this up as a small PR whenever you want the interim, and it composes with the pin (the test stays red until real carry, but operators have a supported recovery path).
• Option 2 (regclass-keyed declaration + storage resolved at first use): the only one that makes a plain pg_dump | psql correct, but in this codebase it is a real refactor, not a column swap: it splits declaration (metadata, dumpable) from materialization (the eager back-fill at :249), and every one of those nine readers has to tolerate a "declared but not yet materialized" projection until first use builds it. That is the schema-and-lifecycle change you flagged as pre-1.0, and it is yours to call.

My recommendation: land the pin + docs now; take Option 1 as the interim if you want operators covered before the pre-1.0 Option-2 decision; and whichever you pick, the pinned test flips to green as the acceptance check. Happy to implement Option 1 on your word.

[jdatcmd]

Reviewed. The three options are costed correctly, and I think there is a fourth
that nobody has priced. It is much cheaper than option 2 and strictly better than
option 1.

Option 4: dump the declaration, keep the materialization local

Split the two things that are currently one row, but split them by table
rather than by lifecycle:

• pgcolumnar.projection stays exactly as it is: storage-id keyed, attnums,
  not dumped, the thing all six reader modules consult. No reader changes.
• A new pgcolumnar.projection_declaration(rel regclass, name name, columns text[], sort_key text[]) records the intent, keyed by regclass and
  storing column names. Registered with pg_extension_config_dump, so
  pg_dump carries it for the same reason it carries pgcolumnar.options.
• add_projection and drop_projection maintain both.
• pgcolumnar.rebuild_projections() reads the declarations and materializes any
  that have no storage.

After a restore the declarations are present and the storage is not, so one
command rebuilds them.

Why this is cheap, checked against the code rather than assumed

add_projection(rel regclass, name text, columns text[], sort_key text[])
already takes names, not attnums. A rebuild is therefore a direct call to the
existing machinery, not a new code path. The eager back-fill at
columnar_projection.c:249 stays exactly as it is.

The reason option 2 is a real refactor is that it makes a declared projection
visible to readers before it is materialized, so all of them must tolerate that
state. Option 4 never creates that state: readers only ever see
pgcolumnar.projection, and a row appears there only when the storage exists.
That is the whole difference in cost.

Where it is honest about its limit

This does not make a plain pg_dump | psql fully correct. It needs one
operator command afterwards, so on that axis it is option 1, not option 2.

What it fixes is the actual defect in the title. Today the information is
destroyed: the restored database has no record that a projection was ever
declared, and nothing to recover from. After this, the declaration survives the
dump and the recovery is a single supported call rather than DDL an operator has
to have thought to capture beforehand. Option 1 only helps the operator who knew
to run dump_projections() before losing the data, which is the operator least
likely to need help.

One precedent worth noting

Storing column names rather than attnums is now the right answer twice in this
codebase in one week: pgcolumnar.options is regclass-keyed for exactly this
reason, and #292 stores sort_by as names because a plain dump renumbers attnums
on restore. Option 4 is the same pattern a third time, which is usually a sign it
is the grain of the problem rather than a trick.

Recommendation

Option 4, and I am happy to implement it. But it adds a catalog table, which is
the pre-1.0 decision flagged in the issue, and it lands the same week as #292's
sort_by column and the newly documented reload-required posture for catalog
changes. So it is the owner's call, not mine.

If the answer is not yet, I would take option 1 as the interim exactly as
ChronicallyJD proposed, because it costs nothing and gives operators something.
What I would not do is leave this at option 3: the pinned test and the docs are an
honest floor, not a resolution, and this issue is still a silent loss of user
intent.