Provided by: slony1-doc_1.2.15-1_all bug
 

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 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 save‐
        points, 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
               (Optional) The ID of the current origin of the set. Default val‐
               ue 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 ex‐
               ecute 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  alterTableForReplica     
        tion(tab_id). This means that all of these tables are  locked  by  this
        slon(1) process for the duration of the SQL script execution.
 
        If  a table’s columns are modified, it is very important that the trig‐
        gers 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 ex‐
        panded into the appropriate replacement tokens.
 
        This uses “schemadocddlscript_complete( integer, text, integer )”  [not
        available as a man page].
 

EXAMPLE

        EXECUTE SCRIPT (
           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  com‐
        pletes, 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  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, 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.
 
                                17 November 2008              EXECUTE SCRIPT(7)