PgHero.capture_space_stats fails with "No unique index found for #{name_or_columns}"

[thisIsLoading]

Hi,

i was trying to capture space stats but this fails with:

irb(main):001> PgHero.capture_space_stats
   (358.5ms)  SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN 'r' THEN 'table' WHEN 'm' then 'matview' ELSE 'index' END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND c.relkind IN ('r', 'm', 'i') ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/
ArgumentError: No unique index found for id

(irb):1:in `<main>': No unique index found for id (ArgumentError)

          raise ArgumentError, "No unique index found for #{name_or_columns}"
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
irb(main):002> 

pghero is install via

gem 'pghero', github: 'ankane/pghero'

i could imagine that the error might be related to the fact that i am using timescaldb hypertables (which dont have an ID column)?

is there a way to still get those stats?

thanks

[ankane]

Hi @thisIsLoading, I'm not really sure how to reproduce the error (the method works fine if I remove the id column from pghero_space_stats), but in general, using a hypertable to store space stats isn't supported.

[thisIsLoading]

hi @ankane maybe you misunderstood. i do not store the stats in a hypertable. the rails db just happens to have a hypertable.

i installed it exactly like you pointed out in the readme

[thisIsLoading]

and my thought was about that these OTHER hypertables might cause pghero to error out, but i dont know.

[ankane]

I'm able to reproduce the error if I delete the pghero_space_stats table, so I think that's the actual issue.

[luizkowalski]

I see the same error using the pghero from GitHub/main. The database is Fly.io's Postgres

[alexeevit]

Just ran into the same issue, but it seems to be a Rails bug (at least Rails 7.1.1, that's my case). UPD: everything was way simpler — I forgot to create the query_stats table.