I would like to suggest a logic clarification (or modification) to the training for the archiving. It's possible there has been an oversight, or the training may lead to a dangerously wrong solution, or I'm just an idiot. (More than one option may apply.)
https://learn.outsystems.com/training/journeys/web-developer-exam-2993/data-archiving-best-practice/odc/7017
Here is the current logic in question:
Text below it it, whole section pasted for clarity:
Set Archive Threshold - Sets the number of records to archive by iteration. This threshold can be defined using a Setting, so it can be adjusted without redeploying the app.
Set StartTime - Sets a local variable with the current time when the execution starts.
CheckRecordsToArchive - Validates if there are records left to archive, according to the conditions defined using the control columns.
Has Records to Archive? - Validates the output of the previous query and executes the archive branch if there are records to archive.
MoveIntoArchiveStorage_InBulk - Executes a bulk INSERT INTO SELECT statement from the primary storage to the archive storage, combined with a clause to limit the amount of archived records (using the Archive Threshold set in 1). It implements the archive condition.
MainStorage_SetArchivedFlag_InBulk - Executes a bulk UPDATE to set the IsArchived flag of the records archived in the previous step.
Can Process More? - Validates if the current execution duration is approaching the Timer’s timeout (using an expression like DiffMinutes(StartTime, CurrTime()) < 15, for a default timeout of 20 minutes). If there’s still time, it continues checking for more records to archive. This approach helps prevent the Timer from timing out and having to repeat work.
CommitTransaction - Commits the transaction to make all changes permanent in the database.
WakeArchiveData - Re-wakes the Timer to check if there are still records to archive.
My suggestion it that something should be added to step 6 "MainStorage_SetArchivedFlag_InBulk" to explicitly clarify that they should not use the exact same logic (where clause) used in step 5 to select items in step 6, because the data may have changed, leaving the data in query different.
This will result in the wrong records being flagged as archived, and subsequently getting deleted by the clean-up process. They need to do some sort of check (inner join?) against the archive table.
I understand this is an edge case, but we all know one in a million edge cases tend to happen within a week of functionality going live.
Maybe there is an assumption that someone will know to do it this way, and I'm just being dumb, but the wording of the guidelines would tend to lead to someone reusing the same query logic, especially as an inner join query would be assumed to be much slower....?
Does anyone from OutSystems training, or in fact, their business processes team, have any thoughts on this. It this a flaw in the training content, or in the archiving logic that was envisioned?
Or am I just barking up the wrong tree here?
(I'm guessing this scenario is used in the exam as well.)
Hello.
This is a basic example. If new records show up they can be copied. No need to overcomplicate.
What sometimes is done is:
But 3 needs some limit to avoid edge cases. A max date or something like that.
Hi Nuno,
"If new records show up they can be copied" - sorry I should clarify.
If you run query CheckRecordsToArchive to get the records, and then run a bulk update MainStorage_SetArchivedFlag_InBulk to update the records, there's no way to be sure the records in the second query are the same records in the first query, unless you are restricting by say, "created date", which wouldn't make a lot of sense anyway.
It might be that the author is intending to do a join to the "archive storage" table, and finding records in the main table that are in the archive storage table... but they haven't specified this, and the performance would be terrible.
An auxiliary table might be the best solution, as you suggest, where the bulk delete links to the auxiliary table, but that's not what the training/best-practice says to do.
I know it's not my place to questions OutSystems "best practices", but it seems to me like what the "best practice" is saying to do is conceptually absolutely the wrong way to do it???
Kind Regards
Nathan
The videos are not a ""One size fits all". Each developer must think how it can work on their factory.
But when you say the records wouldn't be the same... If I'm archiving, it means records are older than x days and are in some immutable status. Otherwise they need to be in the main table.
If you do it by Date (more than x days) the filter will match every time.
If you do it by Time (more than x*24*60*60 seconds) of course a few will join the list as it runs.
The best approach for the second case would be to either save all the ids (not great for performance when we work with several thousands of records) but we can also just save the time of start and only work on records last changed previous to the instant when timer started to fetch data.
"it means records are older than x days and are in some immutable status" - what if other records reach that status between the first aggregate to copy the records and the second one to remove them from the main table?
(I'm assuming you aren't meaning truly immutable, and mean a status they can't leave, because if it was truly immutable the field would be redundant.)
I fully agree with your logic of creating a separate table of IDs to use in both the copy and delete queries. This is probably the most accurate way of ensuring data integrity.
However, my point still stands... the training article is "data-archiving-best-practice", and it's clear that if people follow the instructions in the "best practice", they may end up with potential duplicates in the archive or missing data... that's not a very good "best practice".
The best practice needs revising based on feedback everyone here has given.
Hello Nathan :)"My suggestion it that something should be added to step 6 "MainStorage_SetArchivedFlag_InBulk" to explicitly clarify that they should not use the exact same logic (where clause) used in step 5 to select items in step 6, because the data may have changed, leaving the data in query different."First, I agree with Nuno. If you are archiving data, that "should" mean that the data is not being used actively in a process. So it shouldn't be at risk of being changed anymore.But assuming that even archived data can be reopened, and you could get a situation where someone reopens a process just in the middle of the record being archived, you could find yourself in the situation you mentioned. Yes, edge case, but it's a possibility.In this situation, I would implement the system in a different way. First, I would mark the required records to be archived and then I would move the records set to be archived to the archive.Depending on the context and usual amounts of data to be archived, I could even move the MainStorage_SetArchivedFlag_InBulk outside the loop and mark all records that need to be archived at once. I would than make a loop where I just move the records marked to the archive, limiting the number of records, check if there is still records to move and restart the loop.Most likely I would do one timer to mark the records to be archived and a different one to move them, called in a different time/day.I would also add logic to the applications to prevent records marked to be archived from being changed. Or to removed the flag in case they are changed. The second has other problems, requiring you to guarantee the records can't be changed (if marked to be archived) during the time window in which the second timer executes, to prevent locks and race conditions (I think this is the easiest way to solve this problem, but it's tricky). Same thing with Purging.As you can see, in the case of the documentation/training, it's important to understand that those are basic examples and they do not intend to exhaust all the possibilities and edge cases. The best practice is to archive, the examples are just that, examples. :)Cheers!
Hi Eduardo,
Yours is probably the simplest solution, however as you say, you need to change every other process to make sure they don't change data marked as archived, and add appropriate manual error resolution if it happens.
However, my point is still... the training is "data-archiving-best-practice", and it's clear that if people follow the instructions in the "best practice", they may end up with potential duplicates in the archive or missing data... that's not a very good "best practice".
Hi Nathan.I think I understand your point. And I think you are right. The document really needs rework. But I think the problem is deeper than what we have been discussing.The problem is that a Best Practice and the different ways of Implementing that same Best Practice are not the same thing.And the document generates a lot of confusion here.You see, "Archiving" is the best practice. All the code shown in there is just one example of how to implement an archiving system in OutSystems. But we are missing the "assumptions". Because whenever we decide on how to implement something, we base the algorithm on the "requirements and characteristics" (the assumptions). So, the implementation there HAS assumptions, but they are not specified.And that's one of a big problem, because together with the title and introduction, it can lead someone to think that is the way it should be done "always". And that is never true related to any implementation, because different assumptions will require from us to work with a different implementation. So, I think those documents deserve a lot more work than we initially thought, so they talk more about the Best Practice, and clarify the Implementation as one possibility based on certain assumptions.Cheers!
Yes, that's very much it, however I have to disagree on one point... "the code shown in there is just one example of how to implement an archiving system in OutSystems"... I'd say it's an example of how NOT to implement archiving in OutSystems!
It would be better if they just said "copy the data to a new table and delete it from the old table in batches using timed jobs" and let people think through the problem themselves. If you give 9 detailed steps and an image of the action, people will use it as a guide and reproduce that... remember these nine steps and the image of the action are OutSystems Best Practices! It may even be in the exams!
I've picked up on this because of decades fighting with databases, having learnt by mistake... even people who understand the risks of data changes during batch jobs say "well, it probably won't happen"... it always does though, usually about a month after the archiving code goes in to production, and it will be a year before anyone notices there's data missing!
As you say, the document needs rework, which is what I was hoping for.
Perhaps include some of the solutions here, and explain why they are needed.
Now OutSystems just need to find a volunteer!
I agree with you. I just think when talking about best practices, any code should be shown and seen as an example on how to implement the best practice, with the assumptions and remarks on potential pitfalls.Hopefully they will improve those documents :)
Any idea how to "nudge" OutSystems on this?
The documentation has the ability for you to leave feedback.I'll prepare something but anyone can give feedback directly from the document page.
There appear to be a couple of replies I can see, so I apologise for not reading them:
those were ai generated spam replies, they are deleted
Dorine... Are you calling me AI? :P lolOne of the comments was a comment I made as an answer to the other comment... If it was deleted, mine probably went away together. :)