#48764 [Opn]: PDO_pgsql::query always uses implicit prepared statements if v3 proto available

 ID:               48764
 User updated by:  mark dot kirkwood at catalyst dot net dot nz
 Reported By:      mark dot kirkwood at catalyst dot net dot nz
 Status:           Open
 Bug Type:         PDO related
 Operating System: Linux 2.6.28 (Ubuntu) amd64
 PHP Version:      5.3CVS-2009-07-01 (snap)
 New Comment:

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;


Previous Comments:
------------------------------------------------------------------------

[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