Add check "Do not index arrays via btree - use gin instead"

[mfvanek]

See https://habr.com/ru/articles/800121/

SELECT
    c.relname,  -- наименование отношения
    ic.relname, -- наименование индекса
    a.amname    -- тип индекса 
FROM pg_catalog.pg_index AS i
	INNER JOIN pg_catalog.pg_class AS ic ON i.indexrelid = ic.oid
    INNER JOIN pg_catalog.pg_am AS a ON ic.relam = a.oid AND a.amname = 'btree'
    INNER JOIN pg_catalog.pg_class AS c ON i.indrelid = c.oid
WHERE
    -- проверяем существование в индексе колонки с типом массива
    EXISTS (SELECT * FROM pg_catalog.pg_attribute AS att
                INNER JOIN pg_catalog.pg_type AS typ ON typ.oid = att.atttypid
                WHERE att.attrelid = i.indrelid
                    AND att.attnum = ANY ((string_to_array(indkey::text, ' ')::int2[])[1:indnkeyatts])
                    AND typ.typcategory = 'A')

[mfaulther]

Hi, @mfvanek

May i take this issue ?

[mfvanek]

Hi @mfaulther,

May i take this issue ?

Yeah, sure, fell free to contribute.

P.S. Recently I updated contributing guide

[mfaulther]

@mfvanek
Found some problems with NamedParameterParser.
Placeholder substitution clashes with PostgreSQL array slicing, which is used in the SQL query for this task (as both use the symbol :).

This part of query (line 21):

and col.attnum = any((string_to_array(i.indkey::text, ' ')::int2[])[:i.indnkeyatts])

becomes:

and col.attnum = any((string_to_array(i.indkey::text, ' ')::int2[])[?.indnkeyatts])

which is not that we need

What do you think about adding support for escaping symbol \ ?

If i write:

and col.attnum = any((string_to_array(i.indkey::text, ' ')::int2[])[\:i.indnkeyatts])

symbol : will not be replaced with '?' and will remain as is:

and col.attnum = any((string_to_array(i.indkey::text, ' ')::int2[])[:i.indnkeyatts])

[mfvanek]

Hi @mfaulther,
Thank you for feedback.

What do you think about adding support for escaping symbol \ ?

I think we need to support arrays operations as it was done in Spring
https://github.com/spring-projects/spring-framework/blob/main/spring-jdbc/src/main/java/org/springframework/jdbc/core/namedparam/NamedParameterUtils.java

I'm going to fix it in 1-2 days.

[mfvanek]

@mfaulther
It turned out to be much easier than I expected.
Fix is already in the master branch.

[mfvanek]

Hi @mfaulther,
do you plan to send PRs to starter and demo apps?
If not I'll make it myself.
I'm going to release a new version this weekend.

[mfaulther]

Hi @mfvanek

do you plan to send PRs to starter and demo apps?

I'm going to do it this evening

[mfvanek]

I'm going to do it this evening

Cool! I'll wait for your PR

[mfvanek]

@mfaulther
I've just released pg-index-health version 0.11.0
Thank you very much for being part of it.

P.S. The next step is to release pg-index-health-test-starter

[mfvanek]

@mfaulther
Could you transfer your example from mfvanek/pg-index-health-demo#194 to https://github.com/mfvanek/pg-index-health-spring-boot-demo as well?