PHP :: Bug #48764 :: PDO_pgsql::query always uses implicit prepared statements if v3 proto available

Bug #48764	PDO_pgsql::query always uses implicit prepared statements if v3 proto available
Submitted:	2009-07-01 23:50 UTC	Modified:	2009-10-07 17:51 UTC
From:	mark dot kirkwood at catalyst dot net dot nz	Assigned:	mbeccati (profile)
Status:	Closed	Package:	PDO related
PHP Version:	5.3CVS-2009-07-01 (snap)	OS:	Linux 2.6.28 (Ubuntu) amd64
Private report:	No	CVE-ID:	None

View Developer Edit

[2009-07-01 23:50 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports

[2009-07-02 00:17 UTC] 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-08-13 00:54 UTC] 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-08-13 05:22 UTC] 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-10-07 17:40 UTC] svn@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-10-07 17:42 UTC] mbeccati@php.net

Please try using this snapshot:

  http://snaps.php.net/php5.3-latest.tar.gz
 
For Windows:

  http://windows.php.net/snapshots/

[2009-10-07 17:51 UTC] mbeccati@php.net

This bug has been fixed in SVN.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

	php.net \| support \| documentation \| report a bug \| advanced search \| search howto \| statistics \| random bug \| login
go to bug id or search bugs for


Copyright © 2001-2024 The PHP Group All rights reserved.	Last updated: Thu Nov 21 09:01:32 2024 UTC