Horton

Re: display hot standby state in psql prompt

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Fujii Masao <masao.fujii@gmail.com>, Srinath Reddy Sadipiralla <srinath2133@gmail.com>, Greg Sabino Mullane <htamfids@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Chao Li <li.evan.chao@gmail.com>
Date: 2025-10-31T21:00:51Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. psql: Add %i prompt escape to indicate hot standby status.

  2. Mark search_path as GUC_REPORT

Attachments


On 30/10/2025 11:16, Jim Jones wrote:
> if (!hs || !ro)
>   strlcpy(buf, _("unknown"), sizeof(buf));
> else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0)
>   strlcpy(buf, _("read-only"), sizeof(buf));
> else
> {
>   const char *tr = NULL;
>   PGresult   *res;
> 
>   res = PQexec(pset.db, "SHOW transaction_read_only");
>   if (PQresultStatus(res) == PGRES_TUPLES_OK &&
>   PQntuples(res) == 1)
>   tr = PQgetvalue(res, 0, 0);
> 
>   if (!tr)
>     strlcpy(buf, _("unknown"), sizeof(buf));
>   else if (strcmp(tr, "on") == 0)
>     strlcpy(buf, _("read-only"), sizeof(buf));
>   else
>     strlcpy(buf, _("read/write"), sizeof(buf));
> 
>   PQclear(res);
> }

While reviewing another patch, I had another idea to further minimise
the overhead of checking transaction_read_only, namely, to check it only
when within a transaction block:

if (!hs || !ro)
  strlcpy(buf, _("unknown"), sizeof(buf));
else if (strcmp(hs, "on") == 0 || strcmp(ro, "on") == 0)
  strlcpy(buf, _("read-only"), sizeof(buf));
else
{
  PGTransactionStatusType tstatus = PQtransactionStatus(pset.db);

  /*
   * Check transaction_read_only only when in a transaction
   * block.  When idle (not in a transaction), the value of
   * transaction_read_only is the same as
   * default_transaction_read_only, which we already checked
   * above.  Avoiding the query improves performance,
   * especially for prompt redisplays.
   */

  if (tstatus == PQTRANS_IDLE)
    strlcpy(buf, _("read/write"), sizeof(buf));
  else
  {
    /* In a transaction block, need to check transaction_read_only */
    const char *tr = NULL;
    PGresult *res;

    res = PQexec(pset.db, "SHOW transaction_read_only");
    if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1)
      tr = PQgetvalue(res, 0, 0);

    if (!tr)
      strlcpy(buf, _("unknown"), sizeof(buf));
    else if (strcmp(tr, "on") == 0)
      strlcpy(buf, _("read-only"), sizeof(buf));
    else
      strlcpy(buf, _("read/write"), sizeof(buf));

    PQclear(res);
  }
}

The idea is to skip the test if tstatus == PQTRANS_IDLE, which indicates
that it is not in a transaction block, making the check for
transaction_read_only unnecessary.

Thoughts on this approach?

v7 attached.

Best, Jim