Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help

       EXECUTE SCRIPT -	Execute	SQL/DDL	script

       EXECUTE SCRIPT (options);

       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 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 up-
       dating 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  replica-
	      tion  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 spec-
	      ified as a string	literal	in single quotes.

	EVENT NODE = ival
	      (Mandatory  unless  EXECUTE ONLY ON is given) The	ID of the cur-
	      rent origin of the set. If EXECUTE ONLY ON is given, EVENT  NODE
	      must specify the same node or be omitted.

	      (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

       See also	the warnings in	distribution documentation on DDL changes.

       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 ex-
       panded into the appropriate replacement tokens.

       This uses ddlscript(integer,text,integer).

	  FILENAME = '/tmp/changes_2008-04-01.sql',

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

       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 waits for	the command submitted to the previous event node to be
       confirmed on the	specified event	node before submitting this command.

       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 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	(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 ap-
       plication 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 re-
       sult 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.

				  30 May 2016	      SLONIK EXECUTE SCRIPT(7)


Want to link to this manual page? Use this URL:

home | help