Re: \gexec \watch

Hi Oleksii

On 2018-Dec-06, Oleksii Kliukin wrote:

> The other question is whether such a command would execute the
> original query every time watch is invoked. Consider, e.g. the
> following one:
> 
> select format('select now() as execution_time, %L as generation_time', now()) \gexec
> execution_time  | 2018-12-06 12:15:24.136086+01
> generation_time | 2018-12-06 12:15:24.13577+01
> 
> If we make \gexec + \watch combination re-execute only the output of
> the original query (without the query itself), then the generation
> time column will stay constant through all \watch invocations.

Hmm, I think reusing the first query is not terribly useful.  My
example (thus far) is something like this

select format('select tableoid::regclass, * from %s where ctid = ''(%s,%s)''', relation::regclass, page, tuple) 
  from pg_locks 
 where locktype = 'tuple' and 
       pid in (select pid from pg_locks where granted = false and locktype = 'transactionid') and
       database = (select oid from pg_database where datname = current_database())  
\gexec [\watch]

which is supposed to report the current tuple-level conflicts (two
updates concurrently in the same tuple, etc).  I want to get the
PK/replica identity[*] of all tuples that some backend is currently
waiting for; if the query remains constant, it will return me the
identity of the tuple located in the CTID of the tuples that conflicted
in the first iteration, which is completely useless.

[*] Right now it just reports all columns rather than PK ... I intend to
add that bit next.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services