Thanks.
But could you please be more practical?

Will those two INSERT go ALWAYS in the same transaction on a subscriber or
not?
In other words, does slon daemon try to execute all subscriber updates in
the same transaction as on master or not?

If yes, deferrable constraints are okay.
If not, nobody should use deferrable constraints together with Slony,
because sometimes query sequence correct for master will be incorrect for
slave.

On 7/18/07, Christopher Browne <cbbrowne at ca.afilias.info> wrote:
>
> "Dmitry Koterov" <dmitry at koterov.ru> writes:
> > Hello.
> > The question is: does Slony correctly support DEFERRABLE foreign keys
> during the replication?
> > Suppose I have a table with deferrable foreign key constraint referenced
> to self:
> > tbl(id INTEGER, parent_id INTEGER);
> > The field parent_id references to the table tbl with deferrable
> constraint:
> > ALTER TABLE tbl ADD CONSTRAINT parent FOREIGN KEY (parent_id)
> > REFERENCES tbl(id) DEFERRABLE INITIALLY DEFERRED;
> > Then I insert a child element in tbl BEFORE inserting a parent one:
> > BEGIN;
> > INSERT INTO tbl(id, parent_id) VALUES(1, 2); -- child first!
> > INSERT INTO tbl(id, parent_id) VALUES(2, null); -- parent
> > COMMIT;
> > It is correct, because the constraint is deferrable and checked at the
> end of transaction.
> > But I suppose that in the event log this two INSERTs will possibly (?)
> be presented one after one and in DIFFERENT trtansactions, so, the first =
one
> will fail with
> > constraint check error immediately. Please say, could it happen or not?
> Or, if some statements were initially in a single transaction, slon daemon
> will run them in a
> > single transaction too on a replica?
> > P.S.
> > Please do not advice to change the order of INSERTs, because in a real
> application I run these inserts as:
> > INSERT INTO tbl(id, parent_id) SELECT * FROM othertbl;
> > and the order is totally unpredictable.
>
> Slony-I does not expressly, as such "support deferrable foreign keys;"
> most of Slony-I's features are not directed towards supporting one or
> another specific PostgreSQL capability.  Instead, it seeks to provide
> functionality that works alongside the DBMS so that the usual answer
> to questions of the form "Does Slony-I support <X>?" ought to be "No
> reason why it shouldn't!"
>
> That is indeed the case in this case.
>
> Yes, in the event log, your two INSERTs will be submitted; one of the
> things Slony-I does is to attach a sequence value to the log (called
> log_actionid).  The first INSERT will have a smaller value than the
> second one, and when updates are applied to subscribers, log_actionid
> is considered as part of the ordering of updates.  Thus, whether those
> INSERTs are in the same transaction or not, the first one will indeed
> be applied before the second one.
>
> The "log_actionid" supports the notion, in Slony-I, of applying
> changes in what it terms an "agreeable order."  See section 2.2 of the
> Slony-I Concept paper.
>
> http://developer.postgresql.org/~wieck/slony1/Slony-I-concept.pdf
> --
> output =3D ("cbbrowne" "@" "ca.afilias.info")
> <http://dba2.int.libertyrms.com/>
> Christopher Browne
> (416) 673-4124 (land)
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20070718/=
951670fb/attachment.htm