Delete records older than (Transaction Groups) - How does this work?

Ignition
1

Question, when this setting is turned on, does this do a delete row as part of the same query call to SQL? or does it issue a second query immediately before or after the insert query?

Just curious.

2

It’s a separate task issuing its own queries as needed.

3

A vendor once told me this:

Es. for “Delete Records older than” X :

  • X < 1 hour, the delete task execute every minute
  • 1 hour< X <12 hours, every 15 minutes
  • X > 12 hours, every hour

Of course in addition to @PGriffith’s answer :wink:

4

How does it determine how old a record is?
I have a transaction group that only updates the t_stamp of a record when a certain device id is read and set it to delete records older then 90 days. It seems to delete records that were first created 90 days ago even if they were just updated today.

5

I can promise SQLBridge doesn’t have any knowledge of anything beyond what’s in the database. However, it’s not just a straightforward ‘prune everything with an age > x’; the actual logic looks like this:

String ndxQuery = String.format("SELECT max($%s$) FROM %s WHERE $%s$<?", indexCol, tableName, tsCol);
String deleteQuery = String.format("DELETE FROM %s WHERE $%s$<=?", tableName, indexCol);
deleteTime = new Timestamp(conn.getCurrentDatabaseTime() - deleteAge);
ndx = conn.runScalarPrepQuery(ndxQuery, deleteTime);
if (ndx != null) {
    conn.runPrepUpdate(deleteQuery, ndx);
}
6

That explains the behavior. For what I’m doing, I’ll have to use a scheduled gateway event then.

Thanks!