7.2. Updating the Database Schema on a Running Database


VoltDB Home » Documentation » Using VoltDB

7.2. Updating the Database Schema on a Running Database

Many normal changes to the database schema and stored procedures can be made "on the fly", in other words while the database is running. These changes include:

  • Adding, removing, or updating tables, columns, and indexes

  • Adding or removing materialized views and export-only tables

  • Adding, removing, or updating stored procedures and the security permissions for accessing them

Live schema updates are done by creating an updated application catalog and deployment file and telling the database process to use the new catalog. You do this with the @UpdateApplicationCatalog system procedure, or from the shell prompt using the voltadmin update command. The process is as follows:

  1. Make the necessary changes to the source code for the stored procedures and the schema.

  2. Recompile the class files and the application catalog as described in Chapter 5, Building Your VoltDB Application.

  3. Use the @UpdateApplicationCatalog system procedure or voltadmin update command to pass the new catalog and deployment file to the cluster.

For example:

$ voltdb compile -o mycatalog.jar  myschema.sql
$ voltadmin update mycatalog.jar mydeployment.xml

7.2.1. Validating the Updated Catalog

When you submit a catalog update, the database nodes do a comparison of the new catalog and deployment configuration with the currently running catalog to ensure that only supported changes are included. If unsupported changes are included, the command returns an error.

Most schema changes are supported. The only changes that are not currently allowed are changes that add constraints to an existing index or column or that make changes to the contents of an existing view. To make these more complex changes, you need to save and restore the database to change the catalog, as described in Section 7.3, “Updating the Database Using Save and Restore”.

7.2.2. Managing the Update Process

Updating the application catalog lets you modify the database schema and its stored procedures without disrupting the normal operations. However, even when a change is allowed, you should be careful of the impact to client applications that use those procedures. For example, if you remove a table or change the parameters to a stored procedure while client applications are still active, you are likely to create an error condition for the calling applications.

In general, the catalog update operates like a transaction. Before the update, the original attributes, including permissions, are in effect. After the update completes, the new attributes and permissions are in effect. In either case, any individual call to the stored procedure will run to completion under a consistent set of rules.

For example, if a call to stored procedure A is submitted at approximately the same time as a catalog update that removes the stored procedure, the call to stored procedure A will either complete successfully or return an error indicating that the stored procedure no longer exists. If the stored procedure starts, it will not be interrupted by the catalog update.

In those cases where you need to make changes to a stored procedure that might negatively impact client applications, the following process is recommended:

  1. Perform a catalog update that introduces a new stored procedure (with a new name) that implements the new function. Assuming the original stored procedure is A, let's call its replacement procedure B.

  2. Update all client applications, replacing calls to procedure A with calls to procedure B, making the necessary code changes to accommodate any changed behavior or permissions.

  3. Put the updated client applications into production.

  4. Perform a second catalog update removing stored procedure A, now that all client application calls to the original procedure have been removed.