[rt-users] clean up user table

[Gordon Messmer]

A while back I inherited an RT3 installation that used the mail gateway 
without any filtering.  For several years, the users manually "deleted" 
tickets from their queues every day.  Naturally, this let to some very 
bloated tables in the SQL database.  I was able to use RTx-Shredder to 
remove all of the "deleted" tickets, which pruned the tickets, 
attachments, and transactions tables.  However, even with the newest 
RTx-shredder and RT3, the "users" module won't help me delete users who 
aren't associated with tickets.

Under ideal conditions, RT3 performance is still acceptable, but I'm 
tired of trying to match up the magical versions.  3.6.5 works with 
MySQL 5.0, but whichever 3.6.x we used previously was awfully slow.  
With MySQL 4.0, the older RT3 was quick.  I'm currently running on 
PostgreSQL 8.1, which is not quite as quick as MySQL 5.0 was, but 
doesn't seem to need the "right" release of RT3 to work correctly.  *BLAH*

To restore sanity permanently, I need to clean out the 
CachedGroupMembers (254475 rows), GroupMembers (87456 rows), Groups 
(95138 rows), Principals (110714 rows), and Users (15574 rows) tables.  
(ACLs, too?)

I presume that people need to do this, and its been done before, but I 
can't find instructions on how to do so.  Is there something that I need 
to fix in order to use RTx-Shredder?  Is there a set of SQL commands 
that will delete users who aren't associated with tickets?  The farthest 
I've yet come is this query, which gives me the list of users who don't 
own tickets.  However, it doesn't include users who opened existing 
tickets, which it needs to, or other valid relations which should be 
preserved:

SELECT DISTINCT
  users.id,
  users.name,
  users.password,
  users.comments,
  users.emailaddress,
  users.realname
FROM users
INNER JOIN principals ON users.id = principals.id
LEFT JOIN tickets ON principals.id = tickets.Owner
WHERE Tickets.id IS NULL;

Any suggestions?