voryx / pgasync Goto Github PK
View Code? Open in Web Editor NEWAsynchronous Reactive Postgres Library in Pure PHP
Asynchronous Reactive Postgres Library in Pure PHP
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:
$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.
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
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.