SLONIK EXECUTE SCRIPT(7Configuration and Action commanSLONIK EXECUTE SCRIPT(7)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 repli‐
cation 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 replica‐
tion 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 spec‐
ified as a string literal in single quotes.
EVENT NODE = ival
(Mandatory unless EXECUTE ONLY ON is given) The ID of the cur‐
rent 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 distribution documentation on DDL changes.
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 ddlscript(integer,text,integer).
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.
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 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 can‐
not 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 speci‐
fied 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 cor‐
rect 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.
18 January 2015 SLONIK EXECUTE SCRIPT(7)
