m6w6/ext-pq

PostgreSQL client library (libpq) binding

ext-pq's Issues

When will release new version for PECL?

Thank

JSON decoding is broken

Given the script:

<?php
$query = <<<EOF
SELECT '0'::jsonb UNION SELECT '"text"'::jsonb;
EOF;

$conn = new \pq\Connection('...');
$conn->defaultFetchType = \pq\Result::FETCH_ASSOC;

$result = $conn->exec($query);

var_dump($result->fetchAll());

with the latest version and PHP 8.2 the result is:

array(2) {
  [0]=>
  array(1) {
    ["jsonb"]=>
    string(6) ""text""
  }
  [1]=>
  array(1) {
    ["jsonb"]=>
    string(1) "0"
  }
}

but with the previous one and PHP 7.4 it is:

array(2) {
  [0]=>
  array(1) {
    ["jsonb"]=>
    string(4) "text"
  }
  [1]=>
  array(1) {
    ["jsonb"]=>
    int(0)
  }
}

Ayn

Bad links for documentation, in README.md

Support PHP 7

Hi.
Support PHP 7, is planned?

Stub does not provide types of @var in docblock for pq\Connection::$socket and pq\LOB::$stream

The stub file provided as pq.stub.php on PECL page and as pq.php in JetBrains' stubs repo is missing type after @var tags in pq\Connection::$socket and pq\LOB::$stream

	/**
	 * The server socket resource.
	 *
	 * @public
	 * @readonly
	 * @var
	 */
	public $socket;
...
	/**
	 * The stream connected to the *large object*.
	 *
	 * @public
	 * @readonly
	 * @var
	 */
	public $stream;

I think it should rather be

	/**
	 * The server socket resource.
	 *
	 * @public
	 * @readonly
	 * @var resource
	 */
	public $socket;
...
	/**
	 * The stream connected to the *large object*.
	 *
	 * @public
	 * @readonly
	 * @var resource
	 */
	public $stream;

based on documentation of these classes.

The problem is that when I'm trying to parse phpstorm-stubs using phpdocumentor/reflection-docblock this ends up being the only stub that gives me a problem because of InvalidTag.
What I actually trying to acomplish is to resurrect php-language-server which indexes stubs from JetBrains' repo and after updating to the latest reflection-docblock this is the only problem I am facing atm. I can make a workaround by assuming mixed type in this case, but it would be great if the stubs could be updated. I don't know how the pq.stub.php file from PECL page was generated so if someone could point me in that direction I could provide a MR

Broken build with postgresql 9.2

/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c: In function 'php_pqres_object_read_diag':
/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c:531:5: error: 'PG_DIAG_SCHEMA_NAME' undeclared (first use in this function)
    {PG_DIAG_SCHEMA_NAME,  "schema_name"},
     ^
/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c:531:5: note: each undeclared identifier is reported only once for each function it appears in
/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c:532:5: error: 'PG_DIAG_TABLE_NAME' undeclared (first use in this function)
    {PG_DIAG_TABLE_NAME,  "table_name"},
     ^
/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c:533:5: error: 'PG_DIAG_COLUMN_NAME' undeclared (first use in this function)
    {PG_DIAG_COLUMN_NAME,  "column_name"},
     ^
/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c:534:5: error: 'PG_DIAG_DATATYPE_NAME' undeclared (first use in this function)
    {PG_DIAG_DATATYPE_NAME,  "datatype_name"},
     ^
/builddir/build/BUILD/php-pecl-pq-1.1.0/NTS/src/php_pqres.c:535:5: error: 'PG_DIAG_CONSTRAINT_NAME' undeclared (first use in this function)
    {PG_DIAG_CONSTRAINT_NAME, "constraint_name"},
     ^

How to get detail error code?

We need to know specifically error code, to create specific exception classes.

The documents have a table of detailed error codes:
http://www.postgresql.org/docs/current/static/errcodes-appendix.html

How to get them in pq\Result?

Thank.

Connection::PERSISTENT - not DEALLOCATE prepared statement

$db = new pq\Connection($dsn, pq\Connection::PERSISTENT);
$db->exec('PREPARE name AS SELECT 1;');

Reuse next time

Uncaught pq\Exception\DomainException: ERROR: prepared statement "name" already exists

Test failure with libpq 9.2

========DIFF========
--
         }
       }
     }
035+ 
036+ Fatal error: Uncaught pq\Exception\DomainException: ERROR:  could not determine data type of parameter $1 in /builddir/build/BUILD/php81-php-pecl-pq-2.2.0/NTS/tests/async010.php:16
037+ Stack trace:
038+ #0 /builddir/build/BUILD/php81-php-pecl-pq-2.2.0/NTS/tests/async010.php(16): pq\Connection->execParams('select $1', Array)
039+ #1 {main}
040+   thrown in /builddir/build/BUILD/php81-php-pecl-pq-2.2.0/NTS/tests/async010.php on line 16
035- array(1) {
036-   ["execParams"]=>
037-   array(1) {
038-     [0]=>
039-     string(3) "123"
040-   }
041- }
042- DONE
========DONE========
FAIL asnyc query not cleaned before sync exec [tests/async010.phpt] 

ext-pq depends on raphf, but raphf last release is 4 years ago

Is that ok?

listenAsync - don't work polling

$db = new pq\Connection($dsn);

$db->listenAsync('name', function($name, $message)
{
    echo $message;
});

$r = [$db->socket];
$w = $e = null;

while(stream_select($r, $w, $e, 10))
{
    $db->poll();
}

Next for 10 seconds - psql:

NOTIFY name, 'Hello World!';

PHP script callback notification not called, if use $db->listen(...) then everything works.

FETCH_CLASS

I did not find in the documentation option fetch FETCH_CLASS
Like PDO::FETCH_CLASS
http://php.net/manual/en/pdo.constants.php

You want to add this option, or we write the implementation in PHP userland?
Thank.

API issue: Connection#$encoding

Assigning this can fail. At the moment this can't be easily detected without installing global error handlers (it currently emits an E_NOTICE

ext-pq/src/php_pqconn.c

Line 199 in 75f3b1b

php_error(E_NOTICE, "Unrecognized encoding '%s'", zenc->val);

). Notably, it always fails if you attempt to set it before the connection has been established when connecting asynchronously.

As this is something that could contribute to a security issue if it fails, can we throw an exception instead? A failure to set an encoding anywhere should always be handled - or at least handle-able - IMHO.

Failed test with 7.1.0RC6

Detected in Fedora QA, since PHP update to 7.1.0RC6
https://apps.fedoraproject.org/koschei/package/php-pecl-pq?collection=f26

PASS cancel [tests/cancel001.phpt] 
TEST 17/53 [tests/conv001.phpt]
========DIFF========
002+ 
003+ Fatal error: Uncaught pq\Exception\DomainException: ERROR:  invalid input syntax for type json
004+ DETAIL:  The input string ended unexpectedly.
005+ CONTEXT:  JSON data, line 1: {"int":123,"obj":{"a":1,"b":2,"c":3 in /builddir/build/BUILD/php-pecl-pq-2.1.1/ZTS/tests/conv001.php:198
006+ Stack trace:
007+ #0 /builddir/build/BUILD/php-pecl-pq-2.1.1/ZTS/tests/conv001.php(198): pq\Connection->execParams('SELECT $1 as hs...', Array, Array)
008+ #1 {main}
009+   thrown in /builddir/build/BUILD/php-pecl-pq-2.1.1/ZTS/tests/conv001.php on line 198
002- array(1) {
...

pq\Types is restricted to non-composite types

See https://mdref.m6w6.name/pq/Types/:%20Overview#comment-3879318698

execAsync - gets another results

$sream = new pq\Connection;

$sream->execAsync("SELECT 'my'", function($res)
{
    print_r($res->fetchRow());
});

$sream->exec("SELECT 'another'");

Print:

Array
(
    [0] => my
)
Array
(
    [0] => another
)

This normal behavior?

Queue for async queries

libpq

PQsendQuery cannot be called again (on the same connection) until PQgetResult has returned a null pointer, indicating that the command is done.

http://www.postgresql.org/docs/current/static/libpq-async.html

We need not wait for the result to send a lot of independent inquiries.

$db->execAsync($sql_1);
$db->execAsync($sql_2);
$db->execAsync($sql_3);

Even if one of the queries return error, other two must be fulfilled regardless.
I think where to do it right, in user app or in module php-pq?
Thank.

Not to propose merge queries :)

$db->execAsync("$sql_1; $sql_2; $sql_3");

Persistent connect, query "RESET ALL" always executed at the end of the script?

Perhaps to make conditions if a new session uses the same $application_name not to RESET ALL?

New async API for PHP 7

The use of callbacks, there are disadvantages

1. You can not catch exceptions
2. It is difficult to controls (is progress, is done, cancel) the current state executing query
3. Not API to return value

Callback Hell :)
http://callbackhell.com/

In other languages, it is easy to solve, async/await - $result = await $db->exec($sql), when there will be this in PHP is not known.

Here's what I suggest, is the draft, but it works:

class PrototypeAsync extends pq\Connection
{
    public function execAsync($query, $callable = null)
    {
        if(is_string($query)) // for backward compatibility
        {
            return parent::execAsync($query);
        }

        $coroutine = $query();

        do
        {
            $sql = $coroutine->current();

            try
            {
                $result = parent::exec($sql); // Must be in reality - async work in PQ

                $coroutine->send($result);
            }
            catch(Throwable $e)
            {
                $coroutine->throw($e);
            }
        }
        while($coroutine->valid());

        return $coroutine;
    }
}

//------------ User Land ----------------

$db = new PrototypeAsync($dsn);

$coroutine = $db->execAsync(function()
{
    return (yield 'SELECT 1')->fetchRow();
});

if($coroutine->valid()) // is true - executing in progress
{
    // await in EventLoop
    //....
    // or cancel
    // $coroutine->throw(new Exception);
}
else // is executed, get result
{
    $coroutine->getReturn(); // Array([0] => 1)
}

// Full example - three queries and catching exceptions
$coroutine = $db->execAsync(function()
{
    (yield 'SELECT 1')->fetchCol($one);

    echo "Result: $one\r\n";

    try
    {
        (yield 'SELECT throw')->fetchCol($value);
    }
    catch(Throwable $e)
    {
        echo 'Catch error: '.$e->getCode()."\r\n";
    }

    (yield 'SELECT 2')->fetchCol($two);

    echo "Result: $two\r\n";

    return $one + $two;
});

$ret = $coroutine->getReturn();

echo "Return: $ret\r\n";

Print

Result: 1
Catch error: 8
Result: 2
Return: 3

What do you think about the use coroutine?

This does not solve the problem of independent queries queue, this for only related queries or single query.

Thank.

Segmentation fault after pg_terminate_backend

Hi!
This code simulates remote aborted connection, while waiting query result.

<?php

$c = new pq\Connection('user=postgres', pq\Connection::ASYNC);

$w = [$c->socket];
$r = $e = null;

if(stream_select($r, $w, $e, null))
{
	while(true)
	{
		switch($c->poll()) {
			case pq\Connection::POLLING_READING:
				$r = [$c->socket];
				stream_select($r, $w, $e, null);
				break;

			case pq\Connection::POLLING_OK:
				if($c->busy) {
					$c->getResult();
				}
				else {
					$c->execAsync('SELECT pg_terminate_backend(pg_backend_pid())');
				}

				break;
		}
	}
}

PHP 7.1.4 (cli) (built: Apr 11 2017 18:59:26) ( NTS )

Thank.

raphf - is thread not safe

Hello.

PHP 7.1.5 (cli) (built: May  9 2017 17:55:16) ( ZTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.1.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.1.5, Copyright (c) 1999-2017, by Zend Technologies

libuv Support => enabled
Version => 0.2.1
libuv Version => 1.10

<?php

$loop  = uv_default_loop();

$thread = function()
{
	echo "THREAD-CALL\n";
};

$after = function()
{
	echo "THREAD-CALL-AFTER\n";
};

uv_queue_work($loop, $thread, $after);
uv_queue_work($loop, $thread, $after);

$timer = uv_timer_init($loop);

uv_timer_start($timer, 1000, 1000, function()
{
	static $i = 0;

	echo "TIMER: $i\n";

	$i++;
});

uv_run($loop);

*** Error in `zts-php': double free or corruption (fasttop): 0x000055ae15c1b1f0 ***
======= Backtrace: =========
/lib64/libc.so.6(+0x791fb)[0x7f21dc3b81fb]
/lib64/libc.so.6(+0x8288a)[0x7f21dc3c188a]
/lib64/libc.so.6(cfree+0x4c)[0x7f21dc3c52bc]
zts-php(zend_hash_destroy+0x146)[0x55ae146ab706]
zts-php(tsrm_free_interpreter_context+0x4a)[0x55ae146218ea]
/usr/lib64/php-zts/modules/uv.so(+0x1fbb2)[0x7f21d0d23bb2]
/usr/lib64/php-zts/modules/uv.so(+0x1fc60)[0x7f21d0d23c60]
/lib64/libuv.so.1(+0x96d4)[0x7f21d0ae76d4]
/lib64/libpthread.so.0(+0x76ca)[0x7f21ddcd56ca]
/lib64/libc.so.6(clone+0x5f)[0x7f21dc446f7f]
======= Memory map: ========
....
Aborted (core dumped)

If delete raphf.so in php.ini then everything works well.

How to compile the pq without raff?
We use pq in php-cli as daemon process, persistent connection do not need it.

Thank.

PHP 7.1.11 core dump

Hello

escapeBytea is not compatible with php 7.1.11 , When I execute it php exits with Segmentation fault

$c = new pq\Connection('dbname=postgres host=127.0.0.1 port=5432 user=postgres');
var_dump($c->escapeBytea('test'));

Any chance for a new release?

The current version is incompatible with PHP 7.3. A new release with e00dd6d included would be great!

Restore listeners and statements, after Connection::reset

We plan to implement in the application layers, restore listeners and statements, after Connection::reset.

Or perhaps it is better if it is implemented in ext-pq?

Missing status

In PostgreSQL 12.3

	CONNECTION_NEEDED,			/* Internal state: connect() needed */
	CONNECTION_CHECK_WRITABLE,	/* Check if we could make a writable connection. */
	CONNECTION_CONSUME,			/* Wait for any pending message and consume them. */
	CONNECTION_GSS_STARTUP		/* Negotiating GSSAPI. */

These are missing.
Notice: as CONNECTION_GSS_STARTUP is 11 this may breaks 2 tests (async001 and async002)

- 2(,\d)*,0
+ 2(,\d+)*,0

Change in config9.m4 break build

Please revert to config9.m4 from 0.5.5

Change results to (PD_DIR=/usr/include)

PHP_ADD_LIBRARY_WITH_PATH(pq, $PQ_DIR/$PHP_LIBDIR, PQ_SHARED_LIBADD)

Which add an invalid rpath.

ERROR   0002: file '/usr/lib64/php-zts/modules/pq.so' contains an invalid rpath '/usr/include/lib' in [/usr/include/lib]
ERROR   0002: file '/usr/lib64/php/modules/pq.so' contains an invalid rpath '/usr/include/lib' in [/usr/include/lib]

Failed test with Postgresql < 9.3

Only because of missing entries in "diag"

Version 1.1.1 and 2.1.1:

async exec [tests/async003.phpt]
async exec params [tests/async004.phpt]
async prepared statement [tests/async005.phpt]
async unbuffered exec [tests/async006.phpt]
cancel [tests/cancel001.phpt]

pq\DateTime::__toString return type

Under PHP 8.2 this extension logs a notice:

NOTICE: PHP message: PHP Warning:  pq\DateTime::__toString() implemented without string return type in Unknown on line 0

pq\Connection::listen - require polling?

This does not work:

$conn1 = new pq\Connection($dsn);
$conn1->listen("queue", function($channel, $message, $backend_pid) {
    echo $message;
});

$conn2 = new pq\Connection($dsn);
$conn2->notify("queue", "Hello World!");

That's work, if send any query $conn1->exec(';') after $conn2->notify

$conn1 = new pq\Connection($dsn);
$conn1->listen("queue", function($channel, $message, $backend_pid) {
    echo $message;
});

$conn2 = new pq\Connection($dsn);
$conn2->notify("queue", "Hello World!");

$conn1->exec(';');

This is a bug or so conceived?
I need async event-loop without polling, like pub/sub in Redis :)

Windows builds

Are there any Windows builds distributed for this?

Bug POLLING_OK?

Test for php-cli

<?php

use pq\Connection;

$db   = new Connection('user=postgres application_name=Test', Connection::ASYNC);

$dbIo = new EvIo($db->socket, Ev::READ, function() use($db)
{
    try
    {
        echo "Connect PostgreSQL server...\r\n";

        if($db->poll() === Connection::POLLING_OK)
        {
            echo "Restart PostgreSQL server...\r\n";

            exec('systemctl restart postgresql-9.6');

            if($db->poll() === Connection::POLLING_OK)
            {
                echo "IS BUG - POLLING_OK\r\n";

                try
                {
                    $db->execAsync('');
                }
                catch(Throwable $e)
                {
                    exit("Error: ".$e->getMessage()."\r\n");
                }
            }

            exit("IS OK\r\n");
        }
    }
    catch(Throwable $e)
    {
        echo "Error: ".$e->getMessage()."\r\n";
    }
});

$db->poll();
Ev::run();

with-pq=NO

I run

pecl install pq

I answer NO to where to find the postgresql library/headers [yes] : and the following error occured

ERROR: `/var/tmp/pq/configure --with-php-config=/usr/bin/php-config --with-pq=b' failed

How can I install pq in a machine that has no postgres I want to connect to postgres remotely*

Batch mode and query pipelining for PostgreSQL 9.6

Hi.

I read an interesting article, all advise
http://blog.2ndquadrant.com/postgresql-latency-pipelining-batching/

This is a good solution of the problem of queuing queries for PostgreSQL 9.6

Docs
Examples

What do you think about it?
Thank.

Support non-blocking writes

http://www.postgresql.org/docs/9.5/static/libpq-async.html#LIBPQ-PQSETNONBLOCKING

The mechanics of this are relatively trivial, but exposing it to userland is less so.

It's possible that PQflush() could be folded sanely into Connection#poll() but I'm not sure (haven't fully thought it out). Either way, Various Async methods will need to suddenly have a return value where they previously didn't have one, indicating whether the consumer will need to watch the socket for writability and re-flush. This doesn't need to be a BC-break - non-blocking defaults to off so behaviour will not change and the return value can be safely ignored.

This has been supported by libpq for some time (8.4 is as far back as I bothered to look) so it won't introduce a new dependency on a higher version or a new layer of #ifdef hell.

GNUisms in Makefile.fragment cause BSD make not to install headers

m6w6/ext-raphf#4

Failed to acquire cancel (invalid socket)

What is exactly the following error?

Failed to acquire cancel (invalid socket)

Sometime I get it

<?
$cancel = new Cancel($this->_pgConnection);
$cancel->cancel();

Incompatible with PHP 8

When extension is loaded it fails with this error - Fatal error: Class pq\Result must implement interface Traversable as part of either Iterator or IteratorAggregate in Unknown on line 0

php -v

PHP 8.0.0beta3 (cli) (built: Sep 12 2020 08:32:49) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.0-dev, Copyright (c) Zend Technologies

php --ini

php --ini
Configuration File (php.ini) Path: /usr/local/lib
Loaded Configuration File:         /usr/local/lib/php.ini
Scan for additional .ini files in: /usr/local/php8/lib/conf.d
Additional .ini files parsed:      /usr/local/php8/lib/conf.d/20-raphf.ini,
/usr/local/php8/lib/conf.d/30-pq.ini

php.ini:

extension_dir=/usr/local/lib/php/extensions/no-debug-non-zts-20200804/

PHP configure options:

./configure --enable-opcache --with-zlib --with-zip --enable-sockets --with-pear --with-curl --enable-pdo --with-pdo-pgsql --with-openssl --enable-pcntl --with-pgsql --with-config-file-scan-dir=/usr/local/php8/lib/conf.d

TimeZone issue

My database and php script time zone are GMT , but auto converted string to pq\DateTime has Asia/Shanghai timezone , How can I correct it?