0

Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log?

If that's not possible, is a dblink from Postgresql to Oracle possible?

2 Answers 2

1

The implementation of materialized views in Oracle and Postgres is different. What you're asking would require a level of integration of Postgres into Oracle's proprietary API - to register as a known client MV so that the log correctly updates itself - that I don't believe exists. It would also require a functioning database link to the Oracle database.

This extension looks like it can help Postgres connect to Oracle; I haven't used it myself, but it looks like it should work (although it also appears to have some limitations): https://pgxn.org/dist/oracle_fdw/

At that point, you would need to query the source tables directly, rather than existing MV Logs. Oracle maintains those logs automatically, truncating them or removing rows when it determines that all registered MVs have refreshed a certain set of changes. If there are no registered MVs, then the logs will grow out of control forever. It won't be long before they severely impact the performance of your updates and the Oracle DB (storage requirements, backups, memory, etc.). There are also situations in Oracle where an entire MV log can become invalid and require a complete refresh of the MV to reset. My point is that without the proper client registration, the data in those logs is pretty much useless, or worse than useless: harmful to maintain.

0

You can use oracle_fdw to connect from PostgreSQL to Oracle and query a materialized view log. I have done that.

Back then I tried to implement replication that way. The problem that led me to give up the project was that the Oracle materialized view log is undocumented, and I could not get all the information I needed. But barring that, there should be no problem.

3
  • If Postgres isn't registered with the Oracle as a client of the MV Log, then the MV Log will not be correctly maintained. It will either be truncated prematurely when some other MV in Oracle refreshes, or it will never be truncated (because there are no registered MVs for it) and it will grow out of control. This will affect Oracle's performance drastically if the source table changes often. There are other things in Oracle that can invalidate an MV Log and force the need for a complete refresh. Oracle can handle that automatically, but Postgres will not have the level of integration required.
    – pmdba
    Apr 23, 2020 at 17:00
  • Thank you pmdba and Laurenz Albe!
    – MacJava
    Apr 23, 2020 at 18:13
  • @pmdba You can use DBMS_MVIEW.PURGE_LOG to purge the materialized view log; this can be called via oracle_fdw. Of course you are right that it doesn't work if another materialized view uses that log. Apr 23, 2020 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.