yamltodb 0.7.1 unsuccessfully attempts to alter the type of columns in views.

[squareproton]

DROP SCHEMA IF EXISTS test_view CASCADE;
CREATE SCHEMA test_view;
SET search_path TO test_view;

CREATE VIEW "one" AS SELECT 1::int;

dbtoyaml produces

...
schema test_view:
  owner: unit
  view one:
    definition: ' SELECT 1 AS int4;'
    owner: unit

Change view. DROP VIEW "one"; CREATE VIEW "one" AS SELECT 'one'::text;.

Execute yamltodb using yml as before yeilds

CREATE OR REPLACE VIEW test_view.one AS
    SELECT 1 AS int4;

Which won't work as postgres considers it impossible to alter the type of a column in already existing view. I've been lucky so far in that just dropping the view and then recreating it has been possible every time. I expect things to get ugly when other objects depend on the view.

Pyrseas 0.7.1, postgres 9.2.6

[jmafc]

Changes to views (as well as changes to foreign key constraints) is one of the weak spots of yamltodb currently. It should be possible to detect the problem in your test case and issue the DROP/CREATE instead, but as you point out it gets ugly when there are other dependencies. Much remains to be done in this area.

[jmafc]

Part of the problem with the example given is that the column in the first view is named int4 while in the second view the column's name is text. The psql error then is: ERROR: cannot change name of view column "text" to "int4". IOW, the error is in attempting to rename the column.

A better example would be creating the first view as CREATE VIEW v1 AS SELECT 1::integer AS c1; and the second view as CREATE VIEW v1 AS SELECT 'one'::text AS c;. Then you do get the alter type error, i.e., ERROR: cannot change data type of view column "c1" from text to integer.

Nevertheless, in order to address this problem properly, we would have to have dbtoyaml output the view column names and datatypes so that yamltodb could compare both names and types and determine what action is appropriate.