Rt-shredder not working properly after switching from mysql to postgresql

RT Users
1

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.

Since 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:

[6719] [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.

2

I looked online for postgresql syntax and found:
example:
SELECT ‘2001-01-01’::timestamp + ‘1 year’::interval;

elsewhere I found:
Please see the FAQ at:
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

I tried the link for the FAQ, but it failed. So another search:

/jeff

3

Thanks Jeff for your reply! But note both expressions I mentioned initially:

1.
and lastupdated < (now()-'2 weeks'::interval)
2.
and (lastupdated+'2 weeks'::interval) < now()

Both of these are valid and fully working in postgresql (and variations of your example). The issue I have is the fact that:

  1. You are tricked into believing that the expression you write is handled by the database while it actually seems to be handled/parsed by the app before talking to the db.
  2. If I use the first expression it is silently ignored. The expression is obviously not considered, but no feedback whatsoever is given to me of this. Instead there is data removed from the database.