[PATCH v1] command_tag_format — protocol-level command tag negotiation via _pq_
Hi hackers,
PostgreSQL has had a protocol feature negotiation framework since
7.4 (the _pq_ namespace in startup parameters) -- over 20 years --
but it's never been used in practice. During a recent "hacking
Postgres" session with Andrey Borodin and Kirk Wolak [1]https://www.youtube.com/watch?v=VKuxQZlvd8E, we
drafted a proof-of-concept that puts _pq_ to real use.
One thing that keeps coming up is confusion around INSERT's command
tag: "INSERT 0 1" -- what is that zero? [2]https://www.linkedin.com/posts/l%C3%A6titia-avrot_postgresql-database-backend-activity-7431694565855617024-Lb1u[3]https://mydbanotebook.org/posts/what-does-insert-0-1-actually-tell-you/ It's a vestigial OID
field, hardcoded to zero since PG12 dropped table OIDs. We can't
change the default without breaking every existing client (libpq's
PQcmdTuples hardcodes the "INSERT oid count" pattern), but protocol
negotiation could solve this cleanly. There are also cases where
seeing the table name in the command tag would be genuinely useful
-- for example, when restoring from a dump with many tables, it
helps to see which table is receiving INSERTs at any given moment.
The attached patch (not meant to be taken as-is, just to raise
discussion) implements protocol-level command tag negotiation via
_pq_.command_tag_format. The client sends it in the startup packet
and gets one of three formats:
legacy - INSERT 0 N (default, fully backward compatible)
verbose - INSERT tablename N
fqn - INSERT schema.tablename N
The GUC is PGC_INTERNAL (cannot be changed via SET or options=-c),
stored in a separate Port field, and applied via PGC_S_OVERRIDE
after GUC init. Old clients always get legacy. New clients
connecting to old servers have _pq_ silently ignored.
Test results with stock PG17 psql (old client) and a Python script
that sends raw _pq_ startup packets (also attached):
Old PG17 psql, default -> INSERT 0 1 (safe)
Old PG17 psql, options=-c -> FATAL: cannot be changed (blocked)
Old PG17 psql, SET -> ERROR: cannot be changed (blocked)
_pq_ verbose -> INSERT proto_test 1 (works)
_pq_ fqn -> INSERT public.proto_test 1 (works)
_pq_ not sent -> INSERT 0 1 (legacy default)
Does this all make sense? Is it worth thinking further in this
direction?
[1]: https://www.youtube.com/watch?v=VKuxQZlvd8E
[2]: https://www.linkedin.com/posts/l%C3%A6titia-avrot_postgresql-database-backend-activity-7431694565855617024-Lb1u
[3]: https://mydbanotebook.org/posts/what-does-insert-0-1-actually-tell-you/
--
Nik
Hi,
On 2026-03-11 21:22:14 +0000, nik@postgres.ai wrote:
PostgreSQL has had a protocol feature negotiation framework since
7.4 (the _pq_ namespace in startup parameters) -- over 20 years --
but it's never been used in practice.
Wasn't that added in
commit ae65f6066dc
Author: Robert Haas <rhaas@postgresql.org>
Date: 2017-11-21 13:56:24 -0500
Provide for forward compatibility with future minor protocol versions.
Previously, any attempt to request a 3.x protocol version other than
3.0 would lead to a hard connection failure, which made the minor
protocol version really no different from the major protocol version
and precluded gentle protocol version breaks. Instead, when the
client requests a 3.x protocol version where x is greater than 0, send
the new NegotiateProtocolVersion message to convey that we support
only 3.0. This makes it possible to introduce new minor protocol
versions without requiring a connection retry when the server is
older.
PG 14 / 2017 is quite a while after 7.4...
legacy - INSERT 0 N (default, fully backward compatible)
verbose - INSERT tablename N
fqn - INSERT schema.tablename N
Pretty doubtful this survives the complexity / gain tradeoff.
Separately, doing extra work during command handling isn't free either. We've
spent a decent amount of effort in the past lowering it, see e.g.
commit ac998020802
Author: David Rowley <drowley@postgresql.org>
Date: 2022-12-16 10:31:25 +1300
Speed up creation of command completion tags
I'm loathe to add work to every statement.
Greetings,
Andres Freund
On Wed, Mar 11, 2026 at 2:39 PM Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2026-03-11 21:22:14 +0000, nik@postgres.ai wrote:
PostgreSQL has had a protocol feature negotiation framework since
7.4 (the _pq_ namespace in startup parameters) -- over 20 years --
but it's never been used in practice.Wasn't that added in
commit ae65f6066dc
Author: Robert Haas <rhaas@postgresql.org>
Date: 2017-11-21 13:56:24 -0500Provide for forward compatibility with future minor protocol versions.
Previously, any attempt to request a 3.x protocol version other than
3.0 would lead to a hard connection failure, which made the minor
protocol version really no different from the major protocol version
and precluded gentle protocol version breaks. Instead, when the
client requests a 3.x protocol version where x is greater than 0, send
the new NegotiateProtocolVersion message to convey that we support
only 3.0. This makes it possible to introduce new minor protocol
versions without requiring a connection retry when the server is
older.PG 14 / 2017 is quite a while after 7.4...
Right, I confused it, _pq_ namespace reserved long, long ago, but the
actual NegotiateProtocolVersion mechanism
is from 2017 indeed. My bad.
legacy - INSERT 0 N (default, fully backward compatible)
verbose - INSERT tablename N
fqn - INSERT schema.tablename NPretty doubtful this survives the complexity / gain tradeoff.
Separately, doing extra work during command handling isn't free either. We've
spent a decent amount of effort in the past lowering it, see e.g.commit ac998020802
Author: David Rowley <drowley@postgresql.org>
Date: 2022-12-16 10:31:25 +1300Speed up creation of command completion tags
I'm loathe to add work to every statement.
On performance: the extra work (relname lookup) only runs when a
client explicitly opts in. The default legacy path adds just one
integer comparison, so almost nothing. The two new QueryCompletion
pointers are initialized to NULL and never touched in legacy/default
mode.
That said, this was meant purely as a discussion starter -- is pq the
right mechanism for per-connection feature negotiation like this, or
would something else be preferred?
For example, when restoring from a large dump, we see a lot of "INSERT
0 N" emitted – that's not super convenient. If pg_dump would use this
(and I think, in this case the overhead would be really acceptable),
then we would see something like "INSERT tblname N", understanding
what table already received data.
Nik