0

I have multiple PHP scripts running 24/7, each on different machines, that fetch information about users, from the Internet.

These users are in a table in a (postgresql) database. Every time a new user registers in the app, a new record is inserted in the table, which assigns it to one of the currently running PHP scripts (distributing the load evenly).

Is there a way so that the PHP script it has been assigned to can react in real time, in order to begin fetching information about the user immediately?

(The current setup I have is that each PHP script queries the table every 1 min, and checks if new users have been assigned to it... but more frequent queries increase database load, and in that 1 min window I am not fetching information from the newly added user.)

Improve this question
5
  • The short answer, "No." The long answer, start looking at Node.js. Commented Mar 14, 2016 at 16:36
  • Check postgres manual for LISTEN and NOTIFY. Commented Mar 14, 2016 at 16:36
  • BTW Why didi you tag the question with mysql? Commented Mar 14, 2016 at 16:41
  • I'm also interested in a possible solution for MySQL, in case there is a super-easy way to accomplish this. Commented Mar 14, 2016 at 16:43
  • LISTEN / NOTIFY in five minutes pomm-project.org/news/using-postgresql-notify-in-5-minutes.html Commented Mar 14, 2016 at 21:47

2 Answers 2

Reset to default
1

Check this example of PHP code for postgres LISTEN and NOTIFY: http://php.net/manual/en/function.pg-get-notify.php

Postgres man on LISTEN/NOTIFY: http://www.postgresql.org/docs/current/static/sql-notify.html http://www.postgresql.org/docs/current/static/sql-listen.html

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

But I'd still have to call pg_get_notify() every 1 min to see if new notifications have been issued, wouldn't I? I assume this is more lightweight than querying a table and that I can increase the poll frequency?
@hallaplay835 No, you don't need to call it every minute. It will block the script execution until it receives a notification. If you don't want to get the script blocked then there are workarounds which require polling a socket. Anyway, just google for the examples. LISTEN/NOTIFY is definitely a way to go for PostgreSQL real time events.
0

Trigger an HTTP request from a sys_exec() launch from an SQL trigger ? But it's seems overkill.

DELIMITER $$

CREATE TRIGGER `test_after_insert` AFTER INSERT ON `test`
FOR EACH ROW BEGIN

SET @exec_var = sys_exec('/usr/bin/php /myApp/after_insert.php')
END;
$$

DELIMITER ;

I know it's not a postgres script but it should work.

--- EDIT ---

And after in after_insert.php :

function httpPost($url, $data)
{
    $curl = curl_init($url);
    curl_setopt($curl, CURLOPT_POST, true);
    curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($data));
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($curl);
    curl_close($curl);
    return $response;
}

sources : How to make an HTTP Post

Improve this answer

5 Comments

This would launch after_insert.php... which would issue an HTTP request? So the HTTP request is sent to the machine where the originally running PHP script is... and how would it be alerted?
Hum, yes why not ? SQL Trigger -> sys_exec -> after_insert.php -> http post to your remote script. I will update my answer
My PHP script A.php runs on a machine where there is not a web server. In case I installed one, the request would be handled by another PHP script B.php that would have to communicate somehow with A. How? Inter-process communication? Maybe a tick handler? Are you sure this is more efficient than querying the table continuously?
I would recommend to avoid using such solutions which depend on execution of external programs. It is not safe, not efficient, not portable and not cool! Make use of LISTEN/NOTIFY feature instead.
I think it's the most efficient @pumbo, maybe not cool, but efficient, portable and safe. Safer than an open endpoint pointing on a select query. Why don't install a web server ? You just add an entry in your iptables on the A server receiving the query from the ip of the A one and an nginx exposing your B.php script. Maybe I don't really understand the problem ... There is no IPC or tick handler to use ...

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.