Simple Query: Rows Over the Wire

Why this stage exists

Back in The Core Engine, the result-set stage made you separate what a statement produced from how it is printed: rows, a count of affected rows, a message, an error, each its own kind of result, with a printer deciding how it looks. Table mode and CSV mode were the first two printers. This stage adds a third, one that prints for programs instead of people.

When a client sends a Query, the server runs the statement exactly as a REPL line, as the session of that connection. Then, instead of printing text, it sends the result as messages:

After this stage, psql works, and so does any program using a PostgreSQL driver. Not every feature works, but a SELECT does.

Four kinds of result, four kinds of answer

ReadyForQuery comes after every one of them. It is the client's signal that the answer is complete, so a client that sent a query reads until it sees one. Everything the REPL adds for a person stays out: the [EXEC] line, the (n rows) footer, the column separators. The row count goes into the tag instead. The 0 in INSERT 0 1 is a relic PostgreSQL keeps for old clients (it was the new row's object id), and drivers expect it.

Describing columns: names and type ids

RowDescription comes first, once per result, and tells the client how many columns there are, what they are called and what type each one has. Types are not names but numbers, PostgreSQL's type ids (OIDs). A driver uses them to turn the text of each value into the right kind of value in its own language: 23 becomes an integer, 1043 a string, 16 a boolean.

A RowDescription is sent even when no row matches. "Two columns and no rows" is a different answer from "no result at all", and a driver must be able to tell them apart.

Values are text, and NULL is not

Every value travels as text, written the way the REPL writes it: 42, ann, 1.50. A truth value is the exception: PostgreSQL sends t and f, and drivers expect those, so that is what goes on the wire where the REPL prints true and false.

Each value is preceded by its length. That length is also how NULL travels: -1 means "no value", with no bytes after it. An empty string is something else, a value whose length is 0:

Debug commands over the wire

tx status, sessions and the other debug commands are not SQL, but they are too useful to lose when you connect with psql. So they answer as rows: one text column named droid, one row per line the REPL would print, and the tag SHOW. explain does the same with a column named QUERY PLAN, as PostgreSQL's own EXPLAIN does, and the tag EXPLAIN.

Output contract for this stage

These are the exact messages the tests check. The full definition is on the output contract page, section 95.

What you should see when done

psql now works against your server:

$psql -h 127.0.0.1 -p 5433 -U droid droid
droid=> create table t (id int primary key, name varchar(20));
CREATE TABLE
droid=> insert into t (id, name) values (1, 'ann');
INSERT 0 1
droid=> insert into t (id, name) values (2, null);
INSERT 0 1
droid=> select id, name, id > 1 as big from t order by id;
 id | name | big
----+------+-----
  1 | ann  | f
  2 |      | t
(2 rows)
 
droid=> tx status
       droid
-------------------
 [TX] Status: IDLE
(1 row)
 

psql printed the table itself, in its own format, from your RowDescription and DataRows. It even right-aligned the id column, because type 23 told it the column is a number.

Common bugs in this stage

• A DataRow length that is off by a few bytes. The client then reads the next message from the wrong place, and fails on a message that is correct. Compute every length from what was actually appended, never by hand.
• NULL sent as an empty string, or as the text NULL. Both break the difference that NULL exists to make.
• Sending the [EXEC] line, or (2 rows). They belong to the REPL's printer. The row count is in the tag.
• No RowDescription for an empty result. SELECT 0 with no description leaves the driver without the columns.
• Forgetting ReadyForQuery after an error or an empty query. The client then waits forever.
• true and false for truth values. psql shows them, but drivers that parse booleans expect t and f.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: give the printer an interface, a struct of function pointers such as begin_rows, row and status, with one implementation for the table, one for CSV and one that appends wire messages to a connection's output buffer. The engine calls the interface and never knows which one it has.
• C++: a ResultSink base class with virtual methods, and a WireSink that holds a reference to the connection.
• Rust: a trait ResultSink, implemented by the REPL printer and by a WireSink<'a> that borrows the connection's output buffer.
• Zig: an interface struct holding a context pointer and function pointers, or a tagged union of the three printers, switched on where a result is emitted.