|Summary:||Add support for replacing db-objects when there are references to them, without removing the references first|
|Product:||SQL||Reporter:||Martin van Dinther <martin.van.dinther>|
|Component:||all||Assignee:||SQL devs <bugs-sql>|
Description Martin van Dinther 2019-07-17 15:55:27 CEST
User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:67.0) Gecko/20100101 Firefox/67.0 Build Identifier: Currently it is not allowed to replace (or drop and re-create) a db-object (such as a table, column, view, function, procedure, aggregate, loader, trigger) when the db-object is referenced by another db-object (such as a view, function, procedure, aggregate, loader, index or trigger). You first have to manually remove all the referencing db-objects (depth-first) before you can continue. In a DROP statement you can use the CASCADE option, which removes all referencing db-objects cascading completely, but this can lead to an undesired removal of many db-objects without control, which is not what you want if you only want to replace for instance the definition of a view or function or procedure or column. Also it is cumbersome and very time consuming to find out which objects are going to be dropped when CASCADE is used and recreate all those dependent db-objects in a specific order again, provided you still have the original and up-to-date create_db_objects.sql script(s). Some RDBMS (e.g. Oracle) allow you to replace a db-object even if there are references to it from other db-objects. It just marks the dependent db-objects (which are defined as SQL script code such as a view, function, procedure, aggregate, loader, index or trigger) into an invalid state. Whenever you query or call those invalidated db-objects, the SQL processor detects the invalid state and it automatically tries to re-parse the script code of the db-object(s) such that it becomes valid again, potentially recursively (depth-first). If it succeeds it continues the processing of the original query or call. If it fails it returns an error (compiling object xyz failed) about the incorrect db-object. Whenever a table or column or view or function or procedure or aggregate or loader or trigger is dropped or replaced: the is_valid column of the referencing objects (view, function, procedure, aggregate, loader, index or trigger) is set to false, cascading through for all objects referencing the object which is marked as invalid. For this we need to extend system tables: sys.functions, sys._tables and sys.trigger with new column: is_valid boolean not null default true. This enhancement would allow a user to replace an object without the need to drop all referencing objects first and next recreate all dropped objects again, saving a lot of time. It is also very useful for the db-upgrade program as much less drop ... and create ... statements are needed. Reproducible: Always Steps to Reproduce: Example 1: change data type of an existing column while there is a view (or function or procedure) defined which references the column. create table t1 (id int primary key, name char(20)); create view v1 as select * from t1 order by name, id; insert into t1 values (100, 'me, myself and I'); select * from v1; alter table t1 drop column name; -- this currently fails due to dependency from v1, but should be possible in the future -- this command will implicitly set view v1 as invalid alter table t1 add column name varchar(99) not null; select * from v1; -- this last query will automatically revalidate the view definition and mark it as valid again. Example 2: change a table while there is a view (or function or procedure) defined which references the table. create table t2 (id int primary key, name char(20)); create view v2 as select * from t2 order by name, id; insert into t2 values (100, 'me, myself and I'); select * from v2; drop table t2; -- this command will implicitly set view v2 as invalid create table t2 (id serial, cat varchar(5), name varchar(99) not null); insert into t2 (cat, name) values ('A', 'me, myself and I'); select * from v2; -- this last query will automatically revalidate the view definition and mark it as valid again. Example 3: change a view while there is a view (or function or procedure) defined which references the view. create view v3a ... create view v3b as select * from v3a where state = 'ok'; create view v3c as select count(*) as count, c3 from v3b group by c3 order by c3; select * from v3b; select * from v3c; create or replace view v3a ... -- this command will implicitly set view v3b and v3c as invalid select * from v3c; -- this last query will automatically revalidate the view definitions and mark them as valid again. The implementation would require: - extending system tables sys.functions, sys._tables and sys.trigger with column: is_valid boolean not null default true sys.functions for: user defined functions, procedures, aggregates, filter and loader functions sys._tables for: user defined views sys.trigger for: user defined triggers - an internal C function which can set a specifc db-object and all its referencing db-objects to invalid state (is_valid = false) cascading. - an internal C function which can re-validate a db-object which is marked as invalid state (is_valid = false) cascading depth-first and update its dependencies. - a utility system function: sys.revalidate(str objtype, str schema, str objname) to (re-)validate the specific script object(s), or all objects in that schema if objname is null. - the SQL parser would need to add checks on is_valid column state of a needed db-object and invoke the internal C function to re-validate the db-object script(s) when required.