db error: ERROR: column ix.indnkeyatts does not exist

[uxoof]

Dear amazing Martin team,

failed start martin. with error message:

[2024-03-05T02:24:14Z INFO  martin] Starting Martin v0.13.0
[2024-03-05T02:24:14Z INFO  martin] Config file is not specified, auto-detecting sources
[2024-03-05T02:24:14Z INFO  martin::config] Initializing main cache with maximum size 536870912B
[2024-03-05T02:24:14Z INFO  martin::pg::pool] Connecting with SSL support: Config { user: Some("postgres"), password: Some(_), dbname: Some("petadb"), options: None, application_name: None, ssl_mode: Prefer, host: [Tcp("XX.XX.XX.XX")], hostaddr: [], port: [5432], connect_timeout: None, tcp_user_timeout: None, keepalives: true, keepalives_idle: 7200s, keepalives_interval: None, keepalives_retries: None, target_session_attrs: Any, channel_binding: Prefer }
[2024-03-05T02:24:14Z ERROR martin] Postgres error while querying available tables: db error: ERROR: column ix.indnkeyatts does not exist
    HINT: Perhaps you meant to reference the column "ix.indnatts".

my postgres version 9.6.11

[sharkAndshark]

Hi @uxoof You may need to upgrade postgres to 11.x
Martin use query_available_tables.sql to query avaliable table source.

...
JOIN pg_index ix on
                    ix.indrelid = class.oid and
                    ix.indnkeyatts = 1 and -- consider single column indices only
                    attr.attnum = ix.indkey[0]
...

But the pg_index.indnkeyatts was introduced from postgres 11.0. We may need to update our document or support old postgres versions. @nyurik

[sharkAndshark]

• pg_index doc of postgres 9.6
• pg_index doc of postgres 10.x
• pg_index doc of postgres 11.x

[uxoof]

thanks for your advice, @sharkAndshark . for now, i choose to build martin from git and change "ix.indnkeyatts" with "ix.indnatts" . it works well.

Hopely, next martin releases support PostgreSQL 9.6.

[nyurik]

@uxoof granted PG 9.6 was very popular, but you do realize there has been 7 more versions released since then, it has been 8 years since it was first released, and 3 years since it received last update? It might simply be not safe to run due to potential security bugs. See PG versions. I don't mind supporting PG 9.6 if there is a simple enough SQL fix that would support it, but it would be increasingly difficult to support it going forward.

[nyurik]

P.S. One important thing we should do is detect PG version and report issues if it is too old. We already do that for postgis, but not for postgresql itself. Tracking in #1230

[sharkAndshark]

It would be beneficial if we could extend support to PostgreSQL 9.6 with minor SQL modifications. However, we should not guarantee that in our documentation.

Unable to locate version 9.6 in the Support Matrix.

PostgreSQL version	PostGIS 2.5 EOL	PostGIS 3.0	PostGIS 3.1	PostGIS 3.2	PostGIS 3.3	PostGIS 3.4	PostGIS 3.5
PostGIS release date	2018/09/23	2019/10/20	2020/12/18	2021/12/18	2022/08/27	2023/08/15	2024/xx/xx
16	No	No	No	No	Yes	Yes	Yes
15	No	No	No	Yes	Yes	Yes	Yes
14	No	No	Yes	Yes	Yes	Yes	Yes
13	No	Yes	Yes	Yes	Yes	Yes	Yes
12	Yes	Yes	Yes	Yes	Yes	Yes	No
11 EOL	Yes	Yes	Yes	Yes	Yes	No	No