6
votes
Fortnightly Programming Q&A Thread
General Programming Q&A thread! Ask any questions about programming, answer the questions of other users, or post suggestions for future threads.
Don't forget to format your code using the triple backticks or tildes:
Here is my schema:
```sql
CREATE TABLE article_to_warehouse (
article_id INTEGER
, warehouse_id INTEGER
)
;
```
How do I add a `UNIQUE` constraint?
Doing my best to keep this succinct: I’ve been building a SaaS website that processes video game time-series market data. The infrastructure is fairly standard - jQuery + Bootstrap + Flask + Postgres (w/ Timescale).
I’m looking to set up an alerts system for end users, to flag when the current price of an item meets a threshold, or the avg/min/max over a certain window meets a threshold. My tentative strategy so far has been to create enums for the time window, threshold, etc. and use those fixed values to populate options on the front end and prevent injection on the back end. I’m also hoping to use the enum values directly to form dynamic SQL queries that get processed on a 1 minute interval to update the status of an alert record (e.g.
SELECT MAX(…) FROM item_prices WHERE item_id=… AND timestamp >= NOW() - interval …;). The alerts don’t need to be perfectly instantaneous, so this should work on a small scale, but with a few hundred queries at ~100 ms/query this approach will exceed the 1 minute window in run time if run synchronously.Do I just make a python worker script to run these queries async and host it on my DB server, or is there a more elegant way to run that sort of thing async within the DB itself? I’m using pg_cron to do a lot of similar periodic jobs on the DB directly (and Timescale has a similar built-in scheduler), but plpgsql doesn’t appear to support async.