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

[Vick Khera]

For the last few weeks, one of my support staff was not getting
watcher notifications for one of our queue groups because he was
neither in nor out of the required group.  I believe having RI in the
DB would have prevented this.

The details:

We have groups called FooWatchers for all people who should be
watchers for a given set of queues related to Foo.  One of my staff
went on vacation, so we removed him from the group.  It seemed to have
worked fine, as he was no longer getting those messages.  He was also
removed from another group at the same time.

Upon his return, one of the groups added him back just fine.  The
other, however, reported that the user was already in the group.  The
groups membership page does not list him as a member, and indeed his
name is in the "add users" list so we can add him.

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.

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.



Versions: FreeBSD 6.3, Postgres 8.3.7, RT 3.6.7, Perl 5.8.9 (man I
have a lot of upgrading to do on this box...)