search

risingwavelabs / risingwave

Best-in-class stream processing, analytics, and management. Perform continuous analytics, or build event-driven applications, real-time ETL pipelines, and feature stores in minutes. Unified streaming and batch. PostgreSQL compatible.
https://go.risingwave.com/slack
Apache License 2.0
6.76k stars 557 forks source link

pg_index does not include primary keys #17252

Closed neverchanje closed 2 months ago

neverchanje commented 2 months ago

Is your feature request related to a problem? Please describe.

Primary keys are automatically indexes in Postgres, specially tagged via the indisprimary column in pg_index;

In RisingWave, our pg_index only shows indexes created via CREATE INDEX.

Since we do not have other ways around to show all indexes and PKs within a database schema in a single query, we have to iterate over all tables and query through show columns from <rel>. This is very inconvenient and error-prone.

Describe the solution you'd like

We should at least include primary keys in pg_index and consider adding them to rw_indexes;

Describe alternatives you've considered

No response

Additional context

No response

yezizp2012 commented 2 months ago

For PKs, what content should we fill in column indexdef? Should we leave it empty or construct a fake one like CREATE UNIQUE INDEX XXXX_index ON public.XXXX USING btree (col1, col2)?

neverchanje commented 2 months ago

@yezizp2012

indexdef is in the table pg_indexes What I proposed to add is the information for pg_index.

I did't see tools accessing pg_indexes so far, so it'll be fine to leave it empty as it is now.

yezizp2012 commented 2 months ago

They all relies on rw_indexes, which contains a definition column. Well, let's leave it empty for now.