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 com‐
       mon 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  impli‐
       cation  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  sepa‐
       rately.  As  a result, it is fine for later statements to refer to objects or attributes created or modi‐
       fied 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  (un‐
       til  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  re‐
       pair_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.

                                                22 September 2023                       SLONIK EXECUTE SCRIPT(7)