2

Is there any way to execute more sql prepared statements at once? Or at least use something to achieve this result, can it be emulated with transactions?

pg_send_query can execute more statements (from php docs "The SQL statement or statements to be executed.") but pg_send_execute and pg_send_prepare can work only with one statement.

The query parameter has the following description

"The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.) If any parameters are used, they are referred to as $1, $2, etc."

from http://www.php.net/manual/en/function.pg-send-prepare.php

Is there any way to send more statements at once to make less roundtrips between php and postgresql like the pg_send_query does?

I don't want to use pg_send_query because without parameter binding I can have sql injection vulnerabilities in my code.

codeassembly
  • 1,112
  • 1
  • 9
  • 17
  • What's stopping you from just sending the queries in sequence, each one in its own `pg_send_execute()`? – staticsan Oct 14 '10 at 22:37
  • 1
    I want to send everything in one call to avoid database roundtrips http://en.wikipedia.org/wiki/Round-trip_delay_time – codeassembly Oct 15 '10 at 08:53

2 Answers2

1

The round trips to the DB server shouldn't be your bottleneck as long as you are (a) using persistent connections (either directly or via a pool) and (b) aren't suffering from the "n+1 selects" problem.

New connections have an order of magnitude overhead which slows things down if done on every query. The n+1 problem results in generating far more trips than is really needed if the application retrieved (or acted upon) sets of related rows rather than doing all operations one at a time.

See: What is the n+1 selects problem?

Community
  • 1
  • 1
Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • 1
    Hosting the DB on the same server reduces latency if connection is via Unix sockets. Using TCP/IP connection will add a noticeable penalty (ie. there is a `host=...` line in the `$connection_string` for `pg_connect()`). – jmz Mar 06 '11 at 16:51
  • We have a single application server, which needs to interact with DBs in various parts of the world. Here, the round trips are the bottleneck so it can happen. – pir Oct 27 '20 at 22:35
0

Separate your queries by semicolon:

UPDATE customers SET last_name = 'foo' WHERE id = 1;UPDATE customers SET last_name = 'bar' WHERE id = 2;

Edit: Okay you cannot do this on the call side:

The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.)

Another way would be to call a stored procedure with this method and this SP issues multiple statements.

Maxem
  • 2,634
  • 1
  • 20
  • 14
  • I don't want to concatenate parameters to the query, I want to bind them to a prepared stament like this UPDATE customers SET last_name = $1 WHERE id = $2;Next statement;Another statement; in order to avoid sql injection. – codeassembly Oct 14 '10 at 21:56
  • can I make a generic stored procedure than can accept a string containing multiple statements separated with semicolons and a variable number of parameters representing the statements parameters? It sounds like a very big hack and I think that even if possible it will have lots of unwanted side effects :) – codeassembly Oct 14 '10 at 22:03
  • Sorry but I don't know php well enough to help you further, but I'd stay away from generic stored procedures. – Maxem Oct 14 '10 at 22:05
  • Should php not provide a way to do what you want, create a stored procedure for every use case you have (update customer 1 and 2 on condition A, update product 1 to 232 if on condition B etc.) – Maxem Oct 14 '10 at 22:07
  • That seems a lot of work, I need something simple and elegant, performance is not that important, if I can achieve it in a simple way fine but if I have to work hard and waste time then is ok to send the queries one by one and use that time to do something more useful. – codeassembly Oct 14 '10 at 22:15