Thread

1. PoC - psql - emphases line with table name in verbose output

   Pavel Stehule <pavel.stehule@gmail.com> — 2026-03-29T16:42:46Z

   Hi
   
   Sometimes I have to run vacuum verbose in environments with hundreds of
   tables. The result is pretty unreadable. Attached patch try introduce some
   emphasis of interesting lines from INFO output. It is proof concept if some
   game with output has some benefits or not.
   
   What do you think about this?
   
   Regards
   
   Pavel
   

2. Re: PoC - psql - emphases line with table name in verbose output

   Pavel Stehule <pavel.stehule@gmail.com> — 2026-04-14T03:42:24Z

   Hi
   
   rebase, new commit message and minor cleaning
   
   Regards
   
   Pavel
   

3. Re: PoC - psql - emphases line with table name in verbose output

   Jim Jones <jim.jones@uni-muenster.de> — 2026-04-23T15:17:47Z

   Hi Pavel
   
   On 14/04/2026 05:42, Pavel Stehule wrote:
   > rebase, new commit message and minor cleaning
   Thanks for the patch!
   
   I tested the patch and setting PG_COLOR highlights the INFO messages.
   
   A few observations:
   
   == string matching is locale-fragile ==
   
   Since the code relies on these fixed strings ...
   
   if (level == PG_LOG_INFO && sgr_info_command &&
     (strncmp(buf, "INFO:  vacuuming", strlen("INFO:  vacuuming")) == 0 ||
      strncmp(buf, "INFO:  repacking", strlen("INFO:  repacking")) == 0 ||
      strncmp(buf, "INFO:  analyzing", strlen("INFO:  analyzing")) == 0))
   
   .. the conditions only work if lc_messages is set to English. For
   instance, in German you get a different string, which means that
   highlighting won't work:
   
   $ psql postgres -c "VACUUM VERBOSE pg_class;" 2>&1 | grep INFO
   INFO:  Vacuum von »postgres.pg_catalog.pg_class«
   INFO:  beende Vacuum der Tabelle »postgres.pg_catalog.pg_class«:
   Index-Scans: 0
   
   $ psql postgres -c "ANALYSE VERBOSE pg_class;" 2>&1 | grep INFO
   INFO:  analysiere »pg_catalog.pg_class«
   INFO:  »pg_class«: 15 von 15 Seiten gelesen, enthalten 452 lebende
   Zeilen und 0 tote Zeilen; 452 Zeilen in Stichprobe, schätzungsweise 452
   Zeilen insgesamt
   INFO:  finished analyzing table "postgres.pg_catalog.pg_class"
   
   == fixed command list ==
   
   Future verbose operations, if not added to this list, would silently get
   no highlighting.
   
   I'm wondering if it is possible to achieve it (locale-agnostic) only for
   certain commands without touching the code on the server side. Only by
   checking strings it'll be difficult to identify which INFO messages to
   highlight.
   
   Thanks!
   
   Best, Jim
   
   
   
   
   

4. Re: PoC - psql - emphases line with table name in verbose output

   Pavel Stehule <pavel.stehule@gmail.com> — 2026-04-24T06:56:36Z

   Hi
   
   čt 23. 4. 2026 v 17:17 odesílatel Jim Jones <jim.jones@uni-muenster.de>
   napsal:
   
   > Hi Pavel
   >
   > On 14/04/2026 05:42, Pavel Stehule wrote:
   > > rebase, new commit message and minor cleaning
   > Thanks for the patch!
   >
   > I tested the patch and setting PG_COLOR highlights the INFO messages.
   >
   > A few observations:
   >
   > == string matching is locale-fragile ==
   >
   > Since the code relies on these fixed strings ...
   >
   > if (level == PG_LOG_INFO && sgr_info_command &&
   >   (strncmp(buf, "INFO:  vacuuming", strlen("INFO:  vacuuming")) == 0 ||
   >    strncmp(buf, "INFO:  repacking", strlen("INFO:  repacking")) == 0 ||
   >    strncmp(buf, "INFO:  analyzing", strlen("INFO:  analyzing")) == 0))
   >
   > .. the conditions only work if lc_messages is set to English. For
   > instance, in German you get a different string, which means that
   > highlighting won't work:
   >
   > $ psql postgres -c "VACUUM VERBOSE pg_class;" 2>&1 | grep INFO
   > INFO:  Vacuum von »postgres.pg_catalog.pg_class«
   > INFO:  beende Vacuum der Tabelle »postgres.pg_catalog.pg_class«:
   > Index-Scans: 0
   >
   > $ psql postgres -c "ANALYSE VERBOSE pg_class;" 2>&1 | grep INFO
   > INFO:  analysiere »pg_catalog.pg_class«
   > INFO:  »pg_class«: 15 von 15 Seiten gelesen, enthalten 452 lebende
   > Zeilen und 0 tote Zeilen; 452 Zeilen in Stichprobe, schätzungsweise 452
   > Zeilen insgesamt
   > INFO:  finished analyzing table "postgres.pg_catalog.pg_class"
   >
   > == fixed command list ==
   >
   > Future verbose operations, if not added to this list, would silently get
   > no highlighting.
   >
   > I'm wondering if it is possible to achieve it (locale-agnostic) only for
   > certain commands without touching the code on the server side. Only by
   > checking strings it'll be difficult to identify which INFO messages to
   > highlight.
   >
   
   I am afraid this is the end of this direction. :-/
   
   Please, can you check the functionality (only in english). I am interested
   if this is just helpful and if it makes sense to continue in this feature.
   Unfortunately, there are not too many possibilities about possible formats,
   colors in terminals (that can work mostly everywhere).
   
   I don't think it is possible to implement this without  communication
   protocol enhancement. And if we will do this, the next question is if we
   cannot use this for some more complex information about the executed
   command.
   
   For example - I thought about the possibility of teaching psql to read
   progress stat tables - so can be nice, if the server can send some
   information to client - maybe pgstat_progres_update can send INFO
   
   like - "emphasize: nextinfo, pid: xxxx, progress table: pg_stat_vacuum,
   commandtype: vacuum, ....
   
   Maybe a different approach - instead of a plain text message, we can send
   messages of this type in client side parsable format - if I am not wrong,
   we are able to parse json on client side. json is still readable for humans
   for old clients. On the client side we decide what and how we will display.
   This can be more generic than just for VERBOSE mode of ANALYZE, VACUUM or
   REINDEX.
   
   some like
   
   elog(INFO_CLIENT, '{ "cmdtag": "VACUUM", "state":"started", "progress_tab":
   "pg_stat_progress_vacuum", "table_name": "yyy", "schema_name":"xxx", ...)
   
   elog(INFO_CLIENT, '{"cmdatag": "VACUUM", "state":"finished",
   "pages_removed": 0, "pages_ ...
   
   I don't see some simple and nice solution at this moment. Maybe just using
   new line after INFO with details
   
   so results can looks like
   
   INFO:  vacuuming "postgres.pg_catalog.pg_class"
   INFO:  finished vacuuming "postgres.pg_catalog.pg_class": index scans: 0
   pages: 0 removed, 15 remain, 15 scanned (100.00% of total), 0 eagerly
   scanned
   tuples: 0 removed, 452 remain, 0 are dead but not yet removable
   removable cutoff: 701, which was 0 XIDs old when operation ended
   frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
   visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
   all-visible)
   index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
   identifiers removed
   avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
   buffer usage: 75 hits, 0 reads, 0 dirtied
   WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes,
   0 buffers full
   memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
   64.00 MB each)
   system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
   
   INFO:  vacuuming "postgres.pg_catalog.pg_proc"
   INFO:  finished vacuuming "postgres.pg_catalog.pg_proc": index scans: 0
   pages: 0 removed, 101 remain, 1 scanned (0.99% of total), 0 eagerly scanned
   tuples: 0 removed, 3437 remain, 0 are dead but not yet removable
   removable cutoff: 701, which was 0 XIDs old when operation ended
   new relfrozenxid: 701, which is 17 XIDs ahead of previous value
   frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
   visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
   all-visible)
   index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
   identifiers removed
   avg read rate: 4.534 MB/s, avg write rate: 1.133 MB/s
   buffer usage: 15 hits, 4 reads, 1 dirtied
   WAL usage: 1 records, 1 full page images, 5871 bytes, 5752 full page image
   bytes, 0 buffers full
   memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
   64.00 MB each)
   system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
   
   INFO:  vacuuming "postgres.pg_toast.pg_toast_1255"
   INFO:  finished vacuuming "postgres.pg_toast.pg_toast_1255": index scans: 0
   pages: 0 removed, 2 remain, 2 scanned (100.00% of total), 0 eagerly scanned
   tuples: 0 removed, 7 remain, 0 are dead but not yet removable
   removable cutoff: 701, which was 0 XIDs old when operation ended
   new relfrozenxid: 701, which is 17 XIDs ahead of previous value
   frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
   visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
   all-visible)
   index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
   identifiers removed
   avg read rate: 19.462 MB/s, avg write rate: 2.780 MB/s
   buffer usage: 36 hits, 7 reads, 1 dirtied
   WAL usage: 1 records, 1 full page images, 4255 bytes, 4136 full page image
   bytes, 0 buffers full
   memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
   64.00 MB each)
   system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
   
   This is small change and maybe it can be enough
   
   Regards
   
   Pavel
   
   
   > Thanks!
   >
   > Best, Jim
   >
   >
   

5. Re: PoC - psql - emphases line with table name in verbose output

   Jim Jones <jim.jones@uni-muenster.de> — 2026-04-24T10:41:30Z

   
   On 24/04/2026 08:56, Pavel Stehule wrote:
   > Please, can you check the functionality (only in english). I am
   > interested if this is just helpful and if it makes sense to continue in
   > this feature. Unfortunately, there are not too many possibilities about
   > possible formats, colors in terminals (that can work mostly everywhere). 
   
   
   I tested it yesterday with lc_messages = 'en_GB.UTF-8' and it worked
   just fine. And I also agree that it is currently hard to spot tables in
   the verbose output.
   
   
   > I don't think it is possible to implement this without  communication
   > protocol enhancement. And if we will do this, the next question is if we
   > cannot use this for some more complex information about the executed
   > command.
   > 
   > For example - I thought about the possibility of teaching psql to read
   > progress stat tables - so can be nice, if the server can send some
   > information to client - maybe pgstat_progres_update can send INFO 
   > 
   > like - "emphasize: nextinfo, pid: xxxx, progress table: pg_stat_vacuum,
   > commandtype: vacuum, ....
   > 
   > Maybe a different approach - instead of a plain text message, we can
   > send messages of this type in client side parsable format - if I am not
   > wrong, we are able to parse json on client side. json is still readable
   > for humans for old clients. On the client side we decide what and how we
   > will display. This can be more generic than just for VERBOSE mode of
   > ANALYZE, VACUUM or REINDEX.
   > 
   > some like
   > 
   > elog(INFO_CLIENT, '{ "cmdtag": "VACUUM", "state":"started",
   > "progress_tab": "pg_stat_progress_vacuum", "table_name": "yyy",
   > "schema_name":"xxx", ...)
   > 
   > elog(INFO_CLIENT, '{"cmdatag": "VACUUM", "state":"finished",
   > "pages_removed": 0, "pages_ ...
   
   
   I think it is feasible. The question is now is rather, is it worth the
   trouble just to highlight an output?
   
   I also don't see an easy way to implement this feature. It's virtually
   impossible to do that without some change in the server side. I took a
   look at the code and perhaps NoticeProcessor() at common.c would be
   better than pg_log_generic_v() for that, but still the problem of
   identifying the INFO messages remains.
   
   One option would be to create a new SQLSTATE and add it to the ereport
   calls, e.g. ERRCODE_VERBOSE_PROGRESS_INFO
   
   if (verbose)
   {
     if (vacrel->aggressive)
       ereport(INFO,
   	errcode(ERRCODE_VERBOSE_PROGRESS_INFO),
   	(errmsg("aggressively vacuuming \"%s.%s.%s\"",
   	  vacrel->dbname, vacrel->relnamespace,
             vacrel->relname)));
     else
       ereport(INFO,
   	errcode(ERRCODE_VERBOSE_PROGRESS_INFO),
   	(errmsg("vacuuming \"%s.%s.%s\"",
   	  vacrel->dbname, vacrel->relnamespace,
   	  vacrel->relname)));
   }
   
   Then in NoticeProcessor() check for it and act accordingly:
   
   const char *state = PQresultErrorField(result, PG_DIAG_SQLSTATE);
   if (state && strcmp(state, "00001") == 0)
    ... apply color / blank line / bold / whatever
   
   But it also looks like we'd be using SQLSTATES incorrectly. That would
   certainly require a bit more research.
   
   
   > I don't see some simple and nice solution at this moment. Maybe just
   > using new line after INFO with details
   
   
   It's much less invasive with more or less the same effect. However, the
   Error Message Style Guide explicitly says "Don't put any specific
   assumptions about formatting into the message texts" and "Don't end a
   message with a newline"[1]. So I'm afraid it's not an option either :\
   Of course, it could also disturb external tools that parse the verbose
   output, but that alone wouldn't be a blocker IMHO.
   
   Thanks!
   
   Best, Jim
   
   1 -
   https://www.postgresql.org/docs/current/error-style-guide.html#ERROR-STYLE-GUIDE-FORMATTING