Bug: Postgres index columns not shown in proper order

Summary:

HeidiSQL currently shows only CONSTRAINT indexes in table columns order.

Postgres composite keys and indexes are not necessarily defined in the same order as the columns are defined in a given table. This leads to problems when writing SQL to take advantage of an index, but referring to the output the "Indexes" tab for the table.

Not all indices are shown - I believe it shows CONSTRAINTs but not additionally defined indices.

Additionally, the Indexes tab may list two PRIMARY KEY indexes, if the columns contained by the primary key overlap the UNIQUE key.

Steps to reproduce:

Use the following SQL to create a table, and look at the output of the Indexes tab.

Expect to see:

Three indexes listed, one for each of the Primary, Unique, and normal index, with columns in the proper order.

Actually see:

Two indexes listed (Primary and Unique), with columns in wrong order and Primary listed twice.

SQL:

CREATE TABLE public.test_indices_table
(
col_1 integer NOT NULL, 
col_2 character varying(128) NOT NULL, 
col_3 text, 
col_4 integer NOT NULL, 
col_5 timestamp(0) with time zone NOT NULL, 
col_6 integer NOT NULL, 
CONSTRAINT test_indices_ordered_pk PRIMARY KEY (col_6, col_1, col_2), 
CONSTRAINT test_indices_unique UNIQUE (col_4, col_3)
) 
WITH (
OIDS = FALSE
);
CREATE INDEX test_indices_normal_ndx
ON public.test_indices_table
USING btree
(col_5 DESC NULLS LAST);

To fix:

Use the following queries, at your discretion, to populate the Indexes tab.

-- List all indices for a PG table

-- note 'indkey' lists which column, in that order, comprise the index

WITH ndx_list AS
(
SELECT pg_index.indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname = 'test_indices_table'
AND pg_class.oid = pg_index.indrelid
)
SELECT pg_class.relname AS index_name, i.indisunique, i.indisprimary, i.indkey
FROM pg_class, pg_index i
WHERE pg_class.oid = i.indexrelid
AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)

-- List all indidces with all column info, in proper order

WITH ndx_list AS
(
SELECT pg_index.indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname = 'test_indices_table'
AND pg_class.oid = pg_index.indrelid
), ndx_cols AS
(
SELECT pg_class.relname AS index_name, UNNEST(i.indkey) AS col_ndx, i.indisunique, i.indisprimary
FROM pg_class, pg_index i
WHERE pg_class.oid = i.indexrelid
AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)
)
SELECT ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary,
a.attname, format_type(a.atttypid, a.atttypmod), a.attnum
FROM pg_class c, pg_attribute a
JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
WHERE c.oid = 'test_indices_table'::regclass
AND a.attrelid = c.oid

-- list all indices, collapse the full column_names to a list

WITH ndx_list AS
(
SELECT pg_index.indexrelid
FROM pg_index, pg_class
WHERE pg_class.relname = 'test_indices_table'
AND pg_class.oid = pg_index.indrelid
), ndx_cols AS
(
SELECT pg_class.relname AS index_name, UNNEST(i.indkey) AS col_ndx, i.indisunique, i.indisprimary
FROM pg_class, pg_index i
WHERE pg_class.oid = i.indexrelid
AND pg_class.oid IN (SELECT indexrelid FROM ndx_list)
)
SELECT ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary,
array_to_string(array_agg(a.attname), ', ') as ndx_column_names
FROM pg_class c, pg_attribute a
JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
WHERE c.oid = 'test_indices_table'::regclass
AND a.attrelid = c.oid
GROUP BY ndx_cols.index_name, ndx_cols.indisunique, ndx_cols.indisprimary

-- unrelated, but useful:

-- Get just the list of columns in a table,

--   incl. column types (even if Postgres custom defined type)

  SELECT a.attnum, a.attname, 
format_type(a.atttypid, a.atttypmod)
FROM pg_class c, pg_attribute a
WHERE c.oid = 'test_indices_table'::regclass
AND a.attrelid = c.oid
AND a.attnum > 0
ORDER BY a.attnum

Thanks for the detailed fix proposals.

r4910 should fix the order of columns and non-displayed normal indexes. But there are other bugs now, wrt the index name. I will have to spend some more time on that.

The above "WITH ndx_list.." query seems to fail on some systems. See this thread for a user report.

The exact query which failed on his 9.3 server:

WITH ndx_list AS (SELECT pg_index.indexrelid FROM pg_index, pg_class
WHERE
pg_class.relname='XPUserSettingsAspect'
AND pg_class.oid = pg_index.indrelid), ndx_cols AS
(SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, CASE i.indisprimary WHEN true THEN 'PRIMARY' ELSE CASE i.indisunique WHEN true THEN 'UNIQUE' ELSE 'KEY' END END AS CONSTRAINT_TYPE FROM pg_class, pg_index i WHERE pg_class.oid=i.indexrelid AND pg_class.oid IN (SELECT indexrelid FROM ndx_list) ) SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE, a.attname AS COLUMN_NAME FROM pg_class c, pg_attribute a JOIN ndx_cols ON (a.attnum=ndx_cols.col_ndx)
WHERE c.oid='XPUserSettingsAspect'::regclass
AND a.attrelid=c.oid;

ERROR: relation "xpusersettingsaspect" does not exist

I see - thanks for including the error message, which is what tipped me off.

Postgres implicitly lowercases all tablenames, unless they're specifically surrounded in double quotes. I don't know the inner working of the '::regclass' cast, but I can't seem to get it to work with the mixed case table name. (It always gives an error.)

I modified the query so that the needed oids are passed from the previous CTE expressions, thus eliminating the need for the use of the ::regclass cast.

This query seems to give me the same output on my existing tables, and also worked on a test table I created with a mixed case name.

(The JOIN changes in the second CTE are optional - I used those just for my own clarity while debugging, but they're functionally equivalent to what was there before.)

WITH ndx_list AS (
SELECT pg_index.indexrelid, pg_class.oid
FROM pg_index, pg_class
WHERE pg_class.relname = 'XPUserSettingsAspect'
AND pg_class.oid = pg_index.indrelid
),
ndx_cols AS
(
SELECT pg_class.relname, UNNEST(i.indkey) AS col_ndx, 
CASE i.indisprimary
WHEN true
THEN 'PRIMARY'
ELSE
CASE i.indisunique
WHEN true THEN 'UNIQUE'
ELSE 'KEY'
END 
END AS CONSTRAINT_TYPE,
pg_class.oid
FROM pg_class
JOIN pg_index i ON (pg_class.oid = i.indexrelid)
JOIN ndx_list ON (pg_class.oid = ndx_list.indexrelid)
) 
SELECT ndx_cols.relname AS CONSTRAINT_NAME, ndx_cols.CONSTRAINT_TYPE,
a.attname AS COLUMN_NAME
FROM pg_attribute a
JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx)
JOIN ndx_list ON (ndx_list.oid = a.attrelid AND ndx_list.indexrelid = ndx_cols.oid)

Thanks again! Your query works fine here. I have put it into r4925 and we'll see whether it also works for vemotion.