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
From a767a27209a5dae0fdb0ab59d528b188d27ad813 Mon Sep 17 00:00:00 2001
From: Nikolay Samokhvalov <[email protected]>
Date: Wed, 11 Mar 2026 20:57:24 +0000
Subject: [PATCH] Add command_tag_format protocol-level negotiation
Add a new protocol-level option _pq_.command_tag_format that allows
clients to negotiate richer command completion tags at connection time.
The INSERT command completion tag has carried a vestigial OID field
since PostgreSQL 12 dropped support for table OIDs: INSERT always
returns 'INSERT 0 N' where the '0' is meaningless. Changing this
in the default wire format would break existing clients, since
libpq's PQcmdTuples() hardcodes the 'INSERT oid count' parse pattern.
This patch adds protocol-level negotiation via _pq_.command_tag_format
in the startup packet, with three modes:
legacy - INSERT 0 N (default, fully backward compatible)
verbose - INSERT tablename N
fqn - INSERT schema.tablename N
The verbose and fqn modes also populate relation names for UPDATE,
DELETE, and MERGE command tags.
The GUC is registered as PGC_INTERNAL with GUC_REPORT, ensuring:
- Old clients that don't send _pq_.command_tag_format always get
the legacy format (zero breakage)
- New clients connecting to old servers have _pq_ silently ignored
- SET, options=-c, ALTER SYSTEM, and postgresql.conf cannot change
the format (only the _pq_ startup packet path works)
- The server auto-reports the negotiated format to the client
Implementation details:
- The _pq_ handler in ProcessStartupPacket() stores the requested
format in a new Port field (pq_command_tag_format), separate from
guc_options, to prevent the regular GUC path from setting it
- The value is applied in process_startup_options() after normal
GUC initialization via SetConfigOption(PGC_INTERNAL, PGC_S_OVERRIDE)
- QueryCompletion is extended with relname/nspname fields, populated
from the executor's ResultRelInfo after query completion
- BuildQueryCompletionString() formats the tag based on the
negotiated mode
- No changes to libpq (fe-exec.c) -- old protocol is untouched
Discussion: (none yet)
---
src/backend/tcop/backend_startup.c | 22 ++++++++++----
src/backend/tcop/cmdtag.c | 35 +++++++++++++++++++++--
src/backend/tcop/pquery.c | 24 ++++++++++++++++
src/backend/utils/init/postinit.c | 11 +++++++
src/backend/utils/misc/guc_parameters.dat | 10 +++++++
src/backend/utils/misc/guc_tables.c | 8 ++++++
src/include/libpq/libpq-be.h | 3 ++
src/include/tcop/cmdtag.h | 9 ++++++
8 files changed, 115 insertions(+), 7 deletions(-)
diff --git a/src/backend/tcop/backend_startup.c b/src/backend/tcop/backend_startup.c
index c517115927c..ff1d620cf37 100644
--- a/src/backend/tcop/backend_startup.c
+++ b/src/backend/tcop/backend_startup.c
@@ -778,12 +778,24 @@ ProcessStartupPacket(Port *port, bool ssl_done, bool gss_done)
else if (strncmp(nameptr, "_pq_.", 5) == 0)
{
/*
- * Any option beginning with _pq_. is reserved for use as a
- * protocol-level option, but at present no such options are
- * defined.
+ * Options beginning with _pq_. are protocol-level options.
+ * Recognized options are mapped to their corresponding GUCs.
*/
- unrecognized_protocol_options =
- lappend(unrecognized_protocol_options, pstrdup(nameptr));
+ if (strcmp(nameptr, "_pq_.command_tag_format") == 0)
+ {
+ /*
+ * Protocol-level option: store for deferred application
+ * in process_startup_options() after GUC init. This
+ * is NOT added to guc_options so that old-style
+ * options=-c cannot set it (GUC is PGC_INTERNAL).
+ */
+ port->pq_command_tag_format = pstrdup(valptr);
+ }
+ else
+ {
+ unrecognized_protocol_options =
+ lappend(unrecognized_protocol_options, pstrdup(nameptr));
+ }
}
else
{
diff --git a/src/backend/tcop/cmdtag.c b/src/backend/tcop/cmdtag.c
index d38d5b390b9..9fe513150f9 100644
--- a/src/backend/tcop/cmdtag.c
+++ b/src/backend/tcop/cmdtag.c
@@ -14,6 +14,7 @@
#include "postgres.h"
#include "tcop/cmdtag.h"
+#include "utils/guc.h"
#include "utils/builtins.h"
@@ -36,11 +37,16 @@ static const CommandTagBehavior tag_behavior[] = {
#undef PG_CMDTAG
+/* GUC variable: command tag format style */
+int command_tag_format = COMMAND_TAG_FORMAT_LEGACY;
+
void
InitializeQueryCompletion(QueryCompletion *qc)
{
qc->commandTag = CMDTAG_UNKNOWN;
qc->nprocessed = 0;
+ qc->relname = NULL;
+ qc->nspname = NULL;
}
const char *
@@ -147,8 +153,33 @@ BuildQueryCompletionString(char *buff, const QueryCompletion *qc,
{
if (tag == CMDTAG_INSERT)
{
- *bufp++ = ' ';
- *bufp++ = '0';
+ if (command_tag_format == COMMAND_TAG_FORMAT_LEGACY)
+ {
+ /* Legacy: INSERT 0 N */
+ *bufp++ = ' ';
+ *bufp++ = '0';
+ }
+ else if ((command_tag_format == COMMAND_TAG_FORMAT_VERBOSE ||
+ command_tag_format == COMMAND_TAG_FORMAT_FQN) &&
+ qc->relname != NULL)
+ {
+ /* Verbose/FQN: INSERT [schema.]table N */
+ *bufp++ = ' ';
+ if (command_tag_format == COMMAND_TAG_FORMAT_FQN &&
+ qc->nspname != NULL)
+ {
+ Size nsplen = strlen(qc->nspname);
+ memcpy(bufp, qc->nspname, nsplen);
+ bufp += nsplen;
+ *bufp++ = '.';
+ }
+ {
+ Size rellen = strlen(qc->relname);
+ memcpy(bufp, qc->relname, rellen);
+ bufp += rellen;
+ }
+ }
+ /* Modern: INSERT N (nothing extra before count) */
}
*bufp++ = ' ';
bufp += pg_ulltoa_n(qc->nprocessed, bufp);
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index d8fc75d0bb9..a1e812f9327 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -26,6 +26,9 @@
#include "tcop/pquery.h"
#include "tcop/utility.h"
#include "utils/memutils.h"
+#include "catalog/namespace.h"
+#include "utils/rel.h"
+#include "utils/lsyscache.h"
#include "utils/snapmgr.h"
@@ -181,6 +184,27 @@ ProcessQuery(PlannedStmt *plan,
tag = CMDTAG_UNKNOWN;
SetQueryCompletion(qc, tag, queryDesc->estate->es_processed);
+
+ /* For verbose/FQN command tags, attach relation info for DML */
+ if (command_tag_format >= COMMAND_TAG_FORMAT_VERBOSE &&
+ (tag == CMDTAG_INSERT || tag == CMDTAG_UPDATE ||
+ tag == CMDTAG_DELETE || tag == CMDTAG_MERGE) &&
+ queryDesc->plannedstmt != NULL &&
+ queryDesc->plannedstmt->resultRelations != NIL &&
+ queryDesc->estate->es_result_relations != NULL)
+ {
+ int ri_index = linitial_int(queryDesc->plannedstmt->resultRelations) - 1;
+ if (ri_index >= 0 &&
+ ri_index < (int) queryDesc->estate->es_range_table_size &&
+ queryDesc->estate->es_result_relations[ri_index] != NULL &&
+ queryDesc->estate->es_result_relations[ri_index]->ri_RelationDesc != NULL)
+ {
+ ResultRelInfo *rri = queryDesc->estate->es_result_relations[ri_index];
+ qc->relname = RelationGetRelationName(rri->ri_RelationDesc);
+ qc->nspname = get_namespace_name(
+ RelationGetNamespace(rri->ri_RelationDesc));
+ }
+ }
}
/*
diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index b59e08605cc..6362640071b 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -1309,6 +1309,17 @@ process_startup_options(Port *port, bool am_superuser)
SetConfigOption(name, value, gucctx, PGC_S_CLIENT);
}
+
+ /*
+ * Apply protocol-negotiated options. These use PGC_INTERNAL context
+ * with PGC_S_OVERRIDE source, so they bypass the normal GUC access
+ * controls. This ensures only the _pq_ protocol path can set them;
+ * SET and options=-c are blocked by PGC_INTERNAL.
+ */
+ if (port->pq_command_tag_format != NULL)
+ SetConfigOption("command_tag_format",
+ port->pq_command_tag_format,
+ PGC_INTERNAL, PGC_S_OVERRIDE);
}
/*
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index a5a0edf2534..9e839c8f454 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -434,6 +434,16 @@
check_hook => 'check_cluster_name',
},
+
+{ name => 'command_tag_format', type => 'enum', context => 'PGC_INTERNAL', group => 'CLIENT_CONN_STATEMENT',
+ short_desc => 'Controls the format of INSERT command completion tags.',
+ long_desc => 'legacy: INSERT 0 N (default, backward compatible). verbose: INSERT tablename N. fqn: INSERT schema.tablename N. Can be set via _pq_.command_tag_format startup parameter for protocol-level negotiation.',
+ flags => 'GUC_REPORT',
+ variable => 'command_tag_format',
+ boot_val => 'COMMAND_TAG_FORMAT_LEGACY',
+ options => 'command_tag_format_options',
+ includes => 'tcop/cmdtag.h',
+},
# we have no microseconds designation, so can't supply units here
{ name => 'commit_delay', type => 'int', context => 'PGC_SUSET', group => 'WAL_SETTINGS',
short_desc => 'Sets the delay in microseconds between transaction commit and flushing WAL to disk.',
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 38aaf82f120..30e7b14cade 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -19,6 +19,7 @@
*--------------------------------------------------------------------
*/
#include "postgres.h"
+#include "tcop/cmdtag.h"
#ifdef HAVE_COPYFILE_H
#include <copyfile.h>
@@ -148,6 +149,13 @@ static const struct config_enum_entry client_message_level_options[] = {
{NULL, 0, false}
};
+static const struct config_enum_entry command_tag_format_options[] = {
+ {"legacy", 0, false},
+ {"verbose", 1, false},
+ {"fqn", 2, false},
+ {NULL, 0, false}
+};
+
const struct config_enum_entry server_message_level_options[] = {
{"debug5", DEBUG5, false},
{"debug4", DEBUG4, false},
diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h
index 921b2daa4ff..565bc49fcf3 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -152,6 +152,9 @@ typedef struct Port
char *cmdline_options;
List *guc_options;
+ /* Protocol-negotiated command tag format (from _pq_.command_tag_format) */
+ char *pq_command_tag_format;
+
/*
* The startup packet application name, only used here for the "connection
* authorized" log message. We shouldn't use this post-startup, instead
diff --git a/src/include/tcop/cmdtag.h b/src/include/tcop/cmdtag.h
index cf2e87b98f3..eaf864f7a75 100644
--- a/src/include/tcop/cmdtag.h
+++ b/src/include/tcop/cmdtag.h
@@ -30,6 +30,8 @@ typedef struct QueryCompletion
{
CommandTag commandTag;
uint64 nprocessed;
+ const char *relname; /* relation name for verbose command tags */
+ const char *nspname; /* schema name for FQN command tags */
} QueryCompletion;
@@ -56,6 +58,13 @@ extern bool command_tag_display_rowcount(CommandTag commandTag);
extern bool command_tag_event_trigger_ok(CommandTag commandTag);
extern bool command_tag_table_rewrite_ok(CommandTag commandTag);
extern CommandTag GetCommandTagEnum(const char *commandname);
+
+/* GUC: command tag format style */
+#define COMMAND_TAG_FORMAT_LEGACY 0 /* INSERT 0 N (default, backward compat) */
+#define COMMAND_TAG_FORMAT_VERBOSE 1 /* INSERT tablename N */
+#define COMMAND_TAG_FORMAT_FQN 2 /* INSERT schema.tablename N */
+
+extern int command_tag_format;
extern Size BuildQueryCompletionString(char *buff, const QueryCompletion *qc,
bool nameonly);
--
2.43.0
"""
Send a raw PostgreSQL startup packet with _pq_.command_tag_format.
Then execute INSERT and check the CommandComplete message.
"""
import socket
import struct
import sys
def make_startup_packet(user, database, extra_params=None):
"""Build a v3 startup packet with optional _pq_ parameters."""
params = []
params.append(b"user\x00" + user.encode() + b"\x00")
params.append(b"database\x00" + database.encode() + b"\x00")
if extra_params:
for k, v in extra_params.items():
params.append(k.encode() + b"\x00" + v.encode() + b"\x00")
params.append(b"\x00") # terminator
body = b"".join(params)
# Version 3.0
version = struct.pack("!I", 196608) # 3 << 16
length = struct.pack("!I", 4 + len(version) + len(body))
return length + version + body
def read_message(sock):
"""Read one PG protocol message. Returns (type_byte, payload)."""
hdr = sock.recv(1)
if not hdr:
return None, None
msg_type = hdr.decode("ascii")
length_bytes = sock.recv(4)
length = struct.unpack("!I", length_bytes)[0]
payload = b""
remaining = length - 4
while remaining > 0:
chunk = sock.recv(remaining)
if not chunk:
break
payload += chunk
remaining -= len(chunk)
return msg_type, payload
def send_query(sock, sql):
"""Send a simple query."""
body = sql.encode() + b"\x00"
msg = b"Q" + struct.pack("!I", 4 + len(body)) + body
sock.sendall(msg)
def run_test(format_value=None):
extra = {}
if format_value:
extra["_pq_.command_tag_format"] = format_value
label = format_value or "default"
print(f"\n=== Testing with _pq_.command_tag_format={label} ===")
sock = socket.socket(socket.AF_UNIX, socket.SOCK_STREAM)
sock.connect("/tmp/.s.PGSQL.5432")
packet = make_startup_packet("postgres", "postgres", extra if extra else None)
sock.sendall(packet)
# Read auth + parameter status + ready
ready = False
while not ready:
msg_type, payload = read_message(sock)
if msg_type is None:
print("Connection closed!")
return
if msg_type == "E":
# Error
err = payload.decode("utf-8", errors="replace")
print(f" ERROR: {err}")
sock.close()
return
if msg_type == "R":
# Auth request
auth_type = struct.unpack("!I", payload[:4])[0]
if auth_type == 0:
pass # AuthOK
if msg_type == "S":
# ParameterStatus
parts = payload.rstrip(b"\x00").split(b"\x00")
if len(parts) == 2:
name, value = parts[0].decode(), parts[1].decode()
if name == "command_tag_format":
print(f" GUC_REPORT: command_tag_format = {value}")
if msg_type == "Z":
ready = True
# Create table
send_query(sock, "CREATE TABLE IF NOT EXISTS proto_test(id int)")
while True:
msg_type, payload = read_message(sock)
if msg_type == "Z":
break
# INSERT
send_query(sock, "INSERT INTO proto_test VALUES (777)")
while True:
msg_type, payload = read_message(sock)
if msg_type == "C":
tag = payload.rstrip(b"\x00").decode()
print(f" CommandComplete: {tag}")
if msg_type == "Z":
break
# Cleanup
send_query(sock, "DELETE FROM proto_test WHERE id = 777")
while True:
msg_type, payload = read_message(sock)
if msg_type == "Z":
break
# Terminate
sock.sendall(b"X\x00\x00\x00\x04")
sock.close()
# Run tests
run_test(None) # No _pq_ param → legacy
run_test("verbose") # _pq_ verbose → INSERT tablename N
run_test("fqn") # _pq_ fqn → INSERT schema.tablename N
run_test("legacy") # _pq_ legacy → INSERT 0 N
run_test("modern") # _pq_ modern → should fail (removed)
