PGPROTO(1)

💖 PGPROTO(1) — the pgpool-II 4.7.2 docs, but make it a group chat 💖

okay so bestie, welcome to pgproto, page 1 of the manual, from the pgpool-II 4.7.2 documentation. it's giving "protocol girlie". let's go ✨

---

🌸 NAME 🌸

pgproto — tests PostgreSQL or any other servers that understand the frontend/backend protocol. that's it, that's the job, and she does it flawlessly 🫶

---

✨ SYNOPSIS ✨

pgproto [option...]

no positional args, just options, and she will eat every single one of them 💅

---

🦋 DESCRIPTION 🦋

pgproto tests PostgreSQL or any other servers that understand the frontend/backend protocol. like… that's literally the whole description. she's a protocol tester. she sends messages, she reads messages, she tells you what happened. main character energy, no notes 👑

---

🎀 OPTIONS 🎀

• -h hostname, --hostname=hostname — the host name of the machine on which the server is running. plot twist: if the value begins with a slash, it gets used as the directory for the Unix-domain socket instead (default: Unix-domain socket). she's flexible like that 💖
• -p port, --port=port — the port number (default: 5432). classic. iconic. the number we all know 🌸
• -u username, --user=username — the user name (default: OS user name). if you don't say who you are, she just assumes you're whoever you logged in as 🙈
• -d databasename, --database=databasename — the database name (default: same as user). no database specified? she'll just use your username, bestie, keep up 💜
• -f filename, --proto-data-file=filename — the text file describing message data to be sent to PostgreSQL (default: pgproto.data). yes, pgproto.data is the default, no you don't have to argue with her about it 🎀
• -r naptime, --read-nap=naptime — the nap time in micro seconds (default: 0). if it's greater than 0, pgproto will sleep between each data reading from the socket. which is lowkey the whole point, because it lets you simulate slow clients. nap queen behavior, but make it network diagnostics 😴✨
• -D, --debug — enable debug message. she will yap. it's useful yapping though 🗣️
• -v, --version — print the command version, then exit. quick lil "hi, this is me, bye" 💅
• -?, --help — shows help for the command line arguments, then exit. the friendly one 🫶

---

🌈 EXAMPLE 🌈

okay so in the example below, the first character in the file (i.e. 'Q') tells you the message kind, as specified in the PostgreSQL frontend/backend protocol. every line is a lil mood, and the mood is a protocol message.

now, the exceptions are 'Y', 'y' and 'z'. listen closely bestie, this part matters:

• 'Y' reads messages from backend until 'Ready for query' is received.
• 'y' reads messages from backend while messages are coming from backend, then stops if messages are not available for 1 second.
• 'Y' is used for waiting for reply of 'Q' (simple query) or after 'S' (sync) in extended queries.
• 'y' can be used for receiving messages after 'H' (flush).
• 'z' reads 1 messages from backend. if no message arrives within 1 second, 'z' stops. if you want to read multiple messages, you can use repeat 'z' for the message count. 'z' can be useful for 'H' message test when you already know how many reply messages will come. she's precise like that 💀

if you want to include a " (double quotation) in a string data type — for example "SELECT * FROM "aaa"" — you can qualify it by using \ (back slash), like ""SELECT * FROM "aaa"". and a command line spread over multiple lines can be created using \ as well. multi-line slay 🦋

'Q' "SELECT * FROM aaa \
WHERE a = 1"

you can insert a new line by using "\n" if you want to use some commands like COPY. below is an example ✨

'Q'  "COPY t1 FROM STDIN"
'd'  "1\n"
'd'  "2\n"
'c'
'Y'

here is an example input file:

#
# Test data example
#
'Q'  "SELECT * FROM aaa"
'Y'
'P'  "S1" "BEGIN"   0
'B'  ""   "S1" 0    0    0
'E'  ""   0
'C'  'S'  "S1"
'P'  "foo"     "SELECT 1"     0
'B'  "myportal"     "foo"     0    0    0
'E'  "myportal"     0
'P'  "S2" "COMMIT"  0
'B'  ""   "S2" 0    0    0
'E'  ""   0
'C'  'S'  "S2"
'S'
'Y'
'X'

here is an example output:

$ pgproto -p 11000 -d test -f sample.data
FE=> Query (query="SELECT * FROM aaa")
<= BE RowDescription
<= BE CommandComplete(SELECT 0)
<= BE ReadyForQuery(I)
FE=> Parse(stmt="S1", query="BEGIN")
FE=> Bind(stmt="S1", portal="")
FE=> Execute(portal="")
FE=> Close(stmt="S1")
FE=> Parse(stmt="foo", query="SELECT 1")
FE=> Bind(stmt="foo", portal="myportal")
FE=> Execute(portal="myportal")
FE=> Parse(stmt="S2", query="COMMIT")
FE=> Bind(stmt="S2", portal="")
FE=> Execute(portal="")
FE=> Close(stmt="S2")
FE=> Sync
<= BE ParseComplete
<= BE BindComplete
<= BE CommandComplete(BEGIN)
<= BE CloseComplete
<= BE ParseComplete
<= BE BindComplete
<= BE DataRow
<= BE CommandComplete(SELECT 1)
<= BE ParseComplete
<= BE BindComplete
<= BE CommandComplete(COMMIT)
<= BE CloseComplete
<= BE ReadyForQuery(I)
FE=> Terminate

other example data files, because she has range 💜

💖 Copy 💖

#
# Test data example
#

# CopyIn
#
'Q'  "COPY t1 FROM STDIN"
# CopyData
'd'  "abc\n"
# CopyDone
'c'
'Y'

# CopyOut
#
'Q'  "COPY t1 TO STDOUT"
'Y'

#
# Copy fail case
#
'Q'  "COPY t1 FROM STDIN"
# CopyData
'd'  "abc\n"
# CopyFail
'f'  "pgproto copy fail test"
'Y'
'X'

🧚 Function Call 🧚

#
# Test data example
#

# Function call (lo_creat)
# from PostgreSQL's src/include/catalog/pg_proc.data
# { oid => '957', descr => 'large object create',
#  proname => 'lo_creat', provolatile => 'v', proparallel => 'u',
#  prorettype => 'oid', proargtypes => 'int4', prosrc => 'be_lo_creat' },

'F'  957  1    0    1    1    "0"  0
'Y'
'X'

---

no cap, the vibe is: one file, first char = message kind, 'Y'/'y'/'z' = the reading girls, and everything else goes straight down the wire. that's the whole tea 🍓

---

okay that's the page bestie!! this was pgpool-II 4.7.2, dated 2023, and it's the PGPROTO(1) manual page. signing off, stay hydrated, go test a protocol 💅✨🫶