3.2. Database Schema Changes (DDL)

3.2.1. DDL Changes with Execute Script

The SLONIK EXECUTE SCRIPT (slonik) command allows you to submit a SQL script (that can, but is not required to) contain DDL commands. This script will be executed on the event node and then (optionally) replicated to every other node in the cluster. You should keep the following in mind when using SLONIK EXECUTE SCRIPT

If you pass the changes through Slony-I via SLONIK EXECUTE SCRIPT (slonik), this allows you to be certain that the changes take effect at the same point in the transaction streams on all of the nodes. This may not be important to you depending on the nature of your change. You should still make sure that no transactions are changing the tables that your script uses while the EXECUTE SCRIPT command is running on the master.

It is essential to use EXECUTE SCRIPT if you alter the names of tables or the namespace they reside in. If you do not then Slony-I will be unaware of the new table name.

It's worth making a couple of comments on "special things" about SLONIK EXECUTE SCRIPT:

• The script must not contain transaction BEGIN or END statements, as the script is already executed inside a transaction though nested transactions are allowed as long are processed within the scope of a single transaction whose BEGIN and END you do not control.

• If there is anything broken about the script, or about how it executes on a particular node, this will cause the slon daemon for that node to panic and crash. You may see various expected messages (positive and negative) in Section 4.7.6.2. If you restart the slon, it will, most likely, try to repeat the DDL script, which will, almost certainly, fail the second time in the same way it did the first time.

  The implication of this is that it is vital that modifications not be made in a haphazard way on one node or another. The schemas must always stay in sync.

• If slon; fails due to a failed DDL change then you should manually (via psql) make the required changes so that the DDL change succeeds the next time slon attempts it.

3.2.2. Applying DDL Changes Directly

DDL changes can be applied directly on a node through an application such as psql. The DDL changes will not be replicated by Slony-I and therefore must be manually applied to every relevant node. The following points should be kept in mind when applying DDL changes directly.

• While DDL changes are not automatically replicated, any INSERT,UPDATE,DELETE statements that you execute will be captured for replication, when run against the origin node. This means that you should not include DDL changes and DML inside the same script when apply DDL directly, because the script will not behave properly when you execute it on other nodes.

  If you, instead, apply DDL using EXECUTE SCRIPT, it is fine to intersperse DDL and DML within the script, as Slony-I handles that appropriately.

• You are responsible for ensuring that your scripts get applied on all other nodes at the correct point in the replication stream (e.g. - on or before the appropriate SYNC event). The best way of doing this with respect to adding and deleting columns is to make sure that new columns always get added on the replica nodes first and that columns being removed are dropped from the master before they are dropped from the replicas. That way, new columns are always available on the subscriber on or before the time they will be needed, and obsolete ones remain on the subscriber until after the last possible reference to them has been replicated.

  Warning

  If columns being added or dropped are mandatory (NOT NULL) or have default values, you will need to go through a longer process to ensure constraints are satisfied at each point in time on all nodes. For instance, if dropping a column that has a NOT NULL constraint, it may take multiple ALTER TABLE statements on each node in order to successfully accomplish this, as the constraint needs to be relaxed first.

• DDL changes that rename a replicated table do not inform Slony-I of the new table name. If you change then name of a replicated table you must allow Slony-I to find the new table name by calling schemadocupdaterelname(integer, integer)

• DDL changes that alter either a primary key, a unique constraint that slony is using, or DDL changes that drop columns that come before the key or unique constraint that Slony-I is using will require Slony-I too reconfigure the arguments on the logtrigger. The function schemadocrepair_log_triggers(only_locked boolean) will reconfigure the trigger arguments of any Slony-I log triggers that are out of date. If true is passed to this function it will only adjust tables that are already locked by the current transaction (if you perform your alter table within a transaction and then call repair_log_triggers() as part of the same transaction then the altered tables will be locked). If you pass false to this function then the function will obtain an exclusive lock on any table that needs the trigger to be reconfigured.