Behaviour of Incremental Materialized Views and Partioned tables

sraoss / pgsql-ivm

IVM (Incremental View Maintenance) development for PostgreSQL

Other

129 stars 12 forks source link

Behaviour of Incremental Materialized Views and Partioned tables #132

Open serra92 opened 3 years ago

serra92 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

I think this is the same issue reported here.

serra92 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

Thank you very much @yugo-n