On 4/24/07, Richard Huxton <dev@xxxxxxxxxxxx> wrote:
Pat Maddox wrote: > I want to start using slony for replication, and have a question about > setting table IDs when creating replication sets. The docs say that > you have to be careful in what IDs you assign to the tables - if > there's a relationship between two tables, the parent needs to have a > lower ID. Can you give a reference for this? AFAIK the only requirement is that all linked tables need to be in the same set. I seem to recall reading that if you use inheritence you should check the parent comes before its children, but nothing about fkeys.
Thanks for your reply, Richard. http://www.onlamp.com/pub/a/onlamp/2004/12/16/slony_install.html?page=2 says "Be careful when setting the ID number of a table; it also designates the order in which Slony will lock the tables. This means that master tables should have IDs lower than those of detail tables. The relationship hierarchy of your schema should help you determine the order of the numbers. If the ordering of the table IDs is backward or incorrect, there may be problems with deadlocking the slon process or PostgreSQL." Also directly from the documentation http://slony.info/documentation/stmtsetaddtable.html "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 LOCK SET command for example. So these numbers should represent any applicable table hierarchy to make sure the slonik command scripts do not deadlock at any critical moment."
http://cbbrowne.com/info/faq.html "Q: Is the ordering of tables in a set significant? A: Most of the time, it isn't. You might imagine it of some value to order the tables in some particular way in order that "parent" entries would make it in before their "children" in some foreign key relationship; that isn't the case since foreign key constraint triggers are turned off on subscriber nodes."
Sounds like theoretically it could matter, but in practice it doesn't. I'd like a more definite answer though. Pat
