Thread: Setting table ids in slony
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. So let's take two tables, users and articles. Users: id serial primary key name varchar(80) Articles: id serial primary key title varchar(80) body text user_id (foreign key on users.id) If I'm using slony to replicate this db, then I need to give the users table an id of 1, and articles should be 2, right? What happens when I add a table that's higher up on the heirarchy? For example now I want to add a companies table. Companis: id serial primary key name varchar(80) Users: company_id (fkey on companies.id) Now would I have to reassign IDs for the tables? companies->1, users->2, articles->3? Thanks for any help. Pat
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. 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." -- Richard Huxton Archonet Ltd
On 4/24/07, Richard Huxton <dev@archonet.com> 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
> Sounds like theoretically it could matter, but in practice it doesn't. > I'd like a more definite answer though. You could ask the slony guys directly ;) They'd know a lot more about it than the pg-general list would. http://lists.slony.info/mailman/listinfo -- Postgresql & php tutorials http://www.designmagick.com/
Pat Maddox wrote: > Sounds like theoretically it could matter, but in practice it doesn't. > I'd like a more definite answer though. Well, most of the locking issues with Slony seem to be with administrative commands (setting up a replication set, altering it) which require taking locks. If your application(s) lock tables in the order C,B,A and slony in A,B,C then they can deadlock waiting on each other. This is a problem you'll face any time you have two sets of exclusive locks interacting. I'm fortunate in that the systems I deal with all allow for some downtime in application access, so I just schedule slony changes for these periods. -- Richard Huxton Archonet Ltd