MisterRaindrop commented on issue #1850:
URL: https://github.com/apache/cloudberry/issues/1850#issuecomment-4989193044
Thanks a lot for the detailed report, the screenshots, and for offering to
send a PR — that context made this much easier to dig into. 🙏
I tried to reproduce it and I think there's some good news: the
vacuum/autovacuum logic itself looks correct here, and I believe the failing
object is actually a *different* relation than the PXF foreign table. There's a
really easy trap in how the file path maps back to a table (I walked straight
into it myself while testing), so I wanted to share what I found in case it
helps you get unblocked faster.
## Short version
- `VACUUM`/autovacuum never process foreign tables — they're skipped by
`relkind = 'f'` in every relevant code path (confirmed in code, and empirically
with 12,000 PXF foreign tables present).
- The `could not open file "base/17279/103349"` message names the file by
its **`relfilenode`**, not by the table's **`oid`**. In Greenplum/Cloudberry
those two come from independent counters, so for user relations `relfilenode !=
oid`.
- That's the trap: looking up `WHERE oid = 103349` can land on a PXF foreign
table purely by coincidence, while the file `.../103349` actually belongs to a
*different* relation whose `relfilenode = 103349` — a regular
(heap/AO/matview/toast) table whose data file is missing on disk.
- So the underlying cause looks like a **catalog ↔ storage inconsistency**
(a `pg_class` entry whose data file isn't there) — the kind of thing a
migration/restore or a segment-recovery gap can leave behind. It doesn't seem
related to PXF.
For reference, I tested on a 3-segment `gpdemo` built from current `main`;
the vacuum/foreign-table code paths are identical between the
`2.1.0-incubating` tag and `main`, so this should apply to your 2.1.0.
## 1. Foreign tables aren't vacuumed
A `gp_exttable_server` foreign table has no storage:
```
relname=pxf_delta_hdfs_t_consent_audit | relkind=f | relam=0
relfilenode=0 | relfrozenxid=0 | reltoastrelid=0 | age=2147483647
```
The `relfrozenxid = 0` is what makes `age()` show `2147483647` (so it
*looks* like it urgently needs a wraparound vacuum), but that value is inert —
foreign tables are filtered out by `relkind` in all of:
`get_all_vacuum_rels()`, `do_autovacuum()`, the `vacuum_rel()` guard, and
`vac_update_datfrozenxid()` (so `datfrozenxid` isn't held back by them either).
`RelationInitPhysicalAddr()` also returns early for storage-less relations.
With **12,000** PXF foreign tables present:
```
=== bare VACUUM; with ~12k PXF foreign tables -> crash? ===
VACUUM >>> completed without error
=== VACUUM ANALYZE; ===
VACUUM >>> completed without error
```
And on the autovacuum side: 0 of the 12,000 foreign tables even appear in
`pg_stat_all_tables`, and with `log_autovacuum_min_duration=0` the logs only
ever show regular tables being processed — never a foreign table.
## 2. Where the error actually comes from
`could not open file "%s"` is raised in `mdopenfork()`
(`src/backend/storage/smgr/md.c`), reached during vacuum via:
```
vacuum_rel -> heap_vacuum_rel -> RelationGetNumberOfBlocks
-> smgrnblocks -> mdnblocks -> mdopenfork(EXTENSION_FAIL)
-> file missing -> ERROR: could not open file "base/<db>/<relfilenode>"
```
This is only reachable for relations that *have* storage (`relkind` in `r /
m / t / AO-aux`), so the failing object is a stored table with a missing data
file — never a foreign table.
## 3. The `oid` vs `relfilenode` bit (the easy trap)
The data file `base/<db>/<N>` is named by `relfilenode`, and in
GPDB/Cloudberry `relfilenode` is assigned from a counter separate from `oid`:
```
reg_heap: oid=16387 | relfilenode=16384 | filepath = base/5/16384
```
Because the two counters draw from overlapping ranges, one relation's `oid`
can equal a *different* relation's `relfilenode`. So `WHERE oid = <N>` doesn't
necessarily identify the file `base/<db>/<N>` — the reliable lookup is `WHERE
relfilenode = <N>`.
## 4. A reproduction that matches the report ("Every time")
I created a few regular tables alongside the 12k foreign tables and
simulated what a migration/restore gap leaves behind — a `pg_class` entry whose
data file never landed on disk:
```
mig_3: oid=60601 relfilenode=24585 file=base/5/24585 relkind=r
STEP 1: remove the coordinator data file base/5/24585 (the "gap")
STEP 2: bare VACUUM; three times
#1: ERROR: could not open file "base/5/24585": No such file or directory
#2: ERROR: could not open file "base/5/24585": No such file or directory
#3: ERROR: could not open file "base/5/24585": No such file or directory
STEP 3: only the broken table fails
VACUUM mig_1 (intact): OK
VACUUM mig_3 (gap): ERROR: could not open file "base/5/24585"
STEP 4: the same trap, reproduced (and it happened on its own)
WHERE oid = 24585 -> pxf_t_3 | relkind=f (a PXF foreign table!)
WHERE relfilenode = 24585 -> mig_3 | relkind=r (the actual culprit)
STEP 5: fixing the broken entry restores VACUUM
DROP TABLE mig_3; -> VACUUM; -> OK
```
This lines up with what you saw: `VACUUM;` fails the same way every time,
the number resolves to a foreign table by `oid` but to a broken regular table
by `relfilenode`, and clearing the broken entry makes `VACUUM` work again. (I
didn't engineer the `oid 24585 == foreign table` collision — it happened
naturally once there were ~12k foreign tables around, which is probably exactly
why the `oid` lookup was so convincing.)
## 5. Suggested next step on your side
Could you try the `relfilenode` lookup on the affected DB? That should point
at the real table:
```sql
-- the real relation behind the file (note: relfilenode, not oid)
SELECT oid, relname, relkind, relnamespace::regnamespace
FROM pg_class WHERE relfilenode = 103349;
-- check per segment (the file may be missing on only one)
SELECT gp_segment_id, relname, relkind
FROM gp_dist_random('pg_class') WHERE relfilenode = 103349
UNION ALL
SELECT -1, relname, relkind FROM pg_class WHERE relfilenode = 103349;
-- when it was last touched
SELECT * FROM pg_stat_last_operation
WHERE objid = (SELECT oid FROM pg_class WHERE relfilenode = 103349);
```
`gpcheckcat <db>` and checking whether `<datadir>/base/17279/103349` exists
on each segment/coordinator would also help confirm it. Once identified,
reloading that table from the source (or `DROP` + recreate from the source DDL)
should clear the error. Happy to help interpret the output if you paste it here.
## 6. On the high CPU
That part looks like a scale effect rather than foreign-table vacuuming:
with ~12k relations, every autovacuum cycle still scans the large `pg_class`,
and the PG14-era stats collector rewrites a `pgstat` file that grows with the
total relation count (a known cost before the shared-memory stats rework). The
foreign tables themselves aren't being processed.
## One idea worth discussing
If it'd be useful, database-wide `VACUUM` could be made a bit more forgiving
so that a single relation with a missing file logs a `WARNING` and is skipped,
instead of aborting the whole command — that way one bad table doesn't block
vacuuming everything else. Totally separate from the foreign-table question,
but might be a nice small robustness improvement if you're still keen to send a
PR. Happy to collaborate on it either way — thanks again for the great report!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
