SLONIK EXECUTE SCRIPT

Description

Executes a script containing arbitrary SQL statements on all nodes that are subscribed to a set at a common controlled point within the replication transaction stream.

The specified event origin must be the origin of the set. The script file must not contain any START or COMMIT TRANSACTION calls. (This changes somewhat in PostgreSQL 8.0 once nested transactions, aka savepoints, are supported) In addition, non-deterministic DML statements (like updating a field with CURRENT_TIMESTAMP) must be avoided, since the data changes done by the script are explicitly not replicated.

See also the warnings in Section 17.

Note that this is a potentially heavily- locking operation, which means that it can get stuck behind other database activity.

In versions up to (and including) the 1.2 branch, at the start of this event, all replicated tables are unlocked via the function alterTableRestore(tab_id). After the SQL script has run, they are returned to "replicating state" using alterTableForReplication(tab_id). This means that all of these tables are locked by this slon process for the duration of the SQL script execution.

If a table's columns are modified, it is essential that the triggers be regenerated (e.g. - by alterTableForReplication(tab_id)), otherwise the attributes in the logtrigger() trigger may be inappropriate for the new form of the table schema.

Note that if you need to make reference to the cluster name, you can use the token @CLUSTERNAME@; if you need to make reference to the Slony-I namespace, you can use the token @NAMESPACE@; both will be expanded into the appropriate replacement tokens.

This uses schemadocddlscript_complete( integer, text, integer ).

Example

EXECUTE SCRIPT (
   SET ID = 1,
   FILENAME = '/tmp/changes_2008-04-01.sql',
   EVENT NODE = 1
);
    

Locking Behaviour

Up until the 2.0 branch, each replicated table received an exclusive lock, on the origin node, in order to remove the replication triggers; after the DDL script completes, those locks will be cleared.

After the DDL script has run on the origin node, it will then run on subscriber nodes, where replicated tables will be similarly altered to remove replication triggers, therefore requiring that exclusive locks be taken out on each node, in turn.

As of the 2.0 branch, Slony-I uses a GUC that controls trigger behaviour, which allows deactivating the Slony-I-created triggers during this operation without the need to take out exclusive locks on all tables. Now, the only tables requiring exclusive locks are those tables that are actually altered as a part of the DDL script.

Version Information

This command was introduced in Slony-I 1.0.

Before Slony-I version 1.2, the entire DDL script was submitted as one PQexec() request, with the implication that the entire script was parsed based on the state of the database before invokation of the script. This means statements later in the script cannot depend on DDL changes made by earlier statements in the same script. Thus, you cannot add a column to a table and add constraints to that column later in the same request.

In Slony-I version 1.2, the DDL script is split into statements, and each statement is submitted separately. As a result, it is fine for later statements to refer to objects or attributes created or modified in earlier statements. Furthermore, in version 1.2, the slonik output includes a listing of each statement as it is processed, on the set origin node. Similarly, the statements processed are listed in slon logs on the other nodes.

In Slony-I version 1.0, this would only lock the tables in the specified replication set. As of 1.1, all replicated tables are locked (e.g. - triggers are removed at the start, and restored at the end). This deals with the risk that one might request DDL changes on tables in multiple replication sets.

In version 2.0, the default value for EVENT NODE was removed, so a node must be specified.