The /indexes query builds index_attributes by joining pg_attribute against the index relation but filtering it with the table's column numbers:
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(idx.indkey)
Since a.attrelid = c.oid is the index relation, a.attnum runs 1..N in index-key order, but idx.indkey holds the underlying table's column numbers. Those only line up when the index sits on the table's first column(s) in order. For anything else the join drops columns or matches nothing at all.
Repro:
create table public.t (id int, name text, email text, age int);
create index t_email_name on public.t (email, name);
create index t_age on public.t (age);
GET /indexes returns t_email_name with only name in index_attributes; email is gone.
t_age is missing from the list entirely, and GET /indexes/:id for it returns "Cannot find a index with ID".
Expression and partial indexes on non-leading columns disappear the same way. The current test only covers users_pkey (on id, the first column), which happens to be the one case where the index attnum equals the table attnum, so the bug never shows up.
The index relation's own pg_attribute rows are already exactly the index columns in index order, so filtering on a.attnum > 0 (like columns.sql.ts does) returns the right attributes.
The
/indexesquery buildsindex_attributesby joiningpg_attributeagainst the index relation but filtering it with the table's column numbers:Since
a.attrelid = c.oidis the index relation,a.attnumruns 1..N in index-key order, butidx.indkeyholds the underlying table's column numbers. Those only line up when the index sits on the table's first column(s) in order. For anything else the join drops columns or matches nothing at all.Repro:
GET /indexesreturnst_email_namewith onlynameinindex_attributes;emailis gone.t_ageis missing from the list entirely, andGET /indexes/:idfor it returns "Cannot find a index with ID".Expression and partial indexes on non-leading columns disappear the same way. The current test only covers
users_pkey(onid, the first column), which happens to be the one case where the index attnum equals the table attnum, so the bug never shows up.The index relation's own
pg_attributerows are already exactly the index columns in index order, so filtering ona.attnum > 0(likecolumns.sql.tsdoes) returns the right attributes.