17. Database Schema Changes (DDL)

When changes are made to the database schema, e.g. - adding fields to a table, it is necessary for this to be handled rather carefully, otherwise different nodes may get rather deranged because they disagree on how particular tables are built.

If you pass the changes through Slony-I via SLONIK EXECUTE SCRIPT (slonik) / schemadocddlscript_complete( integer, text, integer ) (stored function), this allows you to be certain that the changes take effect at the same point in the transaction streams on all of the nodes. That may not be so important if you can take something of an outage to do schema changes, but if you want to do upgrades that take place while transactions are still winding their way through your systems, this is necessary.

It is essential to use EXECUTE SCRIPT if you alter tables so as to change their schemas. If you do not, then you may run into the problems described here where triggers on modified tables do not take account of the schema change. This has the potential to corrupt data on subscriber nodes.

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. In PostgreSQL version 8, the introduction of nested transactions changes this somewhat, but you must still remain aware that the actions in the script 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 26.6.2. If you restart the slon, it will, more likely than not, try to repeat the DDL script, which will, almost certainly, fail the second time in the same way it did the first time. I have found this scenario to lead to a need to go to the "master" node to delete the event from the table sl_event in order to stop it from continuing to fail.

  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.

• For slon to, at that point, "panic" is probably the correct answer, as it allows the DBA to head over to the database node that is broken, and manually fix things before cleaning out the defective event and restarting slon. You can be certain that the updates made after the DDL change on the provider node are queued up, waiting to head to the subscriber. You don't run the risk of there being updates made that depended on the DDL changes in order to be correct.

• When you run SLONIK EXECUTE SCRIPT, this causes the slonik to request, for each table in the specified set, an exclusive table lock.

  It starts by requesting the lock, altering the table to remove Slony-I triggers, and restoring any triggers that had been hidden:

  BEGIN;
  LOCK TABLE table_name;
  SELECT _oxrsorg.altertablerestore(tab_id);
  --tab_id is _slony_schema.sl_table.tab_id

  After the script executes, each table is "restored" to add back either the trigger that collects updates at the origin or that denies updates on subscribers:

  SELECT _oxrsorg.altertableforreplication(tab_id);
  --tab_id is _slony_schema.sl_table.tab_id
  COMMIT;

  Note that this is what allows Slony-I to take notice of alterations to tables: before that SYNC, Slony-I has been replicating tuples based on the old schema; after the DDL_SCRIPT, tuples are being replicated based on the new schema.

  On a system which is busily taking updates, it may be troublesome to "get in edgewise" to actually successfully engage all the required locks. The locks may run into deadlocks. This points to two ways to address this:

• You may be able to define replication sets that consist of smaller sets of tables so that fewer locks need to be taken in order for the DDL script to make it into place.

  If a particular DDL script only affects one table, it should be unnecessary to lock all application tables.

  Note: Actually, as of version 1.1.5 and later, this is NOT TRUE. The danger of someone making DDL changes that crosses replication sets seems sufficiently palpable that slon has been changed to lock ALL replicated tables, whether they are in the specified replication set or not.

• You may need to take a brief application outage in order to ensure that your applications are not demanding locks that will conflict with the ones you need to take in order to update the database schema.

• In Slony-I versions 1.0 thru 1.1.5, the script is processed as a single query request, which can cause problems if you are making complex changes. Starting in version 1.2, the script is properly parsed into individual SQL statements, and each statement is submitted separately, which is a preferable handling of this.

  The trouble with one query processing a "compound statement" is that the SQL parser does its planning for that entire set of queries based on the state of the database at the beginning of the query.

  This causes no particular trouble if those statements are independent of one another, such as if you have two statements to add two columns to a table.

  alter table t1 add column c1 integer; alter table t1 add column c2 integer;

  Trouble arises if a subsequent query needs to reference an earlier one. Consider the following DDL statements...

  alter table t1 add column c1 integer; create sequence s1; update t1 set c1=nextval('s1'); alter table t1 alter column c1 set not null; alter table t1 add primary key(c1);

  Up until Slony-I version 1.2, this query would fail. It would specifically fail upon reaching the UPDATE statement, complaining that column c1 doesn't exist. This happens because all of those queries are parsed based on the state of things immediately before the query. So, the UPDATE is evaluated based on a table definition before the new column was added. Oops.

  If you are running one of the earlier versions, the workaround is that you invoke a separate SLONIK EXECUTE SCRIPT request with a separate script, cutting off to a new script each time a statement refers to something created in previous statements.

  In Slony-I version 1.2, there is a state machine that pulls apart the DDL script into individual statements. Each statement is submitted as a separate PQexec() request, with the result that this is no longer an issue.

Unfortunately, this nonetheless implies that the use of the DDL facility is somewhat fragile and fairly dangerous. Making DDL changes must not be done in a sloppy or cavalier manner. If your applications do not have fairly stable SQL schemas, then using Slony-I for replication is likely to be fraught with trouble and frustration. See the section on locking issues for more discussion of related issues.

There is an article on how to manage Slony-I schema changes here: Varlena General Bits