Postgres trigger notifies using PHP

Question

I have a problem or misunderstanding using a Postgre trigger -> execute notification -> into a PHP stream.

My platform is PHP (5.6) on centos with Postgres.

I have to add a trigger with a notification table and whenever a new notification is added to the notification that SMS should send to this user.

So here is added such a trigger

CREATE FUNCTION xxx_sms_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE BEGIN PERFORM pg_notify('sms', NEW.id||'' ); RETURN new; END; 

and in php, inserting new notifications works fine.

Now I have a separate file in which this pg_notify capture, launched by "pg_get_notify" , is added , here I could not get this stream completely, like how Postgres can run some unknown php script without running it as a service or as can i make it work?

Answer 1

You need a php script running as a service. If it will be the language that receives the notification that you provide. As @FelipeRosa says that the script will need to connect to the database and then issue at least one command:

 listen sms; 

A good listening example on the main site ( http://www.php.net/manual/en/function.pg-get-notify.php )

I did not code in php after a few years. I recently implemented this logic in python, but it should be about the same. I did a little research, and I can find select () in php, but it seems that the postgres socket descriptor is not available in php, so you cannot use select () in php if you cannot find the postgres socket descriptor.

Anyway, this thread is here ( http://postgresql.1045698.n5.nabble.com/Is-there-any-way-to-listen-to-NOTIFY-in-php-without-polling-td5749888.html ) . Here is an example poll for your side of the PHP script down below. You can listen to the previous one (once), and then put pg_get_notify () in a loop with sleep there for the time that you are ready to notify in the queue.

Just fwiw, in python I won’t poll, I select .select (pg_conn, ...), when the data comes in a postgres connection, I check it for notifications, so there’s no “poll”. It would be nice if you could find a way to use select () in php instead of a loop.

-g

Answer 2

This is a close-knit example that registers interest in inserting a table, waits for a notification (or timeout), and answers the caller. We use a timestamp preceded by the letter “C” to identify the notification channel, since Postgres requires the channel name to be the correct identifier.

Postgres sql

 /* We want to know when items of interest get added to this table. Asynchronous insertions possible from different process or server */ DROP TABLE IF EXISTS History; CREATE TABLE History ( HistoryId INT PRIMARY KEY, MYKEY CHAR(17), Description TEXT, TimeStamp BIGINT ); /* Table of registered interest in a notification */ DROP TABLE IF EXISTS Notifications; CREATE TABLE Notifications ( NotificationId INT PRIMARY KEY, Channel VARCHAR(20), MYKEY CHAR(17) ); /* Function to process a single insertion to History table */ CREATE OR REPLACE FUNCTION notify_me() RETURNS trigger AS $BODY$ DECLARE ch varchar(20); BEGIN FOR ch IN SELECT DISTINCT Channel FROM Notifications WHERE MYKEY=NEW.MYKEY LOOP /* NOTIFY ch, 'from notify_me trigger'; */ EXECUTE 'NOTIFY C' || ch || ', ' || quote_literal('from notify_me') || ';'; DELETE FROM Notifications WHERE Channel=ch; END LOOP; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; /* Trigger to process all insertions to History table */ DROP TRIGGER IF EXISTS HistNotify ON History CASCADE; CREATE TRIGGER HistNotify AFTER INSERT ON History FOR EACH ROW EXECUTE PROCEDURE notify_me(); 

Php code

 // $conn is a PDO connection handle to the Postgres DB // $MYKEY is a key field of interest $TimeStamp = time(); // UNIX time (seconds since 1970) of the request $timeout = 120; // Maximum seconds before responding // Register our interest in new history log activity $rg = $conn->prepare("INSERT INTO Notifications (MYKEY, Channel) VALUES (?,?)"); $rg->execute(array($MYKEY, $TimeStamp)); // Wait until something to report $conn->exec('LISTEN C'.$TimeStamp.';'); // Prepend 'C' to get notification channel $conn->exec('COMMIT;'); // Postgres may need this to start listening $conn->pgsqlGetNotify (PDO::FETCH_ASSOC, $timeout*1000); // Convert from sec to ms // Unregister our interest $st = $conn->prepare("DELETE FROM Notifications WHERE Channel=?"); $st->execute(array($TimeStamp)); 

Answer 3

According to this, you must first force the application to listen to the required channel by issuing the "LISTEN" command, for example, via pg_query, it can notify messages in the application in front of you.

Answer 4

His example:

PHP script (I called it teste.php - Same thing at http://php.net/manual/pt_BR/function.pg-get-notify.php ):

 $conn = pg_pconnect("dbname=mydb"); if (!$conn) { echo "An error occurred.\n"; exit; } while(true){ pg_query($conn, 'LISTEN SMS;'); $notify = pg_get_notify($conn); if (!$notify) { echo "No messages\n"; // change it as u want } else { print_r($notify); //your code here } sleep(2); } 

Keep a runnig script (I assumed u use linux):

 php teste.php > log.txt 2>&1 & 

Note that:

2> & 1 redirects both standard output and standard error to the log.txt file.

& runs everything in the background

You can follow log.txt with this command:

 tail -f log.txt 

Answer 5

Here is an example of how to port the "Python path" mentioned by @Greg to PHP. After running the script below - open a new connection with postgres db and request NOTIFY "test", 'I am the payload'

Sources:

• http://initd.org/psycopg/docs/advanced.html#asynchronous-notifications
• https://gist.github.com/chernomyrdin/96812377f1ac5bf567b8

---

 <?php $dsn = 'user=postgres dbname=postgres password=postgres port=5432 host=localhost'; $connection = \pg_connect($dsn); if (\pg_connection_status($connection) === \PGSQL_CONNECTION_BAD) { throw new \Exception( sprintf('The database connect failed: %s', \pg_last_error($connection)) ); } \pg_query('LISTEN "test"'); while (true) { $read = [\pg_socket($connection)]; $write = null; $except = null; $num = \stream_select( $read, $write, $except, 60 ); if ($num === false) { throw new \Exception('Error in optaining the stream resource'); } if (\pg_connection_status($connection) !== \PGSQL_CONNECTION_OK) { throw new \Exception('pg_connection_status() is not PGSQL_CONNECTION_OK'); } elseif ($num) { $notify = \pg_get_notify($connection); if ($notify !== false) { var_dump($notify); } } }