voryx / pgasync Goto Github PK

If connection is refused, no error is sent to the error handler.

Hi,

I haven't been able to find the exact reason by now, however it seems that we've got a serious memory leak somewhere around.

While using this library together with ReactPHP webserver (using FrameworkX), each single query to DB takes some memory which GC can't free after all. FrameworkX is implementation detail here.

Steps to reproduce:

1. Make clean installation of FrameworkX (or whatever what uses ReactPHP webserver)
2. For each web request, resolve Client from DI and then execute such code:

$this->client
        ->query('SELECT * FROM search_history LIMIT 1;')
        ->subscribe(new CallbackObserver(
            function($row) {},
            fn($e) => throw $e,
            function() {}
        ));

There is not even need to fetch and return data.

3. Use AB or WRK or anything else to spam some requests, i.e. 10k. Actually, you can probably do the same with simple for each(range(1,10000)) and executing query inside the loop. Used memory will never be freed.

Does anyone have any idea where it comes from or how to prevent it, even temporarily?

First thank you for this lib, it's very nice to be able to use postgres with php through a non-blocking lib 👍

I encounter an issue with the Connection::start step.
I tried some of your sample (see code below) but the socket creation failed due to a dns error.

The problem is the script stops without showing the Failed message as expected.

<?php                                                                            
                                                                                 
require(__DIR__.'/../vendor/autoload.php');                                      
                                                                                 
$client = new \PgAsync\Client([                                                  
    'host' => 'database',                                                        
    'port' => 5432,                                                              
    'user' => 'loop',                                                            
    'database' => 'loop',                                                        
    'password' => 'loop'                                                         
]);                                                                              
     
echo "query\n";
                                                                            
$client->query('SELECT * FROM channel')->subscribe(new \Rx\Observer\CallbackObserver(
    function ($row) {                                                            
        var_dump($row);                                                          
    },                                                                           
    function ($e) {                                                              
        echo "Failed.\n";                                                        
    },                                                                           
    function () {                                                                
        echo "Complete.\n";                                                      
    }                                                                            
)); 

Current output:

query

output expected:

query
Failed.

Disconnect can cause endless loop because it calls processQueue. If the connection is in CONNECTION_BAD state, disconnect and processQueue will keep calling each other.

Is it possible to use PgAsync for reading the PipelineDB continuous streams?

Calling Client::getIdleConnection() assigns a connection right away - because this is used in query() would be much better to defer until the query is subscribed to and would be even better if it were an observable.

When a queued statement is cancelled, only the Sync command gets bypassed. I don't think this causes any issues, just extra traffic and time.

The cancellation should get rid of all of the commands for the statement.

This is code will produce extra commands even though the entire second statement is disposed prior to sending the commands (note this is on a single connection):

$pgAsyncConnection->executeStatement('SELECT 1')
    ->merge($pgAsync->executeStatement('SELECT 2'))
    ->take(1)
    ->subscribe(function ($x) {
        echo json_encode($x) . "\n";
    });

Now that we are using docker, we should be able to setup the tests to run on multiple versions of PostgreSQL.

When using executeStatement on a Client, if the connection fails (auth error in my case), the cryptic error below is produced:

Fatal error: Uncaught Exception: Observer not set on command.

I'm following the example given here and I get the following exception:

 Required parameter $observer follows optional parameter $description

  at vendor/voryx/pgasync/src/PgAsync/Command/Sync.php:13
      9▕     use CommandTrait;
     10▕ 
     11▕     private $description;
     12▕ 
  ➜  13▕     public function __construct(string $description = "", ObserverInterface $observer)
     14▕     {
     15▕         $this->description = $description;
     16▕         $this->observer    = $observer;
     17▕     }

I'm using php 8. If I understand correctly, this error occurs because of this deprecation. Either $description needs to be required (no default value), or $observer should have a default value.

Note, the exception does not occur with simple queries (ie, non-prepared). Any chance of getting this updated for php 8?

If there is a disconnect that happens, the backend may or may not complete the request. If the non-SELECT query is wrapped in a transaction and there is a disconnect, the transaction will be automatically rolled back.

I've got the following error when trying to query the dabase:

PHP Fatal error:  Uncaught exception 'UnderflowException' in /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Message/DataRow.php:35
Stack trace:
#0 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Message/ParserTrait.php(20): PgAsync\Message\DataRow->parseMessage('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#1 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(202): PgAsync\Message\DataRow->parseData('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#2 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#3 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(216): PgAsync\Connection->onData('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#4 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('T\x00\x00\x01\x93\x00\x0Enumero_i...')
#5 [internal function]: PgAsync\Connection->onData('T\x in /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Message/DataRow.php on line 35

Fatal error: Uncaught exception 'UnderflowException' in /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Message/DataRow.php:35
Stack trace:
#0 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Message/ParserTrait.php(20): PgAsync\Message\DataRow->parseMessage('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#1 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(202): PgAsync\Message\DataRow->parseData('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#2 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#3 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(216): PgAsync\Connection->onData('D\x00\x00\x00k\x00\x0E\x00\x00\x00\x0261\x00\x00...')
#4 /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('T\x00\x00\x01\x93\x00\x0Enumero_i...')
#5 [internal function]: PgAsync\Connection->onData('T\x in /vagrant/www/contactgram/vendor/voryx/pgasync/src/PgAsync/Message/DataRow.php on line 35

Code:

require 'vendor/autoload.php';

$client = new \PgAsync\Client([
    'host' => '127.0.0.1',
    'port' => 5432,
    'user' => 'username',
    'password' => 'password',
    'database' => 'database',
], \EventLoop\getLoop());

$client->query('SELECT * FROM numbers LIMIT 1')->subscribe(
    new \Rx\Observer\CallbackObserver(
        function ($row) {
            var_dump($row);
        },
        function ($e) {
            var_dump($e);
            echo "Failed.\n";
        },
        function () {
            echo "Complete.\n";
        }
    )
);

Is this a configuration thing? Do I have to use some specific version of Postgres or turn on/off some setting?

Hi,

Is there a good way to listen to triggers using your library?

Thanks,
Maarten

Getting the following error in certain situations:

Not sure what is going on, but I suspect it's due to a row lock

Fatal error: Uncaught Exception: Observer not set on command. in /opt/app/vendor/voryx/pgasync/src/PgAsync/Command/CommandTrait.php:27
Stack trace:
#0 /opt/app/vendor/voryx/pgasync/src/PgAsync/Connection.php(493): PgAsync\Command\Close->error(Object(Exception))
#1 /opt/app/vendor/voryx/pgasync/src/PgAsync/Connection.php(514): PgAsync\Connection->failAllCommandsWith(Object(Exception))
#2 /opt/app/vendor/voryx/pgasync/src/PgAsync/Connection.php(435): PgAsync\Connection->processQueue()
#3 /opt/app/vendor/voryx/pgasync/src/PgAsync/Connection.php(314): PgAsync\Connection->handleErrorResponse(Object(PgAsync\Message\ErrorResponse))
#4 /opt/app/vendor/voryx/pgasync/src/PgAsync/Connection.php(258): PgAsync\Connection->handleMessage(Object(PgAsync\Message\ErrorResponse))
#5 /opt/app/vendor/voryx/pgasync/src/PgAsync/Connection.php(236): PgAsync\Connection->processData('E\x00\x00\x00USFATAL\x00VFA...')
#6 /opt/app/vendor/evenement/evenement/src/Evenement/EventEmitterTrait.php(123): PgAsync\Connection->onData('E\x00\x00\x00USFATAL\x00 in /opt/app/vendor/voryx/pgasync/src/PgAsync/Command/CommandTrait.php on line 27

Hello!

Excuse me, if I do not have much experience in async programming on PHP. I just can not figure out why querying does not work in Ratchet WampServerInterface onOpen callback.

class Application implements WampServerInterface
{
    public function __construct()
    {
        $this->conn = new Client([
            "host" => "127.0.0.1",
            "port" => "5432",
            "user" => "postgres",
            "password" => "password",
            "database" => "foobar"
        ]);
    }

    public function onOpen(ConnectionInterface $conn)
    {
        echo 'Connected';

        // $conn defined in constructor
        $this->conn->query("INSERT INTO \"table\" (\"field\") VALUES ('value')")->subscribe(new \Rx\Observer\CallbackObserver(
            function ($row) {
                echo 'Query next';
                var_dump($row);
            },
            function ($e) use ($conn) {
                $conn->close();
            },
            function () {
                echo 'Query completed';
            }
        ));
    }
}

When I establish websocket connection, 'Connected' printed, but neither 'Query next/Query completed' printed, nor new row appears in database. Exact same code works fine in "plain" script.

<?php

require 'vendor/autoload.php';

class Foo
{
    function bar()
    {
        $conn = new Client([
            "host" => "127.0.0.1",
            "port" => "5432",
            "user" => "postgres",
            "password" => "password",
            "database" => "foobar"
        ]);

        $conn->query("INSERT INTO \"table\" (\"field\") VALUES ('value')")->subscribe(new \Rx\Observer\CallbackObserver(
            function ($row) {
                echo 'Next';
                var_dump($row);
            },
            function ($e) {
            },
            function () {
                echo 'Completed';
            }
        ));
    }
}

$foo = new Foo;
$foo->bar();

In this case, 'Completed' printed and new row appeared in database.

Hello,

I'm testing if this library will work in an Ubuntu machine, and it doesn't seem to be able to get data from the database. I go to vendor/voryx/pgasync/example/ and edit Prepare.php to add password and change the database and username to connect to my local PostgreSQL instance. Then I run php Prepare.php and there is not output. It just hangs. I also try other files like SequentialQueries.php and it hangs too. When I try SimpleQuery.php, it displays:

php SimpleQuery.php
Connection State: 1
Connection State: 1
There are 2 connections. (0)
Connection State: 1
Connection State: 1
There are 2 connections. (1)
^C

Hope this helps in diagnosing this problem. Thank you for your help.

The statement fails when trying to set a boolean column to php false. It does work, however, with integer value zero or the string 'false':

require 'vendor/autoload.php';

$client = new \PgAsync\Client([
    'host' => '127.0.0.1',
    'port' => 5432,
    'user' => 'username',
    'password' => 'password',
    'database' => 'database',
], \EventLoop\getLoop());

//$args = [true, 1];   // ok
//$args = ['true', 1]; // ok
//$args = ['t', 1]; // ok
//$args = [1, 1]; // ok
$args = [false, 1]; // this fails
//$args['false', 1]; // ok
//$args = ['f', 1]; // ok
//$args = [0, 1]; // ok

$upd = 'UPDATE test SET b = $1 WHERE id = $2 RETURNING *';
$client->executeStatement($upd, $args)->subscribe(
    new \Rx\Observer\CallbackObserver(
        function ($row) {
            echo "Result.\n";
            var_dump($row);
        },
        function ($e) {
            echo "Failed.\n";
            echo $e, PHP_EOL;
            \EventLoop\getLoop()->stop();
        },
        function () {
            echo "Complete.\n";
            \EventLoop\getLoop()->stop();
        }
    )
);

Dataset:

CREATE TABLE test (
    id serial not null,
    b boolean,
    primary key(id)
);
insert into test(b) values(true);

Result:

Failed.
exception 'PgAsync\ErrorException' with message 'ERROR: invalid input syntax for type boolean: "" while executing "UPDATE test SET b = $1 WHERE id = $2 RETURNING *"' in /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php:386
Stack trace:
#0 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(260): PgAsync\Connection->handleErrorResponse(Object(PgAsync\Message\ErrorResponse))
#1 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(211): PgAsync\Connection->handleMessage(Object(PgAsync\Message\ErrorResponse))
#2 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('E\x00\x00\x00SSERROR\x00C22...')
#3 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(216): PgAsync\Connection->onData('E\x00\x00\x00SSERROR\x00C22...')
#4 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('1\x00\x00\x00\x04E\x00\x00\x00SSERRO...')
#5 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(216): PgAsync\Connection->onData('1\x00\x00\x00\x04E\x00\x00\x00SSERRO...')
#6 /vagrant/project/vendor/voryx/pgasync/src/PgAsync/Connection.php(231): PgAsync\Connection->onData('3\x00\x00\x00\x041\x00\x00\x00\x04E\x00\x00\x00S...')
#7 [internal function]: PgAsync\Connection->onData('3\x00\x00\x00\x041\x00\x00\x00\x04E\x00\x00\x00S...', Object(React\Stream\Stream))
#8 /vagrant/project/vendor/evenement/evenement/src/Evenement/EventEmitterTrait.php(64): call_user_func_array(Array, Array)
#9 /vagrant/project/vendor/react/stream/src/Stream.php(133): Evenement\EventEmitter->emit('data', Array)
#10 [internal function]: React\Stream\Stream->handleData(Resource id #55, Object(React\EventLoop\StreamSelectLoop))
#11 /vagrant/project/vendor/react/event-loop/src/StreamSelectLoop.php(232): call_user_func(Array, Resource id #55, Object(React\EventLoop\StreamSelectLoop))
#12 /vagrant/project/vendor/react/event-loop/src/StreamSelectLoop.php(201): React\EventLoop\StreamSelectLoop->waitForStreamActivity(NULL)
#13 /vagrant/project/vendor/voryx/event-loop/src/EventLoop.php(43): React\EventLoop\StreamSelectLoop->run()
#14 [internal function]: EventLoop\EventLoop::EventLoop\{closure}()
#15 {main}

Auto reconnect or throw an exception if connection closed for listen() method.

To close connection

1. select * from pg_stat_activity where datname = 'db'
2. select pg_terminate_backend(pid) - get pid from 1.

I need interface to query database without prepared statement. Just simple query.

I need it because pgbouncer in transaction pooling mode doesnt support prepared statements.

Is there any way to query postgres without prepared statement using this library?

Hi guys,
I want to contribute, but I think it is too hard to run the tests, because it is necessary to configure Postgres.
I suggest using postgres in a docker container to resolve this problem and lower initial barrier for new contributors

This is a official image https://hub.docker.com/_/postgres

The basic example in README.md isn't working in my case. I got the below error message when I wanted to connect to the database:

Uncaught Exception: Bad connection: Unhandled authentication message: 10 in /home/yalda/PhpstormProjects/testing-voryxPgAsyncLib/vendor/voryx/pgasync/src/PgAsync/Connection.php:514

Also, I double-checked that I set up the user & database correctly in psql and recheck all of it with a classic example of pure PHP code which is worked very well but when I use your library it is not even connect.

OS: Ubuntu 22.04
Postgres Version: 14.7

This is the basic example which throws the exception:

require "vendor/autoload.php";

$client = new PgAsync\Client([
    "host" => "127.0.0.1",
    "port" => "5432",
    "user" => "yalda",
    "database" => "yalda",
    "password" => "****"
]);

$client->query('select * from accounts')->subscribe(
    function ($row) {
        var_dump($row);
    },
    function ($e) {
        echo "Failed.\n";
    },
    function () {
        echo "Complete.\n";
    }
);

Since yesterday I'm having "FATAL: remaining connection slots are reserved for non-replication superuser connections" errors. When I started using PgAsync a few weeks ago I raised max_connections to 999 and this number was enough until now.

So I tried enabling auto_disconnect, despite not having tested this before. Things wouldn't work at all. My expectation was that when auto_disconnect is set to true, each call to the query and executeStatement methods would result in a new connection, but htop shows a growing list of idle connections. I even tried to use PgBouncer in transaction mode and have the application connect to it instead of the database, but this didn't work too.

I took a look at the source code. New connections are stored for later use regardless of the auto_disconnect setting. I think that if we are going to discard the connection after the query completes anyway, we shouldn't even add them to the pool. So I tried this change on /src/PgAsync/Client.php, line 68:

        $connection          = new Connection($this->parameters, $this->loop);
        if (!$connection->auto_disconnect) { // I changed this property visibility to public just for testing
            $this->connections[] = $connection;
        }

And then things worked, with or without PgBouncer: no more accumulation of idle connections, and the application would work just like before the problem started. So, it appears that in some situations, the test executed at line 61 might result in true even if connection isn't open anymore, causing the application to hang.

Also, when we are not using auto_disconnect, the connections previously added to the pool don't appear to ever be reutilized: when the "remaining connection slots are reserved" errors started, there were hundreds of iddle connections. They remain in idle status until Postgres terminates them.

I'm now going to experiment auto_disconnect + PgBouncer on production and see how it performs.

Hi there,

I'm wondering how I can add ?sslmode=require to the library?

I've tried uncommenting this in Connection.php but it doesn't work unfortunately:

                // $ssl = new SSLRequest();
                // $stream->write($ssl->encodedMessage());

Thank you so much!

If database restarts, the driver should handle that. Close out connections and create new ones etc.

$ uname -a
Linux serverName 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux
$ php -v
PHP 7.0.12-1~dotdeb+8.1 (cli) ( NTS )
Copyright (c) 1997-2016 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.12-1~dotdeb+8.1, Copyright (c) 1999-2016, by Zend Technologies

Test case:

$client = new PgAsync\Client([
    'host' => 'localhost',
    'port' => '6432',
    'user'     => 'testing',
    'database' => 'testing',
    'password' => '123456',
]);

$countResults = 0;
for($i = 0; $i < 10000; $i++) {
    $client->query('SELECT 1')->subscribe(new \Rx\Observer\CallbackObserver(
        function () {
            // we dont really need rows in this testcase
        },
        function () use(&$countResults) {
            ++$countResults;
            print $countResults . PHP_EOL;
        },
        function () use(&$countResults) {
            ++$countResults;
            print $countResults . PHP_EOL;
        }
    ));
}

Expected result: output of numbers range from 1 to 10000.
Actual result: output of number range from 1 to 500 - 2000.