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