I have recently moved my rt from mysql to postgresql.
On my old server I had a cron running rt-shredder ones a week. But to let people have the opportunity to change there minds I delay deletion 1 week. So my command would look like something like this:
$SBIN/rt-shredder --plugin "Tickets=query,Status = 'deleted' AND LastUpdated < DATE_SUB(now(), INTERVAL 1 WEEK);limit,1000" --force --sqldump $($L).sql > $($L).log 2>&1
But after moving to postgresql the
LastUpdated < DATE_SUB(now(), INTERVAL 1 WEEK) section does not limit the number of tickets deleted.
DATE_SUB is a mysql specific function I think it should have thrown an exception, but it just ignores the timelimit and removes everything up to
now(). This is not critical in our system but might be super critical to some users.
I have tried rewriting the limiting statement but cant get it to work as expected. The following did not change anything and everything is still deleted:
and lastupdated < (now()-'2 weeks'::interval)
But if I just switch order it will throw an exception.
and (lastupdated+'2 weeks'::interval) < now()
This is part of the exception:
 [Wed Feb 14 10:07:18 2018] [error]: Couldn't parse query: Can't use string ("AND") as a HASH ref while "strict refs" in use at /storage/_rt/rt442/sbin/../lib/RT/Tickets.pm line 3050. (/storage/_rt/rt442/sbin/../lib/RT/Tickets.pm:3209)
So I guess there is some parsing going on that I have not been able to grok.
The easy solution for me is to rewrite call and make the 1 week calculation before calling *rt-shredder. Like this:
$SBIN/rt-shredder --plugin "Tickets=query,Status = 'deleted' AND LastUpdated < '$(date -d-1week +\%F)'; limit,1000" --force --sqldump $($L).sql > $($L).log 2>&1
But I still think this is worth noting and would probably be considered a bug.