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 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 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 23.4.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 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 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
While it is vitally necessary to use EXECUTE SCRIPT to propagate DDL modifications to tables that are being replicated, there are several sorts of changes that you might wish to handle some other way:
There are various sorts of objects that don't have triggers that Slony-I doesn't replicate, such as stored functions, and it is quite likely to cause you grief if you propagate updates to them associated with a replication set where EXECUTE SCRIPT will lock a whole lot of tables that didn't really need to be locked.
If you are propagating a stored procedure that isn't used all the time (such that you'd care if it was briefly out of sync between nodes), then you could simply submit it to each node using psql, making no special use of Slony-I.
If it does matter that the object be propagated at the same location in the transaction stream on all the nodes, then you but no tables need to be locked, then you need to use EXECUTE SCRIPT, locking challenges or no.
You may want an extra index on some replicated node(s) in order to improve performance there.
For instance, a table consisting of transactions may only need indices related to referential integrity on the "origin" node, and maximizing performance there dictates adding no more indices than are absolutely needed. But nothing prevents you from adding additional indices to improve the performance of reports that run against replicated nodes.
It would be unwise to add additional indices that constrain things on replicated nodes, as if they find problems, this leads to replication breaking down as the subscriber(s) will be unable to apply changes coming from the origin that violate the constraints.
But it's no big deal to add some performance-enhancing indices. You should almost certainly not use EXECUTE SCRIPT to add them; that leads to some replication set locking and unlocking tables, and possibly failing to apply the event due to some locks outstanding on objects and having to retry a few times before it gets the change in. If you instead apply the index "directly" such as with psql, you can determine the time at which the table lock is introduced. Adding an index to a table will require an exclusive lock for the time it takes to build the index; that will implicitly stop replication, while the index builds, but shouldn't cause any particular problems. If you add an index on a table that takes 20 minutes to build, replication will block for 20 minutes, but should catch up quickly enough once the index is created.
Slony-I stores the "primary index" name in sl_table, and uses that name to control what columns are considered the "key columns" when the log trigger is attached. It would be plausible to drop that index and replace it with another primary key candidate, but changing the name of the primary key candidate would break things.
A method for testing DDL changes has been pointed out as a likely "best practice."
You need to test DDL scripts in a non-destructive manner.
The problem is that if nodes are, for whatever reason, at all out of sync, replication is likely to fall over, and this takes place at what is quite likely one of the most inconvenient times, namely the moment when you wanted it to work.
You may indeed check to see if schema scripts work well or badly, by running them by hand, against each node, adding BEGIN; at the beginning, and ROLLBACK; at the end, so that the would-be changes roll back.
If this script works OK on all of the nodes, that suggests that it should work fine everywhere if executed via slonik. If problems are encountered on some nodes, that will hopefully allow you to fix the state of affairs on those nodes so that the script will run without error.
| Warning |
If the SQL script contains a COMMIT; somewhere before the ROLLBACK; , that may allow changes to go in unexpectedly. |