Since I didn't get much comment/request, so I changed it the way I
like. (I'll use zend_parse_parameter() later, since existing
functions are not using it yet)
- get rid of some functions and made easier to use async query
- garbage collection at request shutdown
- pg_send_query() changes mode to nonblocking internally
- pg_is_busy() calls PQconsumeInput() internally
It's much easier to use now.
Aync Query Functions:
- pg_send_query() - send query
- pg_get_result() - get async query result
- pg_is_busy() - executing async query or not
- pg_request_cancel() - candel currently executing query
Misc Functions:
- pg_reset() - reconnect to server. Useful when backend is died.
- pg_status() - current connection status.
README, patch and test script is attached. (Make sure you use CGI
bin, set output_buffering=Off and edit database connection
parameter. There are some limitations in libpq. You may not be
able to execute query asyncronously)
Comments are welcome.
--
Yasuo Ohgaki
Index: pgsql.c
===================================================================
RCS file: /repository/php4/ext/pgsql/pgsql.c,v
retrieving revision 1.130
diff -u -r1.130 pgsql.c
--- pgsql.c 11 Oct 2001 23:33:40 -0000 1.130
+++ pgsql.c 25 Nov 2001 06:28:36 -0000
@@ -94,6 +94,12 @@
PHP_FALIAS(pg_clientencoding, pg_client_encoding,
NULL)
PHP_FALIAS(pg_setclientencoding, pg_set_client_encoding, NULL)
#endif
+ PHP_FE(pg_reset, NULL)
+ PHP_FE(pg_status, NULL)
+ PHP_FE(pg_send_query, NULL)
+ PHP_FE(pg_request_cancel, NULL)
+ PHP_FE(pg_get_result, NULL)
+ PHP_FE(pg_is_busy, NULL)
{NULL, NULL, NULL}
};
/* }}} */
@@ -147,7 +153,17 @@
static void _close_pgsql_link(zend_rsrc_list_entry *rsrc TSRMLS_DC)
{
PGconn *link = (PGconn *)rsrc->ptr;
+ PGresult *res;
+ PQsetnonblocking(link,1);
+ if (PQisBusy(link)) {
+ if (!PQrequestCancel(link)) {
+ php_error(E_WARNING,"PostgreSQL: failed to cancel qeury. %s",
+PQerrorMessage(link));
+ }
+ }
+ while ((res = PQgetResult(link))) {
+ PQclear(res);
+ }
PQfinish(link);
PGG(num_links)--;
}
@@ -158,7 +174,17 @@
static void _close_pgsql_plink(zend_rsrc_list_entry *rsrc TSRMLS_DC)
{
PGconn *link = (PGconn *)rsrc->ptr;
+ PGresult *res;
+ PQsetnonblocking(link,1);
+ if (PQisBusy(link)) {
+ if (!PQrequestCancel(link)) {
+ php_error(E_WARNING,"PostgreSQL: failed to cancel qeury. %s",
+PQerrorMessage(link));
+ }
+ }
+ while ((res = PQgetResult(link))) {
+ PQclear(res);
+ }
PQfinish(link);
PGG(num_persistent)--;
PGG(num_links)--;
@@ -187,12 +213,22 @@
static int _rollback_transactions(zend_rsrc_list_entry *rsrc TSRMLS_DC)
{
PGconn *link;
+ PGresult *res;
if (Z_TYPE_P(rsrc) != le_plink)
return 0;
link = (PGconn *) rsrc->ptr;
+ PQsetnonblocking(link,1);
+ if (PQisBusy(link)) {
+ if (!PQrequestCancel(link)) {
+ php_error(E_WARNING,"PostgreSQL: failed to cancel qeury. %s",
+PQerrorMessage(link));
+ }
+ }
+ while ((res = PQgetResult(link))) {
+ PQclear(res);
+ }
PGG(ignore_notices) = 1;
PQexec(link,"BEGIN;ROLLBACK;");
PGG(ignore_notices) = 0;
@@ -226,7 +262,7 @@
PHP_INI_BEGIN()
STD_PHP_INI_BOOLEAN("pgsql.allow_persistent", "1", PHP_INI_SYSTEM,
OnUpdateInt, allow_persistent, php_pgsql_globals,
pgsql_globals)
STD_PHP_INI_ENTRY_EX("pgsql.max_persistent", "-1", PHP_INI_SYSTEM,
OnUpdateInt, max_persistent, php_pgsql_globals,
pgsql_globals, display_link_numbers)
- STD_PHP_INI_ENTRY_EX("pgsql.max_links", "-1", PHP_INI_SYSTEM,
OnUpdateInt, max_links, php_pgsql_globals,
pgsql_globals, display_link_numbers)
+ STD_PHP_INI_ENTRY_EX("pgsql.max_links", "-1", PHP_INI_SYSTEM,
+ OnUpdateInt, max_links, php_pgsql_globals,
+ pgsql_globals, display_link_numbers)
PHP_INI_END()
/* }}} */
@@ -262,6 +298,9 @@
REGISTER_LONG_CONSTANT("PGSQL_NUM", PGSQL_NUM, CONST_CS | CONST_PERSISTENT);
REGISTER_LONG_CONSTANT("PGSQL_BOTH", PGSQL_BOTH, CONST_CS | CONST_PERSISTENT);
+ REGISTER_LONG_CONSTANT("PGSQL_CONNECTION_BAD", CONNECTION_BAD, CONST_CS |
+CONST_PERSISTENT);
+ REGISTER_LONG_CONSTANT("PGSQL_CONNECTION_OK", CONNECTION_OK, CONST_CS |
+CONST_PERSISTENT);
+
return SUCCESS;
}
/* }}} */
@@ -721,7 +760,7 @@
PGresult *pgsql_result;
ExecStatusType status;
pgsql_result_handle *pg_result;
-
+
switch(ZEND_NUM_ARGS()) {
case 1:
if (zend_get_parameters_ex(1, &query)==FAILURE) {
@@ -743,6 +782,10 @@
ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link",
le_link, le_plink);
convert_to_string_ex(query);
+ if (PQisBusy(pgsql)) {
+ php_error(E_NOTICE,"PostgreSQL: Cannot execute query while executing
+async query.");
+ RETURN_FALSE;
+ }
pgsql_result = PQexec(pgsql, Z_STRVAL_PP(query));
if (pgsql_result) {
@@ -751,7 +794,6 @@
status = (ExecStatusType) PQstatus(pgsql);
}
-
switch (status) {
case PGRES_EMPTY_QUERY:
case PGRES_BAD_RESPONSE:
@@ -979,7 +1021,7 @@
}
return ret;
}
-/* }}} */
+/* }}} */
#define PHP_PG_FIELD_NAME 1
#define PHP_PG_FIELD_SIZE 2
@@ -1962,7 +2004,232 @@
}
/* }}} */
#endif
+
+
+/* {{{ proto long pg_status(resource conn)
+ Get connection status */
+PHP_FUNCTION(pg_status)
+{
+ zval **pgsql_link = NULL;
+ int id = -1;
+ PGconn *pgsql;
+
+ switch(ZEND_NUM_ARGS()) {
+ case 0:
+ id = PGG(default_link);
+ CHECK_DEFAULT_LINK(id);
+ break;
+ case 1:
+ if (zend_get_parameters_ex(1, &pgsql_link)==FAILURE) {
+ RETURN_FALSE;
+ }
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
+
+ ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link",
+le_link, le_plink);
+ Z_LVAL_P(return_value) = PQstatus(pgsql);
+ Z_TYPE_P(return_value) = IS_LONG;
+}
+
+/* }}} */
+
+/* {{{ proto void pg_reset(resource conn)
+ Reset connection */
+PHP_FUNCTION(pg_reset)
+{
+ zval **pgsql_link = NULL;
+ int id = -1;
+ PGconn *pgsql;
+
+ switch(ZEND_NUM_ARGS()) {
+ case 0:
+ id = PGG(default_link);
+ CHECK_DEFAULT_LINK(id);
+ break;
+ case 1:
+ if (zend_get_parameters_ex(1, &pgsql_link)==FAILURE) {
+ RETURN_FALSE;
+ }
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
+ ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link",
+le_link, le_plink);
+ PQreset(pgsql); /* status may be checked with pg_status() */
+}
+
+/* }}} */
+
+/* Following functions are for asyncronous query
+ * Report bugs to [EMAIL PROTECTED]
+ */
+#define PHP_PG_ASYNC_IS_BUSY 1
+#define PHP_PG_ASYNC_REQUEST_CANCEL 2
+
+/* {{{ php_pgsql_do_async
+ */
+void php_pgsql_do_async(INTERNAL_FUNCTION_PARAMETERS, int entry_type)
+{
+ zval **pgsql_link = NULL;
+ int id = -1;
+ PGconn *pgsql;
+
+ switch(ZEND_NUM_ARGS()) {
+ case 0:
+ id = PGG(default_link);
+ CHECK_DEFAULT_LINK(id);
+ break;
+ case 1:
+ if (zend_get_parameters_ex(1, &pgsql_link)==FAILURE) {
+ RETURN_FALSE;
+ }
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
+
+ ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link",
+le_link, le_plink);
+
+ switch(entry_type) {
+ case PHP_PG_ASYNC_IS_BUSY:
+ PQconsumeInput(pgsql);
+ Z_LVAL_P(return_value) = PQisBusy(pgsql);
+ Z_TYPE_P(return_value) = IS_LONG;
+ break;
+ case PHP_PG_ASYNC_REQUEST_CANCEL:
+ Z_LVAL_P(return_value) = PQrequestCancel(pgsql);
+ Z_TYPE_P(return_value) = IS_LONG;
+ break;
+ default:
+ php_error(E_ERROR,"Pgsql module error. Report this error");
+ break;
+ }
+ convert_to_boolean_ex(&return_value);
+}
+/* }}} */
+
+/* {{{ proto bool pg_async_request_cancel([resource connection])
+ Cancel request */
+PHP_FUNCTION(pg_request_cancel)
+{
+ php_pgsql_do_async(INTERNAL_FUNCTION_PARAM_PASSTHRU,
+PHP_PG_ASYNC_REQUEST_CANCEL);
+}
+/* }}} */
+
+/* {{{ proto query bool pg_isbusy([resource connection])
+ Get connection is busy or not */
+PHP_FUNCTION(pg_is_busy)
+{
+ php_pgsql_do_async(INTERNAL_FUNCTION_PARAM_PASSTHRU, PHP_PG_ASYNC_IS_BUSY);
+}
+/* }}} */
+
+/* {{{ proto bool pg_async_exec([resource connection], string qeury)
+ Send asynchronous query */
+PHP_FUNCTION(pg_send_query)
+{
+ zval **query, **pgsql_link = NULL;
+ int id = -1;
+ PGconn *pgsql;
+ PGresult *res;
+ int leftover = 0;
+
+ switch(ZEND_NUM_ARGS()) {
+ case 1:
+ if (zend_get_parameters_ex(1, &query)==FAILURE) {
+ RETURN_FALSE;
+ }
+ id = PGG(default_link);
+ CHECK_DEFAULT_LINK(id);
+ break;
+ case 2:
+ if (zend_get_parameters_ex(2, &pgsql_link, &query)==FAILURE) {
+ RETURN_FALSE;
+ }
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
+ ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link",
+le_link, le_plink);
+
+ convert_to_string_ex(query);
+ if (PQsetnonblocking(pgsql, 1)) {
+ php_error(E_NOTICE,"PostgreSQL: Cannot set connection to nonblocking
+mode in pg_send_query()");
+ }
+ if (PQisBusy(pgsql)) {
+ php_error(E_WARNING,"PostgreSQL: Cannot send multiple query using
+pg_send_query()");
+ RETURN_FALSE;
+ }
+ while ((res = PQgetResult(pgsql))) {
+ PQclear(res);
+ leftover = 1;
+ }
+ if (leftover) {
+ php_error(E_NOTICE,"PostgreSQL: There are results on this
+connection.");
+ }
+ if (!PQsendQuery(pgsql, Z_STRVAL_PP(query))) {
+ RETURN_FALSE;
+ }
+ if (PQsetnonblocking(pgsql, 0)) {
+ php_error(E_NOTICE,"PostgreSQL: Cannot set connection to blocking mode
+in pg_send_query()");
+ }
+ RETURN_TRUE;
+}
+/* }}} */
+
+
+/* {{{ proto query resouce pg_arync_result([resource connection])
+ Get asynchronous query result */
+PHP_FUNCTION(pg_get_result)
+{
+ zval **pgsql_link = NULL;
+ int id = -1;
+ PGconn *pgsql;
+ PGresult *pgsql_result;
+ pgsql_result_handle *pg_result;
+
+ switch(ZEND_NUM_ARGS()) {
+ case 0:
+ id = PGG(default_link);
+ CHECK_DEFAULT_LINK(id);
+ break;
+ case 1:
+ if (zend_get_parameters_ex(1, &pgsql_link)==FAILURE) {
+ RETURN_FALSE;
+ }
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
+
+ ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link",
+le_link, le_plink);
+ if (PQsetnonblocking(pgsql, 1)) {
+ php_error(E_NOTICE,"PostgreSQL: Cannot set connection to nonblocking
+mode in pg_get_result()");
+ }
+ pgsql_result = PQgetResult(pgsql);
+ if (!pgsql_result) {
+ /* no result */
+ RETURN_FALSE;
+ }
+ if (PQsetnonblocking(pgsql, 0)) {
+ php_error(E_NOTICE,"PostgreSQL: Cannot set connection to blocking mode
+in pg_get_result()");
+ }
+ pg_result = (pgsql_result_handle *) emalloc(sizeof(pgsql_result_handle));
+ pg_result->conn = pgsql;
+ pg_result->result = pgsql_result;
+ pg_result->row = -1;
+ ZEND_REGISTER_RESOURCE(return_value, pg_result, le_result);
+}
+/* }}} */
+
#endif
/*
Index: php_pgsql.h
===================================================================
RCS file: /repository/php4/ext/pgsql/php_pgsql.h,v
retrieving revision 1.33
diff -u -r1.33 php_pgsql.h
--- php_pgsql.h 26 Sep 2001 21:44:48 -0000 1.33
+++ php_pgsql.h 25 Nov 2001 06:28:37 -0000
@@ -94,6 +94,12 @@
PHP_FUNCTION(pg_client_encoding);
PHP_FUNCTION(pg_set_client_encoding);
#endif
+PHP_FUNCTION(pg_reset);
+PHP_FUNCTION(pg_status);
+PHP_FUNCTION(pg_send_query);
+PHP_FUNCTION(pg_request_cancel);
+PHP_FUNCTION(pg_get_result);
+PHP_FUNCTION(pg_is_busy);
void php_pgsql_do_connect(INTERNAL_FUNCTION_PARAMETERS,int persistent);
int php_pgsql_get_default_link(INTERNAL_FUNCTION_PARAMETERS);
@@ -102,7 +108,7 @@
char *get_field_name(PGconn *pgsql, Oid oid, HashTable *list);
void php_pgsql_get_field_info(INTERNAL_FUNCTION_PARAMETERS, int entry_type);
void php_pgsql_data_info(INTERNAL_FUNCTION_PARAMETERS, int entry_type);
-
+void php_pgsql_do_async(INTERNAL_FUNCTION_PARAMETERS,int entry_type);
typedef struct pgLofp {
PGconn *conn;
<?php
/**
* PostgreSQL async query function test script.
*
* There are number of restrictions to make async query work.
* Refer to libpq manual for details.
*
* pg_send_query does not Multiple query, however user
* may retrive result separately.
*
* This script is for php binary. Disable output buffering
* to see the difference.
*
* [EMAIL PROTECTED]
*/
/***** CONFIG ******/
$table = 'pgsql_async_func_test'; // Test table name to create
$numrec = 50000; // Number of records to create
$connstr = 'host=dev dbname=yohgaki user=yohgaki'; // Database to connect
/******** test funcitons ********/
function create_test_table($db)
{
global $table;
$sql="
SELECT
a.attname,
a.attnum,
t.typname,
a.attlen,
a.atttypmod,
a.attnotNULL,
a.atthasdef
FROM
pg_class as c,
pg_attribute a,
pg_type t
WHERE
a.attnum > 0 AND
a.attrelid = c.oid AND
c.relname = '$table' AND
a.atttypid = t.oid
ORDER BY
a.attnum;
";
$res = pg_exec($db,$sql);
if (!pg_numrows($res)) {
echo "Creating test table..\n";
$sql = "CREATE TABLE $table (str text);";
if (pg_exec($db,$sql) !== FALSE)
{
$val = 'Async query can improve application performance
significantly. Please test and report any failure to [EMAIL PROTECTED] There are
cases that async query does not work as expected. Refer to libpq manual for details. I
also implemented functions that I need pg_status() - returns connection stauts and
pg_reset() - reset connection. (Useful when connection is broken for some reason -
most likely backend is died)';
for ($i=0; $i < 50000; $i++) {
pg_exec($db,"INSERT INTO $table (str) VALUES
('$val');");
if (!($i % 100)) {
echo '.';
}
}
echo "\n";
$val = ' XYZ '; // To make select actually return
a row. 'XYZ' is search pattern.
pg_exec($db,"INSERT INTO $table (str) VALUES ('$val');");
}
else {
die("ERR: Failed creating test
table.".pg_errormessage()."\n\n\n\n\n\n");
}
}
else {
echo "Test table exists\n";
}
}
function check_status($db)
{
$status = pg_status($db);
switch ($status) {
case PGSQL_CONNECTION_OK:
echo "Status: Connection is ready\n";
break;
case PGSQL_CONNECTION_BAD:
echo "Status: Connectin is BAD\n";
break;
default:
echo "Status: Unkonwn ($status)\n";
break;
}
return $status;
}
function execute_query($db,$async = TRUE)
{
global $table;
echo "Executing query..";
$ret = null;
$sql = "
SELECT * FROM $table WHERE str like '%XYZ%';
";
if ($async) {
return pg_send_query($db,$sql);
}
if (!($ret = pg_exec($db,$sql))) {
echo "Query failed..\n";
}
return $ret;
}
function check_result($result)
{
if (!$result){
echo "Check result: NG\n";
return false;
}
echo "Check result: Ok\n";
return true;
}
function dump_result($result)
{
if (check_result($result)) {
$cnt = pg_numrows($result);
for($i=0; $i < $cnt; $i++) {
$rec = pg_fetch_array($result,$i,PGSQL_ASSOC);
print_r($rec);
}
}
}
/******** MAIN **********/
$db = pg_connect($connstr);
create_test_table($db);
////////////// nonblocked query /////////////////////
echo "\n *** non-blocking qeury *** \n\n";
check_status($db);
execute_query($db);
while (pg_is_busy($db))
{
echo ".";
sleep(1);
}
echo "\n";
$result = pg_get_result($db);
dump_result($result);
/////////////// blocked query /////////////////////////
echo "\n *** blocking qeury *** \n\n";
check_status($db);
$result = execute_query($db, false);
echo "\n";
dump_result($result);
?>
--
PHP Development Mailing List <http://www.php.net/>
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
