Support to partitioned tables

[fcovatti]

I was thinking on using pg_ivm for a database that has lots of data at 5 minutes granularity and that I have the base table daily partitioned. My ideation was to create an incremental materialized views for 1 hour aggregated data. If I got it correctly from the documentation as my base table is partitioned I would not be able to use pg_ivm?

[yugo-n]

Unfortunately, partitioned tables are not supported in the current pg_ivm.

I've also heard the opinion from another person that IVM should support partitioned tables because such big tables are exactly the use cases. So, I would like consider it for a future release.

[medivh666]

When will incremental refresh of partitioned tables be supported，

[Bessonov]

@medivh666 as soon as you contribute it to the project 👍

[jasonmp85]

I'm working on using pg_ivm with partitioned tables… I removed lines in createas.c prohibiting its use against partitioned tables and expected something to break, but thing seem to be working.

Are partitioned tables prohibited for a known reason, or were these lines included out of safety?

I ask because it would be nice to have input on what needs fixing, given that the triggers and maintenance seem to be working alright.

[jasonmp85]

Ok turns out my initial limited tests were not fully representative. I think things were working well with single relations, but after going into the pg_ivm tests and modifying the tables to be partitioned I've found that queries with multiple relations in their target list (this includes self-joins) crash the server. So that's something I should look into.

Of course there's still the attaching and detaching of partitions to think about, as before, but this more fundamental problem is here, too.