Last active
January 16, 2020 17:14
-
-
Save rdlowrey/8114597 to your computer and use it in GitHub Desktop.
Example usage of new non-blocking pgsql behavior
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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. | |
} | |
} | |
} |
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)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Maybe you can explain my problem with async execution of the query:
https://stackoverflow.com/questions/52946285/why-php-sleep-blocks-postgresql-query