1

I am using PostgreSQL in my Web API project. I have created Web APIs using .net core. Now I have a situation, where I want to process the inserted or updated data and store the processed data in some other table.

I was thinking to create a trigger for the table that will invoke the REST API created in .net core along with the data and in .net core, I can easily process the data and will insert it into the table I want. The API will be OneWay web service - i.e. fire and forget.

But I was wondering, whether it is a good idea to invoke web service from the database? If it is, then how could I invoke a OneWay Web service from Postgres trigger?

Another approach I found was to use LISTEN/NOTIFY. For this approach, https://shashangka.com/2020/05/17/listen-postgresql-in-asp-net-core/ article seems fine.

I was wondering, which of the approach will be better for my use case and why?

Improve this question

1 Answer 1

Reset to default
2

You could write a trigger that calls a web service in PL/Python, but I don't think it is a good idea to do something like that from a trigger:

  • Even though you think that your HTTP communication is one-way, it isn't, since HTTP is implemented on top of the connection-oriented TCP protocol. There is some back-and-forth involved in establishing a TCP connection and sending a request. Now if for example the destination host were unreachable, you would have to wait for a surprisingly long timeout.

  • Database transactions should be as short as possible, so that locks are not held longer than necessary and connection pooling works well (not to mention VACUUM). So any database-external activity of uncertain duration should be avoided during a database transaction. But a trigger always runs in the same transaction as the statement that triggers its execution.

I think the idea with LISTEN/NOTIFY or an approach with a queuing table would be better: the trigger just creates a task, and a worker process picks up the tasks and client-side code sends the HTTP request.

Improve this answer
3
  • So for the queuing table, you are saying that I should then insert the data to be processed in some temp table from the trigger and create a worker service that will continuously check for the data in that temp table. Once I have finished processing the record, I should delete that record from the temp table. Is this what you meant?
    – Sunny
    Commented Oct 29, 2020 at 7:36
  • Yes, exactly, except it has to be a permanent table.
    – Laurenz Albe
    Commented Oct 29, 2020 at 8:14
  • ah yes!! my bad permanent table but temp record.
    – Sunny
    Commented Oct 29, 2020 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.