"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 = ("cbbrowne" "@" "ca.afilias.info")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)