Skip to content

foreignTables.retrieve by id returns the wrong table when more than one exists #1121

Description

@hsusul

Describe the bug

PostgresMeta.foreignTables.retrieve({ id }) (and GET /foreign-tables/:id) does not filter foreign tables by OID. idsFilter is computed and passed into the columns CTE, but not into FOREIGN_TABLES_SQL, so the query returns every foreign table and the handler takes data[0].

With two or more foreign tables, retrieve-by-id can return a different table than the requested ID. Columns for the requested OID may attach to the wrong row (often an empty columns array).

To Reproduce

// two foreign tables: foreign_table and foreign_table_2
const listed = await pgMeta.foreignTables.list({ includeColumns: false })
const second = listed.data.find((ft) => ft.name === 'foreign_table_2')
const retrieved = await pgMeta.foreignTables.retrieve({ id: second.id })
// retrieved.data.name === 'foreign_table'  (wrong)

Expected behavior

The foreign table whose id matches is returned, including its columns. Missing IDs still yield “Cannot find a foreign table with ID …”.

Views/tables/materialized views already pass idsFilter into their relation CTE.

Actual behavior

The first foreign table row is returned regardless of the requested id.

Additional context

FOREIGN_TABLES_SQL already supports idsFilter; generateEnrichedForeignTablesSql just never forwards it. Existing tests only retrieve by name, and the fixture has a single foreign table.

  • Library: @supabase/postgres-meta
  • Branch analyzed: master (641831e)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions