"ERROR: cannot change name of view column" on a view when applying diff to DB

[pms967]

Bug description

Trying to apply to the actual DB a "diff" including a column name change in a view results in this error:

Could not execute the SQL command.
Message returned: ERROR: cannot change name of view column "dsid" to "msid"
HINT: Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.

This may be a PostgreSQL bug, though?

According to the logs, apparently pgmodeler tried to use "CREATE OR REPLACE VIEW", which should have worked?!

ERROR:  cannot change name of view column "dsid" to "msid"
HINT:  Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.
STATEMENT:  CREATE OR REPLACE VIEW public.vw_action_sequence_matrix

[rkhaotix]

Hi @pms967

I need to check that... Could you provide steps to reproduce?

[pms967]

Just create a schema including a view with several columns and create an actual DB from it. Then in pgmodeler rename one of the columns of the view, run a diff WRT the running DB, try to apply the changes, and you should get that error.

BTW: this is the code generated by pgmodeler "diff" output:

-- object: public.vw_action_sequence_matrix | type: VIEW --
-- DROP VIEW IF EXISTS public.vw_action_sequence_matrix CASCADE;
CREATE OR REPLACE VIEW public.vw_action_sequence_matrix
AS 
SELECT ds.ptype,
    ds.pstate,
    ds.step,
    ds.dtype,
    ds.msid,
    ds.is_optional,
    dw.priority AS action_priority,
    dw.role,
    dw.action,
    dw.notes AS workflow_notes
   FROM (
	main_sequence ds
	CROSS JOIN LATERAL ( 
          SELECT 
            dw_1.dtype,
            dw_1.priority,
            dw_1.role,
            dw_1.action,
            dw_1.notes
           FROM doc_workflow dw_1
          WHERE (dw_1.dtype = ds.dtype)
          ORDER BY dw_1.priority
		) dw
	)
  ORDER BY ds.ptype, ds.pstate, ds.step, dw.priority;
-- ddl-end --

previously, the column "msid" was called "dsid". That's the only change WRT the version in the DB.

As for the diff options, I was using these:

[pms967]

This may be a PostgreSQL bug, though?

According to the logs, apparently pgmodeler tried to use "CREATE OR REPLACE VIEW", which should have worked?!

Could it be that Postgresql tries to be "too smart", but fails? That is, it recognize the the new view query is identical to the old one except for a column name, so instead of dropping and re-creating the whole view it tries to simply change the modified column name, but uses the wrong command to do so?

[rkhaotix]

Could it be that Postgresql tries to be "too smart", but fails? That is, it recognize the the new view query is identical to the old one except for a column name, so instead of dropping and re-creating the whole view it tries to simply change the modified column name, but uses the wrong command to do so?

Maybe. Try to force the recreation of the view, by generating a DROP/CREATE instead of CREATE OR REPLACE.

[pms967]

Maybe. Try to force the recreation of the view, by generating a DROP/CREATE instead of CREATE OR REPLACE.

Of course... that worked.

[rkhaotix]

Maybe. Try to force the recreation of the view, by generating a DROP/CREATE instead of CREATE OR REPLACE.

Of course... that worked.

Well, at least it's not a bug on pgModeler (I hope!) :)

But this is kinda strange, since PostgreSQL is suggesting a wrong procedure in the returned hint.

[rkhaotix]

Actually, doing some researches, its a limitation of the CREATE OR REPLACE command, for security reasons PostgreSQL disallow using OR REPLACE in a view definition where the column name changes from the original.

Said that, pgModeler maybe need a diff step that generates ALTER VIEW ... RENAME COLUMN ... . But the main problem is that RENAME operations are not supported yet. I'm planning some alternatives, but none of them still satisfies me. :(

[pms967]

Well, I guess simply issuing DROP VIEW IF EXISTS and then CREATE (without REPLACE) when modifying a view should do the trick... after all, being a view that's safe, there's no risk of unintended data loss.

[pms967]

BTW, should we submit a bug report to psql devs? At the very least, they should fix the error message, explaining the real reason why the operation failed. As it is it's confusing, and even provide a wrong hint...

[rkhaotix]

BTW, should be submit a bug report to psql devs? At the very least, they should fix the error message, explaining the real reason why the operation failed. As it is it's confusing, and even provide a wrong hint...

It's a good idea. This isn't the first time I see confusing error messages from PostgreSQL.

[pms967]

Should I do it or you'll prefer to do it yourself?