Wiping materialized views (… WITH NO DATA)

[skalee]

I propose adding another method which will wipe (depopulate) materialized views.

Rationale

It's needed in tests. Without wiping materialized views, at least in PostgreSQL, data these views are populated with may leak from one test to another, causing incorrect test behavior. For this reason, all materialized views should be depopulated in some setup or teardown hook.

Feature description

The new feature is about calling REFRESH MATERIALIZED VIEW <matviewname> WITH NO DATA.

I propose doing either of following:

1. adding a brand new API method that depopulates a view. It could be named e.g. #wipe_materialized_view, #clear_materialized_view, #depopulate_materialized_view
2. adding another keyword parameter to existing #refresh_materialized_view method. A new option could be named e.g. depopulate, no_data.

I am not sure if concurrent and cascade options that are currently available make sense in this context, but likely yes.

Further considerations

I believe it is worth to consider yet another method that wipes all defined materialized views. I am thinking about something similar to the pseudocode below:

names = execute_sql("SELECT matviewname FROM pg_matviews")
for name in names
  execute_sql("REFRESH MATERIALIZED VIEW " + name + " WITH NO DATA")
end

[derekprior]

I can see how this would be useful, but if I had a lot of materialized views I might find this step wasteful (assuming it was part of setup or teardown between each test) if it were to operate on every materialized view indiscriminately.

On one hand, we could do something like this:

1. Adding support for no_data: true to Scenic.database.refresh_materialized_view
2. Exposing this option in the templated model that gets written with the scenic model generator
3. Exposing #materialized_views or #views(materialized: true) to the adapter so you can programatically get all meterialized views
4. Adding a test helper that has to be manually required/called that exposes something like clear_materialized_views (maybe optionally taking a list of names or something).

On the other hand, if you're finding it useful to wipe materialized views in your tests, that means you need to populate them in your tests when needed as well. So while we can help you be resetting state, you still need to refresh when you need correct contents... so what did you gain by wiping them in the first place?

[calebhearth]

Would database transactions be a solution here? Whether the view is pre-populated or filled (or changed) as part of the test it  should return to its initial state right?On Aug 23, 2024, at 12:12 PM, Derek Prior ***@***.***> wrote: I can see how this would be useful, but if I had a lot of materialized views I might find this step wasteful (assuming it was part of setup or teardown between each test) if it were to operate on every materialized view indiscriminately. On one hand, we could do something like this: Adding support for no_data: true to Scenic.database.refresh_materialized_view Exposing this option in the templated model that gets written with the scenic model generator Exposing #materialized_views or #views(materialized: true) to the adapter so you can programatically get all meterialized views Adding a test helper that has to be manually required/called that exposes something like clear_materialized_views (maybe optionally taking a list of names or something). On the other hand, if you're finding it useful to wipe materialized views in your tests, that means you need to populate them in your tests when needed as well. So while we can help you be resetting state, you still need to refresh when you need correct contents... so what did you gain by wiping them in the first place? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>