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 an origin of a set.  The script file must  not  contain
       any  START  or  COMMIT  TRANSACTION  calls  but SAVEPOINTS are allowed.  In addition, non-
       deterministic DML statements (like updating a  field  with  CURRENT_TIMESTAMP)  should  be
       avoided, since the data changes done by the script will be different on each node.

       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 replication data stream, so
              the file does not need to be accessible on any of the nodes.

       SQL = 'sql-string-to-execute'
              Instead of a filename the SQL statements to execute can be specified  as  a  string
              literal in single quotes.

       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 can be a
              single node value or a comma separated list of nodes. This option causes the script
              to  be  propagated  by  all  nodes  but  executed only on the specified nodes.  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_nodes text)” [not available as a man page].

EXAMPLE

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

       EXECUTE SCRIPT (
          FILENAME = '/tmp/changes_2008-04-01.sql',
          EVENT NODE = 1,
          EXECUTE ONLY ON='1,2,3'
       );

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.

       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.

       As  of  version  2.2 the DDL performed by an EXECUTE SCRIPT is stored in the sl_log_script
       table instead of sl_event.

                                           28 July 2024                  SLONIK EXECUTE SCRIPT(7)