Provided by: slony1-2-doc_2.0.7-3build1_all bug


       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
              (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 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.

       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(integer,  text, integer)” [not
       available as a man page].


          SET ID = 1,
          FILENAME = '/tmp/changes_2008-04-01.sql',
          EVENT NODE = 1


       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.  Due  to bug #137 you should avoid concurrent writes to
       the tables being modified by the script while the script is running.

       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 (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.

                                3 December 2011       SLONIK EXECUTE SCRIPT(7)