Provided by: slony1-doc_1.2.15-1_all
EXECUTE SCRIPT - Execute SQL/DDL script
EXECUTE SCRIPT (options);
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
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
(Optional) The ID of the current origin of the set. Default
value is 1.
EXECUTE ONLY ON = ival
(Optional) The ID of the only node to actually execute the
script. This option causes the script to be executed, by
slonik(1), only on the one node specified. The default is to
execute the script on all nodes that are subscribed to the set.
See also the warnings in “Database Schema Changes (DDL)” [not available
as a man page].
Note that this is a locking [“Locking Issues” [not available as a man
page]] operation, which means that it can get stuck behind other
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(1) process for the duration of the SQL script
If a table’s columns are modified, it is very important that the
triggers be regenerated, otherwise they 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 )” [not
available as a man page].
EXECUTE SCRIPT (
SET ID = 1,
FILENAME = ’/tmp/changes_2004-05-01.sql’,
EVENT NODE = 1
Each replicated table receives 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.
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.
17 November 2008 EXECUTE SCRIPT(7)