Connections for PostgreSQL Listen Notify



  • I have a PHP script that listens to the NOTIFY triggered by a PostgreSQL trigger. When my script "hears" the notification it executes other commands inside this loop, like SELECT, UPDATE, DELETE, etc...

    Eg.: $conn->exec('LISTEN "notify_hello"');
    

    while (1) {
    $result = "";
    // wait for one Notify 10seconds instead of using sleep(10)
    $result = $conn->pgsqlGetNotify(PDO::FETCH_ASSOC, 10000);

    if ( $result ) {
    
        $stmt = $conn->prepare ( 'SELECT * FROM table' );
    
        ...
    }
    ...
    

    }

    My question is not related to programming or how to use LISTEN, but directly with the database.

    Currently I take advantage of the already open connection with the database to perform the other DQL/DML operations inside the loop, my question is, if I can use this already open connection, because theoretically it is busy monitoring the notifications of PostgreSQL's NOTIFY resource or if I should create a new connection whenever I run a DQL/DML command and terminate them after the operation I performed?



  • There is no problem with your current approach as long as you are satisfied with a single thread executing the requests one after the other. You won't lose a notification just because it arrives while you are running some SQL statement.

    If you want several threads to execute the requests concurrently, you could start a new connection and a new thread for that. However, it is wasteful to open a database connection for only a single request. A better solution would be to have a number of workers that each have their database connection, and you assign the task to some worker that is currently idle.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2