trusty (7) SLONIK_EXECUTE_SCRIPT.7.gz

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)