Materialized View in Postgresql

[deepak sagta]

How to get data from materialized views of Postgresql  in debezium

[Gunnar Morling]

Hi Deepak,

You asked the same question in the blog (https://debezium.io/blog/2018/07/19/advantages-of-log-based-change-data-capture/), but I think I actually misunderstood when I replied there.

Is it that you'd like to capture change events when the contents of an actual materialized view in Postgres (as defined via CREATE MATERIALIZED VIEW ...) have changed? If so, it'd depend on whether these changes will be published by Postgres to logical decoding plug-ins as the one used by Debezium. I haven't tried that out, but I suppose that this is probably not the case. Instead, you'd have to capture changes from the tables on which the view is sourced and update you own "view" in some sink DB.

--Gunnar

[deepak sagta]

yes you are write, but how i can handle in database level. It means Materialized views are not supported by Debezium.

[Jiri Pechanec]

Is it possible to use a helper table using `SELECT INTO`?

J.

[deepak sagta]

when we use 'SELECT  INTO' , then it will not give me CDC Data.

[Jiri Pechanec]

I see where the problem is. SELECT INTO will not create primary key for the new table and the events are then skipped. If you fiorst create the table (including PK settings) and then execute INSERT INTO ... SELECT * then you'll get the events replicated

J.

[deepak sagta]

i have situation i have created a materialized view in Postgresql when i refresh it i see the data on wal but with a different name of table like pg_temp_xx not with the name of materialized view name.

[Jiri Pechanec]

IMHO the view is materialized into a temporary table. Such tables are not replicated.

J.

[deepak sagta]

i have seen this post https://issues.jboss.org/browse/DBZ-277 but how he had done this. I have done in same way he was doing.

[Gunnar Morling]

I don't think the reporter of that issue streamed changes from a view. The issue is about the presence of views causing trouble when capturing other tables in the same DB, which got fixed back then.