Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X0jGw-0002DE-FB for pgsql-interfaces@arkaria.postgresql.org; Sat, 28 Jun 2014 03:25:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X0jGu-0000zX-Rx for pgsql-interfaces@arkaria.postgresql.org; Sat, 28 Jun 2014 03:25:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X0jGt-0000zO-IM for pgsql-interfaces@postgresql.org; Sat, 28 Jun 2014 03:25:47 +0000 Received: from mail-bn1ln0188.outbound.protection.outlook.com ([2a01:111:f400:7c10::188] helo=na01-bn1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X0jGm-0002wS-1n for pgsql-interfaces@postgresql.org; Sat, 28 Jun 2014 03:25:45 +0000 Received: from BY2PR04MB584.namprd04.prod.outlook.com (10.141.220.150) by BY2PR04MB583.namprd04.prod.outlook.com (10.141.220.145) with Microsoft SMTP Server (TLS) id 15.0.959.24; Sat, 28 Jun 2014 03:25:31 +0000 Received: from BY2PR04MB584.namprd04.prod.outlook.com ([10.141.220.150]) by BY2PR04MB584.namprd04.prod.outlook.com ([10.141.220.150]) with mapi id 15.00.0974.002; Sat, 28 Jun 2014 03:25:31 +0000 From: James Duong To: "pgsql-interfaces@postgresql.org" Subject: libpq batched parameterized query execution Thread-Topic: libpq batched parameterized query execution Thread-Index: Ac+Sf89pqIgffAjdTEatsijm2NBlkg== Date: Sat, 28 Jun 2014 03:25:30 +0000 Message-ID: Accept-Language: en-CA, en-US Content-Language: en-US X-MS-Has-Attach: yes X-MS-TNEF-Correlator: x-originating-ip: [216.129.72.131] x-microsoft-antispam: BCL:0;PCL:0;RULEID: x-forefront-prvs: 0256C18696 x-forefront-antispam-report: SFV:NSPM; SFS:(199002)(189002)(501574003)(80022001)(31966008)(15202345003)(66066001)(17760045003)(86362001)(19580395003)(74502001)(95666004)(2656002)(99286002)(19618635001)(87936001)(64706001)(16236675004)(79102001)(106356001)(74316001)(101416001)(229853001)(76576001)(15975445006)(20776003)(81542001)(15395725005)(18206015023)(105586002)(74662001)(81342001)(50986999)(99936001)(19300405004)(46102001)(107046002)(85306003)(54356999)(76482001)(33646001)(19625215002)(21056001)(83322001)(19580405001)(92566001)(99396002)(4396001)(19617315011)(77982001)(85852003)(83072002)(2351001)(107886001)(567094001)(24736002); DIR:OUT; SFP:; SCL:1; SRVR:BY2PR04MB583; H:BY2PR04MB584.namprd04.prod.outlook.com; FPR:; MLV:sfv; PTR:InfoNoRecords; A:1; MX:1; LANG:en; Content-Type: multipart/related; boundary="_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_"; type="multipart/alternative" MIME-Version: 1.0 X-OriginatorOrg: simba.com X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-interfaces Precedence: bulk Sender: pgsql-interfaces-owner@postgresql.org --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: multipart/alternative; boundary="_000_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_" --_000_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi, I'm wondering if it's possible to get libpq to batch multiple PQsendQueryPa= rams() calls together. It seems like currently we have to issue a PQsendQueryParams() call then ca= ll PQgetResult() until it returns NULL before we can start the execution. W= hat would be good would be either: 1. I can keep sending them while the server is still processing the c= urrent one. After sending them all, I can keep calling PQgetResult() to ite= rate through them. 2. I can tell libpq "send all these PQsendQueryParams() calls togethe= r in one batch" to avoid excessive network roundtrips. Maybe there's another option I don't see. COPY isn't really a solution, as = I'm trying to take generic user input and send it to PostgreSQL as quickly = as possible without parsing it. PQexec() can have semi-colon separated quer= ies, but I need real parameters for this case. Thanks James Duong | Senior Computer Scientist | Simba Technologies Inc. Tel +1.604.633.0008 ext. 120 | Fax +1.604.633.0004 | jamesd@simba.com 938 West 8th Avenue | Vancouver, BC | Canada | V5Z 1E5 The Data Access and Analytics Experts | www.simba.com [Description: Description: Description: Description: twitter] [Description: Description: Description: Description: y= outube] [Description: Des= cription: Description: Description: linkedin] [Description: Description: Description: Description: fac= ebook] [Description: Description: Description: Description: google-plus] [Description: Descripti= on: Description: Description: typepad] This email message is for the sole use of the intended recipient(s) and may= contain confidential and privileged information. Any unauthorized review,= use, disclosure, or distribution is prohibited. If you are not the intend= ed recipient, please contact the sender by reply email and destroy all copi= es of the original message. Thank you. --_000_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I’m wondering if it’s possible to get li= bpq to batch multiple PQsendQueryParams() calls together.

 

It seems like currently we have to issue a PQsendQue= ryParams() call then call PQgetResult() until it returns NULL before we can= start the execution. What would be good would be either:

1.     &= nbsp; I can keep sending them while the server is still p= rocessing the current one. After sending them all, I can keep calling PQget= Result() to iterate through them.

2.     &= nbsp; I can tell libpq “send all these PQsendQueryP= arams() calls together in one batch” to avoid excessive network round= trips.

 

Maybe there’s another option I don’t see= . COPY isn’t really a solution, as I’m trying to take generic u= ser input and send it to PostgreSQL as quickly as possible without parsing = it. PQexec() can have semi-colon separated queries, but I need real parameters for this case.

 

Thanks

 

James Duong |<= span style=3D"font-size:8.0pt;font-family:"Tahoma","sans-ser= if";color:#262626;mso-fareast-language:EN-CA"> Sen= ior Computer Scientist |<= span style=3D"font-size:8.0pt;font-family:"Tahoma","sans-ser= if";color:#262626;mso-fareast-language:EN-CA"> Simba Technologies Inc.=

Tel +1.604.633.0008 ext. 120 |<= span style=3D"font-size:8.0pt;font-family:"Tahoma","sans-ser= if";color:#595959;mso-fareast-language:EN-CA"> Fax +1.604.6= 33.0004 |<= span style=3D"font-size:8.0pt;font-family:"Tahoma","sans-ser= if";color:#262626;mso-fareast-language:EN-CA"> jamesd@simba.com= =

=  

= 938 West 8th Avenue |<= span style=3D"font-size:8.0pt;font-family:"Tahoma","sans-ser= if";color:#262626;mso-fareast-language:EN-CA"> Vancouver, BC |<= span style=3D"font-size:8.0pt;font-family:"Tahoma","sans-ser= if";color:#262626;mso-fareast-language:EN-CA"> Canada |<= b> V5Z 1E5=

The Data Access and Analytics Experts  |<= b>  www.simba.co= m

=  

3D"Description:  3D"Description:  3D"Description:  3D"Description:  3D"Description=  3D"Des=

 

Thi= s email message is for the sole use of the intended recipient(s) and may co= ntain confidential and privileged information.  Any unauthorized review, use, disclosure, or distribution is prohibited.  If you are n= ot the intended recipient, please contact the sender by reply email and des= troy all copies of the original message.  Thank you.

 

--_000_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_-- --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: image/png; name="image001.png" Content-Description: image001.png Content-Disposition: inline; filename="image001.png"; size=499; creation-date="Sat, 28 Jun 2014 03:25:28 GMT"; modification-date="Sat, 28 Jun 2014 03:25:28 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAZVJREFUeNqUUzmuwkAMdULYChqQqCjCBRD3SEERjoE4Ai0FLWegoskt qICWivRELEHsS36ev2wN+aL4libyePye33gcK0kSmk6n60ql4sK3LIvEZJ+Nw47HY9jtdps0mUzW URQlz+eT1+Px+LPMuPjAAOuUy2UXjNvtlv5jtm0TsE4ul6PL5cIyv5nIN3MQA9a53W50v9/1APvR aMT+YDBQkNkPIUCuA/Dr9dIk089WTe//QQQsK0ibosFSqUT5fJ4Bw+FQAYVCgfr9PheQmCoAs8h6 v9+cnH1CxHCGYmLwHXxM6aiOBVCn06F6va6A8/msV0IDUdzGB0EQYYGoWq1yxdlsxiAxnCEHiqFG r2AqSCeMWq0WzedzOp1OFASBKvN9n3a7HavDHkRKIH2ANRoNjq1WK70zAGZesVj8VSAJJsF+v6da rUae530M0mazUaWCYQVxHHNTwGh2WJqW/aFQHZjr9cpzEC4WC7fdbnPHv420Oc6Hw4GWyyVeIbQQ 6PV66zTBzc68uc/+zukrhOPxuPkjwABIBoFrj9VtCQAAAABJRU5ErkJggg== --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: image/png; name="image002.png" Content-Description: image002.png Content-Disposition: inline; filename="image002.png"; size=625; creation-date="Sat, 28 Jun 2014 03:25:28 GMT"; modification-date="Sat, 28 Jun 2014 03:25:28 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAhNJREFUeNqUUztrKlEQ/nZdH4mgFmkkFhoFGxtBG4sgRAsbq3S2WqXL z7CKBlvB0l4QLGzURss0EYsV8iwMaATFV8x+c+8GL9db3A9m58zMmfdZZb/fo1Kp6G63248DKIoC 2v6F2Ww2vrm5CaBcLuuvr6/7zWbzF22326N60tvb275UKumqy+Xya5qGj48PxONxdDod4ZQnk4nw Y2SxWODxePyaw+HAYrGQcul4f3+PZDKJu7s79Pt9XF1d4f39HZlMBs1mE7e3tz8t2u12qEaZWK/X IE+lUtB1Hel0Go+Pj8jlchgMBnh6esLn56dw3jv00Yx+5MAKIpEIrFYrAoEA2Nbp6alwgnaeeZ9n ViAB+KGSCoIBqDNmg1qthmAwiOVyiXq9jpOTk59kBKuQALvdTpQkzoAGY0U/mQ9BG6GqqvhprVYL Dw8PIvwPuAVjlVAlitEbB1gsFlEoFKQdk/L5vGQ71JHo8/X1Bc3su91uy/Cq1aoEe35+hs/nE+ds NovhcIhoNAqn04lGo4H5fP6rFUZjAF40iYHOzs5wcXEh2fhwKF9eXoocCoVgs9kkgGa2wHIIyuZG jqHb7WI6nUoi+qic6mq1kmnTmefRaIRYLCYyKRwOixMf1/X1tQyQ90iK8Wz18/Nzv9frFYOZ3Xws Jg7XyaDcwMvLy1ihIZFI6MblP35n0+FYwN/nca/XC3wLMAA3PHF4PW2VPwAAAABJRU5ErkJggg== --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: image/png; name="image003.png" Content-Description: image003.png Content-Disposition: inline; filename="image003.png"; size=515; creation-date="Sat, 28 Jun 2014 03:25:29 GMT"; modification-date="Sat, 28 Jun 2014 03:25:29 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAaVJREFUeNp8U7vOAUEYPbsGhWujU6zCI+g1HoGgVKs8iEIroVUqlWqi Uolit6AiQVC47j9nktmM23+Sncs33zn7XWYs3/dRrVbdWCzmQMKyLE6gnWvO73bifD57g8EgJyqV ilur1RwJ/MI7mfA8zymXy66Ix+PO5XLBZDLBZrNBOp1WhP1+j0wm81WQQqlUCuSKSCSC9XqN5/OJ ZDKJZrOpnDqdDg6Hw8+oTqcTotEoxOPxwPV6VUaKmX+53W5B+KZd2+73OwQH7bhYLNBut9XhfD5H IpF4Idm2rVIMhUKvAoxCO7VaLXVYr9fR7/fVerlcIp/P43g8otfrYbvdKl+mbZOsP7PKJkgmGFGj 0cBut1Nk/tzmQDANipi5aoxGI3S73UCEvgT9bSrpfMyCmQUdDoeYzWYf94ICQhPfBcLh8AfB3GuO YAR6wyprsMf/CQQpqEESOf9KgeemuN6rzpVKJb9QKKhbx+IIIT66oNPT/Wd68vFhOp1CSJInr7KT zWZfwv4Wsu4O385qtaKwZ9FQLBZd6RQ852/34d0u1954PM79CTAAc8gDYQLQIjgAAAAASUVORK5C YII= --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: image/png; name="image004.png" Content-Description: image004.png Content-Disposition: inline; filename="image004.png"; size=500; creation-date="Sat, 28 Jun 2014 03:25:29 GMT"; modification-date="Sat, 28 Jun 2014 03:25:29 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAZZJREFUeNqMU7uqAjEQndUoq7i9lawgCFaWVtoKi4hgYW/tlwmCoh/g Z6iFi4KdqPjC990zkpDo3ss9sEwymTNnZpK1Xq8XVSqVeTwed+mfAOd2u/nj8TgryuXyvNFouPl8 nizLUgEA9ljrVmIymbjgigDuZrOhwWAQqqQn0tfpdJrAFZFIhKbTKT2fzy+yrviJ3W5H4IrH44F+ lIIOJCiVStRutymVSrGv1WqxHzx8AspY6Ip63yA7jqN8iJVnnEBm+g2S3Ol0aLVaGWcQ5wqQ8X6/ /9n7crk09tFo9J2AyxCCLpeLEdDv99nK6nq9Htt6vc7Wtu13AigGj4j2+70xyOv1GlqJnAE4qoJY LKZakJBK3W6XCc1m03gH4HD18grP53PoG8A51qfTyagIMahSFAoFqlartFgsQh8SEugVSX8mk+Ez cTweabvd8hAPh8NXAqgA6/VazQhXCw64GKIfqLu5XI4SicTXNSaTSbbFYlH50O5sNkOsb4Hged48 8LuffyNQq9V4PxwOjZ8qgD8ajbI/AgwArJIl5ihNjUUAAAAASUVORK5CYII= --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: image/png; name="image005.png" Content-Description: image005.png Content-Disposition: inline; filename="image005.png"; size=734; creation-date="Sat, 28 Jun 2014 03:25:29 GMT"; modification-date="Sat, 28 Jun 2014 03:25:29 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAoBJREFUeNpcU09IYnEQ/vQ98x9iSESgHdKQ2hJEOoR2kAgv4rKnDkHk JYS9L7i3XVi6GXgTDyKIsXuuk56iS1FBQR4lPAhdhDX/VabuzOB7LA18/P6838x838w8QyqVmgPw i/B5MpnY8MEMBoNgPB7LSm9gNBrb9OkP3f1U6SL78vKyp6oqGKPRSB5pjmyvr6+wWq14e3uTMzk6 TSbTNwpkULvdbmJzcxOBQACKomB9fV0C9Xo9DAYDFItF1Ot1nUEmk8HJyQkuLy9hNpv31MPDw8nC woJ8oGBYW1tDOp3G09MTjo+P8fz8LIH5G6+zs7OSoN/vM1uHGo1GJ9lsVh7a7Xbc39+jXC7j4OAA i4uLuLu7k9Xv98NisYiEjY0NkTkzMzNW+YK0CD3WT5e4vr7G/v4+lpeXZb+ysoJkMqkXNhQKCdi4 iHC73RgOh0KNz51ORzQzTaZdqVRQrVaFYT6fR6lUwunpqTAyctatrS24XC7Ryc4+nw/tdhs3NzfC joNxAuqWzoI7wlC8Xm86GAxaCLpzLBZDLpdDo9EQBsxKQ6vVQq1WkwTcYUMikfhLXXCyjJ2dHUQi ERwdHeHi4gI2m0131IaImXDQqdy2kSk2m02cn5+LE9NaWlqSYvIAMX121FZ25GDv7+9SdJUfcXE8 Hg9ouqSdxApOpxOFQkEv5Mfx1lgp38mINg2VWfRdXV2Jczgcxvz8PG5vb4U2mybnv/1QJRoKt4bH djrneHh4wO7uLuLxOLa3t3F2dqZn1t5M9x2FWheiIJ+4Fhq4XTyRPOKrq6tSH+0n+4C86nA4vpLO Fh2+EMwcljVzMR8fH2U+OCODg0+tQ/hN+PFPgAEAnfiBIspdYvkAAAAASUVORK5CYII= --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_ Content-Type: image/png; name="image006.png" Content-Description: image006.png Content-Disposition: inline; filename="image006.png"; size=489; creation-date="Sat, 28 Jun 2014 03:25:30 GMT"; modification-date="Sat, 28 Jun 2014 03:25:30 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJ bWFnZVJlYWR5ccllPAAAAYtJREFUeNqck72KwkAUhc8kUfxNY0RRbMRKbW18CAshL5DXsNkivpCF YCV2FoqlIohoIyZoCAiCoFnvXWYX3PVvDwyTmczcOfebOyIIArRarQD/ULvdFur5fA6EEFAUBdTf tnvz1Pr9/oemqirIBYkmZV8sFlEoFBCLxXhMa/b7PdbrNTabDa+jOQ4gB+FwGLVaDfV6HdFo9K71 w+GA4XCIyWQCcc2Dj08mkzBNE9ls9mUGrutCu4otNpvNtzaT0uk0lFAohEwmg3w+/+ei0+n0MAg7 uFwuv6wtFgsYhsEwHwYgiES32+3CcRzM53NUq1VOKRKJPE1DoxTIxXg85luwLAulUuklBsvl8gci 3Tn118J6utH3ffR6PUyn0y8HJKq4crnM6di2zVApaDwe/9642+2wWq2YUSqVYj7sIJfLoVKpYDQa MQP6cTweMZvN2JGsRDoskUgwXBpzSW+328DzPKprZqDr+lu1oA0GA7ZNliQPKXnK7bcUPzT60el0 XnrOMhWpRqMhPgUYAO7zoGub54IcAAAAAElFTkSuQmCC --_009_da267149dbb840f1868fab047ba3ff2dBY2PR04MB584namprd04pro_--