[42P18] ERROR: malformed array literal: "hash"

[GrandFelix]

Im running 9.6 and Im getting below issue:

[22P02] ERROR: malformed array literal: "hash" Detail: Array value must start with "{" or dimension information. Where: PL/pgSQL function pg_qualstats_index_advisor(integer,integer,text[]) line 27 at assignment

if I fix mentioned line like

IF pg_catalog.current_setting('server_version_num')::bigint < 100000 THEN
        forbidden_am := forbidden_am || ARRAY['hash'];
END IF;

I get [22023] ERROR: cannot extract elements from a scalar

any tip ?

[rjuju]

Thanks for the report! I indeed did a poor job with regards to older version compatibility. I just pushed https://github.com/powa-team/pg_qualstats/commit/1c7aa0e7f368e80b518950d9d1ad5e504f6ceeaa which should fix this problem, can you confirm that the advisor is working as expected after that?

Note that while testing against some older versions, I realize that using jsonb breaks the compatibility with postgres 9.3. I'll try to see if I can switch back to json to avoid that. There's also another issue in the regression tests due to the ^@ operator that doesn't exist in all supported versions, I'll also fix that.

[GrandFelix]

Hi. No problem! thanks for your work!

I have tried it and I still get [22023] ERROR: cannot extract elements from a scalar

[rjuju]

Thanks!

What is the exact query you're using? It looks like you're using something like:

SELECT v
  FROM jsonb_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
  ORDER BY v::text COLLATE "C";

and if no index is suggested, this will error out. I pushed https://github.com/powa-team/pg_qualstats/commit/0732013223040898f326bdf754b25771c015a510 so that an empty array is returned rather than null so the query shouldn't fail anymore.

[rjuju]

And I found another bug leading to no result being returned when all found predicates can be optimized, fixed in last commit. You're more likely to get results now.

[GrandFelix]

Hi. Now it works! Instead of using jsonb_array_elements I must use json_array_elements because it's not returning jsonb anymore. And yes, Im using exact same query as you have posted above :)

Thank you!

[rjuju]

Great! Yes, I switched to json to avoid breaking compatibility with pg 9.3, I should have mentioned that.

Thanks a lot for the report!