NAME

       SET ADD TABLE - Add a table to a Slony-I replication set

SYNOPSIS

       SET ADD TABLE (options);

DESCRIPTION

       Add an existing user table to a replication set. The set cannot currently be subscribed by
       any other node - that functionality is supported by the SLONIK MERGE SET(7) command.

       SET ID = ival
              ID of the set to which the table is to be added.

       ORIGIN = ival
              Origin node for the set. (Optional)

       ID = ival
              Unique ID of the table. These ID's are not  only  used  to  uniquely  identify  the
              individual  table  within the replication system. The numeric value of this ID also
              determines the order in which the tables are locked in a SLONIK LOCK SET(7) command
              for  example.  So  these  numbers might represent any applicable table hierarchy to
              make sure the slonik command scripts do not deadlock at  any  critical  moment.  If
              this  parameter is omitted then slonik will check every node that 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.

              Note  that  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.

       FULLY QUALIFIED NAME = 'string'
              The  full  table  name  including  the  name of the schema.  This can be omitted if
              TABLES is specified 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. 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  regular  expression  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.

       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

       This  uses  “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].

EXAMPLE

       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 adding tables incorrectly:

       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.

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

                                        21 September 2017                 SLONIK SET ADD TABLE(7)