On 4/24/07, Brad Nicholson <bnichols at ca.afilias.info> wrote:
> On Tue, 2007-04-24 at 05:39 -0600, 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.
> >
> > 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?
>
> Correct.
>
> > 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?
>
> That would be one way of doing it.  You would have to drop these tables
> from replication and re-add them with the new id's though.
>
> If this scenario is one that you expect to face, I'd recommend leaving
> gaps in your table id's so you can plug things in later without having
> to do this.

Thanks for the info.  Here's a response I got from someone on the
postgres mailing list:

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."

How does that tie in?  I've read somewhere else that the locking
issues crop up if you use table inheritance.  We definitely won't be.
So do I still need to worry about the ordering of the IDs?

Pat