pg.ddx.io  GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
[pgjdbc/pgjdbc] issue #4533: getProcedures(), getFunctions() and getIndexInfo() return extra rows when an object in another catalog has the same OID
2+ messages / 2 participants
[nested] [flat]

* [pgjdbc/pgjdbc] issue #4533: getProcedures(), getFunctions() and getIndexInfo() return extra rows when an object in another catalog has the same OID
@ 2026-11-18 16:37  "byn-peter-mitteregger (@byn-peter-mitteregger)" <noreply+byn-peter-mitteregger@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: byn-peter-mitteregger (@byn-peter-mitteregger) @ 2026-11-18 16:37 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

**Describe the issue**

`DatabaseMetaData.getProcedures()`, `getFunctions()` and `getIndexInfo()` join `pg_catalog.pg_description` on `objoid` only. OIDs are unique only within one system catalog. When an object in another catalog has the same OID, each of its comments becomes one more result row, with that object's comment as `REMARKS`.

After `pg_upgrade` such collisions are common. `pg_upgrade` keeps the OIDs of `pg_class`, `pg_type`, `pg_enum`, `pg_authid`, tablespaces and databases, but functions and procedures are re-created in the new cluster, whose OID counter starts at 16384. So functions land in the same OID range as the existing tables.

We hit this after a PostgreSQL 16 → 18 upgrade. A procedure got the OID of a table with 26 comments (table and columns), and `getProcedures()` returned 26 rows for it. Spring's `SimpleJdbcCall` then failed with `Unable to determine the correct call signature - multiple signatures for '<name>': found [...] procedures`, so the procedure could no longer be called.

The same mistake was fixed for `getTables()` in #2245 (`d.classoid = 'pg_class'::regclass`).

**Driver Version?** 42.7.14 (also 42.7.13; `master` at 9c6eeaa has the same queries)

**Java Version?** 21.0.8

**OS Version?** Ubuntu 24.04 (WSL2)

**PostgreSQL Version?** 18.6. The join is the same for every server version.

**To Reproduce**

The collision can be produced without `pg_upgrade` by moving the OID counter back with `pg_resetwal -o`, because PostgreSQL only checks that an OID is unique within the target catalog. Use a throwaway cluster:

```sh
export PGHOST=/tmp PGPORT=54188 PGUSER=postgres
D=/tmp/oidrepro
initdb -D $D -U postgres -A trust
pg_ctl -D $D -o "-k /tmp" -l $D.log start
createdb test
psql -d test -v ON_ERROR_STOP=1 <<'SQL'
create table t_proc (a int, b int);
comment on table t_proc is 'table t_proc';
comment on column t_proc.a is 'column t_proc.a';
comment on column t_proc.b is 'column t_proc.b';
create table t_func (a int, b int);
comment on table t_func is 'table t_func';
comment on column t_func.a is 'column t_func.a';
comment on column t_func.b is 'column t_func.b';
create function f_idx() returns int language sql as 'select 1';
comment on function f_idx() is 'function f_idx';
create table t_idx (a int);
SQL

# with_oid <oid> <sql>: run <sql> with the OID counter set to <oid>
with_oid() {
  pg_ctl -D $D stop
  pg_resetwal -o "$1" $D
  pg_ctl -D $D -o "-k /tmp" -l $D.log start
  psql -d test -v ON_ERROR_STOP=1 -c "$2"
}
# procedure p_dup gets the OID of table t_proc
with_oid "$(psql -d test -Atc "select 't_proc'::regclass::oid")" \
  "create procedure p_dup() language sql as 'select 1'; comment on procedure p_dup() is 'procedure p_dup';"
# function f_dup gets the OID of table t_func
with_oid "$(psql -d test -Atc "select 't_func'::regclass::oid")" \
  "create function f_dup() returns int language sql as 'select 1'; comment on function f_dup() is 'function f_dup';"
# index i_dup gets the OID of function f_idx
with_oid "$(psql -d test -Atc "select 'f_idx()'::regprocedure::oid")" \
  "create index i_dup on t_idx (a); comment on index i_dup is 'index i_dup';"
```

Then run:

```java
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class OidCollisionRepro {
  public static void main(String[] args) throws Exception {
    try (Connection conn = DriverManager.getConnection(
        "jdbc:postgresql://localhost:54188/test?user=postgres")) {
      DatabaseMetaData md = conn.getMetaData();
      print("getProcedures(null, \"public\", \"p_dup\")",
          md.getProcedures(null, "public", "p_dup"), "PROCEDURE_NAME");
      print("getFunctions(null, \"public\", \"f_dup\")",
          md.getFunctions(null, "public", "f_dup"), "FUNCTION_NAME");
      print("getIndexInfo(null, \"public\", \"t_idx\", false, false)",
          md.getIndexInfo(null, "public", "t_idx", false, false), "INDEX_NAME");
    }
  }

  private static void print(String call, ResultSet rs, String nameColumn) throws SQLException {
    int rows = 0;
    StringBuilder sb = new StringBuilder();
    try (ResultSet r = rs) {
      while (r.next()) {
        rows++;
        sb.append("    ").append(r.getString(nameColumn))
            .append("  REMARKS=").append(r.getString("REMARKS")).append('\n');
      }
    }
    System.out.println(call + " -> " + rows + " row(s), expected 1");
    System.out.print(sb);
  }
}
```

**Expected behaviour**

One row for the procedure, one for the function and one for the index column, each with its own comment as `REMARKS`.

What actually happens, with 42.7.14 against 18.6:

```
getProcedures(null, "public", "p_dup") -> 4 row(s), expected 1
    p_dup  REMARKS=procedure p_dup
    p_dup  REMARKS=table t_proc
    p_dup  REMARKS=column t_proc.a
    p_dup  REMARKS=column t_proc.b
getFunctions(null, "public", "f_dup") -> 4 row(s), expected 1
    f_dup  REMARKS=function f_dup
    f_dup  REMARKS=table t_func
    f_dup  REMARKS=column t_func.a
    f_dup  REMARKS=column t_func.b
getIndexInfo(null, "public", "t_idx", false, false) -> 2 row(s), expected 1
    i_dup  REMARKS=function f_idx
    i_dup  REMARKS=index i_dup
```

**Cause**

- [`getProcedures()`](https://github.com/pgjdbc/pgjdbc/blob/9c6eeaab40b429bd5bc8030973ba49280a50f41c/pgjdbc/src/main/java/...): `LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid)`. The `classoid` check sits in the next join, `LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc')`, and because that is a left join it removes no rows.
- [`getIndexInfo()`](https://github.com/pgjdbc/pgjdbc/blob/9c6eeaab40b429bd5bc8030973ba49280a50f41c/pgjdbc/src/main/java/...): `LEFT JOIN pg_catalog.pg_description d ON (ci.oid = d.objoid)`
- [`getFunctions()`](https://github.com/pgjdbc/pgjdbc/blob/9c6eeaab40b429bd5bc8030973ba49280a50f41c/pgjdbc/src/main/java/...): `LEFT JOIN pg_catalog.pg_description d ON p.oid=d.objoid`

**Suggested fix**

Match `classoid` and `objsubid` in the join, as `getTables()` already does:

```diff
-          + " LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid) "
-          + " LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') "
-          + " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "
+          + " LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid "
+          + "   AND d.classoid='pg_catalog.pg_proc'::pg_catalog.regclass AND d.objsubid=0) "
```

The same goes for `getFunctions()` (`pg_proc`) and `getIndexInfo()` (`pg_class`). With these three changes, the program above returns exactly one row for each call, with the object's own comment.

^ permalink  raw  [nested|flat] 2+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #4533: getProcedures(), getFunctions() and getIndexInfo() return extra rows when an object in another catalog has the same OID
@ 2026-11-18 16:44  "davecramer (@davecramer)" <noreply+davecramer@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: davecramer (@davecramer) @ 2026-11-18 16:44 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

It would be great if you created a PR

^ permalink  raw  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-18 16:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-18 16:37 [pgjdbc/pgjdbc] issue #4533: getProcedures(), getFunctions() and getIndexInfo() return extra rows when an object in another catalog has the same OID "byn-peter-mitteregger (@byn-peter-mitteregger)" <noreply+byn-peter-mitteregger@github.com>
2026-11-18 16:44 Re: [pgjdbc/pgjdbc] issue #4533: getProcedures(), getFunctions() and getIndexInfo() return extra rows when an object in another catalog has the same OID "davecramer (@davecramer)" <noreply+davecramer@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox