Example usage of new non-blocking pgsql behavior

pgsql-async.php

<?php

// Connect asynchronously (new constant for bitwise arg 2: PGSQL_CONNECT_ASYNC)
if (!$db = pg_connect($conn_str, PGSQL_CONNECT_ASYNC)) {
    echo "pg_connect() error\n";
} elseif (pg_connection_status($db) === PGSQL_CONNECTION_BAD) {
    echo "pg_connect() error\n";
} elseif (!$stream = pg_socket($db)) {
    echo "pg_socket() error\n";
}

// New connection status constants exposed
$conn_status = [
    PGSQL_CONNECTION_BAD => "CONNECTION_BAD",
    PGSQL_CONNECTION_OK => "CONNECTION_OK",
    PGSQL_CONNECTION_STARTED => "CONNECTION_STARTED",
    PGSQL_CONNECTION_MADE => "CONNECTION_MADE",
    PGSQL_CONNECTION_AWAITING_RESPONSE => "CONNECTION_AWAITING_RESPONSE",
    PGSQL_CONNECTION_AUTH_OK => "CONNECTION_AUTH_OK",
    PGSQL_CONNECTION_SSL_STARTUP => "CONNECTION_SSL_STARTUP",
    PGSQL_CONNECTION_SETENV => "CONNECTION_SETENV"
];

$isReadable = function($stream) {
    $read = [$stream]; $write = $ex = [];
    return (bool) stream_select($read, $write, $ex, $usec = 1, 0);
};
$isWritable = function($stream) {
    $write = [$stream]; $read = $ex = [];
    return (bool) stream_select($read, $write, $ex, $usec = 1, 0);
};

while (TRUE) {
    // Demonstrate the newly exposed connection status constants
    printf("Connect status: %s\n", $conn_status[pg_connection_status($db)]);

    // New pg_connect_poll function + new constants for determining the async connection status
    switch (pg_connect_poll($db)) {
        case PGSQL_POLLING_READING:
            while (!$isReadable($stream));
            break;
        case PGSQL_POLLING_WRITING:
            while (!$isWritable($stream));
            break;
        case PGSQL_POLLING_FAILED:
            die("async connect error\n");
        case PGSQL_POLLING_OK:
            echo "Connection established!\n";
            break 2;
    }
}




// Send query, manually flush send buffer if necessary.
$sent = pg_send_query($db, "SELECT * FROM ".$table_name.";", $shouldBlock = FALSE);
if ($sent === FALSE) {
	echo "pg_send_query() error\n";
} else if ($sent === 0) {
    echo "manually flushing\n";
    while (TRUE) {
        if ($isWritable($stream)) {
            switch (pg_flush($db)) {
                case 1: break; // Data still remains to flush. Loop again.
                case 0: break 2; // All data flushed ... woohoo!
                case -1: echo "pg_flush() error\n"; break 2;
            }
        }
    }
}

while (TRUE) {
    if ($isReadable($stream)) {
        if (!pg_consume_input($db)) {
            echo "pg_consume_input() error\n";
            break;
        } elseif (!pg_connection_busy($db)) {
            break; // Woot! We've received the full result.
        }
    }
}

I presume PGRES_POLLING_READING is a typo and should be PGSQL_POLLING_READING ?

@IMSoP Yes! That is a typo. PGRES_POLLING_READING is the libpq constant name. Thanks for the heads-up. Fixing the gist now ...

Maybe you can explain my problem with async execution of the query:
https://stackoverflow.com/questions/52946285/why-php-sleep-blocks-postgresql-query

Is there a nearly asynchronous way to cancel an in-progress query right now? (e.g. if the server is under high load, it will take a long time to respond to cancellation requests (even for simple queries) with pg_cancel_query(), which calls PQgetResult() until there are no more results. Or to help prevent issues in if accidentally issuing queries with bad query plans)

I also asked in https://bugs.php.net/bug.php?id=79134

Or would anyone recommend something else instead? (set STATEMENT_TIMEOUT, etc., which I considered but thought would cause issues with pgbouncer, or if some queries ever needed longer timeouts than others)