Re: Setting table ids in slony

On 4/24/07, Richard Huxton <dev(at)archonet(dot)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