Stay In Same Db Connection To Receive Db Notifications

Hi all, I try to get notified from DB using pg_get_notify(). However to get it working, I need to stay in same connection session. In order to avoid to put my function in my view, I use an ajax call which run this function like :




Yii::app()->clientScript->registerScript('script',"

var token='".Yii::app()->request->csrfToken."';

setInterval(function() {do_I();}, 5000);


function do_I() {

  $.ajax({

    type: 'POST',

    url: 'getNotify',

    data: {YII_CSRF_TOKEN:token},

    dataType: 'JSON',

    timeout: 6000,

    success: function(response){

      if(response.success=='notify') {

        $.fn.yiiGridView.update('my-grid',{data:{pageSize:$pageSize}});

      }

    }

  });

}",CClientScript::POS_READY);






function actionGetNotify()

  {

    $conn = Yii::app()->db;

    $listen = $conn->createCommand('LISTEN chann;')->query();

    //while(1) {

      $notify = pg_get_notify($conn);

      if ($notify) {

        echo json_encode(array('success' => 'notify'));

        break;

      }

      else {

        echo json_encode(array('success' => 'nothing'));

      }

    //}

    //exit();

  }



However with that, it will open a new connection session every time which cannot work. I tried to use a while(true) until to get a notification, but my browser page stay in loading state indefinitely. So my question is how can I stay in same connection session without staying in same function ?

I found an attribute persistent that can be set to true. However, how to get back the connection in my variable $conn when I call back the function… and how to close this connection when user decides to change of url?

This is not solvable so you should probably avoid using database notifications.

PHP either opens up a new connection for each request or uses a pool of already open connections. Remember that you can have many concurrent requests so you need at least few open database connections at the same time. In conclusion, there is no guarantee that if you make another request from the same tab in a browser it is going to use the same database connection if there are more than one open.

Check out my nfy extension. It’s a simple database queue implementation for sending and receiving messages. There is also a small JS library provided to display notifications, you can use that as an example how to poll them.

Thanks for reply, your extension looks great, however I think I’m gonna use a simpler way to do my stuff. just refreshing grid every x seconds should be enough =)