Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B1278B5DBF0 for ; Fri, 26 Aug 2011 00:30:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 96198-03 for ; Fri, 26 Aug 2011 03:30:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com [74.125.83.46]) by mail.postgresql.org (Postfix) with ESMTP id EA4B4B5D803 for ; Fri, 26 Aug 2011 00:30:10 -0300 (ADT) Received: by gwaa12 with SMTP id a12so2393965gwa.19 for ; Thu, 25 Aug 2011 20:30:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=from:to:subject:date:message-id:mime-version:content-type :content-transfer-encoding:x-mailer:content-language:thread-index :x-cr-hashedpuzzle:x-cr-puzzleid; bh=ShNKtGeE4hV8GKuVEp69iedRplFJcWFcKF2eF8+4cxI=; b=IdvRGIx41O6OGUdMyMVoAoHO2QaHZeaJW6Zp9z0Y3BTtXrofBGSqf2dXu0s1VHAlpB MRmnH84xkcw0lHZwnrMi826+Y5P4ZPKWFF13boGM6Nqy9I49Wz1c9OgEJW8Hi1vrjSZU YSwOSLOnV8Q1xJWaM38keWf/oEuBcN6vukp84= Received: by 10.101.44.4 with SMTP id w4mr520935anj.108.1314329410496; Thu, 25 Aug 2011 20:30:10 -0700 (PDT) Received: from Duality (67-40-130-232.hlrn.qwest.net [67.40.130.232]) by mx.google.com with ESMTPS id c5sm1144425anh.1.2011.08.25.20.30.09 (version=SSLv3 cipher=OTHER); Thu, 25 Aug 2011 20:30:09 -0700 (PDT) From: "mark" To: Subject: pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log. Date: Thu, 25 Aug 2011 21:30:24 -0600 Message-ID: <000401cc63a0$7e3bd7b0$7ab38710$@com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 12.0 Content-Language: en-us Thread-Index: AcxjoHzigDzar8djR4abdJZUNyAKCg== x-cr-hashedpuzzle: Aewa A1kQ BVl/ Bvub CK2b ChvB CqxK DV5N Din1 EyT5 Fi4b Gx4C HEOl Hfb4 HwgY I5mA; 1; cABnAHMAcQBsAC0AcABoAHAAQABwAG8AcwB0AGcAcgBlAHMAcQBsAC4AbwByAGcA; Sosha1_v1; 7; {4ECBBF55-1E05-4490-A073-9A793F1F90D5}; ZAB2AGwAaABuAHQAcgBAAGcAbQBhAGkAbAAuAGMAbwBtAA==; Fri, 26 Aug 2011 03:30:22 GMT; cABkAG8AIABlAG0AdQBsAGEAdABlACAAcAByAGUAcABhAHIAZQBzACAAbgBvAHQAIAB3AG8AcgBrAGkAbgBnAC4AIABzAHQAaQBsAGwAIABnAGUAdAB0AGkAbgBnACAAcABhAHIAcwBlAC0AYgBpAG4AZAAtAGUAeABlAGMAdQB0AGUALQBkAGUAYQBsAGwAbwBjAGEAdABlACAAaQBuACAAcABnACAAbABvAGcALgA= x-cr-puzzleid: {4ECBBF55-1E05-4490-A073-9A793F1F90D5} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201108/2 X-Sequence-Number: 4214 Hi all, I am trying to convince some of our php guys to not use prepared statements. The reason for this is, first reasons is we are seeing no benefit IMO. performance wise nearly all of the statements are being prepared, executed and then deallocated in nearly all cases. The other times it's probably only run twice so it's still not a win. The second reason is I want to be able to use transaction pooling with pgbouncer. This is the primary driving force. So someone mashed up a quick bit of php to start testing a few thing. (more about checking what error cases look like than anything else) The problem we found is that even with "PDO::ATTR_EMULATE_PREPARES => true" I still see it preparing statements when I set log_min_duration to 0. I am not a php person but I googled around and I can't seem to figure out why the below isn't working as expected. Any tips on what are we doing wrong? What should this look like to disable/emulate prepared statements. I am expecting that if this were working we wouldn't see parse,bind,execute,deallocate in the Postgres log. Apologies if this is the wrong list for this sort of thing. -Mark true)); // replace this with a valid int to test the expected case. $rname = 'P1\'Partner'; // Purposely trying to bind a string to an integer column to see what happens // when using client-side prepare emulation vs not using it. $stmt = $dbh->prepare("SELECT * FROM my_reseller WHERE reseller_id=:rname"); $stmt->bindParam(':rname', $rname); $stmt->execute(); /* Print out results if you want to see them. foreach($stmt->fetch() as $row) { print_r($row); print("\n"); } */ $dbh = null; } printf("%17s: %.4f secs for %d iterations, avg: %.6f secs\n" , 'date' , microtime(true) - $tStart , $i , (microtime(true) - $tStart)/$i ); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "
"; die(); } ?>