yamltodb fails when datatype of view column changes

[davereinhart]

I have a SQL view where the datatype of a column has changed, and yamltodb fails with:

TypeError: Cannot change datatype of view column 'submitted_on'

Looks like it is related to this closed issue and commit: https://github.com/perseas/Pyrseas/issues/90 https://github.com/perseas/Pyrseas/commit/12d1192cc2aac1d6f52594cc187fe0e943f72483

If it's not possible for yamltodb to handle those, is there a way to bypass the error so I can get the result to modify manually? It looks like prior to this commit it was generating an output which just needed some minor changes.

[jmafc]

Prior to the change, yamltodb was generating a CREATE OR REPLACE VIEW that was then rejected by Postgres. A possible solution (which we did for certain CONSTRAINTs, for example) would be to generate a DROP VIEW followed by a CREATE VIEW. However, that may only work for a simple view that depends on a table. If a view depends on a view then we'd have to generate DROP VIEW CASCADE (or a series of DROP VIEWs in the correct order) followed by multiple CREATE VIEWs. With the new dependency tracking, maybe something could be done but I'm really not sure. It may be easier to implement code to bypass this particular error or similar errors, perhaps by replacing the raise by outputting a comment, e.g., / Cannot change datatype of view column 'name' /.

[davereinhart]

I can see how DROP VIEW CASCADE could work, but it would require the CREATE VIEW statements to be in the correct order. I found this recursive query on SO for determining the dependency order on any given view or table:

WITH RECURSIVE t AS (
  SELECT c.oid,c.relname, c.reltype, c.relkind, 0 as level FROM pg_class c where relname='view_name' UNION ALL
  SELECT c.oid,c.relname, c.reltype, c.relkind, a.level+1 FROM t a
  JOIN pg_depend d ON d.refobjid=a.oid
  JOIN pg_rewrite w ON w.oid= d.objid AND w.ev_class!=a.oid
  JOIN pg_class c ON c.oid=w.ev_class
) SELECT distinct * FROM t order by level;

Since that would be more involved, I think changing the raise to a comment as you suggested, and keeping the DROP VIEW and CREATE VIEW statements in the output to be manually reordered would be the second best option.

[jmafc]

FYI, we don't need a special query thanks to @dvarrazzo implementation of "dependency tracking" (in essence a topological sort of almost all the db objects). However, the "tracking" did not include table columns, because they're like different animals, so to speak. It was also implemented before I added capturing of the view columns and datatypes (mainly because they could be used to fix #90!). I'm not sure what would happen if we issued a DROP VIEW at the point where we find a view dependent on another that also has a column or datatype change (although it may not be too difficult to come up with a simple test case). Since I haven't been very active on Pyrseas (only released 0.9 recently because I upgraded something to PG 11 and found that 0.8 didn't work--even though the changes were already in master), I can't say when something may be done on this.