Man Linux: Main Page and Category List

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) The ID of the current origin of the set.

       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 [“Locking Issues” [not
       available as a man page]] operation, which means that it can get  stuck
       behind other database activity.

       In  versions up to (and including) the 1.2 branch, 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 execution.

       If a table’s columns are modified, it is essential that the triggers be
       regenerated (e.g. - by alterTableForReplication(tab_id)), otherwise the
       attributes in the logtrigger() trigger 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].

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.

       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.

       As of the  2.0  branch,  Slony-I  uses  a  GUC  that  controls  trigger
       behaviour,  which  allows  deactivating  the  Slony-I-created  triggers
       during this operation without the need to take out exclusive  locks  on
       all  tables.  Now,  the only tables requiring exclusive locks are those
       tables that are actually altered as a part of the DDL script.

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

       In version 2.0, the default value for EVENT NODE was removed, so a node
       must be specified.

                                  12 May 2010         SLONIK EXECUTE SCRIPT(7)