Created 08/01/2023
by KerryBoyko

This is the development community for YALA. Feel free to ask questions and post here, report bugs, say what you think is cool about the site, what you hate about it, what you want to see.

Keep in mind, YALA is not feature complete. It's not really ready to demo at this time, and there needs to be a lot of cleanup in the sourcecode before I can even use this as a "resume" piece. But I'd like to hear from you all the same.

This is a staging area, not production, so the database may get wiped if need be.

Finally, sit back, relax, make a post, kick the tires!

1

Hotness - and the difficulty of working out postgres cron jobs and raw PG SQL

Posted 10 August, 2023 - 5:54 AM to /community/YalaDev by KerryBoyko
Last updated: 16 April, 2024 - 8:56 PM

Okay, so this is embarassing. I'm mostly interacting with my PG database via prisma.io - which is great, but something has come up.

Right now, I have a "posts" table, with an "aggregatedVotes" field to my posts table which goes up and down based on how the user votes. (I also have a post_votes table so that each user can only vote once, and in a pinch can be used to tally votes individually, but that's not algorithmically sound.)

But I also want to calculate "hotness" which is a function of both the total number of votes, and the amount of time that has passed since the post was created. I've created a floating point value - "aggregatedHotness".

It makes sense to me to run a function every 3 hours or so that calculates the values for each post, and updates them accordingly.

This however, looks like a job for pg_cron. And I'm going to be honest, my SQL writing isn't that good.

Basically, here's what I want to do.


For every post in Posts,

  1. I want to calculate how many 48 hour periods (or fractions of 48 hour periods) have passed since the post was created, and store that value in a variable called "period_48" So if the post was created 12 hours ago, the answer is 0.25, if it was created 96 hours ago, the answer is 2.

period_48 = [ (the_time_right_now) - (dateCreated) ] / 48_hrs

  1. IF the posts's lastAggregated field === null OR [(the_time_right_now - lastAggregated) in hours] >= period_48

  2. I want to calculate hotness and upcomingness according to this formula:

hotness = aggregatedVotes / (2^period_48)

and store that value in aggregatedHotness along with a timestamp of when it was last aggregated in lastAggregated.


The trick is I want to run this every 3 hours. So really, it's: 1) What query is going to help me do that, and 2) How and where do I set up the pg_cron job?

I don't quite know the answer, though I'm on the supabase discord hoping to get some help. I'll keep you updated... this is a tricky one that I want to get right.

Create New Comment
Markdown is supported.