Behaviour of Incremental Materialized Views and Partioned tables

[serra92]

Hello,

I've been currently trying out the postgresql build of this repo to experiment the usage of incremental materialized views. The current experimentation I'm doing is not updating the incremental materialized view on inserts. The setup is as follows:

• I have a table called sellout_indicators that is partitioned by a column called field_id
• I'm creating an incremental materialized view that is based on some of those partitions and joins them together

Whenever I do an insert on the master table, the incremental materialized view is not updating its data. Is this expected behaviour?

If you need any further details about the implementation of the table or the query that supports the view, feel free to ask.

[yugo-n]

Thank you for reporting this!

Hmm, indeed, views on partitions do not seem to work well.
Maybe we have to prohibit views on partitioned tables and partitions.
Anyway, I'll investigate this issue more.

Thanks.

[yugo-n]

A similar report is in pqsql-hackers
https://www.postgresql.org/message-id/OS0PR01MB5682576A59A1C765F7AEDE6B82F09%40OS0PR01MB5682.jpnprd01.prod.outlook.com

I think this is the same issue reported here.

[serra92]

This is not a deep search through the code but through some more experimentation and looking into the catalog views, i think what is happening is that the triggers only associate with the specific table itself, whether it is with the partition (that is what is happening on my case) or the master table (on the cases shown on the link you posted). If you would like to see more details on my case, I can show you the query underlying the materialized view and the queries I made on pg_triggers view that I made to understand better what was the underlying issue.

[yugo-n]

Yes, you are right. One reason of this issue is the lack of triggers on partitioned tables or partitions that are not specified in the view definition.
However, even if we create triggers recursively on the parents or children, it would still harder to maintain the view. That is because we will have to convert the format of tuple of modified table to the format of the table specified in the view for cases that the parent and some children have different format. I am not sure whether it is possible, although I'll investigate the way.

[serra92]

Thank you very much @yugo-n