This is part 7 of a post series about EBR.
Visit the index page for all the parts of the series
Introduction
Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. Of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.
An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.
In addition to the challenges raised from the first and second use cases, the current use case introduces another challenge. And unlike the previous challenges, this one cannot be overcome just by using a new edition.
The Current State
At this point, after completing two upgrades already – in part 3 and part 5, we have three editions, with the following actual objects:
V2> select object_name,object_type,status, edition_name
2 from user_objects
3 order by object_name,object_type;
OBJECT_NAME OBJECT_TYPE STATUS EDITION_NAME
------------ ------------ ------- ------------
APP_MGR PACKAGE VALID ORA$BASE
APP_MGR PACKAGE BODY VALID V2
PEOPLE TABLE VALID
PEOPLE_DL PACKAGE VALID V2
PEOPLE_DL PACKAGE BODY VALID V2
PEOPLE_PK INDEX VALID
6 rows selected.
As discussed in part 6, a table is a non-editioned object, and therefore we have a single instance of the PEOPLE table – referenced by all the instances of the PEOPLE_DL package body in all the editions.
The Task
We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).
Our Problem
If we try to add the new column to the table, then the dependent object – the PEOPLE_DL package body – will become invalid:
V2> alter table people add (
2 eye_color varchar2(20)
3 );
Table altered.
V2> select object_name,object_type,status, edition_name
2 from user_objects
3 order by object_name,object_type;
OBJECT_NAME OBJECT_TYPE STATUS EDITION_NAME
------------ ------------ ------- ------------
APP_MGR PACKAGE VALID ORA$BASE
APP_MGR PACKAGE BODY VALID V2
PEOPLE TABLE VALID
PEOPLE_DL PACKAGE VALID V2
PEOPLE_DL PACKAGE BODY INVALID V2
PEOPLE_PK INDEX VALID
6 rows selected.
V2 is currently the active (“exposed”) edition in production in our demo, so obviously what we’ve just done cannot be considered as online upgrade.
Note that in a real life scenario there may still be active sessions in production connected to earlier editions as well. All of them will suffer downtime as a result of the last ALTER TABLE statement:
V2> select object_name,object_type,status, edition_name 2 from user_objects_ae 3 order by object_name,object_type,edition_name; OBJECT_NAME OBJECT_TYPE STATUS EDITION_NAME ------------ ------------ ------- ------------ APP_MGR PACKAGE VALID ORA$BASE APP_MGR PACKAGE BODY VALID ORA$BASE APP_MGR PACKAGE BODY VALID V2 PEOPLE TABLE VALID PEOPLE_DL PACKAGE VALID ORA$BASE PEOPLE_DL PACKAGE VALID V2 PEOPLE_DL PACKAGE BODY INVALID ORA$BASE PEOPLE_DL PACKAGE BODY INVALID V1 PEOPLE_DL PACKAGE BODY INVALID V2 PEOPLE_PK INDEX VALID 10 rows selected.
The General Problem
If a package (or other type of program unit) references a table, then adding a new column to the table may invalidate the package.
There are cases in which invalidation will not occur and the package will remain valid. In my example, for instance, if I qualified the parameter name in the DELETE statement then adding the eye_color column would not cause invalidation.
But there are cases where invalidation is inevitable. For example, if table%rowtype is used in the package.
And the same is true for other changes of table structure, not only for column additions.
As tables are non-editionable object types, we cannot alter them in the privacy of a new edition as we did with packages. So, are we in a dead-end? No, we’re not.
The Solution
We can eliminate the invalidation by adding a view between the package and the table:
If the view simply projects the columns of the table, and the package references the view rather than the table, then adding a new column to the table will not cause any invalidation to the view, and therefore the package will remain valid as well.
But adding the column to the table is just the first part of the task. We also need to make the corresponding changes to the package code, and for that we also need to project the new table column in the view. Well, this is easy, as views are editionable. We can make these changes – both in the view and in the package – in a new edition.
Does it mean that we should create a view for every table, and that we should reference these views (rather than the tables) from all our code? Yes, it does, but not just “a view” – a special type of view.
Why a Special Type of Views?
Using regular views in our SQL statements may add some performance overhead over using the tables explicitly.
In addition, not everything that we can do on tables can be done on (regular) views – for example: defining before and after DML triggers.
That’s why EBR introduced a new and special type of views – Editioning Views.
Editioning Views
Editioning views are first of all… views, and therefore they are editionable.
They are deliberately very limited, compared to regular views:
- There can be only one editioning view per table
- An editioning view may contain only the SELECT and the FROM clauses. Not a WHERE clause, not a GROUP BY clause, nothing but SELECT and FROM.
- The FROM clause may refer only to a single table
- The SELECT list may contain only columns and aliases (no expressions), and each column may appear only once
In short: the view subquery may only project columns of its base table and alias the projected columns.
These deliberate limitations allow us to treat editioning views as if they were tables. There should be no performance penalty for using them. We can define before and after DML triggers on them (and we cannot define instead of triggers on them).
Editioning views are a key building block in the EBR solution for online application upgrades.
No application code (either SQL or PL/SQL) should access the tables directly anymore.
Editioning views are the interface between the application and the tables
Once we adopt this principle, we can have different interfaces to the same table from different editions.
This is very important, because although we have one (non-editioned) table with one structure, the editioning views simulate different structures for this table, at the same time, when connected to different editions.
So before continuing with our example, we have to convert our schema to use editioning views. I’ll dedicate the next post for that.
For other parts of the EBR series, please visit the index page.