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

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

EXAMPLE

       EXECUTE SCRIPT (
          SET ID = 1,
          FILENAME = '/tmp/changes_2008-04-01.sql',
          EVENT NODE = 1
       );

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

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

                                         6 February 2014                 SLONIK EXECUTE SCRIPT(7)