4 ms·

Thanks a lot for your reply! It seems the main gain for your is to move between languages and allow initial iterations/bugfixes without touching the applicatio

by 1996 7y ago

Thanks a lot for your reply! It seems the main gain for your is to move between languages and allow initial iterations/bugfixes without touching the application code. The database has more maintenance issues (like rolling txids) than the application code. I am not sure I want to add more complexity and potential issues to the database. FYI, a materialized view is a potentially long or complex query whose results are cached, so you can say 'select * from complex_query_result' to get them, and refresh the complex_query_result whenever you feel like it. You can also update the query that generates complex_query_result. In practice, MV can give you speed (as you can refresh the MV when you need/want, while keeping the results) and also put the data-logic inside the database (as the MV is defined initially, and can later be updated) if you don't need super fresh results. If you do, use a regular view. In either case, you can use the view approach when parameters are needed, iff you can reduce you query to where parameter=something on the view. Otherwise, you need to use languages like pl sql. As the materialized views queries just return the results to be processed, and I have very little extra to do, your approach seems overkill for my use case.

Twisell 7y ago
Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).
- 1996 7y ago
  Personally I have update scripts for 'version control' of the MV and its dependancies, it is not very painful to do: begin; drop ... cascade; create ...; commit;
- mhd 7y ago
  Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle) I've had good luck with these functions: https://gist.github.com/mateuszwenus/11187288 https://gist.github.com/mateuszwenus/11187288 Allows you to save-and-drop dependent views (materialized or regular) and then restore them after your updates.
  - Twisell 7y ago
    Deeply agree about dependencies being a general problem. Thanks for the script that look pretty clever #bookmarking. Like particularly the approach of "drop what you saved, no more no less". DROP CASCADE is simpler but can have undetected side effect, if this script fail to backup all dependency, logically you will get an error when attempting to delete target and that rocks.

by 1996 7y ago

Twisell 7y ago

Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).

1996 7y ago
Personally I have update scripts for 'version control' of the MV and its dependancies, it is not very painful to do: begin; drop ... cascade; create ...; commit;
mhd 7y ago
Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle) I've had good luck with these functions: https://gist.github.com/mateuszwenus/11187288 https://gist.github.com/mateuszwenus/11187288 Allows you to save-and-drop dependent views (materialized or regular) and then restore them after your updates.
- Twisell 7y ago
  Deeply agree about dependencies being a general problem. Thanks for the script that look pretty clever #bookmarking. Like particularly the approach of "drop what you saved, no more no less". DROP CASCADE is simpler but can have undetected side effect, if this script fail to backup all dependency, logically you will get an error when attempting to delete target and that rocks.