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], 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][3] 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
