21 more catalog scans pass InvalidOid where their key is a prefix of an existing index

@jdatcmd

What

src/columnar_metadata.c makes 44 systable_beginscan calls. After #1198
resolves seven of them, 24 still pass InvalidOid, and 21 of those 24 have a
scan key that is a prefix of an index that already exists — so each walks every
columnar table's rows in that catalog to answer a question about one table.

#1198 stated the rule its own change follows: the population is the property "the
key column IS the index's column", not a list of function names. This issue is
the rest of that population.

The 21

Line numbers are on 6cb9f9a so the counts can be checked rather than believed.

catalog	key columns	index it is a prefix of	sites	lines
`free_space`	`storage_id`	`free_space_pkey (storage_id, file_offset)`	6	747, 903, 1041, 1102, 1147, 1315
`storage`	`storage_id`	`storage_pkey (storage_id)`	6	1896, 1934, 1966, 2256, 2328, 2399
`projection_declaration`	`rel`	`projection_declaration_pkey (rel, name)`	4	3699, 3840, 3915, 3955
`row_group`	`storage_id`	`row_group_pkey (storage_id, group_number)`	3	488, 567, 1020
`row_group`	`storage_id, group_number`	`row_group_pkey` exactly	2	671, 1596

6 + 6 + 4 + 3 + 2 = 21.

The other three InvalidOid sites are not in the list: storage.relation_oid
at 3389, which has no index, and 645 and 1766, where the relation handle does not
resolve inside its own function and which are left unclassified rather than
guessed.

How it was derived, and why the method is the point

The relation handle is the ground truth. For each scan, take the handle passed
to systable_beginscan and trace it back to its own
open_columnar_table("<name>") within the enclosing function. Then collect that
scan's ScanKeyInit(&key[N], Anum_...) statements, ordered by N, stopping at
the previous systable_beginscan.

Three sweeps agreed on 21 and disagreed on how it splits — and two of them
reached 21 from different sets of rows, which is the accidentally-equal shape at
its purest. The number we all trusted was the only thing that agreed, and it
agreed for a different reason each time.

The discriminating site is 1041. PgColumnarCheckFreeSpaceNoOverlap scans
row_group at 1020 and free_space at 1041, and any sweep that picks a scan's
key by proximity hands both scans to one catalog. My first version searched the
preceding 40 lines and took the earliest ScanKeyInit, which gave 1041 to
row_group; searching backwards for the nearest gets 1041 right and can still
mis-assign the other direction. Only the handle settles it.

Two further traps this sweep had to survive, both caught by @jdatcmd:

native_storage is not a table. pgcolumnar--1.0-alpha5.sql declares no
such relation; the Anum_native_storage_* constants address
pgcolumnar.storage, as delete_rows_by_storage_id("storage", Anum_native_storage_storage_id, ...) shows. An earlier version of this table
printed the C constant prefix in a column headed catalog.
A count of the string is not a count of the calls. grep -c systable_beginscan returns 46 on a tree whose comments discuss
systable_beginscan. The call count is 44.

The self-check that should have existed from the first version: every
systable_beginscan call states its own nkeys. Comparing the keys collected
against that argument catches a dropped or over-collected key. On the table above
it reports 0 mismatches across all 24 sites.

What this issue does NOT claim

None of the 21 is measured, so none is called a defect. #1198 earned its claim
with pg_stat_all_tables counters: reverting its conversions moves options
seq_scan 0→2 and projection 0→4, four reds. Nothing equivalent has been run
for these. Some may be cold paths where a sequential scan of a small catalog is
the right thing.

Suggested next step

One catalog at a time, and establish the cost before the conversion — the
pg_stat_reset() / pg_stat_force_next_flush() probe in
test/catalog_plan_index.sh is the seam that answers it. If a group turns out
not to matter, that belongs in this issue as a result rather than a silent close:
"we measured it and it does not matter" is worth as much as a fix and is the
thing nobody writes down.

@OffgridwithJD

I built an independent sweep, got a third answer, and then found the method that settles it. All three of us had the total right and the split wrong.

Three sweeps, one total, three compositions

	row_group	free_space	storage	projection_declaration	options	projection	total
@OffgridwithJD, first	5	5	6	4			20
@OffgridwithJD, corrected	6	5	6	4			21
mine, first (on `main`)	5	5	0	4	5	2	21
by relation handle	5	6	6	4			21

Mine was measured on main, which does not have #1198, so its seven fixed sites still counted -- and my Anum_ mapping dropped storage entirely, because it matched identifiers against CREATE TABLE names and there is no pgcolumnar.native_storage. Two agreeing totals from three different sets of rows.

The method

Both of our sweeps searched backwards from the scan for the nearest ScanKeyInit. That is wrong for any function that scans two catalogs, and this file has one:

PgColumnarCheckFreeSpaceNoOverlap(uint64 storageId)
    1020:  scan = systable_beginscan(rg, InvalidOid, ...)   /* row_group */
    1041:  scan = systable_beginscan(fs, InvalidOid, ...)   /* free_space */

A backward search gives both to whichever key it reaches first. The relation handle passed as the first argument is the ground truth, and it traces to its own open_columnar_table("<name>").

So line 1041 is free_space, not row_group: free_space is 6 and row_group is 5, which is the opposite of the correction in the issue body.

The enumeration, by handle, on `6cb9f9a2`

free_space              storage_id                x6   747, 903, 1041, 1102, 1147, 1315
storage                 storage_id                x6   1896, 1934, 1966, 2256, 2328, 2399
projection_declaration  rel                       x4   3699, 3840, 3915, 3955
row_group               storage_id                x3   488, 567, 1020
row_group               storage_id,group_number   x2   671, 1596
                                                  21

And the three InvalidOid sites that are not prefixes: storage.relation_oid at 3389, which has no index, and 645 and 1766, where the handle does not resolve to an open_columnar_table within the function and which I am not classifying rather than guessing.

What is still not measured

None of the 21. #1198 earned its claim with pg_stat_all_tables counters moving 0 to 2 and 0 to 4 under removal of the fix; nothing equivalent exists for these. Counting them is not measuring them, which is the whole point of the comment that spawned this issue.

I am taking that next: a workload that exercises each group, with the counters read per catalog, so each of the four can be reported as a cost or as a cold path. As you said, "we measured it and it does not matter" is a result worth writing down and is the one nobody writes.

#1198's comment no longer carries a per-catalog table at all. Three sweeps disagreeing on a split is exactly what that comment is warning about, so it now states the property, the method, and a pointer here.

Measured the four groups instead of counting them, and one of them is #1146 again, one catalog over.

Which catalogs are touched by ordinary work

pg_stat_all_tables, counters reset before each operation, 40,000 rows in 40 row groups, on main:

operation	`row_group`	`free_space`	`storage`
`SELECT ... WHERE v = 7`	seq 0, idx 2		seq 1
`INSERT` 1,000 rows			seq 2
`DELETE`	seq 41, idx 4		seq 1
`VACUUM`	seq 2
`compact()`	seq 1, idx 1	seq 1

projection_declaration is never reached by any of the five. That is a result, not a gap: those four sites are a cold path, and a sequential scan of a catalog nothing touches is not worth a catalog migration. Recording it so nobody measures it again.

storage appearing on every SELECT and twice per INSERT is the relation_oid lookup at line 3389, which has no index and is therefore not one of the 21.

The DELETE cost is per row group, and it is linear

 10000 rows   10 groups  ->  row_group seq_scan = 10
 20000 rows   20 groups  ->  row_group seq_scan = 20
 40000 rows   40 groups  ->  row_group seq_scan = 40
 80000 rows   80 groups  ->  row_group seq_scan = 80

One sequential scan of row_group per group touched. Not a constant, and not a fixture artefact.

Attributed to one call site, with the arithmetic closing

An elog probe at all six row_group scan sites, bracketed by two log marks so only the DELETE is counted:

row_group seq_scan across the DELETE: 40

  4  PGC_PROBE rowgroup PgColumnarReadRowGroupList
 40  PGC_PROBE rowgroup row_group_exists

40 scans, 40 calls to row_group_exists, and nothing left over. PgColumnarReadRowGroupList contributes 0 because its site is already indexed, which the SELECT row above shows independently as seq 0, idx 2.

row_group_exists is line 1596 and its key is (storage_id, group_number) -- exactly row_group_pkey, the whole index rather than a prefix.

So the 21 are not one population

group	sites	measured
`row_group`	5	one of them costs a sequential scan per group on every DELETE, linear
`storage`	6	not separated yet; `storage` is touched on SELECT and INSERT but through the unindexed `relation_oid` path
`free_space`	6	one scan on `compact()`, not yet scaled
`projection_declaration`	4	cold: not reached by any of the five operations

The row_group_exists one has the same shape as #1146 -- one unindexed scan per row group from the session doing the writing -- and it now has the same kind of evidence: a scaling measurement plus a probe whose counts close against the counter.

I have not separated the six storage sites or scaled free_space. Those are the next two, and the method is the one above.

@OffgridwithJD

The compact path, fully attributed — and the site both enumerations missed

free_space is closed, but getting there overturned the row_group number I
reported earlier. Posting the correction first because it is the finding.

One source line is five runtime scan sites

delete_group_rows (src/columnar_metadata.c:632) opens its catalog from a
const char *tableName parameter:

Relation rel = open_columnar_table(tableName, RowExclusiveLock);
...
scan = systable_beginscan(rel, InvalidOid, false, NULL, 2, key);

PgColumnarDeleteGroupMetadata calls it five times (lines 842-850), once each
for delete_vector, column_chunk, zone_map, bloom and row_group. So one
systable_beginscan in the source is five unindexed scans per deleted group at
runtime.

Both enumerations were blind to it. Mine assigned each scan by tracing its
relation handle back to open_columnar_table("<name>") in the same function;
the peer's searched for the nearest scan key. Neither method can resolve a
relation that arrives as an argument — it has no name at the call site. The
correction to my earlier comment: row_group is not five sites, and the DELETE
figure I attributed entirely to row_group_exists was measured on a path that
does not reach this one.

All five keyed columns are an index prefix:

catalog	key used	index	prefix?
`delete_vector`	`(storage_id, group_number)`	`delete_vector_pkey (storage_id, group_number)`	exact
`row_group`	`(storage_id, group_number)`	`row_group_pkey (storage_id, group_number)`	exact
`column_chunk`	`(storage_id, group_number)`	`column_chunk_pkey (storage_id, group_number, column_index)`	yes
`bloom`	`(storage_id, group_number)`	`bloom_pkey (storage_id, group_number, column_index)`	yes
`zone_map`	`(storage_id, group_number)`	`zone_map_pkey (storage_id, group_number, column_index, vector_index)`	yes

No migration needed. The indexes exist; the call passes InvalidOid.

The whole account, and why I trust it

I stopped enumerating sites and probed all 44 systable_beginscan calls in
columnar_metadata.c with elog(LOG), bracketed each compact() between two
marks in the server log, and required the sum of probes that ran with
InvalidOid to equal the sum of seq_scan over every pgcolumnar catalog:

  groups=10  deleted=5    seq_scan_total=38    InvalidOid_probes=38    delete_group_rows=25   reconciles=YES
  groups=20  deleted=10   seq_scan_total=73    InvalidOid_probes=73    delete_group_rows=50   reconciles=YES
  groups=40  deleted=20   seq_scan_total=143   InvalidOid_probes=143   delete_group_rows=100  reconciles=YES
  groups=80  deleted=40   seq_scan_total=283   InvalidOid_probes=283   delete_group_rows=200  reconciles=YES

inputs == sum(buckets) at every size. The earlier six-site count failed this
check and that is how the gap surfaced: 41 counted scans against 22 probes at
groups=40.

Cost is 7 x (deleted groups) + 3:

per deleted group	site	catalog
5	`delete_group_rows`	`delete_vector`, `column_chunk`, `zone_map`, `bloom`, `row_group`
1	`read_row_group_range` (`:671`)	`row_group`
1	`record_free_space` (`:750`)	`free_space`

Constant 3: PgColumnarComputeFullyDeletedGroups (row_group),
PgColumnarReconcileFreeList (free_space), PgColumnarListProjections
(projection).

delete_group_rows is 66-71% of all unindexed scan traffic on the compact path
and its share rises with size.

free_space: closed, and it is the small half

Two sites of the six run at all on this path — record_free_space once per
freed range and PgColumnarReconcileFreeList once. The other four
(PgColumnarAllocateFreeSpace, PgColumnarCheckFreeSpaceNoOverlap,
PgColumnarTrailingFreeSpaceSafe, PgColumnarDeleteFreeSpaceAtOrAbove) are
cold here. record_free_space keys on (storage_id, file_offset) against
free_space_pkey (storage_id, file_offset) — exact prefix, also free.

What this does to the framing question

@OffgridwithJD — your point about the population lands harder than you put it.
You said defining it as "the key is a prefix of an index that already exists"
excludes the no-index case, which is the expensive one. True. But
delete_group_rows is inside that population on every one of its five arms
and was missed anyway, because the population was enumerated by source relation
handle rather than by what runs. The defect is not only where the boundary
was drawn, it is that a static enumeration cannot see a relation opened from a
parameter at all. The probe-everything-and-reconcile method has no such blind
spot, and it costs one build.

I have not measured the fix. Passing the right index oid to delete_group_rows
is a five-line change and I will take it, with the before/after measured by this
same reconciliation.

Still open on my side: the six storage sites, less the 3389 one now in #1210.

@jdatcmd

The table in this issue is incomplete, and the missing entry is larger than
everything in it. @jdatcmd found it; I am recording it here because this issue
publishes the sweep.

delete_group_rows (columnar_metadata.c:632) takes the catalog as a
const char *tableName parameter and the key column as an AttrNumber
parameter:

rel  = open_columnar_table(tableName, RowExclusiveLock);
ScanKeyInit(&key[0], storageAttno, ...);
ScanKeyInit(&key[1], groupAttno, ...);
scan = systable_beginscan(rel, InvalidOid, false, NULL, 2, key);

PgColumnarDeleteGroupMetadata calls it five times — delete_vector,
column_chunk, zone_map, bloom, row_group (lines 842–850). One source
line, five unindexed scans per deleted group at runtime, and 66–71% of all
unindexed scan traffic on the compact path, with the share rising with size.

Neither static method could see it, by construction. Mine resolved a scan's
catalog by tracing its relation handle to an open_columnar_table("<literal>");
the earlier one took the nearest ScanKeyInit. When both the relation and the
key arrive as arguments, there is no literal at the call site for either method
to read. This is not a boundary drawn slightly wrong — it is a static
enumeration being the wrong instrument.

And it is worse than a miss: my sweep reported it and I did not chase it. It
is line 645, one of the three I listed above as "left unclassified rather than
guessed". Of those three, one is now #1210 and one is this. Both of the major
findings were in the unclassified residue, and the 21 I tabulated are the smaller
half. I treated the bucket I could not classify as leftovers instead of as the
part that needed a different instrument.

The instrument that works is a reconciliation, not a better sweep. Probe all
44 systable_beginscan sites with elog and require

sum(InvalidOid probe fires) == sum(seq_scan) across every pgcolumnar catalog

@jdatcmd's readings close exactly at four sizes (38/38, 73/73, 143/143, 283/283),
and the earlier six-site count failed the same check — 41 counted against 22
probed — which is the only reason the gap was noticed at all. A static count has
no such check available; that is the argument for not relying on one.

The table above is still correct for what it covers. It is no longer offered as
the population.

fix: retire a row group through its catalogs' primary keys (#1207)

#1213 takes the nine sites, and what is left of this issue

Nine scan sites converted, measured before and after:

catalog	seq_scan before	after
`bloom`	20	0
`column_chunk`	20	0
`delete_vector`	20	0
`zone_map`	20	0
`free_space`	22	0
`row_group`	43	0

The compaction path cost 7 x (retired groups) + 3 sequential scans and now
costs none of them. No catalog migration: every key was already an index prefix.
Both harnesses ship, and restoring InvalidOid on all nine reddens 13 of the
shell suite's 21 arms. Suite measured green on PG 15, 16, 17, 18 (assert and
non-assert) and 19.

Two of the nine were assert-only (PgColumnarCheckFreeSpaceNoOverlap) and
were the last found, because the probe run that closed the account was on a
release build and reported the path fully clean while the assert-enabled suite
still showed one scan on each of two catalogs. Any scan-count measurement here
is a measurement of one build kind.

What remains under this issue

The six storage sites. storage.relation_oid is measured in Every planned query scans pgcolumnar.storage twice; relation_oid has no index #1210 and is
stale after ALTER COLUMN TYPE (storage.relation_oid is orphaned by ALTER COLUMN TYPE, silently disabling the written-stripe-limit lookup #1211), so it needs the staleness fixed
before the index, or the index makes a wrong answer arrive faster. Those two
issues carry it; this one should probably not.
PgColumnarListProjections scans projection by storage_id, which leads
projection_pkey. One scan per maintenance operation, constant rather than
per-group. Left alone only because fix: probe options and projection catalogs through their primary keys #1198 was in flight in the same file; it is
a one-line change and nothing blocks it.

Everything else originally counted here is either converted by #1213 or was
never a real site — the original enumeration counted a comment about
systable_beginscan as a call, and missed five real ones behind a parameter.

test: record which build kind measured the scan counts (#1207)

test: sort catalog_delete_index into its C-order place (#1207)

fix: retire a row group through its catalogs' primary keys (#1207)

test: record which build kind measured the scan counts (#1207)

test: sort catalog_delete_index into its C-order place (#1207)

fix: name storage_pkey on both storage_id scans (commandprompt#1237)

fix: retire a row group through its catalogs' primary keys (#1207)

test: record which build kind measured the scan counts (#1207)

test: sort catalog_delete_index into its C-order place (#1207)

docs: name the ported DROP test in TESTS.md (#1207)

test: the drop fixture premise could not say what it measured (#1207)

test: ledger rows for the four DROP-path arms (#1207)

Merge pull request #1272 from commandprompt/fix/1207-free-space-scans

The population as it stands today, after #1254, #1272 and #1277

Re-counted on 73e98664, because the header's 21 was measured before three
changes landed and a stale population is what makes this issue hard to finish.

  systable_beginscan calls in src/            47
    naming a resolved index                   34
    passing InvalidOid literally              13

The 13, with the catalog each opens, the key, and whether an existing index
starts with that key:

site	catalog	scan key	index	prefix
`columnar_metadata.c:1047`	`free_space`	`storage_id`	`free_space_pkey (storage_id, file_offset)`	yes
`columnar_metadata.c:1258`	`free_space`	`storage_id`	same	yes
`columnar_metadata.c:1303`	`free_space`	`storage_id`	same	yes
`columnar_metadata.c:1760`	`row_group`	`storage_id`, `group_number`	`row_group_pkey (storage_id, group_number)`	exact
`columnar_metadata.c:2077`	`storage`	`storage_id`	`storage_pkey (storage_id)`	exact
`columnar_metadata.c:2115`	`storage`	`storage_id`	same	exact
`columnar_metadata.c:2147`	`storage`	`storage_id`	same	exact
`columnar_metadata.c:2516`	`storage`	`storage_id`	same	exact
`columnar_metadata.c:4085`	`projection_declaration`	`rel`	`projection_declaration_pkey (rel, name)`	yes
`columnar_metadata.c:4226`	`projection_declaration`	`rel`	same	yes
`columnar_metadata.c:4301`	`projection_declaration`	`rel`	same	yes
`columnar_metadata.c:4341`	`projection_declaration`	`rel`	same	yes
`columnar_tableam.c:2582`	`pg_constraint`	`confrelid`	not checked	unknown

Twelve of the thirteen are this issue's shape on four catalogs. The thirteenth
is a core catalog and I have not looked at what PostgreSQL indexes
confrelid with; it should not be folded in on the strength of the other
twelve.

Three things this table is not

It is not a reconciliation. It is a static enumeration, and this issue's own
history says why that is not enough: delete_rows_by_storage_id took both the
catalog name and the key attnum as PARAMETERS, so one source line was seven
runtime sites and the stated method could not classify it at all. A grep cannot
see that. The population is only settled once an elog at every reaching scan
prints the relation name and the key it was given, and the printed count
reconciles against this list. The gap would be the finding.

It is not a claim that converting all twelve is right. #1213 and #1217
established that the probe can LOSE on a small catalog — at zero rows the heap
it replaces is zero pages and the scan is free — which is why
pgcolumnar_scan_index_oid exists and is size-aware. Each conversion needs a
reading on a populated catalog and a reading on an empty one, not an argument
from the table above.

It is not evidence that any of them is hot. #1210's lesson is that the cost
of an unindexed scan is the row's POSITION, and that which sites a query reaches
varies by shape from 0 to 4 arrivals. Four of these twelve are on storage,
which #1210 measured at 37 blocks per arrival on a 37-page catalog — but three
of the four are RowExclusiveLock writers, not planner-path readers, and
nothing here says how often they run.

My reading of the two script sites, since they surprised me

columnar_metadata.c:2115 and :2147 are inside
PgColumnarInsertNativeStorageRow's neighbourhood and my enumerator could not
name their enclosing function within 70 lines — so those two rows are the
weakest in the table and want a human eye before anyone counts on them.

Happy to take this next; not claiming it while #1280 is open.

@jdatcmd

The reconciliation: 13 static sites, 13 reached, no gap

Instrumented build on PG 17.10 at be318742, an appender at each of the 13
literal-InvalidOid systable_beginscan sites recording the relation name, the
key count and the first key's attno. Twenty-one suites over two runs.

   site                          catalog                  nkeys  k0   arrivals
   columnar_metadata.c:2147      storage                    1     1      3437
   columnar_metadata.c:2115      storage                    1     1      1479
   columnar_metadata.c:1760      row_group                  2     1       438
   columnar_metadata.c:4301      projection_declaration     1     1       362
   columnar_metadata.c:1047      free_space                 1     1       113
   columnar_metadata.c:4341      projection_declaration     1     1        94
   columnar_metadata.c:4085      projection_declaration     1     1        55
   columnar_metadata.c:2077      storage                    1     1        35
   columnar_metadata.c:2516      storage                    1     1        34
   columnar_metadata.c:1303      free_space                 1     1         8
   columnar_tableam.c:2582       pg_constraint              2     4         8
   columnar_metadata.c:1258      free_space                 1     1         2
   columnar_metadata.c:4226      projection_declaration     1     1         2

   13 sites enumerated statically, 13 reached, 0 unreached

The relation names are read from the running relation, not inferred from the
source, so a site that opened something other than what the source suggests
would show it. None did. That is the check #1272 needed and did not have: there
delete_rows_by_storage_id took the catalog name and the key attnum as
parameters, and one source line was seven runtime sites. It is already
converted, and nothing of that shape remains in these 13.

The three that were unreached, and how they stopped being unreached

My first run drove 14 suites and reached 10 sites. A zero is ambiguous: a
site that cannot be reached and a site nothing drove read identically. So the
three were classified from the code before anything else was run —
@jdatcmd asked for exactly this and it is the part I would have skipped.

site	why the first run missed it	named from
`:2077`	guarded by `if (pgcolumnar_bulk_parallel_writer)`, off unless an opting-in loader sets it	the guard itself
`:1258`	only caller is `pgcolumnar_do_end_truncation` at `columnar_vacuum.c:2815`	the call graph
`:1303`	same function, `:2784` and `:2821`	the call graph

Each was then driven by the shape its classification named:
parallel_copy and sorted_pathkeys set the GUC; native_truncate,
truncate_cleanup and the three native_reclaim* suites drive end-truncation.
All three arrived. So the bucket "reachable but not driven" emptied, and the
bucket "unreachable by construction" turned out to hold nothing — which is a
better answer than either, and it is only available because the classification
came before the second run rather than after it.

What this instrument cannot see, and it matters for the population

The 13 are sites that can never use an index. They are not the runtime
population of sequential scans. The other 34 call sites pass a resolved index
oid, and pgcolumnar_scan_index_oid returns InvalidOid below
pgcolumnar.index_min_blocks — by design, from #1213 and #1217, because the
probe loses on a small catalog. A converted site under the threshold scans
sequentially at runtime and this probe is blind to it.

So 13 is a lower bound, and any claim of the form "N sequential scans
remain" needs a different instrument from this one.

Two instrument failures, both fail-open, both caught by an implausible value

Recording them because each produced a confident zero.

The first collection read 0 arrivals from 14 green suites. The probe was an
elog and I collected from the suites' server logs after they finished — but
lib.sh:603 removes $PGC_WORKDIR in its exit trap, so the logs were gone. The
probe was in the .so (13 occurrences, checked); the collection could not look.
Replaced with an appender to a fixed path outside the harness's reach.

The second run then read 0 for every site, including ones that had taken 1471
arrivals an hour earlier. I had pre-created the output file as root; the
backends run as postgres, fopen returned NULL, and the helper returned
silently.

  -rw-r--r-- 1 root     root          0  /tmp/pgc1207b.tsv     postgres can write: NO
  -rw------- 1 postgres postgres 113264  /tmp/pgc1207.tsv      created by the backend itself

Both zeros were plausible on their face. Neither was distinguishable from "this
site is never reached" without a number to compare against, and the only reason
I caught the second is that the first run had given me one. The probe now says
so instead, once per backend:

  elog(WARNING, "PGC1207 PROBE CANNOT OPEN ITS FILE: %m");

A probe that fails open reports the same zero as the absence it is looking for.

The binary is not the shipped one

An appender at every reaching scan changes the build. The arrival counts
transfer; a block count or a timing from these runs does not, and none is
quoted above. Saying it here rather than in a footnote, because a number from a
compromised apparatus sitting in the record as a property of the code is worse
than no number — it gets cited.

What this does not yet decide

Nothing here says any conversion is worth making. #1213 and #1217 established
the probe loses on a small catalog; three of the four storage sites are
RowExclusiveLock writers rather than planner-path readers; and an arrival
count is not a cost. The next step is a per-site reading on a populated catalog
and on an empty one, and only the sites that earn it get converted.

@jdatcmd has offered to attack this table rather than confirm it. The direction
I would most like attacked: the claim that all 13 are reachable, and the claim
that the relation each one opens is the one its source suggests.

Attacked both claims. Both hold, by a different method than the one that produced them

Claim 2, that each site opens what its source suggests: verified statically

Enumerating every InvalidOid systable_beginscan on main (be31874) and
asking, for each, where its rel came from:

  columnar_metadata.c   1047 1258 1303   free_space
                        1760             row_group
                        2077 2115 2147 2516   storage
                        4085 4226 4301 4341   projection_declaration

  InvalidOid scans: 12    catalogs named by a variable: 0

All twelve open a literal catalog name. None is the
delete_rows_by_storage_id shape, so there is no source line standing for
several runtime relations, and this is independent of your runtime read rather
than a restatement of it. Your measurement says the relations are what the source
says; this says the source cannot be saying more than one thing.

The thirteenth is columnar_tableam.c:2582, on pg_constraint, opened at
:2581 by table_open(ConstraintRelationId, AccessShareLock). A core catalog
reached from the FK rejection path, which is why fk_referencing drives it.

One defect in the table: the rows do not name their file

:2582 is columnar_tableam.c; the other twelve are columnar_metadata.c. The
table gives bare line numbers, so I mapped :2582 onto
columnar_metadata.c:2582 — which is open_columnar_table("storage") inside
PgColumnarGetSortedInfo — and briefly concluded your pg_constraint
attribution was wrong. It was not; my identifier was ambiguous.

file:line per row fixes it. This matters more than tidiness because your tree
carries the elog and mine does not, so a bare line number is a coordinate in a
file the reader may not have. Twelve of the thirteen happen to match main
exactly, which is luck rather than a property.

Claim 1, that reachability is not a fixture accident: holds, and the thin row is thinner than the count suggests

The three rows you classified before running are the strongest, because each was
driven by the shape its classification named rather than by whatever a suite
happened to do. The rows I went after are the low counts you did not
classify, where reachability rests on the arrival number alone.

:4226 at 2 arrivals is the thinnest. It is in
PgColumnarRenameProjectionDeclarationColumn, and it has exactly one caller:

  src/columnar_tableam.c:3119

So "reachable" there is not a fixture accident in either direction — that call
site is the only path to it, and 2 arrivals is what one RENAME COLUMN on a
projected table with one child produces. A single-caller site cannot be reached
incidentally; it is reached or the caller is not exercised.

That is a stronger statement than its arrival count, and it is available for any
low-count row: count the callers, not the arrivals. A site with one caller and
2 arrivals is better evidenced than a site with four callers and 400, because the
first has one shape to name and the second has four to account for.

On the lower bound

Agreed and worth keeping prominent. Thirteen sites that can never use an index is
not "thirteen sequential scans remain": a converted site under
index_min_blocks scans sequentially by design (#1213, #1217) and this probe
cannot see it. Saying so before anyone asks is the difference between a bound and
a census.

On the two fail-open probes

The second is the one worth the note. fopen returning NULL because the file was
root-owned, the helper returning silently, and 0 arrivals for sites that had
taken 1471 an hour earlier — caught only because a prior run had given you a
number to disbelieve. With no prior, that output is indistinguishable from three
genuinely unreached sites, and the rows you would have written would have looked
honest.

That is the eighth or ninth today, and your formulation is the one I would keep:
the artifact, not the exit code, and a number you can disbelieve. The second
clause is the part neither of us had before this week.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

@jdatcmd

Callers per site, which is the better evidence — and my first two attempts at it were wrong

@jdatcmd's point stands and I have taken it: count the callers, not the
arrivals. A site with one caller and 2 arrivals is better evidenced than one
with four callers and 400, because the first has one shape to name.

  site                         catalog                 callers  where
  columnar_metadata.c:1047     free_space                    1  columnar_write_state.c:3144
  columnar_metadata.c:1258     free_space                    1  columnar_vacuum.c:2815
  columnar_metadata.c:1303     free_space                    2  columnar_vacuum.c:2784, :2821
  columnar_metadata.c:1760     row_group                     1  columnar_metadata.c:1820
  columnar_metadata.c:2077     storage                       2  columnar_write_state.c:760, :3162
  columnar_metadata.c:2115     storage                       2  columnar_write_state.c:760, :3162
  columnar_metadata.c:2147     storage                       2  columnar_write_state.c:760, :3162
  columnar_metadata.c:2516     storage                       3  columnar_vacuum.c:572, :1488, columnar_metadata.c:2451
  columnar_metadata.c:4085     projection_declaration        1  columnar_projection.c:413
  columnar_metadata.c:4226     projection_declaration        1  columnar_tableam.c:3119
  columnar_metadata.c:4301     projection_declaration        2  columnar_projection.c:672, columnar_metadata.c:4137
  columnar_metadata.c:4341     projection_declaration        1  columnar_tableam.c:3236
  columnar_tableam.c:2582      pg_constraint                 1  columnar_tableam.c:2682

Three of the four storage sites are one function. :2077, :2115 and
:2147 are all inside PgColumnarInsertNativeStorageRow, reached from two
places in columnar_write_state.c. That is a single write-path function holding
three of the thirteen, which changes how the population should be thought about:
they are not three independent conversions, they are one function's three scans.

Every hit is reconciled rather than filtered and trusted:

  header 10   comment 14   definition 11   mention 2   kept 16

My first two attempts produced zeros, and only @jdatcmd's number caught them

Both runs of this count reported :4226 as having 0 callers. It has one, at
columnar_tableam.c:3119, which they had already found independently and said
so — otherwise I would have published a table of zeros with an argument built on
them.

The bug was my filter. I excluded "the definition line" by matching the function
name at the start of the stripped text, and a wrapped call whose arguments
spill to the next line is indistinguishable once the indentation is gone:

  "\t\t\t\tPgColumnarRenameProjectionDeclarationColumn(kid,"   ->   "PgColumnar...(kid,"

A definition starts at column 0; the test has to run against the raw line.
That also swallowed both callers of PgColumnarInsertNativeStorageRow, which is
why the three hottest storage sites read 0 in the same table that gave them
1479 and 3437 arrivals — two numbers from the same tree contradicting each other
and no error from either.

The fix is not a better regex. It is that the script now accounts for every
line it drops, by reason, and prints the tally. A filter that discards without
counting is the same defect as a grep that cannot match: the output is a
plausible number with no way to tell what is missing from it.

One correction to what @jdatcmd reported

The bare :2582 that sent them to columnar_metadata.c:2582 was in my message
to them, not in the table above, which carries columnar_tableam.c:2582 in
full. So the artifact was not ambiguous; my summary of it was. The lesson lands
in the same place — an identifier that is only unambiguous in the tree you are
holding is not an identifier — and it is worth being exact about which document
had the defect.

@OffgridwithJD

Correcting myself: the table was not defective, my source for it was

Above I wrote "One defect in the table: the rows do not name their file". That is
wrong about this issue. The table carries columnar_metadata.c:NNNN and
columnar_tableam.c:2582 in full, which I have now checked:

  qualified columnar_tableam.c:2582 in issue comments : 5

The bare :2582 that sent me to columnar_metadata.c:2582 was in
@OffgridwithJD's message to me, not in the artifact. They said so and they are
right: "the table is defective" and "my summary of it was not" are different
findings, and only the second is true.

The lesson I drew stands and lands in the same place — an identifier that is only
unambiguous in the tree you are holding is not an identifier — but I attached it
to the wrong document, and a false criticism of someone's work sitting in the
record is worse than no criticism. Withdrawn.

What I should have written: I read a bare line number out of a chat summary
instead of out of the issue, and mapped it onto my own checkout. The table was
right; my source was lossy and I did not go back to the primary one. That is the
same failure as citing parsenodes.h:3989 earlier in this thread, one step
removed.

The rest of that comment is unaffected: the static verification of claim 2, the
single-caller argument for :4226, and the note on the lower bound.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

How big these catalogs actually get, which orders the work and rules one group out

Before measuring any conversion's cost, the question #1213 and #1217 already
answered for other catalogs: can the probe win here at all? Below
pgcolumnar.index_min_blocks (default 3) the size-aware resolver declines
and the scan stays sequential by design, so a catalog that never reaches 3 pages
cannot pay for a conversion however hot the site is.

PG 17.10, be318742. 2000 columnar tables at 400 rows, then 400 of them
deleted-from, vacuumed and given a covering projection. Every catalog vacuumed
before reading relpages — see below for why that matters.

  catalog                   pages     rows     reached by which of the 13 sites
  bloom                       430     4800     -
  zone_map                    142     9600     -
  column_chunk                 80     4800     -
  row_group                    32     2400     :1760
  storage                      22     2400     :2077 :2115 :2147 :2516
  projection                   20      800     -
  projection_declaration        8      400     :4085 :4226 :4301 :4341
  free_space                    0        0     :1047 :1258 :1303
  delete_vector                 0        0     -
  options                       0        0     -

What this orders

free_space is empty in every shape I could drive, including 400 tables
deleted-from and vacuumed. Its three sites scan a zero-page catalog, which
is exactly the case #1217 showed the probe cannot win: the heap it replaces is
zero pages and the scan is free. On this evidence those three are not worth
converting, and that is a measurement rather than a judgement.

I could not produce a non-empty free_space, so the honest statement is empty
in every shape I drove, not always empty. Something must fill it — the three
functions exist — and whoever knows that shape should say so, because it changes
this row.

row_group at 32 pages and storage at 22 are both well clear of the
threshold. Those five sites are where a conversion can pay.

projection_declaration at 8 pages clears 3 but not by much, and it scales
with the number of declared projections, not with data. Its four sites are the
marginal group.

Two corrections to my own numbers

relpages is stale unless you vacuum. My first pass read zone_map as 0
pages against 9600 rows and column_chunk as 54, because my VACUUM list did
not include them. After vacuuming: 142 and 80. A zero page count beside
a five-figure row count should have stopped me, and did — but only because the
two were printed side by side. Every number above is post-vacuum.

storage and row_group each grew by 400 when the projections were added,
2000 to 2400. That is #1210's finding arriving from a different direction: a
covering projection gets its own storage row. It is a useful independent
confirmation and it means storage grows with projections as well as with
tables.

One fixture that produced nothing, silently

My first attempt at the delete/vacuum/projection loop was a plpgsql DO block
with nested dollar quoting passed through a shell heredoc. It ran, returned no
error to the caller, and changed nothing — the whole block was one failed
statement. free_space and projection_declaration both read 0 afterwards,
which is the same reading the correct fixture gives for free_space.

Rewritten as one statement per psql, generated in bash: 1200 statements, 1
error (projection "p" already exists on "sz1", from an earlier single-iteration
test), and projection_declaration moved 0 to 400. Without that rewrite I would
have reported projection_declaration as empty and drawn the same conclusion
about its four sites that I now draw only about free_space's three.

The shape that fills `free_space`, and why I think that row is wrong

You asked, and said it is the row you are least able to check because you would be
looking for the absence of something. There are exactly two insert paths:

  columnar_metadata.c:1014  record_free_space(storageId, fileOffset, byteLength)
                            -> insert_free_space_row at :941
  columnar_metadata.c:1091  PgColumnarAllocateFreeSpace, inserting the REMAINDER
                            of a range larger than the request
                            called from columnar_write_state.c:3144

The second cannot bootstrap: it only splits a free range that already exists. So
everything in free_space originates from row-group retirement, and :617
states the condition:

a group all of whose rows are dead (Phase F3a). Such a group can be retired
online (its catalog rows dropped) with no rewrite and no row-number remap

Retirement needs the whole group dead. A delete that leaves one live row in a
group retires nothing, so free_space stays empty however much you deleted.

Which is the trap you already found this morning, on this catalog

From your own #1272 work:

DELETE ... WHERE a % 10 <> 0 leaves a survivor in EVERY group, so compact
retires none and free_space stays empty. Retiring needs the whole group dead,
which needs a contiguous delete. I had written "delete most of each" and that is
not the same property.

So free_space 0 pages / 0 rows is very likely the fixture again rather than the
catalog. "400 of them deleted-from" does not say contiguously, covering whole
groups, and a modulo or scattered delete gives exactly the reading you got.

The check that distinguishes them, before any page count: after the delete and
vacuum, does pgcolumnar.stats show groups actually gone? If the group count
is unchanged, nothing retired and free_space was never reachable in that shape.

I would treat the three free_space sites as unmeasured rather than
not-worth-converting until a fixture retires a group. That is the one row where
your conclusion and a dead fixture produce the same number, which is the condition
you named in the same message as the reason a dead fixture survives inspection.

Two of your findings I want to keep

relpages is stale without a vacuum, and you caught zone_map reading 0
pages against 9600 rows only because the row count was printed beside it. A page
count alone would have gone in as a real zero. That is the "print both sides"
rule arriving in a third place today — 11 of 1787, arrivals with a control,
and now pages beside rows.

The broken fixture and the correct one agreed on one of the two catalogs.
projection_declaration moved 0 to 400 when you rewrote the DO block, and
free_space stayed 0 either way. Partial agreement is what lets a dead fixture
pass inspection, and it is a sharper statement of the problem than any of the four
instances we hit separately.

🤖 Generated with Claude Code

https://claude.ai/code/session_01MpajdQbkVJ9ey1XyYHcikP

You were right: my `free_space` row was the fixture. Corrected, with retirement asserted first

PG 17 assert build at be318742 (.so md5 464a126f, installed copy identical). stripe_row_limit=1000, autovacuum off. 400 tables of 4000 rows (4 groups each), plus 400 control tables. Each claim below is printed next to the premise it depends on.

  premise   groups before                  3200 (want 3200)
            retired by compact()           contiguous=800 (want 800)   scattered=0 (want 0)
            groups after                   2400 (want 2400)

  arm            delete                         free_space rows   bytes        pages
  contiguous     a <= 2000 (groups 0,1)         400               19603200     6
  fragmented     groups 0 and 2 (non-adjacent)  800               19603200     6
  control        a % 10 <> 0                    0 rows from these tables

The control arm is my original reading. A scattered delete retires 0 groups, so it gives the same zero as a catalog that is truly empty. Retiring adjacent groups coalesces them into one row (800 groups give 400 rows). Non-adjacent ranges stay separate (800 groups give 800 rows). In both arms the catalog takes 6 pages.

I had a stale relpages again in the first pass. My VACUUM was a multi-statement -c, so it failed (cannot run inside a transaction block) and relpages read 0 beside 400 rows. The 6 above is from pg_relation_size and agrees with relpages once the VACUUM ran properly.

What this means for the three sites

free_space only grows under an insert-only workload. After the retirement I inserted 1000 more rows into each of the 400 tables. Rows and bytes did not change (400 / 19603200 before and after). That is by design. columnar_write_state.c:3132 reuses freed space only when the writer holds ShareUpdateExclusiveLock, i.e. during online compaction. Plain inserts always append. So retirements accumulate until a compaction consumes them or end-truncation removes them.

It clears the 3-page threshold at a few hundred retired ranges across the whole catalog. The scan keys on storage_id, but its cost is the size of the whole heap. 800 ranges took 6 pages. I have not measured the exact crossover.

All three sites are maintenance paths, not query paths:

  :1047  PgColumnarAllocateFreeSpace          once per group an online compaction writes
  :1258  PgColumnarTrailingFreeSpaceSafe      end-truncation
  :1303  PgColumnarDeleteFreeSpaceAtOrAbove   end-truncation

So I am changing the row from not worth converting to reachable, low frequency. The catalog does grow past the threshold. :1047, the hot one of the three, runs once per group an online compaction writes, which makes it a full free_space scan per group written. A compaction of G groups against R free ranges is O(G·R). That is the only case where I would expect conversion to show up in a measurement, and I have not timed it.

What this does not cover: the cost of :1047 under a real compact_rewrite, and what happens at sizes beyond 400 tables.

fix: twelve metadata lookups read their whole catalog (#1207)

Merge pull request #1285 from OffgridwithJD/fix/1207-catalog-scans-by…

21 more catalog scans pass InvalidOid where their key is a prefix of an existing index #1207

Description

What

The 21

How it was derived, and why the method is the point

What this issue does NOT claim

Suggested next step

Activity

jdatcmd commented on Sep 22, 2026

Three sweeps, one total, three compositions

The method

The enumeration, by handle, on 6cb9f9a2

What is still not measured

jdatcmd commented on Sep 23, 2026

Which catalogs are touched by ordinary work

The DELETE cost is per row group, and it is linear

Attributed to one call site, with the arithmetic closing

So the 21 are not one population

jdatcmd commented on Sep 23, 2026

The compact path, fully attributed — and the site both enumerations missed

One source line is five runtime scan sites

The whole account, and why I trust it

free_space: closed, and it is the small half

What this does to the framing question

OffgridwithJD commented on Sep 23, 2026

jdatcmd commented on Sep 23, 2026

#1213 takes the nine sites, and what is left of this issue

What remains under this issue

9 remaining items

OffgridwithJD commented on Sep 25, 2026

The population as it stands today, after #1254, #1272 and #1277

Three things this table is not

My reading of the two script sites, since they surprised me

OffgridwithJD commented on Sep 26, 2026

The reconciliation: 13 static sites, 13 reached, no gap

The three that were unreached, and how they stopped being unreached

What this instrument cannot see, and it matters for the population

Two instrument failures, both fail-open, both caught by an implausible value

The binary is not the shipped one

What this does not yet decide

jdatcmd commented on Sep 26, 2026

Attacked both claims. Both hold, by a different method than the one that produced them

Claim 2, that each site opens what its source suggests: verified statically

One defect in the table: the rows do not name their file

Claim 1, that reachability is not a fixture accident: holds, and the thin row is thinner than the count suggests

On the lower bound

On the two fail-open probes

OffgridwithJD commented on Sep 26, 2026

Callers per site, which is the better evidence — and my first two attempts at it were wrong

My first two attempts produced zeros, and only @jdatcmd's number caught them

One correction to what @jdatcmd reported

jdatcmd commented on Sep 26, 2026

Correcting myself: the table was not defective, my source for it was

OffgridwithJD commented on Sep 27, 2026

How big these catalogs actually get, which orders the work and rules one group out

What this orders

Two corrections to my own numbers

One fixture that produced nothing, silently

jdatcmd commented on Sep 27, 2026

The shape that fills free_space, and why I think that row is wrong

Which is the trap you already found this morning, on this catalog

Two of your findings I want to keep

OffgridwithJD commented on Oct 12, 2026

You were right: my free_space row was the fixture. Corrected, with retirement asserted first

What this means for the three sites

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

The enumeration, by handle, on `6cb9f9a2`

The shape that fills `free_space`, and why I think that row is wrong

You were right: my `free_space` row was the fixture. Corrected, with retirement asserted first