SLONIK EXECUTE SCRIPT

Name

Synopsis

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.

SET ID = ival

The unique numeric ID number of the set
affected by the script

FILENAME = '/path/to/file'

The name of the file containing the SQL script to
execute. This might be a relative path, relative to the location of
the slonik instance you are running, or, preferably,
an absolute path on the system where slonik is to run.

The contents of the file are propagated as part of
the event, so the file does not need to be accessible on any of the
nodes.

EVENT NODE = ival

(Mandatory) The ID of the current origin of the set.

EXECUTE ONLY ON = ival

(Optional) The ID of the only
node to actually execute the script. This option causes the
script to be propagated by all nodes but executed only by one.
The default is to execute the script on all nodes that are
subscribed to the set.

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.

Example

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.