On 9/14/21 05:44, dovecot@ptld.com wrote:
Thank you for the solution of using sql triggers. I was able to get it working that way. I hope it doesn't add too much overhead as it feels like a band-aid and duct-tape fix.
Yes, it's a workaround rather than being able to customize the SQL query. It's a bit of post-processing.
It will run on _any_ update on this table. If those are many, it may or may not be worthwhile to add a conditional, if perhaps evaluating the conditional would be cheaper than FROM_UNIXTIME().
In my case, I use a trigger to save a huge amount of overhead. Since I don't need to know that an account is "recently" used at a resolution higher than 15 minutes, and the database is replicated, and nearly all database (+replication) IO is lastlogin, I'm able to cut it down tremendously when using row-based replication, and nothing is logged when the row wasn't actually modified. Of course this too could have been done in the original query itself.
On another note, IIRC the lastlogin timestamp is a 32-bit integer. We need 64-bit timestamps, for winter is coming.