SLONIK_EXECUTE_SCRIPT(7)

✨ SLONIK EXECUTE SCRIPT ✨

okay bestie so this is a page from the Slony-I 2.2.11 documentation set — it's the SLONIK EXECUTE SCRIPT(7) manual, and it's literally all about one command. sit with me, we're doing this together 💖

---

🌸 NAME 🌸

EXECUTE SCRIPT — Execute SQL/DDL script

like. the whole vibe. that's it. that's the girl 🎀

---

🦋 SYNOPSIS 🦋

EXECUTE SCRIPT (options);

---

💅 DESCRIPTION 💅

So what this does: it executes a script containing arbitrary SQL statements on all nodes that are subscribed to a set — and it does it at a common controlled point within the replication transaction stream. no cap. everybody hits the same moment together 🫶

The specified event origin must be an origin of a set. periodt.

🚨 The script file must not contain any START or COMMIT TRANSACTION calls. SAVEPOINTS are allowed though, she's flexible 💅

Also, 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. and like… that's a problem. that's the whole problem 😭

Now the options, one by one, no skipping, we don't do that here:

• 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. iconic behaviour honestly 👑
• SQL = 'sql-string-to-execute' — instead of a filename, the SQL statements to execute can be specified as a string literal in single quotes. casual. lowkey convenient 🍓
• 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. don't get cute with it 🙈
• 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. main character selection energy 💅

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. she WILL be waiting in line bestie 😭

Also note: 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. cute little placeholders fr fr 🎀

This uses schemadocddlscript_complete(p_nodes text) [not available as a man page].

---

🌈 EXAMPLE 🌈

here's how the girlies actually call it:

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

and with the selective serve:

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

---

🔥 LOCKING BEHAVIOUR 🔥

history lesson time, sit down 🧚

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're executing probably will. so like, that's on your script, not on slonik 💀

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. one at a time. patiently. 🫶

---

⭐ 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. she is nothing if not polite and orderly ✨

---

👑 VERSION INFORMATION 👑

This command was introduced in Slony-I 1.0. the origin story 💖

• 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. rough. tragic. no notes 😭
• 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. glow up confirmed 💅
• 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. periodt.
• In version 2.0, the default value for EVENT NODE was removed, so a node must be specified. no more guessing bestie, you have to say what you mean 🙈
• 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. the manual fixer-upper 🎀
• As of version 2.2 the DDL performed by an EXECUTE SCRIPT is stored in the sl_log_script table instead of sl_event. new home, same girl 🦋

---

and that's the whole page bestie — documented by the Slony-I 2.2.11 squad, dated 19 September 2024. go forth and execute responsibly, you've got this 💖✨