[Rt-devel] piping up about having proper referential integrity in the DB...

[Jesse Vincent]

Vick,


> I turned on statement logging in postgres, and tracing the queries RT
> made, discovered that his ID was still listed as a member of the group
> in the GroupMembers table.

But not in the CachedGroupMembers table? That seems surprising since all
changes to those two tables should happen in lockstep inside a
transaction that gets rolled back on failure. 


 
> Issuing the query 'DELETE FROM GroupMembers WHERE GroupId = 325782 and
> MemberId=146360' then resubmitting the group membership form instantly
> worked to add the membership back.
> 
> 
> So once again, I really really implore you to add proper foreign key
> referential integrity checks into the DB itself, to avoid the DB from
> getting munged like this.

Send a patch. I'll drop it into our testing infrastructure.  The last
time this came up, however, someone told me that to do this "properly",
I would have to either recast the entire schema to use features that only
work on a single database or create a Transactions table for each other
table in RT, create an Attributes table for each other table in RT,
create an ObjectCustomFields table for each table in RT and create an
ACL table for each table in RT. Neither of those solutions was
particularly appealing.  A good partial solution that doesn't break RT
would be great, though. 

Best,
Jesse