talmacschen-arch opened a new issue, #1726:
URL: https://github.com/apache/cloudberry/issues/1726
### Apache Cloudberry version
main (also reproduces on 1.x). Verified against current `main` at commit
`ef0b6fb3462`.
### What happened
When defining a foreign table via the FDW path with a symbolic encoding name
in `OPTIONS`:
```sql
CREATE FOREIGN TABLE ext_t (c text)
SERVER gp_exttable_server
OPTIONS (
formatter 'pxfwritable_import',
format 'custom',
format_type 'b',
location_uris 'pxf://t?PROFILE=jdbc&SERVER=s',
encoding 'UTF8'
);
```
the DDL succeeds, but the encoding actually stored on the foreign table is
**`SQL_ASCII` (0)**, not `UTF8` (`PG_UTF8 = 6`).
Root cause — both the FDW catalog reader and the option validator parse
`encoding` with `atoi()`:
- `src/backend/access/external/external.c:280`
```c
if (pg_strcasecmp(def->defname, "encoding") == 0)
{
extentry->encoding = atoi(defGetString(def)); /* 'UTF8' → 0 */
...
}
```
- `gpcontrib/gp_exttable_fdw/option.c:136-143`
```c
else if (pg_strcasecmp(def->defname, "encoding") == 0)
{
char *encoding = (char *) defGetString(def);
if (!PG_VALID_ENCODING(atoi(encoding))) /* atoi('UTF8') == 0
== SQL_ASCII, passes */
ereport(ERROR, ...);
}
```
`atoi("UTF8")` returns `0`, and `PG_VALID_ENCODING(0)` is true
(`PG_SQL_ASCII`), so validation silently passes. Anyone who writes `encoding
'UTF8'`, `encoding 'utf-8'`, `encoding 'GBK'`, etc. ends up with `SQL_ASCII`.
Confirm with:
```sql
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid = 'ext_t'::regclass;
-- {... ,encoding=UTF8, ...} -- stored verbatim; atoi() at read time → 0
```
Downstream consequences:
1. On the PXF binary path (`format_type='b'`), the PXF server emits UTF-8
bytes via `String.getBytes(StandardCharsets.UTF_8)`. The segment's gpdbwritable
formatter checks the foreign-table encoding and rejects anything other than
UTF-8, surfacing as:
> `gpdbwritable formatter can only import UTF8 formatted data. Define the
external table with ENCODING UTF8...`
The error message is misleading because the user's DDL **does** say
`ENCODING UTF8` — but it has been silently turned into `SQL_ASCII` by `atoi()`.
2. On other formats and protocols, the misconfiguration is silent:
multi-byte text gets stored as opaque bytes, and `length()` / `substring()` /
`LIKE` / downstream re-encoding misbehave only when CJK or accented characters
appear.
By contrast, the legacy `CREATE EXTERNAL TABLE ... ENCODING 'UTF8'` path is
fine because `src/backend/commands/exttablecmds.c:209-216` resolves the name
with `pg_char_to_encoding()` before persisting it as a numeric string into
OPTIONS. Only the FDW OPTIONS entry point bypasses that translation.
### What you think should happen instead
`encoding` in FDW `OPTIONS` should accept the same forms accepted everywhere
else in Postgres / Cloudberry — symbolic names (`UTF8`, `GBK`, `LATIN1`, …)
**and** numeric IDs — and reject everything else with a clear error. Symbolic
names should be resolved via `pg_char_to_encoding()`, just like the legacy
`CREATE EXTERNAL TABLE` path.
Specifically:
| OPTIONS spelling | Today | Expected
|
| --------------------- | ------------------------------------ |
--------------------------------- |
| `encoding '6'` | UTF8 ✅ | UTF8 ✅
(unchanged) |
| `encoding 'UTF8'` | SQL_ASCII ❌ (silent) | UTF8 ✅
|
| `encoding 'utf-8'` | SQL_ASCII ❌ (silent) | UTF8 ✅
|
| `encoding 'GBK'` | SQL_ASCII ❌ (silent) | GBK ✅
|
| `encoding 'bogus'` | SQL_ASCII ❌ (silent) | ERROR —
unrecognized encoding ✅ |
To keep on-disk OPTIONS stable and avoid silently changing the meaning of
already-stored foreign tables on upgrade, the resolved value should be
normalized to a numeric string before being persisted into
`pg_foreign_table.ftoptions` — mirroring `exttablecmds.c:894`. The read paths
(which currently use `atoi`) then continue to work unchanged.
### How to reproduce
```sql
CREATE EXTENSION IF NOT EXISTS gp_exttable_fdw;
CREATE FOREIGN TABLE ext_utf8 (c text)
SERVER gp_exttable_server
OPTIONS (
format 'text',
format_type 't',
location_uris 'file:///tmp/x.txt',
encoding 'UTF8'
);
-- Bug: encoding is stored verbatim and atoi('UTF8')==0 → SQL_ASCII
SELECT ftoptions FROM pg_foreign_table WHERE ftrelid = 'ext_utf8'::regclass;
SELECT e.encoding, pg_encoding_to_char(e.encoding)
FROM pg_exttable e
JOIN pg_class c ON c.oid = e.reloid
WHERE c.relname = 'ext_utf8';
-- encoding | pg_encoding_to_char
-- ----------+---------------------
-- 0 | SQL_ASCII
```
The same misbehavior reproduces on the PXF FDW path; in that case the
gpdbwritable formatter raises the misleading `Define the external table with
ENCODING UTF8` error at SELECT time.
### Operating System
Linux 5.14.0-503.38.1.el9_5.x86_64 (Rocky / RHEL 9.5). Issue is
OS-independent.
### Anything else
- Affected files: `src/backend/access/external/external.c:278-283`,
`gpcontrib/gp_exttable_fdw/option.c:136-143`. The `pxf_fdw` path inherits the
same broken `extentry->encoding` value.
- The runtime "Define the external table with ENCODING UTF8" message lives
in the separate `apache/cloudberry-pxf-server` repository (gpdbwritable
formatter, Java); a follow-up issue/PR will land there to point users at the
OPTIONS clause once the root cause here is fixed.
- `pg_char_to_encoding()` is already declared in
`src/include/mb/pg_wchar.h:567` and used by the legacy `CREATE EXTERNAL TABLE`
path, so no new dependency is needed.
### Are you willing to submit PR?
Yes — a follow-up PR is in preparation.
--
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]
