errCode property of Swoole\Coroutine\PostgreSQL does not provide actual error code, instead always returns 0

[mohsin-devdksa]

Issue:

We are using Swoole\Coroutine\PostgreSQL with ConnectionPool. We are facing two problems:

Problem 1:
In our implementation using Swoole's ConnectionPool, PostgreSQL connections sometimes become inactive over time—either due to timeouts or other unexpected issues. When a query is executed using one of these inactive clients, the query fails.

To handle this, we currently check the query result using $postgreSQL->resultStatus and $postgreSQL->error. If the connection is detected as inactive, we remove and replace it in the pool with a new, active connection.

However, our current detection method isn't ideal:

• The resultStatus code 7 is too generic—it can indicate various errors including syntax issues, not just connection problems.
• Parsing error strings to detect disconnection is unreliable and not robust.

The core question is:
Is there a more reliable or specific method to detect whether a PostgreSQL connection in Swoole is inactive or disconnected, without relying on vague error codes or parsing error messages?

Problem 2:
We've observed that when a query fails in PostgreSQL using Swoole, only the result status is available to determine whether the execution was successful. However, the errCode and notices properties are not set. We believe that relying on errCode would be a more efficient and appropriate method for identifying issues, particularly for detecting connection-related problems, as described in Problem 1, rather than having to interpret both the result status and error message.

1. What did you do? If possible, provide a simple script for reproducing the error.

Below is the code for Problem 1, where we check whether the connection is still active. In this case, the errCode property doesn't work as expected, so the only viable option left is to rely on resultStatus in combination with the error property.

 /**
     * This function checks if the Client Connection is stable and working.
     *
     * @param  mixed $client
     * @return bool
     */
    public function isClientConnected(mixed $client): bool
    {
        // if client is null or false, return false
        if (!$client) {
            return false;
        }

        try {
            // Error Codes PostgreSQL: https://www.postgresql.org/docs/current/errcodes-appendix.html
            // Error Codes Swoole: https://wiki.swoole.com/en/#/other/errno?id=swoole-error-code-list
            // Error Codes Swoole Related to Timeout: https://wiki.swoole.com/en/#/coroutine_client/http_client?id=errcode
            $pgConnErrCodes = ['08003', '57P03', '57014', '08006', '25P03', '57P05'];
            $mysqlConnErrCodes = [2013, 2006];
            $swooleConnErrCodes = [110, 111, 112, 8503];

            // Result Status 7 and error message contains any disconnect message
            // Only consider resultStatus 7 as connection error if accompanied by connection-related error message
            // as resultStatus 7 can be returned for other errors as well

            // Connection-related error messages
            $connectionMessages = [
                'no connection',
                'terminating connection',
                'connection refused',
                'connection closed',
                'connection lost',
                'lost connection',
                'connection',
                'connection reset',
                'disconnected',
                'broken pipe'
            ];

            $errorCode = $client->errCode ?? null;
            $errorMsg = isset($client->error) ? (string) $client->error : '';

            // Check if error message contains any connection-related phrases
            $hasConnectionErrorMsg = false;
            foreach ($connectionMessages as $msg) {
                if (stripos($errorMsg, $msg) !== false) {
                    $hasConnectionErrorMsg = true;
                    break;
                }
            }

            // Check if the connection is connected or not
            if (
                in_array($errorCode, $pgConnErrCodes) ||
                in_array($errorCode, $mysqlConnErrCodes) ||
                in_array($errorCode, $swooleConnErrCodes) ||
                (isset($client->resultStatus) && ($client->resultStatus == 7) && $hasConnectionErrorMsg)
            ) {
                return false;
            }

            // Other Preventive Measure based on https://wiki.swoole.com/en/#/coroutine_client/http_client?id=extra-options
            if (isset($client->statusCode) && ($client->statusCode == -1 || $client->statusCode == -2)) {
                return false;
            }

            return true;
        } catch (\Throwable $e) {
            // Log the exception
            output($e);

            return false;
        }
    }

This is the test script we used to examine various PostgreSQL properties such as error, resultStatus, errCode, resultDiag, and notices. We found that errCode consistently returns 0, and the notices property remains empty.

use Swoole\Coroutine\PostgreSQL;

Co\run(function () {
    $timeout = -1;
    $connectionString = "host=127.0.0.1;port=5432;dbname=test_db;user=test_user;password=test_pwd";
    $pg = new PostgreSQL();

    $isConnected = $pg->connect($connectionString, $timeout);

    if (!$isConnected) {
        output($pg->error);
        output($pg->errCode);
        output($pg->resultStatus);
        output($pg->resultDiag);
        output($pg->notices);
    }

    try {
        $postgreSQLStatement = $pg->query('SELECT firstname, email FROM users LIMIT 2;');
    }
    catch(\Throwable $e) {
        output($e->getMessage());
    }

    // Query method return false on failure. In other case it returns \Swoole\Coroutine\PostgreSQLStatement
    if ($postgreSQLStatement === false) {
        output('Error Message:' . $pg->error);
        output('Error Code: ' . $pg->errCode);
        output($pg->resultStatus);
        output($pg->resultDiag);
        output($pg->notices);
    }
});

2. What did you expect to see?

We expect the errCode property to return the actual error code rather than always returning 0. Additionally, it would be helpful to have a connectionReset method on the PostgreSQL client object to allow reconnection after a disconnect. Ideally, the client should handle disconnection issues internally and automatically retry the connection when executing a query.

3. What did you see instead?

We observed that errCode always returns 0, regardless of the error. Additionally, connections or clients in the connection pool become inactive after some time, leading to errors when attempting to execute queries.

4. What version of Swoole are you using (show your php --ri swoole)?

swoole

Swoole => enabled
Author => Swoole Team <team@swoole.com>
Version => 5.1.5
Built => Nov 14 2024 13:43:57
coroutine => enabled with boost asm context
epoll => enabled
eventfd => enabled
signalfd => enabled
cpu_affinity => enabled
spinlock => enabled
rwlock => enabled
sockets => enabled
openssl => OpenSSL 3.0.13 30 Jan 2024
dtls => enabled
http2 => enabled
json => enabled
curl-native => enabled
pcre => enabled
c-ares => 1.27.0
zlib => 1.3
brotli => E16781312/D16781312
mutex_timedlock => enabled
pthread_barrier => enabled
futex => enabled
mysqlnd => enabled
async_redis => enabled
coroutine_pgsql => enabled

Directive => Local Value => Master Value
swoole.enable_coroutine => On => On
swoole.enable_library => On => On
swoole.enable_fiber_mock => Off => Off
swoole.enable_preemptive_scheduler => On => On
swoole.display_errors => On => On
swoole.use_shortname => On => On
swoole.unixsock_buffer_size => 8388608 => 8388608

5. What is your machine environment used (show your uname -a & php -v & gcc -v) ?

uname -a

Linux 6.8.0-1016-oracle #17-Ubuntu SMP Wed Nov  6 23:01:02 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux

php -v

PHP 8.3.13 (cli) (built: Oct 30 2024 11:28:41) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.3.13, Copyright (c) Zend Technologies
    with Zend OPcache v8.3.13, Copyright (c), by Zend Technologies

gcc -v

Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-linux-gnu/13/lto-wrapper
OFFLOAD_TARGET_NAMES=nvptx-none:amdgcn-amdhsa
OFFLOAD_TARGET_DEFAULT=1
Target: x86_64-linux-gnu
Configured with: ../src/configure -v --with-pkgversion='Ubuntu 13.3.0-6ubuntu2~24.04' --with-bugurl=file:///usr/share/doc/gcc-13/README.Bugs --enable-languages=c,ada,c++,go,d,fortran,objc,obj-c++,m2 --prefix=/usr --with-gcc-major-version-only --program-suffix=-13 --program-prefix=x86_64-linux-gnu- --enable-shared --enable-linker-build-id --libexecdir=/usr/libexec --without-included-gettext --enable-threads=posix --libdir=/usr/lib --enable-nls --enable-bootstrap --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes --with-default-libstdcxx-abi=new --enable-libstdcxx-backtrace --enable-gnu-unique-object --disable-vtable-verify --enable-plugin --enable-default-pie --with-system-zlib --enable-libphobos-checking=release --with-target-system-zlib=auto --enable-objc-gc=auto --enable-multiarch --disable-werror --enable-cet --with-arch-32=i686 --with-abi=m64 --with-multilib-list=m32,m64,mx32 --enable-multilib --with-tune=generic --enable-offload-targets=nvptx-none=/build/gcc-13-fG75Ri/gcc-13-13.3.0/debian/tmp-nvptx/usr,amdgcn-amdhsa=/build/gcc-13-fG75Ri/gcc-13-13.3.0/debian/tmp-gcn/usr --enable-offload-defaulted --without-cuda-driver --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu --with-build-config=bootstrap-lto-lean --enable-link-serialization=2
Thread model: posix
Supported LTO compression algorithms: zlib zstd
gcc version 13.3.0 (Ubuntu 13.3.0-6ubuntu2~24.04) 

[NathanFreeman]

ConnectionPool is a simple connection pool that does not inherently support automatic reconnection. Currently, we determine whether a reconnection is needed by checking the error message of the exception. You can take a look at the following code:
https://github.com/swoole/library/blob/master/src/core/Database/DetectsLostConnections.php

[NathanFreeman]

Since the Swoole 5.1.x branch is in maintenance mode (bug fixes only), you may consider switching to the coroutine-supported pdo_pgsql driver, which is fully compatible with Swoole 5.1

[mohsin-devdksa]

Thanks @NathanFreeman for sharing the DetectsLostConnections class. But I believe the errCode always returning 0 is a bug, and it can be fixed in the 5.1.x versions.