ID: 48764 Updated by: mbecc...@php.net Reported By: mark dot kirkwood at catalyst dot net dot nz -Status: Open +Status: Feedback Bug Type: PDO related Operating System: Linux 2.6.28 (Ubuntu) amd64 PHP Version: 5.3CVS-2009-07-01 (snap) Assigned To: mbeccati New Comment:
Please try using this snapshot: http://snaps.php.net/php5.3-latest.tar.gz For Windows: http://windows.php.net/snapshots/ Previous Comments: ------------------------------------------------------------------------ [2009-10-07 17:40:17] s...@php.net Automatic comment from SVN on behalf of mbeccati Revision: http://svn.php.net/viewvc/?view=revision&revision=289287 Log: - Fixed bug #48764 (PDO_pgsql::query always uses implicit prepared statements if v3 proto available) # original patch by Mark Kirkwood ------------------------------------------------------------------------ [2009-08-13 05:22:17] mark dot kirkwood at catalyst dot net dot nz new patch that handles PDO::ATTR_EMULATE_PREPARES as well diff -Nacr php5.2-200908130230/ext/pdo_pgsql/pdo_pgsql.c php5.2-200908130230.mod/ext/pdo_pgsql/pdo_pgsql.c *** php5.2-200908130230/ext/pdo_pgsql/pdo_pgsql.c 2009-07-18 00:19:00.000000000 +1200 --- php5.2-200908130230.mod/ext/pdo_pgsql/pdo_pgsql.c 2009-08-13 16:49:02.000000000 +1200 *************** *** 80,87 **** */ PHP_MINIT_FUNCTION(pdo_pgsql) { - php_pdo_register_driver(&pdo_pgsql_driver); REGISTER_PDO_CLASS_CONST_LONG("PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT", PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT); return SUCCESS; } /* }}} */ --- 80,87 ---- */ PHP_MINIT_FUNCTION(pdo_pgsql) { REGISTER_PDO_CLASS_CONST_LONG("PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT", PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT); + php_pdo_register_driver(&pdo_pgsql_driver); return SUCCESS; } /* }}} */ diff -Nacr php5.2-200908130230/ext/pdo_pgsql/pgsql_driver.c php5.2-200908130230.mod/ext/pdo_pgsql/pgsql_driver.c *** php5.2-200908130230/ext/pdo_pgsql/pgsql_driver.c 2009-07-18 00:19:00.000000000 +1200 --- php5.2-200908130230.mod/ext/pdo_pgsql/pgsql_driver.c 2009-08-13 16:49:02.000000000 +1200 *************** *** 248,253 **** --- 248,257 ---- } } + if (H->emulate_prepare == 1) { + emulate = 1; + } + if (!emulate && PQprotocolVersion(H->server) > 2) { stmt->supports_placeholders = PDO_PLACEHOLDER_NAMED; stmt->named_rewrite_template = "$%d"; *************** *** 646,652 **** static int pdo_pgsql_set_attr(pdo_dbh_t *dbh, long attr, zval *val TSRMLS_DC) { ! return 0; } static struct pdo_dbh_methods pgsql_methods = { --- 650,668 ---- static int pdo_pgsql_set_attr(pdo_dbh_t *dbh, long attr, zval *val TSRMLS_DC) { ! switch (attr) { ! convert_to_boolean(val); ! ! case PDO_ATTR_EMULATE_PREPARES: ! ((pdo_pgsql_db_handle *)dbh->driver_data)->emulate_prepare = Z_BVAL_P(val); ! return 1; ! ! case PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT: ! ((pdo_pgsql_db_handle *)dbh->driver_data)->emulate_prepare = Z_BVAL_P(val); ! return 1; ! default: ! return 0; ! } } static struct pdo_dbh_methods pgsql_methods = { diff -Nacr php5.2-200908130230/ext/pdo_pgsql/php_pdo_pgsql_int.h php5.2-200908130230.mod/ext/pdo_pgsql/php_pdo_pgsql_int.h *** php5.2-200908130230/ext/pdo_pgsql/php_pdo_pgsql_int.h 2009-07-18 00:19:00.000000000 +1200 --- php5.2-200908130230.mod/ext/pdo_pgsql/php_pdo_pgsql_int.h 2009-08-13 16:49:02.000000000 +1200 *************** *** 40,45 **** --- 40,46 ---- typedef struct { PGconn *server; unsigned attached:1; + unsigned emulate_prepare:1; unsigned _reserved:31; pdo_pgsql_error_info einfo; Oid pgoid; ------------------------------------------------------------------------ [2009-08-13 00:54:37] mark dot kirkwood at catalyst dot net dot nz It seems this could be viewed as part of a larger issue to do with how the various drivers implement ATTR_EMULATE_PREPARES - e.g there are differences between PDO::mysql and PDO::pgsql (as discussed in 2nd paragraph of http://bugs.php.net/bug.php?id=44202), reproduced here as 44202 has been closed: PDO_MYSQL and PDO_PGSQL use different approaches for enforcing emulation. PDO_MYSQL uses $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, ...) and PDO_PGSQL requires you to use $pdo->prepare(..., array(PDO::ATTR_EMULATE_PREPARES => ...)). So, one uses setAttribute() and the other uses prepare(). As PDO is aimed to be a database access layer abstraction, I wonder if this could be unified. ------------------------------------------------------------------------ [2009-07-02 00:17:16] mark dot kirkwood at catalyst dot net dot nz diff -Nacr php5.2-200906300830/ext/pdo_pgsql/pdo_pgsql.c php5.2-200906300830.mod/ext/pdo_pgsql/pdo_pgsql.c *** php5.2-200906300830/ext/pdo_pgsql/pdo_pgsql.c Thu Jan 1 00:46:35 2009 --- php5.2-200906300830.mod/ext/pdo_pgsql/pdo_pgsql.c Wed Jul 1 23:51:24 2009 *************** *** 80,87 **** */ PHP_MINIT_FUNCTION(pdo_pgsql) { - php_pdo_register_driver(&pdo_pgsql_driver); REGISTER_PDO_CLASS_CONST_LONG("PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT", PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT); return SUCCESS; } /* }}} */ --- 80,87 ---- */ PHP_MINIT_FUNCTION(pdo_pgsql) { REGISTER_PDO_CLASS_CONST_LONG("PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT", PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT); + php_pdo_register_driver(&pdo_pgsql_driver); return SUCCESS; } /* }}} */ diff -Nacr php5.2-200906300830/ext/pdo_pgsql/pgsql_driver.c php5.2-200906300830.mod/ext/pdo_pgsql/pgsql_driver.c *** php5.2-200906300830/ext/pdo_pgsql/pgsql_driver.c Thu Jan 1 00:46:35 2009 --- php5.2-200906300830.mod/ext/pdo_pgsql/pgsql_driver.c Thu Jul 2 00:06:20 2009 *************** *** 248,253 **** --- 248,257 ---- } } + if (H->emulate == 1) { + emulate = 1; + } + if (!emulate && PQprotocolVersion(H->server) > 2) { stmt->supports_placeholders = PDO_PLACEHOLDER_NAMED; stmt->named_rewrite_template = "$%d"; *************** *** 646,652 **** static int pdo_pgsql_set_attr(pdo_dbh_t *dbh, long attr, zval *val TSRMLS_DC) { ! return 0; } static struct pdo_dbh_methods pgsql_methods = { --- 650,663 ---- static int pdo_pgsql_set_attr(pdo_dbh_t *dbh, long attr, zval *val TSRMLS_DC) { ! switch (attr) { ! convert_to_boolean(val); ! case PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT: ! ((pdo_pgsql_db_handle *)dbh->driver_data)->emulate = Z_BVAL_P(val); ! return 1; ! default: ! return 0; ! } } static struct pdo_dbh_methods pgsql_methods = { diff -Nacr php5.2-200906300830/ext/pdo_pgsql/php_pdo_pgsql_int.h php5.2-200906300830.mod/ext/pdo_pgsql/php_pdo_pgsql_int.h *** php5.2-200906300830/ext/pdo_pgsql/php_pdo_pgsql_int.h Thu Jan 1 00:46:35 2009 --- php5.2-200906300830.mod/ext/pdo_pgsql/php_pdo_pgsql_int.h Wed Jul 1 23:55:50 2009 *************** *** 40,45 **** --- 40,46 ---- typedef struct { PGconn *server; unsigned attached:1; + unsigned emulate:1; unsigned _reserved:31; pdo_pgsql_error_info einfo; Oid pgoid; ------------------------------------------------------------------------ [2009-07-01 23:50:08] mark dot kirkwood at catalyst dot net dot nz Description: ------------ If the libpq v3 protocol is available, the PDO_pgsql::query will use implicit prepared statements. This can be a big performance hit for apps that execute a lot of small sql statements. It would be good to be able to switch this feature off as required. Example code snippet to illustrate the use case is below: Looking at the php code, it seems that either of PDO::ATTR_EMULATE_PREPARES PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT should be able to do this. However neither actually have any effect in this case. I have a patch that makes the latter work in this case. Reproduce code: --------------- //Example code snippet // (now while this cries out to be converted to use $dbh->prepare and // $stmt->execute(), more complex cases may not just reexecute // the same or similar sql) // $dbh = new PDO($url, $user); $dbh->beginTransaction(); for ($i = 0; $i < $num_execs; $i++) { $stmt = $dbh->query($sql); } //Adding these directives has no effect: // $dbh = new PDO($url, $user, "", array(PDO::ATTR_EMULATE_PREPARES => true)); // //or // $dbh->setAttribute(PDO::PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT, true); Expected result: ---------------- Setting PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT or maybe ATTR_EMULATE_PREPARES should stop server side prepare in PDO::query Actual result: -------------- Server side prepare is always used if libpq v3 protocol is detected. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=48764&edit=1