NAME¶
EXECUTE SCRIPT - Execute SQL/DDL script
SYNOPSIS¶
EXECUTE SCRIPT (options);
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 an origin of a set. The script file must not
contain any
START or
COMMIT TRANSACTION calls but SAVEPOINTS are
allowed. In addition, non-deterministic DML statements (like updating a field
with
CURRENT_TIMESTAMP) should be avoided, since the data changes done
by the script will be different on each node.
- 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 replication
data stream, so the file does not need to be accessible on any of the
nodes.
- SQL = 'sql-string-to-execute'
- Instead of a filename the SQL statements to execute can be specified as a
string literal in single quotes.
- EVENT NODE = ival
- (Mandatory unless EXECUTE ONLY ON is given) The ID of the current origin
of the set. If EXECUTE ONLY ON is given, EVENT NODE must specify the same
node or be omitted.
- EXECUTE ONLY ON = ival
- (Optional) The ID of the only node to actually execute the script. This
can be a single node value or a comma separated list of nodes. This option
causes the script to be propagated by all nodes but executed only on the
specified nodes. 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 potentially heavily-locking operation, which means that it
can get stuck behind other database activity.
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(p_nodes text)” [not
available as a man page].
EXAMPLE¶
EXECUTE SCRIPT (
FILENAME = '/tmp/changes_2008-04-01.sql',
EVENT NODE = 1
);
EXECUTE SCRIPT (
FILENAME = '/tmp/changes_2008-04-01.sql',
EVENT NODE = 1,
EXECUTE ONLY ON='1,2,3'
);
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. In the 2.0 branch this is no
longer the case. EXECUTE SCRIPT won't obtain any locks on your application
tables though the script that you executing probably will.
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.
SLONIK EVENT CONFIRMATION BEHAVIOUR ¶
Slonik waits for the command submitted to the previous event node to be
confirmed on the specified event node before submitting this command.
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 invocation 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 (until 2.0),
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. With version 2.0 no locks on application
tables are obtained by Slony-I
In version 2.0, the default value for
EVENT NODE was removed, so a
node must be specified.
As of version 2.0.7, the log triggers on all replicated tables are checked to
ensure their parameters match the primary key on the table. If they
do
not match, those tables that are exclusively locked as a result of the DDL
request will have the triggers recreated to match the primary key. Tables that
do not have an exclusive lock will
not be corrected, but a warning
message will be generated. The function
repair_log_triggers(only_locked
boolean) may be used manually to correct the triggers on those tables.
As of version 2.2 the DDL performed by an EXECUTE SCRIPT is stored in the
sl_log_script table instead of sl_event.