Index with expression is missing in UI although it exists in DB

[damucz]

Preconditions

• HeidiSQL version:12.6.0.6765 and 12.3.0.6589
• Database type and version: PostgreSQL 15.6 running from docker container postgres:15-alpine
• OS: Windows 11 64bit

Describe the bug

The index using expression is not visible in UI (Table/Indices). All other indices are shown. Index is shown using SQL query directly. I'd expect all the indices are in the list of the indices in table management UI.

To Reproduce

Minimal repro SQL:

DROP TABLE IF EXISTS A;

CREATE TABLE A (
	id INTEGER
);

DROP INDEX IF EXISTS idx;

CREATE INDEX idx ON A ((id+1));
-- CREATE INDEX idx1 ON A (id);   this index is visible
-- CREATE INDEX idx2 ON A ((id));   this index is visible, probably not an expression internally

Click on table A in the left list, select Table: A tab, select Indices (0).
There is no index in the list. There should be one - idx.

Actually any expression causes the index is not shown. Try (id+id).

Following correctly shows the single index idx:

select * from pg_indexes where tablename = 'a';

Output (copied from Heidi as delimited text):

schemaname;tablename;indexname;tablespace;indexdef
public;a;idx;\N;CREATE INDEX idx ON public.a USING btree (((id + 1)))

Screenshots or Crash reports

None.

[ansgarbecker]

HeidiSQL uses such a query to retrieve indexes of a table. Note only the simple indexes appear in the result. Probably you can help to change that query so it includes expression indexes:

WITH
    ndx_list AS (
        SELECT
            pg_index.indexrelid,
            pg_class.oid
        FROM
            pg_index,
            pg_class
        WHERE
            pg_class.relname = 'A'
            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
            )
        WHERE
            pg_table_is_visible(pg_class.oid)
    )
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
    );

[damucz]

This JOIN ndx_cols ON (a.attnum = ndx_cols.col_ndx) is used to get the columns on which the index is defined. The expression index has col_ndx = 0, which filters out everything as there is no specific column. Looks like 0 is special and is used to saying "we can't say which attribute/column it is". Althought I didn't check a documentation, there is no record with attnum = 0 in pg_attribute table, which makes sense.

Considering the above, SQL query to get the list should be rewritten to allow NULL as COLUMN_NAME. And then the GUI is required to take into consideration there can be no column for the index.

It should work for the indices, where an expression is combined with static columns. These work in current HeidiSQL, however you will see the static columns only under such index. Maybe you could show to user there is unspecified column. You should be able to get the expression from the Postgres as well (I could elaborate).

I've got query to get the list correctly, but it's completely different from yours (it's a side product from finding an issue). I'll try to modify your query later.

Note1: Auto-generated CREATE table code in GUI skips the expression index as well.

Note2: The relname is required to be lowercased. pg_class.relname = 'A' doesn't find the table, while pg_class.relname = 'A' does.

[damucz]

Following query works for me:

WITH
    ndx_list AS (
        SELECT
            pg_index.indexrelid,
            pg_class.oid
        FROM
            pg_index,
            pg_class
        WHERE
            pg_class.relname = 'a'
            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
            )
        WHERE
            pg_table_is_visible(pg_class.oid)
    )
SELECT
    ndx_cols.relname AS CONSTRAINT_NAME,
    ndx_cols.CONSTRAINT_TYPE,
    a.attname AS COLUMN_NAME
FROM
		ndx_cols
		LEFT JOIN pg_attribute a ON (a.attnum = ndx_cols.col_ndx)
		JOIN ndx_list ON (
				(ndx_list.oid = a.attrelid OR a.attrelid IS NULL)
				AND ndx_list.indexrelid = ndx_cols.oid
		);

It (left-) joins pg_attribute even when there is not column in pg_attribute. Please note there could be NULL in COLUMN_NAME now.

Diff from the current query:

Output for our real table, where exactly two indices contain expression. One of them consists of expression and three other static columns, while sessions_users_timeoutat contains just on expression only.

[ansgarbecker]

Thanks a lot @damucz !
That would definitely help at least for being able to show such indexes in the GUI. Downside would be that these have to be blocked for editing.
Do you probably know a way to obtain the expression of such indexes as well? If so, I could probably make them editable.

[damucz]

Good question. I tried briefly, but I just get a definition of whole index (CREATE INDEX...).

However DBeaver somehow manages to get it, so I'll look into it more.

[damucz]

WITH
    ndx_list AS (
        SELECT
            pg_index.indexrelid,
            pg_class.oid
        FROM
            pg_index,
            pg_class
        WHERE
            pg_class.relname = 'a'
            AND pg_class.oid = pg_index.indrelid
    )
SELECT
    pg_class.relname AS constraint_name,
    CASE i.indisprimary WHEN TRUE THEN 'PRIMARY' ELSE CASE i.indisunique WHEN TRUE THEN 'UNIQUE' ELSE 'KEY' END END AS CONSTRAINT_TYPE,
    pg_get_indexdef(i.indexrelid, T.ord::INT, TRUE) AS column_name
FROM
    pg_class
    JOIN pg_index i ON (pg_class.oid = i.indexrelid)
    JOIN ndx_list ON (
        pg_class.oid = ndx_list.indexrelid
    ),
    UNNEST(i.indkey) WITH ORDINALITY AS T (indkey,ord)
WHERE
    pg_table_is_visible(pg_class.oid);

This should work. pg_get_indexdef ( index oid [, column integer, pretty boolean ] ) → text returns the correct definition for column of the index (counted from 1). I took ordinality of the UNNEST as the column number. Since the output of the pg_get_indexdef can be used as column_name directly, I removed the third SELECT and any reference to pg_attribute as it is not used anymore.

Compatibility:
UNNEST WITH ORDINALITY should be available since Postgres 9.4. There are older versions covered on Stack Overflow..

Hope it helps and I didn't make a mistake. 🍀
And thank you for the amazing piece of software! ❤️

[damucz]

Same query as above, but using ROW_NUMBER instead of WITH ORDINALITY:

WITH
    ndx_list AS (
        SELECT
            pg_index.indexrelid,
            pg_class.oid
        FROM
            pg_index,
            pg_class
        WHERE
            pg_class.relname = 'a'
            AND pg_class.oid = pg_index.indrelid
    )
SELECT
    pg_class.relname AS constraint_name,
    CASE i.indisprimary WHEN TRUE THEN 'PRIMARY' ELSE CASE i.indisunique WHEN TRUE THEN 'UNIQUE' ELSE 'KEY' END END AS CONSTRAINT_TYPE,
    pg_get_indexdef(i.indexrelid, (ROW_NUMBER() OVER (PARTITION BY pg_class.relname))::int, TRUE) AS COLUMN_NAME
FROM
    pg_class
    JOIN pg_index i ON (pg_class.oid = i.indexrelid)
    JOIN ndx_list ON (
        pg_class.oid = ndx_list.indexrelid
    ),
    UNNEST(i.indkey)
WHERE
    pg_table_is_visible(pg_class.oid);