Steve Singer ssinger at
Tue Dec 7 10:25:20 PST 2010
This design proposal describes what I have in mind for implementing bulk 
table adding.

The goal of this change is to make it easier to add tables to slony.  A 
very common use-case is to want to add all your tables to a single slony 
replication set.  Variations on that theme include 'all the tables in a 
schema' or 'all the tables except these'.

The change proposed solution is to allow users to specify a regular 
expression (regex) that table names will be compared against to find the 

Syntax examples

# existing syntax for a single table.
set add table (set id=2, origin=1, id=5, fully qualified name = 
'public.newtable', comment='some new table');

#Add all tables in public
set add table(set id=1, origin=1, tables='public.*',comment="replicated 
public tables');

#Add a single table with the tables command
set add table(set id=1, origin=1, tables='public.newtable',comment='test');

#syntax to default as much as possible
set add table(set id=1, tables='public.*');

#all tables except one
set add table(set id=1, tables='public.(!password)');

#Starting number for id values
set add table(set id=1, id=1000,tables='public.*');

Implementation Overview

1. Modify parser.y in slonik to take a 'tables' option and make 
fully_qualified_name ,set origin and id all optional.

2.  Add in checks to make sure that either tables or 
fully_qualified_table is specified.

3. Write a routine that will connect to each node with a admin conninfo
and find the next free id for a table (largest id currently used +1).

4. Modify slonik to query the pgsql catalog to get a list of tables to 
add. The intent is to let the pgsql regex handling handle the regex and 
match against tables in the catalog (regex_matches)

5. Modify slonik to loop through this list and add the tables using the 
assigned ids.

(A large part of 4,5 will be done in stored procedures)

6. Update documentation + tests.


To determine the next available table id I intend to loop through each 
node that slonik has an admin conninfo for and get the maximum id value 
from sl_table in each.  I then intend to take maximum of those + 1. This 
method is problematic if you try to do multiple 'set add table' commands 
concurrently.   This is no worse than if two people/scripts manually 
pick the same table id values today.   One option to avoid this is to 
have slonik obtain the config_lock on each node first, then submit the 
setAddTable then release all of the config locks.  I am inclined to do 
this unless someone can think of a reason why it might cause problems.

To find the origin of a set we connect to an admin coninfo node and 
check sl_set for that set.  We then connect to the origin specified in 
sl_set to perform the setAddTable.

Compatibility Issues
These changes should preserve the existing behaviour of the 'set add 
table' command.

More information about the Slony1-hackers mailing list