Comments (4)
Hi @devotis, you can use a postgresql trigger to perform actions before/after certain db operations. Inside the trigger you can emit a sql NOTIFY command and have an external bridge program listen for those events. Programs like these can put the events into a queue of your choice.
You can also listen for sql events directly from, say, a nodejs program like:
var PS = require('pg-pubsub');
if(process.argv.length !== 3) {
console.log("USAGE: DB_URL");
process.exit(2);
}
var url = process.argv[2],
ps = new PS(url);
// change console.log to your own handler
ps.addChannel('postgres_notify_channel_name', console.log);
from postgrest-docs.
Sounds good. Instead of notifying in the triggered function directly, I'm thinking about using the triggers to store row_to_json and trigger-action-name in an event table first. A insert-trigger on the event table would subsequently emit a sql NOTIFY with the original record as json along with the event id as payload. The listening server would update the event record marking it as "heard".
A dead listening server will result in event records not being marked as "heard". And I can do a fallback-poll on the event table and resend events that weren't heard the first time.
This approach overcomes the problem of a dead server that didn't listen and the notification lost. This seems like an easier solution than implementing RabbitMQ (which I've never done before btw). And RabbitMQ can be dead too right? Leaving the notification lost just as well.
Again, I welcome your thoughts :)
PS: These are some links I found while studying the topic:
- https://stackoverflow.com/questions/36602980/is-there-a-way-to-rely-on-postgres-notify-listen-mechanism
- https://stackoverflow.com/questions/30245672/how-can-a-postgres-9-4-server-know-if-a-client-is-listening-on-a-notify-channel
from postgrest-docs.
With some templating you could churn out 1 and 2 with https://github.com/PierreRochard/postgrest-boilerplate/blob/master/sql/create_triggers.sql and https://github.com/PierreRochard/postgrest-boilerplate/blob/master/sql/create_functions.sql
from postgrest-docs.
I hadn't considered that yet. Thanks @begriffs !
I'm working out the architecture for a new platform of applications. I expect a lot of them to need additional tasks upon record creation/modification/deletion. The route of 1. creating a trigger, 2. emitting the NOTIFY command (with the payload), 3. listening for that on the server, process that and 4. having my admin tool listen to the server when it's done processing the notification seems like an awful long of a route to implement for each and every minor hook. And I haven't even mentioned an optional intermediate queue for the notification yet.
And as much as I may going to like Postgres, using this route with many use cases ties me to Postgres. This may be a minor issue, but it's a matter of fact nonetheless.
I'm not saying that I'm not going to try your suggestion because I probably am. I also like the idea of having a separate (node.js) app that is responsible for just handling database events. How do you think about my reservations?
from postgrest-docs.
Related Issues (20)
- Ordering by random HOT 8
- Readthedocs will stop working on September 25 with the current config file HOT 1
- Docker crashing on M1 HOT 3
- limiting HTTP verbs in openapi response HOT 3
- Add messagepack return type HOT 6
- Link to Installation from tutorial
- Move binary installation from tut0.rst to install.rst and add install options to tabs
- Library not loaded on Mac HOT 4
- Rename admin page name to Observability
- Deprecated "External JWT Generation" section using Auth0 Rules
- Chocolatey doesn't add `postgrest` to the PATH
- Drop all plain HTTP snippets in favor of `curl` commands HOT 1
- Missing entries in Preferences section HOT 1
- Show a more prominent version number
- Having more than one internal schema on schema isolation is confusing HOT 4
- Use the term "secret" instead of "password" in Tutorial 1
- Avoid Globbing in Curl examples HOT 2
- Move from tailwind to PicoCSS in HTMX how-to
- Expand on Schema Isolation HOT 2
- Recommend using `row_security = off` for starting up with RLS HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from postgrest-docs.