PostgreSQL protocol rewrites unqualified user tables starting with pg_ to pg_catalog

[discord9]

What type of bug is this?

Incorrect result

What subsystems are affected?

Frontend, Query Engine, Write Protocols

Minimal reproduce step

Using the PostgreSQL protocol, create/use a non-pg_catalog schema and a user table whose unqualified table name starts with pg_:

CREATE DATABASE postgres_protocol;
SET search_path TO 'postgres_protocol';
CREATE TABLE postgres_protocol.pg_compat_test(ts TIMESTAMP TIME INDEX, v INT);
INSERT INTO postgres_protocol.pg_compat_test VALUES ('2024-01-01 00:00:00', 7);

SHOW search_path;
SELECT current_schema();
SELECT v FROM pg_compat_test ORDER BY v;

What did you expect to see?

SHOW search_path / current_schema() report postgres_protocol, and the unqualified table reference should resolve via PostgreSQL search path semantics, so the final query should find postgres_protocol.pg_compat_test and return 7.

In PostgreSQL, pg_catalog is implicitly searched for system objects, but a user table named pg_compat_test in the active schema is not unconditionally rewritten to pg_catalog.pg_compat_test.

What did you see instead?

The final query fails because the compatibility parser rewrites the unqualified table name to pg_catalog.pg_compat_test before planning:

Table not found: greptime.pg_catalog.pg_compat_test

A fully-qualified query works:

SELECT v FROM postgres_protocol.pg_compat_test ORDER BY v;

What operating system did you use?

Linux x86_64

What version of GreptimeDB did you use?

Observed while validating current PR branch based on GreptimeDB v1.1.0/v1.1.1 compatibility binaries and current main-derived source.

Relevant log output and stack trace

The likely cause is the datafusion-pg-catalog compatibility rewrite rule PrependUnqualifiedPgTableName:

// datafusion-pg-catalog-0.17.0/src/sql/rules.rs
if args.is_none()
    && name.0.len() == 1
    && let ObjectNamePart::Identifier(ident) = &name.0[0]
    && ident.value.starts_with("pg_")
{
    *name = ObjectName(vec![
        ObjectNamePart::Identifier(Ident::new("pg_catalog")),
        name.0[0].clone(),
    ]);
}

The rule is intended to support PostgreSQL client introspection queries such as unqualified pg_class, pg_type, or pg_namespace, because PostgreSQL includes pg_catalog in the search path by default. However, using starts_with("pg_") is too broad and also catches user tables like pg_compat_test.

Possible fix: restrict the rewrite to a known allowlist of supported pg_catalog relation names, or implement proper PostgreSQL search path resolution for these compatibility objects.

Related but not exact duplicates:

• Unable to view table structure in db clients (e.g. dbeaver or table plus) #5271 tracks broader PostgreSQL client / pg_catalog compatibility.
• Unsupported search path expr pg_catalog in set variable statement #6152 tracked parsing SET search_path = pg_catalog.

[sunng87]

Yes the rewrite rule should be stricter for pg_ prefix.

[sunng87]

@discord9 can we use other table names so it will not block the runner development

[sunng87]

Ready in datafusion-pg-catalog 0.17.2