[Bucardo-general] problem with referential integrity.

[Grant Maxwell]

Hi Folks

I have setup to very simple tables with a foreign key from one to the  
other. The foreign key rules are that ON UPDATE - CASCADE the change.  
This means that when updating the "referred to" table, every  
"referring" record in the other tables is automagically updated.

This works beautifully - until - i replicate with bucardo. The problem  
is that bucardo is somehow inhibiting the cascade rule and so while  
the referred to table is update, the referring records are not -  
essentially creating a referential integrity problem. Actually the  
problem does not even get triggered as a error. nasty.

Here is the schema

database testref  on server localhost

CREATE TABLE mailaddresslist
(
   pkmailaddressid integer NOT NULL,
   mailaddress character varying(100) NOT NULL,
   CONSTRAINT mailaddressid PRIMARY KEY (pkmailaddressid)
)
WITH (
   OIDS=FALSE
);

CREATE TABLE messages
(
   mailid integer NOT NULL,
   mailaddressid integer NOT NULL,
   message text,
   CONSTRAINT pkeymessage PRIMARY KEY (mailid),
   CONSTRAINT fkmailaddressid FOREIGN KEY (mailaddressid)
       REFERENCES mailaddresslist (pkmailaddressid) MATCH SIMPLE
       ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
   OIDS=FALSE
);

Database testref on server testref_t2
schema same as above

sync rule

testref2->public.testref->mailaddresslist ===>>> testref- 
 >public.testref->mailaddresslist.


     name    |   source   | targetdb | targetgroup | synctype  |  
stayalive | kidsalive | usecustomselect | copytype | copyextra |  
deletemethod | limitdbs | ping | do_listen | checktime | status |  
makedelta | rebuild_index | priority |   txnmode    | disable_triggers  
| disable_rules | analyze_after_copy | overdue  | expired   
|             cdate
------------+------------+----------+-------------+----------- 
+-----------+-----------+-----------------+----------+----------- 
+--------------+----------+------+-----------+-----------+-------- 
+-----------+---------------+----------+-------------- 
+------------------+---------------+--------------------+---------- 
+----------+-------------------------------
  testref_in | testref_t2 | testref  |             | pushdelta |  
t         | t         | f               | copy     |           |  
delete       |        0 | t    | f         |           | active |  
f         | f             |        0 | SERIALIZABLE | pg_class          
| pg_class      | t                  | 00:00:00 | 00:00:00 |  
2009-08-22 15:06:54.718262+10


My theory is that the "disable_rules" value may be causing it. I tried  
setting it to 'none' but i got an "all or none" error.

Any clues please ?

Need any other info ?

regards
Grant



> P please consider the environment before printing this e-mail
>
The information in this e-mail is confidential and may be legally  
privileged. It is intended solely for the addressee. Access to this e- 
mail by anyone else is unauthorised. If you have received this  
communication in error, please notify us immediately by return e-mail  
with the subject heading "Received in error" or telephone +61 2  
80050094, then delete the email and destroy any copies of it. If you  
are not the intended recipient, any disclosure, copying, distribution  
or any action taken or omitted to be taken in reliance on it, is  
prohibited and may be unlawful. Opinions, conclusions and other  
information in this e-mail and any attachments that do not relate to  
the business of the maXan are neither given nor endorsed by it.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090822/fbfdbb95/attachment.html