Provided by: slony1-doc_1.2.14-1ubuntu1_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
              (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
       database activity.

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


          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.

                               19 September 2008             EXECUTE SCRIPT(7)