View dependencies in migration about scenic HOT 9 CLOSED

It's not a feature right now but it's something I'd like to add.

I don't think we need to have the user tell us the dependencies. Postgres itself knows the dependencies. I think we can handle this similar to how we handle recreating materialized view indexes on update. Here's what I'm thinking:

1. Add cascade option to update and drop operations. This behavior should be opt in. It may result in dropping and recreating expensive materials views.
2. On update with cascade, take a snapshot of all views using the adapter's views method. This list comes back in dependency order. This is important.
3. If the list contains materialized views, we will need to store the index definitions on those views.
4. Drop the view we are updating, allowing Postgres to cascade.
5. Get the list of views again and diff the list with the list we had earlier. This will give us an ordered list of views that were dropped.
6. Recreate the missing views in order.
7. Recreate dropped materialized view indexes.

I think that will do it unless I'm missing something. What do you think?

from scenic.

Is "automatic" dependency management a desired feature?

We rolled our own support for views in one project, and some of them have 5+ downstream dependencies. We use a comment directive at the top of the file to declare dependencies, and then we make sure to drop and add the views in the correct order automatically:

-- db/views/a.sql
--!require b
SELECT ... FROM b

-- db/views/b.sql
--!require c
SELECT ... FROM c

-- db/views/c.sql
SELECT ...

Then calling our equivalent of update_view('c') will effectively run this:

drop_view 'a'
drop_view 'b'
drop_view 'c'
create_view 'c'
create_view 'b'
create_view 'a'

If that's of interest, I could take a stab at a PR.

from scenic.

We handn't thought of this use case before now, but I can see its use. The schema dumper doesn't care for dependencies amongst views because it doesn't know them.

We need to see if we can pull the dependencies out of postrgres itself so we can more intelligently dump the schema.

For now, I think this would work if you named the views alphabetically such that the views with no dependencies came first, followed by the views with the dependency. This obviously is not a solution we're pleased with, but it should work for now.

from scenic.

Sorry, I should have known better than to offer help on a feature the day before our baby was due!

What you've written up makes sense. I wasn't thinking about the fact that you could get the correct order out of Postgres. If I ever find time to work on this, I'll submit a PR.

from scenic.

@derekprior That's helpful info. I didn't realize the name has anything to do with it. I was able to resolve the issue by changing the name. Feel free to close this ticket. Thank you very much!

from scenic.

I think it should be addressed, so we'll leave it open as we investigate how we can manage this behind the scenes. A confusing thing like this is definitely a bug, just not one we'd seen coming :D

from scenic.

I didn't get a chance to look into this today, but I want to try dumping views to the schema in the order they were created (right now it's just by name). I think this will "just work"™ due to Postgres' dependency tracking.

It's impossible to create a view if it depends on objects that have already been created. So if I:

1. create_view :parent
2. create_view :child

and then dump them in that order, we're obviously okay. The tricky part comes when I then try to:

1. update_view :parent, version: 2

Under the covers, update_view does a drop and then create. My worry was that this would cause the schema dumper to then have the views backwards if we ordered by creation time. But I don't think this is the case. I believe postgres will actually error on the update_view call because it knows :child still exists and is dependent on :parent.

I think postgres would basically force you to:

1. drop_view :child
2. update_view :parent, version: 2
3. create_view :child

This would obviously leave the insertion order correct for the sake of schema dumping, but it's a pretty terrible user experience.

For that reason, I think we should switch to CREATE OR REPLACE for view updating. We'd still want to manually check that the view we are updating actually exists (because by using update_view we're saying it should) but it's my hope that this will cause the view schema to be updated but the created at time for the view to be maintained (and not require refreshing the :child view). This needs to be tested.

Also worth noting that CREATE OR REPLACE does not work for materialized views so we might need to live with the rough UX for that.

from scenic.

Views will now be dumped in the order they are returned by postgres. In my testing, this seems to be ordered appropriately considering dependencies.

Updating a parent view will require you to first drop and then recreate any dependent views. Postgres knows about the dependency and will enforce them.

from scenic.

Congratulations. 🎊

from scenic.