[Bug] auvacuum worker using not correct logic and trying to vacuum PXF foreghn tables located on Hadoop

[vsbace]

Apache Cloudberry version

We are trying to test Apache Cloudberry 2.1.0 on Astra Linux 1.8.1 (Debian 12 based)

What happened

Hello,
We found the next problem with auvacuum worker using not correct logic and trying to vacuum PXF foreghn tables located on Hadoop.
It is caused to high CPU usage by autovacuum workers on database with high coung of PXF tables

In Cloudberry master log we can see many messages by the next screenshoot we can see

When we can run vacuum on problem database, vacuum crashed by the next error

And we can identify this object as PXF foreighn table

What you think should happen instead

And i think correct about vacuum logic can not work with foreighn tables and in Greenplum logic means not use PXF tables too in this mechanizm.

How to reproduce

I think need to create couple of PXF tables on clean DB.

Operating System

Astra Linux 1.8.1 (Debian 12 based)

Anything else

Every time.

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[tuhaihe]

Maybe @ostinru and @MisterRaindrop need to take a look here.

[MisterRaindrop]

Thanks for reporting this, could you share a bit more information?

1. Version & Environment

• Exact Cloudberry version / commit hash
• OS version

2. Full error output

• The complete autovacuum error messages from the master log (a few lines of surrounding context would be really helpful, rather than a single line)
• If there's a crash / core dump, a gdb backtrace (bt / thread apply all bt) would be great

3. Reproduction details

• Minimal steps to reproduce (how many PXF tables, and the DDL used to create them)
• Is it consistently reproducible, or intermittent?

4. Table metadata (run against the affected database)
   SELECT relname, relkind, relfrozenxid, age(relfrozenxid)
   FROM pg_class
   WHERE relname = '<table_name>';

• Whether these PXF tables are standard foreign tables or go through gp_exttable_fdw (\d+ <table_name>, or check pg_foreign_table / pg_foreign_server)

5. Autovacuum behavior

• If possible, set log_autovacuum_min_duration = 0 and share the log showing which tables autovacuum actually selects and processes — this would confirm it's indeed acting on the foreign tables

[vsbace]

I found a good document about vcauum in Greenplum 7
https://blogs.vmware.com/tanzu/autovacuum-tuning-in-gpdb7/
And i see some warrings about using autovacuum on all tables including AOT tables.
"GPDB7 Autovacuum for Catalog Tables
The autovacuum’s purpose is to automate VACUUM and ANALYZE executions. In previous versions of GPDB, autovacuum is only enabled in template1 to prevent xid wraparound. It is disabled for user tables due to performance issues (more on this later).

Vacuum in gpdb is not a distributed function. Autovacuum is triggered locally on segments and each segment is unaware of other segment’s vacuum status. GPDB7 only enables auto vacuum on catalog tables. The reason for this is that the catalog tables are evenly distributed and individual updates are small. There’s no potential data skew that could result in one segment vacuum process taking much longer and blocking transactions for the whole cluster. Each individual segment’s catalog table can be updated without causing cluster-wide stalling.

On a distributed system, enabling autovacuum for user tables is tricky. Distributed transactions can be stalled by autovacuum running on a user table on a single segment. This means that autovacuuming a user table on a single segment can stall the operations for all of GPBD. This is likely further exacerbated with increasing amount of segments. The experiments of enabling autovacuum in user tables showed an unacceptable level of performance degradation due to desynchronized autovacuums. The autovacuums on different segments would take turns stalling the cluster. This resulted in a laggy or jittery cluster. For this reason, autovacuum on user tables is not enabled in GPDB.

Why not synchronize the autovacuums? This would theoretically prevent major performance regressions in a system where the all tables have similar skew and activity, but what about in other situations? There may would be other potential issues that may be introduced such as keeping the code aligned with upstream Postgres, complexity, or issues with corner cases. We will want to understand and be more certain of the benefits and ramifactions of making vacuum distributed."

[vsbace]

Hi

Thanks for reporting this, could you share a bit more information?

1. Version & Environment

• Exact Cloudberry version / commit hash
• OS version

2. Full error output

• The complete autovacuum error messages from the master log (a few lines of surrounding context would be really helpful, rather than a single line)
• If there's a crash / core dump, a gdb backtrace (bt / thread apply all bt) would be great

Avtovacuum working without errors.
Errors was happen when i tried to run command VACUUM without parameters on problem database.

3. Reproduction details

• Minimal steps to reproduce (how many PXF tables, and the DDL used to create them)
• Is it consistently reproducible, or intermittent?
  It is consistently reproducible.

F TABLE DDL
"CREATE FOREIGN TABLE cbv1.pxf_delta_hdfs_t_consent_audit (
"SN" numeric(5,0),
"S_ID" numeric(38,0),
"EN_TS" timestamp without time zone,
"EN_REASON" numeric(5,0),
"FUNCTION_NUMBER" numeric(5,0),
"CHECK_RESULT" numeric(5,0),
"CS_FLAG" text,
"DATE_CS_GIVEN" timestamp without time zone,
"DATE_CS_EXPIRY" timestamp without time zone,
"CS_POSE" text,
"CS_POSE_DETAILS" text,
"CS_BILITY_FLAG" text,
"CS_USR_DETAILS" text,
"MD5HASH" bytea,
"DELETION_MARK" numeric(1,0),
"VALID_FROM_DATE" timestamp without time zone,
"VALID_TO_DATE" timestamp without time zone,
"SOURCE_SYSTEM" text,
"PYD" numeric(38,0)
)
SERVER gp_exttable_server
OPTIONS (
encoding '6', format_type 'b',
execute_on 'ALL_SEGMENTS',
format 'custom',
formatter 'pxfwritable_import',
is_writable 'false',
location_uris 'pxf:///hdfs/astore/t_cs_audit?PROFILE=hdfs&SERVER=default',
log_errors 'disable'
);"

4. Table metadata (run against the affected database)
   SELECT relname, relkind, relfrozenxid, age(relfrozenxid)
   FROM pg_class
   WHERE relname = '<table_name>';

• Whether these PXF tables are standard foreign tables or go through gp_exttable_fdw (\d+ <table_name>, or check pg_foreign_table / pg_foreign_server)

5. Autovacuum behavior

• If possible, set log_autovacuum_min_duration = 0 and share the log showing which tables autovacuum actually selects and processes — this would confirm it's indeed acting on the foreign tables
  Log output is very high.

[MisterRaindrop]

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:

-- 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!

[vsbace]

Hi, thanks a lot for the detailed answer and fast answer.

1. I have tryed to recreated problem PXf table and did VACUUM has finished succefully after that.
   Thank you we solved this problem.

2. About HIGH CPU usage by autovacuum workers, yes you are right, from detailed logs i can see only tables processing in loop by this worker, but i checked this tables an all this tables has no rows and empty.

This is part for CBDB master log:

And all aggressive vacuuming tables it is a toast tables of AOT tables.

Yes we have a lot of tables in current database.

[vsbace]

Additional information.
I tryed to do VACUUM twice on all tables and after that problem still exists.

[MisterRaindrop]

For the high-CPU part, I reproduced it locally — and the vacuum/autovacuum logic is actually correct; the loop is driven by a held-back OldestXmin, with AO tables just making it visible.

Root cause. AO tables keep relfrozenxid=0 by design, so only their small heap relations (the TOAST and pg_aoseg/pg_aovisimap/pg_aoblkdir aux tables) carry XIDs. An anti-wraparound vacuum can only advance relfrozenxid up to OldestXmin. If OldestXmin is pinned older than recentXid − autovacuum_freeze_max_age, every vacuum sets relfrozenxid to that old value, the age is still over the limit, and autovacuum re-selects it forever — manual VACUUM can't move past OldestXmin either. With many AO tables their toast/aux heaps get swept continuously → the CPU you saw (datfrozenxid is pinned too).

Reproduced (3-seg cluster from main): empty AO tables + one idle BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT 1; to pin OldestXmin, then advanced XIDs past autovacuum_freeze_max_age → ~34 "aggressive vacuum to prevent wraparound of …pg_toast_…"/sec, cutoff … far in the past WARNING firing repeatedly, VACUUM unable to advance the toast relfrozenxid. Terminating the holder session immediately stopped the loop.

Fix = find what's holding OldestXmin back (~200M+ XIDs: an orphaned prepared/2PC txn, a long/idle-in-transaction backend, or a stale replication slot). On the node reporting oldest xmin: 360673:

SELECT * FROM pg_prepared_xacts ORDER BY prepared;
SELECT pid, state, age(backend_xmin) AS xmin_age, xact_start, query
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start;
SELECT slot_name, age(xmin), age(catalog_xmin) FROM pg_replication_slots;

Clear the offender → the next vacuum advances the horizon and the sweeps stop. Cloudberry already flags this with the cutoff for removing and freezing tuples is far in the past WARNING.