Major features and functionality for upgrading the schema of a distributed system

▪Sync Mode – Setting the Sync Mode to “Maintenance” allows schema changes (DDL operations) on tables that are referenced by a publication. It also disables “sync history” tracking temporarily.

▪REPLACE option in the CREATE PUBLICATION command – The REPLACE option allows you to change an existing publication without necessarily requiring a full refresh afterward. When possible, incremental refreshes will still be allowed after the change.

▪Schema version tracking – If either the master or replica sets the persistent catalog-level parameter SYNC_APP_SCHEMA_VERSION (using SET SYNC PARAMETER command), the two servers will refuse to synchronize unless both have the same value for the version. This prevents synchronization when the schema of the replica does not match the schema of the master.

Setting the Sync Mode to “Maintenance” allows schema changes (DDL operations) on tables that are referenced by a publication. If the catalog’s sync mode is not Maintenance, then the server prohibits DDL operations on tables that are used by publications. This means that to change a table, you would have to drop the publication, change the table, and re-create the publication — even if the change to the table (for example, adding a new column) did not affect the publication. Since dropping and re-creating a publication forces replicas to get a full refresh rather than an incremental refresh, you would have to force replicas to get full refreshes every time that you wanted to change a table.

Setting the Sync Mode to Maintenance allows you to alter a table without dropping the publication(s), and therefore without necessarily forcing full refreshes.

Setting the Sync Mode to Maintenance also temporarily disables sync history; in other words, it tells the server not to store data that is used in deciding which records to send to a replica when the replica requests a refresh of the data. This allows you to do some types of major data changes (DML) quickly; you can make the same updates to the master and replica and simply skip over the synchronization step.

However, if you accidentally make the master and replica “out of sync” while you have disabled sync history, the master and replica will not automatically resynchronize (correct the error) the next time that the replica refreshes. Since there is no synchistory to show what changes were made on the master, the master has no reason to send updates to the replica. If the replica gets out of sync, it may stay out of sync indefinitely or until the next full refresh of the publication. You must be very careful when making changes to the master and replica databases when you have the Sync Mode set to Maintenance.

When you set the Sync Mode back to Normal (the default value), the server will resume tracking sync history information, and will also stop allowing DDL operations on tables in publications.

Note that Sync Mode Maintenance does not guarantee that replicas will not be required to get a full refresh when a table is changed. Some changes to a table (for example, dropping a column that is used in a publication) may affect the table (or the publication) enough that the replicas will have to get a full refresh.

The advantage of the REPLACE option is that it allows you to change a publication without necessarily forcing replicas to reregister and get a full refresh. In some cases, replicas may continue getting incremental refreshes.

If you do not use the REPLACE option, any time you want to change a publication, you must drop that publication and re-create it. When a publication is dropped and re-created, replicas must reregister for that publication and must get a full refresh.

When you use the REPLACE option, however, you can modify a publication that already exists. You can change the definition of an existing publication without dropping and re-creating the publication. Since you did not drop and re-create the publication, replicas can continue getting incremental refreshes instead of being forced to get a full refresh.

The REPLACE option does not guarantee that replicas will not be required to get a full refresh when a publication is changed. Some changes to a publication may be significant enough that the replicas will have to get a full refresh. Also, not all operations can be performed with a REPLACE statement. For example, the REPLACE does not allow you to change the publication argument list. If you do need to change the publication argument list, you will have to drop and re-create the publication, and this means that replicas will have to reregister and get a full refresh.

Both the REPLACE option and the SYNC MODE MAINTENANCE setting allow you to make changes that do not necessarily require replicas to get a full refresh. The difference is that SYNC MODE MAINTENANCE allows you to make changes to the schema of a table, while the REPLACE option allows you to make changes to a publication.

Table-level locking allows you to lock (and unlock) an entire table explicitly, for example, for the duration of critical schema upgrade operations. This allows you to ensure that the schema upgrade operations are not interfered with by other operations on the affected tables.

You may lock a table in EXCLUSIVE or SHARED mode. If you plan to change the schema of a table, you will probably want to lock the table in EXCLUSIVE mode. You cannot acquire an exclusive table lock if any other user has locked the table. In a busy system, it may be very difficult to get an exclusive lock on a table. You may have to ask other users to stop using the system (or at least stop using that table) temporarily so that you can get the lock. Once you have the exclusive lock, it will prevent any other users from using that table until you unlock it.

In most situations, locks are released at the end of a transaction. The LOCK TABLE command, however, gives you the option of holding a lock past the end of a transaction. If you do hold a lock past the end of a transaction, then you must explicitly UNLOCK the table to release that lock. Otherwise, the lock will persist until the client application who has obtained the lock, disconnects.

Although table locking is used primarily to make it easier and safer to upgrade schemas, you may use it for other purposes as well. You may use table locks at any time, not just when the Sync Mode is set to Maintenance.

For more details, including the exact syntax to use to get an EXCLUSIVE lock or to get a long lock that lasts past the end of the current transaction, see the descriptions of the SQL commands LOCK TABLE and UNLOCK TABLE in the solidDB^® SQL Guide.

When you change the schema on a master and a replica, you usually cannot do these changes simultaneously. Typically, if you have changed the schema on the master and not the replicas, you do not want the replicas to refresh until they, too, have updated their schema. You may use the bulletin board parameter titled SYNC_APP_SCHEMA_VERSION to help prevent synchronization when the master and replica do not have the same schema.

If either the master or the replica has this parameter set, then when the master and replica try to synchronize they will compare values of this parameter. If the value on the master does not match the value on the replica, then the two will refuse to synchronize. Instead, the replica will merely store the synchronization message. You may re-send that message later by using the MESSAGE FORWARD command after you have updated the replica’s schema.

For example, if the master sets its SYNC_APP_SCHEMA_VERSION to “Version2” while the replica has its value set to “Version1”, then the two will refuse to synchronize.

Note that the master and replica servers merely compare their values for this parameter; they do not actually compare their schemas. If you accidentally set the master’s and replica’s SYNC_APP_SCHEMA_VERSION to the same value when the servers do not actually have the same schema, then the master and replica will try to synchronize.

The servers only use this bulletin board parameter if it is set. This bulletin board parameter is optional, and is not set automatically. There is no default value, and the servers do not automatically “increment” the value each time that their schemas are updated. The actual values are meaningless; the only thing that matters is whether or not the master and replica have the same value or a different value. You may use any value you want, for example, “Version1”, “VersionA”, “XYZ” or any other string (up to the maximum legal length for bulletin board parameter values).