pgcli: \watch command does not work

I guess the question is more “Is \watched supposed to mimic the psql \watch behaviour exactly (as it seems the other pgcli \xxxx functions do?”

If pgcli is just trying to be feature similar, it’s certainly there already, just fixing \? would suffice.

If pgcli is trying to act like a better psql, it’s definitely not correct yet.

Fixed in pgspecial 1.8.0.

@ivyleavedtoadflax Now I got it. This happens when you don’t have a space after the semicolon:

select * from users where id >12;\watch 1;

This will work:

select * from users where id >12; \watch 1;

Yes, it’s a valid bug, because both versions should work. Thanks for the report.

I also just ran into this not working with a multiline query - e.g.

      <bunch of sql...>
      ORDER BY a.query_start; \watch 15;

<result>
SELECT 3
syntax error at or near "\"
LINE 1: \watch 15

If it wasn’t required to have \watch on the same line as the query I’d just use that and move on, but because it is necessary I figured I’d leave a comment. I’m using version 1.10.3 on mac.

Oh, I see, it’s just it’s behaviour is different from psql:

postgres> select * from pg_stat_progress_vacuum;
+-------+---------+------------+-----------+-------------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------+
| pid   | datid   | datname    | relid     | phase             | heap_blks_total   | heap_blks_scanned   | heap_blks_vacuumed   | index_vacuum_count   | max_dead_tuples   | num_dead_tuples   |
|-------+---------+------------+-----------+-------------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------|
| 7364  | 16412   | webarchive | 38269410  | vacuuming heap    | 9302840           | 9302840             | 5261471              | 1                    | 178956970         | 25702032          |
| 5029  | 16412   | webarchive | 38269400  | scanning heap     | 12280964          | 2967063             | 0                    | 0                    | 178956970         | 16943994          |
| 11173 | 16411   | ttrss      | 230002899 | vacuuming indexes | 21                | 21                  | 0                    | 0                    | 6111              | 638               |
+-------+---------+------------+-----------+-------------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------+
SELECT 3
Time: 0.005s
postgres> \watch 1
syntax error at or near "\"
LINE 1: \watch 1
        ^

Time: 0.002s
postgres> select * from pg_stat_progress_vacuum; \watch
+-------+---------+------------+-----------+---------------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------+
| pid   | datid   | datname    | relid     | phase               | heap_blks_total   | heap_blks_scanned   | heap_blks_vacuumed   | index_vacuum_count   | max_dead_tuples   | num_dead_tuples   |
|-------+---------+------------+-----------+---------------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------|
| 7364  | 16412   | webarchive | 38269410  | vacuuming heap      | 9302840           | 9302840             | 5262691              | 1                    | 178956970         | 25702032          |
| 5029  | 16412   | webarchive | 38269400  | scanning heap       | 12280964          | 2973478             | 0                    | 0                    | 178956970         | 16947189          |
| 11173 | 16411   | ttrss      | 230002905 | cleaning up indexes | 3547              | 3547                | 3547                 | 0                    | 1032177           | 0                 |
+-------+---------+------------+-----------+---------------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------+
SELECT 3
syntax error at or near "\"
LINE 1: \watch
        ^

Time: 0.007s
postgres> select * from pg_stat_progress_vacuum; \watch 1
+-------+---------+------------+----------+----------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------+
| pid   | datid   | datname    | relid    | phase          | heap_blks_total   | heap_blks_scanned   | heap_blks_vacuumed   | index_vacuum_count   | max_dead_tuples   | num_dead_tuples   |
|-------+---------+------------+----------+----------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------|
| 7364  | 16412   | webarchive | 38269410 | vacuuming heap | 9302840           | 9302840             | 5263696              | 1                    | 178956970         | 25702032          |
| 5029  | 16412   | webarchive | 38269400 | scanning heap  | 12280964          | 2977029             | 0                    | 0                    | 178956970         | 16953488          |
+-------+---------+------------+----------+----------------+-------------------+---------------------+----------------------+----------------------+-------------------+-------------------+
SELECT 2
Time: 0.005s
Waiting for 1 seconds before repeating

So, the issue is then:

• \watch as a single statement does not work (in psql, \watch with no SQL in the command line assumes you want to watch the previously executed query).
• \watch with no argument does not work (psql defaults to 2 seconds if the interval is not specified).
• In both cases, the (mis)use of the \watch command doesn’t lead to any sort of helpful output.
• \? doesn’t list the mnemonics of \watch at all, so you don’t have anything to base how you are supposed to use it off of.

For reference, here is how it works in psql:

durr@postgres-server /e/a/apt.conf.d> sudo -H -u postgres psql
psql (9.6.6)
Type "help" for help.

postgres=# select * from pg_stat_progress_vacuum;
 pid  | datid |  datname   |  relid   |     phase      | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
------+-------+------------+----------+----------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 7364 | 16412 | webarchive | 38269410 | vacuuming heap |         9302840 |           9302840 |            5321863 |                  1 |       178956970 |        25702032
 5029 | 16412 | webarchive | 38269400 | scanning heap  |        12280964 |           3141117 |                  0 |                  0 |       178956970 |        17191925
(2 rows)

postgres=# \watch
                                                                    Sun Jan 14 22:49:41 2018 (every 2s)

 pid  | datid |  datname   |  relid   |     phase      | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
------+-------+------------+----------+----------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 7364 | 16412 | webarchive | 38269410 | vacuuming heap |         9302840 |           9302840 |            5322342 |                  1 |       178956970 |        25702032
 5029 | 16412 | webarchive | 38269400 | scanning heap  |        12280964 |           3142117 |                  0 |                  0 |       178956970 |        17195062
(2 rows)

                                                                    Sun Jan 14 22:49:43 2018 (every 2s)

 pid  | datid |  datname   |  relid   |     phase      | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
------+-------+------------+----------+----------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 7364 | 16412 | webarchive | 38269410 | vacuuming heap |         9302840 |           9302840 |            5322597 |                  1 |       178956970 |        25702032
 5029 | 16412 | webarchive | 38269400 | scanning heap  |        12280964 |           3142635 |                  0 |                  0 |       178956970 |        17196703
(2 rows)

Personally, I have never used watch as part of the query, but rather I futz with the query until it’s returning what I want, and then watch it, so I initially thought pgcli didn’t support \watch at all.