SLONIK_SET_ADD_TABLE(7)

✨ SET ADD TABLE ✨

okay bestie, we're doing the SLONIK SET ADD TABLE page from the Slony-I 2.2.11 Documentation and honestly? it's giving "let me introduce your table to the replication set" energy. let's go 💅

NAME 🎀

SET ADD TABLE — adds a table to a Slony-I replication set. that's it, that's the vibe 🦋

---

SYNOPSIS ✨

SET ADD TABLE (options);

---

DESCRIPTION 💜

so you've got a user table that already exists, and you want it living inside a replication set. that's this command. one thing tho: the set can't currently be subscribed by any other node yet — that functionality is a whole separate thing, and it's handled by the SLONIK MERGE SET(7) command. iykyk.

here's every option, no skips, no skimming 🌸

• SET ID = ival — the ID of the set you're adding this table to.
• ORIGIN = ival — origin node for the set. this one's optional 🫶
• ID = ival — the unique ID of the table. and listen, these IDs aren't just there to uniquely identify the individual table inside the replication system. the numeric value also decides the order in which tables get locked during a SLONIK LOCK SET(7) command, for example. so these numbers might represent any applicable table hierarchy, so that your slonik command scripts don't deadlock at any critical moment 💀. if you omit this parameter, slonik will check every node it can connect to and find the highest table id being used across all nodes.

this ID must be unique across all sets; you cannot have two tables in the same cluster with the same ID. periodt.

also heads up: Slony-I generates an in-memory array indicating all of the fully qualified table names. if you use large table ID numbers, the sparsely-utilized array can lead to substantial wastage of memory. each potential table ID consumes a pointer to a char, commonly costing 4 bytes per table ID on 32 bit architectures, and 8 bytes per table ID on 64 bit architectures. so like… don't be chaotic with your numbering 🙈

• FULLY QUALIFIED NAME = 'string' — the full table name including the name of the schema. you can skip this one if you're specifying TABLES instead.
• KEY = { 'string' | SERIAL } — optional ✨ the index name that covers the unique and not null set of columns to be used as the row identifier for replication purposes. default is to use the table's primary key. and ngl, the index name is not fully qualified — you must omit the namespace.
• TABLES = 'string' — a POSIX regular expression that specifies the list of tables that should be added. this regex is evaluated by PostgreSQL against the list of fully qualified table names on the set origin to find the tables that should be added. if TABLES is omitted then FULLY QUALIFIED NAME must be specified.

⚠️ Warning — the TABLES option requires that all the tables are in 'good form' to be replicated en masse. the request will fail, not configuring any tables for replication, if it encounters any of the following problems: - each table must have a PRIMARY KEY defined, and a candidate primary key will not suffice. - if a table is found that is already replicated, the request will fail. - the TABLES option needs to automatically assign table ID values, and looks through the configuration on every node specified by SLONIK ADMIN CONNINFO(7), finding the largest ID in use, and starting after that for the table IDs that it assigns. it considers it a 'benign' failure to find a node that does not yet have a Slony-I schema assigned, as that may be expected to occur if tables are configured before all the nodes have been configured using SLONIK STORE NODE(7). if there is no Slony-I schema, then that node certainly hasn't contributed anything to an increase in the table IDs in use. on the other hand, if a node specified by SLONIK ADMIN CONNINFO(7) is not available to be queried, the request will fail. 😭

• COMMENT = 'string' — a descriptive text added to the table entry. little love note for future you 💖
• ADD SEQUENCES= boolean — a boolean value that indicates if any sequences attached to columns in this table should also be automatically added to the replication set. this defaults to false.

so this whole thing rides on schemadocsetaddtable(p_tab_comment integer, p_tab_idxname integer, p_fqname text, p_tab_id name, p_set_id text) [not available as a man page]. iconic of it tbh 👑

---

EXAMPLE 🌈

here's the whole thing in action, bestie:

SET ADD TABLE (
    SET ID = 1,
    ORIGIN = 1,
    ID = 20,
    FULLY QUALIFIED NAME = 'public.tracker_ticket',
    COMMENT = 'Support ticket',
    ADD SEQUENCES=false
);

or 🦋

SET ADD TABLE (
    SET ID=1,
    TABLES='public\\.tracker*'
);

---

ERROR MESSAGES 💀

here are some of the error messages you may encounter if you're adding tables incorrectly. not me showing you the fail comp 😭

• Slony-I: setAddTable_int: table public.my_table PK column id nullable — primary keys (or candidates thereof) are required to have all column defined as NOT NULL. if you have a PK candidate that has columns that are not thus restricted, Slony-I will reject the table with this message.
• Slony-I: setAddTable_int: table id 14 has already been assigned! — the table id, stored in sl_table.tab_id, is required to be unique across all tables/nodes/sets. apparently you have tried to reused a table ID. 🙈
• Slony-I: setAddTable_int(): table public.my_table has no index mt_idx_14 — this will normally occur with candidate primary keys; apparently the index specified is not available on this node.
• Slony-I: setAddTable_int(): table public.my_table not found — worse than an index missing, the whole table is missing. apparently whatever process you were using to get the schema into place everywhere didn't work properly.
• Slony-I: setAddTable_int(): public.my_view is not a regular table — you can only replicate (at least, using SET ADD TABLE) objects that are ordinary tables. that doesn't include views or indexes. (indexes can come along for the ride, but you don't ask to replicate an index…)
• Slony-I: setAddTable_int(): set 4 not found — you need to define a replication set before assigning tables to it.
• Slony-I: setAddTable(): set 4 has remote origin — this will occur if set 4 is configured with, as origin, node 1, and then you submit a SET ADD TABLE request involving that set to some other node than node 1. this would be expected to occur if there was some confusion in the admin conninfo configuration in the slonik script preamble…
• Slony-I: cannot add table to currently subscribed set 1 — Slony-I does not support adding tables to sets that are already participating in subscriptions. instead, you need to define a new replication set, and add any new tables to that set. you might then use SLONIK MERGE SET(7) to merge the new set into an existing one, if that seems appropriate.

---

LOCKING BEHAVIOUR 🔥

on the origin node, this operation requires a brief exclusive lock on the table in order to alter it to add replication triggers. on subscriber nodes, corresponding locking takes place at the time of the SUBSCRIBE_SET event. so yeah, it's quick but it's real 👑

---

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. patient queen behavior fr fr 🫶

---

VERSION INFORMATION 🌱

this command was introduced in Slony-I 1.0. a legend since day one 💅

---

and that's the whole page bestie, slay. this was Slony-I 2.2.11 Documentation, dated 19 September 2024 — mwah, go add those tables, you got this 💖✨🎀