php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: mark dot kirkwood at catalyst dot net dot nz
New email:
PHP Version: OS:

 

 [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 Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Tue Dec 03 17:01:29 2024 UTC