Bug in PG_SUPPORTS_MVIEW 0?

[bjornbak]

I can't use the postgresql 9.3 implementation of materialized views as I need to have read access to the materialized views while they are refreshed. I have to create my own refresh procedure.

In ora2pg 11.4 materialized views where migrated as a table with data, a view and a stored procedures to refresh the table.

The sql part of the materialized view doesn't migrate to clean postgresql sql so I took the code, cleaned it and saved it to execute manually.. The table definition and data was migrated with TYPE TABLE SEQUENCE and TYPE COPY - perfect.

But with 12.0 and PG_SUPPORTS_MVIEW 0 the table for the materialized view is't migrated. If I migrate with TYPE TABLE SEQUENCE MVIEW the refresh views and calls to the stored procedure is produced. But not the definition of neither the table or the stored procedure.

I've tried adding the materialized views to VIEW_AS_TABLE without luck.

I would like to either have the 11.4 functionality of getting the materialized view table defintion and data when doing TYPE TABLE SEQUENCE and TYPE COPY migrations or to have a MVIEW_AS_TABLE variable.

[darold]

That's a bug, disabling PG_SUPPORTS_MVIEW in ora2pg 12.0 should have the same behavior as previous version. This was not the case. Commit 5d138aa should solve the issue.

[bjornbak]

Hmm I doesn't seem to change anything...

Having

PG_SUPPORTS_MVIEW 0

in ora2pg.conf and extracting ddl with

../ora2pg-current/blib/script/ora2pg -c ../ora2pg.conf -t "TABLE SEQUENCE"

the output doesn't contain ddl for the mv tables.

[darold]

Please use:

../ora2pg-current/blib/script/ora2pg -p -c ../ora2pg.conf -t MVIEW

and look at your output file.

[bjornbak]

That gives me the the *_mview views for population and the controlling stored procedures but not the ddl for the tables storing the materialized view data...

In 11.4 this table was part of -t TABLE

[darold]

I don't understand. You don't need to create any table, this is done by the create_materialized_view() function.

Once you have imported that code, you have to call create_materialized_view() using the example given at end of the output file. For example, I have:

SELECT create_materialized_view('mv','mv_mview', change with the name of the colum to used for the index);

arguments depends of the name of the view, you also have to change the third to set the column that should be used to construct the index.

i will try to add more explanation in the code.

[bjornbak]

OK. Then I'm missing a bug that were in 11.4.. With 11.4 I got the virtual table containing the materialized data exported as a table as well..

I liked that solution because it meant that I don't have to wait for the materialized view to be created on the postgresql side before it is usable.

It takes 6 hours for me to create the materialized views as pg9.3 mv's.

I looking at ways minimize the downtime during migration but am considering taking the cost during migration so I can use the new pg9.3 mv's..

[bjornbak]

I'll use the pg9.3 mv's instead.