On 4/26/07, Melvin Davidson <mdavidson at cctus.com> wrote:
>
> Andrew commented
> >It seems silly to encourage people to adopt a particular ordering when
> >1) we have no evidence to demonstrate that ordering matters
> >2) we haven't identified even a theoretical failure case that is based
> on
> >ordering
> >3) we can't reasonably believe that a given ordering is superior for
> >avoiding an unknown issue
>
> Actually, I have encountered a case where the sequence DOES MATTER!
>
> I have two tables with referencial Foreign Key constraints to each
> other.
> Don't ask why, because that is the way it was when I accepted the
> position
> and was told I could not change it. However if I find whom it was that
> did
> it, I am sure they will require hospitalization for some trauma they
> will
> sustain. :)
>
> The bottom line is, the table that has an insert done on it first MUST
> have
> a lower id than then second one, as subscribing will fail if the order
> is
> reversed.



Hi Melvin,

I too am very interested to see this example, especially if it reliably
produces the failure condition. If you can't provide the actual code, can
you please provide a detailed description so that I can implement it as a
test? As best as I can tell from your description above, the schema looks
something like this:

CREATE TABLE a (
  a_id serial PRIMARY KEY,
  b_id integer NOT NULL
);

CREATE TABLE b (
  b_id serial PRIMARY KEY,
  a_id integer NOT NULL REFERENCES a
);

ALTER TABLE a ADD CONSTRAINT a_b_id_fkey FOREIGN KEY a_id REFERENCES a;

This creates two tables with foreign keys into each other. Does that
accurately describe the situation you observed? If so, what is the order
necessary for subscription to succeed? I'd really appreciate your help with
this. It's been a minor intellectual irritant for a few years now and I'd
really like to get it nailed down and clearly understood.

Andrew
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070503/=
b55e5665/attachment.htm