skip to content

In psql 18, what do \parse, \bind_named and the \startpipeline ... \endpipeline commands add, and when would you reach for them?

level: seniorimportance: nice to knowfreq 14%

answer

  1. simple versus extended protocol
  2. named statements from a terminal
  3. send without waiting
  4. a sync ends the implicit transaction
  5. %P in the prompt

basics

~20 s

psql normally uses the simple query protocol. psql 18's \parse creates a named protocol-level prepared statement, \bind_named executes it with parameters and \close_prepared closes it; \startpipeline ... \endpipeline queue queries without waiting for each result, as drivers do.

solid answer

~50 s

Plain psql sends each statement with the simple query protocol; `\bind`, which predates 18, already forced the extended protocol for one unnamed statement. psql 18 adds the named lifecycle: `SELECT ... WHERE parcel_id = $1 \parse find_parcel` sends a Parse message, `\bind_named find_parcel 42 \g` binds and executes it, and `\close_prepared find_parcel` closes it. Meanwhile the statement shows in `pg_prepared_statements` with `from_sql = false`, exactly like one a driver created. The pipeline commands queue work without waiting: `\startpipeline`, statements ending in `;` or `\sendpipeline`, `\syncpipeline` for a sync point, `\flushrequest` plus `\getresults` to read early, then `\endpipeline`. Between syncs the statements form one implicit transaction, and after an error the rest up to the next sync is skipped. Use them to reproduce a driver or pooler problem from a terminal, or to measure what pipelining saves on a slow link.

code

sql · 10 lines
sql
SELECT parcel_id, status FROM tracking.parcels WHERE parcel_id = $1 \parse find_parcel
\bind_named find_parcel 42 \g
SELECT name, from_sql FROM pg_prepared_statements;
\close_prepared find_parcel
\startpipeline
SELECT count(*) FROM tracking.parcels WHERE status = 'stuck';
SELECT parcel_id FROM tracking.parcels WHERE parcel_id = $1 \bind 42 \sendpipeline
\syncpipeline
SELECT now();
\endpipeline

go deeper

for a junior

Recognising that psql 18 added commands for prepared statements and pipelines is enough; no interviewer expects a junior to have used them.

for a middle

Explain the simple versus extended protocol difference and what \parse, \bind_named and \close_prepared do in that light.

for a senior

Describe pipeline semantics: implicit transactions between syncs, skipped statements after an error, %P, and how you would use these commands to reproduce a driver or pooler bug.

for a principal

Judge when pipelining is worth enabling in application drivers, weighing round-trip savings on high-latency links against harder error handling and pooler compatibility.

## Two wire protocols, and why psql used only one PostgreSQL clients talk to the server with one of two message flows. The **simple query protocol** sends a SQL string and gets results back. The **extended query protocol** splits the work into steps (Parse, Bind, Execute, Close and a Sync that ends a group), which is how drivers implement bind parameters and protocol-level prepared statements. Historically **psql** used the simple protocol for everything, so problems that only appear with the extended protocol, the way applications actually talk to the server, were hard to reproduce from a terminal. `\bind` (which predates 18) forced the extended protocol for a single unnamed statement; psql 18 fills in the rest. ## Named prepared statements: \parse, \bind_named, \close_prepared | Command | What it does | |---|---| | `query \parse name` | creates a protocol-level prepared statement from the query buffer by sending a Parse message | | `\bind_named name v1 v2 ...` | binds parameters to that statement; the next `\g` executes it | | `\close_prepared name` | closes the statement; a no-op if it does not exist | An empty string names the **unnamed** prepared statement. A statement created this way appears in `pg_prepared_statements` with **`from_sql = false`**, which separates it from one made by SQL `PREPARE` (`from_sql = true`). Outside a pipeline, psql goes back to the simple protocol for the queries that follow. ## Pipelines in psql **Pipelining** means sending several queries without waiting for each result, so one network round trip carries many statements. psql 18 exposes libpq's pipeline mode: 1. `\startpipeline` enters pipeline mode. 2. Each query ending in `;` is appended to the pipeline; `\sendpipeline` appends the query buffer, typically after `\bind` or `\bind_named`. 3. `\syncpipeline` sends a sync point without leaving the pipeline. 4. `\flushrequest` asks the server to send what it has so far, and `\getresults [n]` reads pending results; `\flush` pushes unsent data manually. 5. `\endpipeline` sends the final sync, reads the remaining results and leaves pipeline mode. While a pipeline is open, every query uses the extended protocol, and `\g`, `\gx` and `\gdesc` are refused. Reading results is explicit. `\getresults 2` reads only the first two pending results, while `\getresults` with no number, or with `0`, reads everything pending. Results only become readable once the server has been asked to send them, by a sync point or a `\flushrequest`, which is why the two commands usually appear together when you want to look at early results without closing the pipeline or ending the implicit transaction. ## Errors, transactions and limits - **Implicit transactions.** Without an explicit `BEGIN`, the statements between two sync points run in one implicit transaction, committed at the sync if they all succeeded and rolled back if one failed. - **Error skipping.** After an error, the server skips the remaining statements up to the next sync point; libpq reports them as aborted. Dependent steps belong between the same pair of syncs, independent ones in separate segments. - **Prompt and counters.** `%P` in `PROMPT1` shows `off`, `on` or `abort`; the variables `PIPELINE_COMMAND_COUNT`, `PIPELINE_SYNC_COUNT` and `PIPELINE_RESULT_COUNT` expose the queue. - **No COPY** inside a pipeline. ## When this earns its keep Few engineers will touch these commands in a normal week, which is why interviewers rarely go past recognising them. They pay off when: - a driver or pooler problem involves named prepared statements, and you want the exact message sequence from a terminal rather than from application code; how PgBouncer tracks such statements is that tool's subject; - you want to show the latency saved by pipelining over a long round trip, before asking a team to enable it in their driver; - you write a regression test in a psql script for behaviour that only appears with the extended protocol. For everyday queries, `;` and the simple protocol remain the right tool.

  • Why would \parse and \bind_named help when debugging a connection-pooler problem?
    Drivers usually prepare named statements over the extended protocol, and a pooler that gives each transaction a different server connection can lose them, so the client later fails because its statement does not exist on the new connection. psql 18 lets you replay that exact message sequence from a terminal instead of from application code. How PgBouncer tracks prepared statements is PgBouncer's subject.
  • How can you see from the psql prompt that a pipeline has hit an error?
    Add `%P` to `PROMPT1`: it shows `off` outside a pipeline, `on` while one is open, and `abort` after an error until the next sync point. The `PIPELINE_COMMAND_COUNT`, `PIPELINE_SYNC_COUNT` and `PIPELINE_RESULT_COUNT` variables hold the queue counters when a script needs them.

  • Saying \parse runs a SQL PREPARE, so from_sql would be true
  • Claiming pipelining makes each statement execute faster on the server
  • Assuming one failed statement in a pipeline never affects the others
  • Believing \bind_named was already available in psql 16
  • Expecting COPY to work inside a psql pipeline

Assumes psql 18: \parse, \bind_named, \close_prepared, the pipeline commands, %P and the PIPELINE_* variables are all new in 18; \bind already existed.