Prepared Statements without Additional Round-Trip

Prepared statements are the best way to avoid SQL injections in applications accepting user input. However they require an additional round-trip to prepare the query, which increases latency when your queries are dynamic and the statements are not re-used. PostgreSQL allows execution of such queries without the additional cost!

The Extended Query Protocol

Prepared statements in PostgreSQL are executed using the extended query protocol, using the following messages.

When statements are prepared and executed independently, they are be prepared with:

and then executed with:

Unnamed Statements

If you need to execute a query only once, you can send the Parse message without a name. This will create an unnamed statement that will be destroyed upon next query on the same connection. PostgreSQL also allows you to send the Bind, Execute and Sync messages without waiting for the response to previous messages. The sequence of messages will now be:

What about failures?

If Parse or any subsequent message cannot be processed, PostgreSQL server will read and discard all messages until the Sync message. It’ll then continue handling other messages received on the connection. So the connection is not left in a corrupt state or closed even if the query being prepared or parameters provided is incorrect, allowing clients to execute parameterized queries in a single round-trip.

How do you use it?

Whether and how you can use this feature depends on your DB driver. Some drivers use this by default even if they expose separate prepare and execute functions, while others may need additional connection parameters. So consult your driver’s documentation or source code.

09 Jul, 2017 postgresql / database / sql