Add support for Materialized Views

[jayadevanm]

The documentation does mention that Views are not supported. It looks like Materialized Views are also not supported, though they are more like tables than views. We can create indexes, they contain data etc.

[bplunkett-stripe]

It's unfortunately not the straight forward (think webs of dependencies), but I do intend to add support for them and have a mental model in my head as to how I will achieve it. I first would like to refactor the SQL generation a bit before I implement (materialized) views.

[KEHyeon]

@bplunkett-stripe I wonder when the View will be added.
and I'm very interested in this project. Can I try refactoring it too?

[bplunkett-stripe]

Sure! You can give it a shot, but a thorough implementation will be pretty complex. I can try to add really simple view support in the future.

[KEHyeon]

@bplunkett-stripe
First, I want to analyze this project code. Can you briefly explain the code and internal principles?

[bplunkett-stripe]

There are three main parts:

1. Schema extraction
2. Schema diffing
3. SQL generation

The latter part is the most complex: we take the diff generated from step 2 and build a graph that we topologically sort in order to get our ordered SQL statements

[kiwicopple]

hey @bplunkett-stripe - we (supabase) would love to help with this one and then switch to using it as our default tool

If you want to spec it out, that's great. Alternatively if you prefer for us to propose something then we can do that too. We're flexible - let us know the best direction. I'm available on emails/dms(all platforms) if you want to chat.

[bplunkett-stripe]

I'll try to take a stab at it this weekend. I think there are some large simplifications I can make to get some sort of MVP out.

I'll add I am basically working on this in my own free time. Ironically, I work on schema-less document databases now, so I have to balance this against other priorities.

[Navbryce]

Rough materialized view support has been added. I still need to add support for indexes...and various "quality of life" features.

[ewan-escience]

When using pg-schema-diff to (as a test only) migrate the RSD from version 5.1.0 to version 5.2.0, I get the following error message:

Error: generating plan: generating plan statements: diffing indexes: diffing for "public"."count_software_mentions_cached_software_idx": checking if index is on partitioned table: could not find table in new schema with name "public"."count_software_mentions_cached"

The index is on a materialized view, which can be found here.

I'm using v1.0.2 of pg-schema-diff (in Docker), golang:1.22.12-bookworm (Docker) and postgres:17.6 (Docker). Is an error like this still expected? Can I tell pg-schema-diff to ignore errors like these?

[jperasmus]

Ran into the same error as @ewan-escience using pg-schema-diff via the Supabase CLI (v2.58.5). I see that indexes on matviews were reverted here

Are there plans to support indexes in the near future, or did you hit a dead end for now?

[surprisetalk]

Yep, running into the same issue:

~/D/superbam pg-schema-diff plan --from-dsn 'postgresql://taylor@127.0.0.1:5432/superbam' --to-dir db.sql                                                                                   
Error: generating plan: generating plan statements: generating migration statements: resolving index diff: generating delete statements for "sb"."sb_stat_t_idx": generating sql: could not find table in new schema with name "sb"."stat"