Обсуждение: \gexec \watch

I just noticed that using \watch after \gexec does not do what I would
like it to do, namely re-execute the returned queries.  Instead, it
executes the returned queries once, then it just returns the queries.
That is:

=# select 'select now()' \gexec \watch
 2018-12-05 19:46:04.928995-03

 select now()

 select now()

 select now()

(This is under \pset tuples_only)

I think to be really useful, this combination ought to work like this
instead:

=# select 'select now()' \gexec \watch
 2018-12-05 19:47:51.045574-03

 2018-12-05 19:47:52.152132-03

 2018-12-05 19:47:53.099486-03

Is any psql hacker interested in fixing this?

-- 
Álvaro Herrera

On Wed, Dec 05, 2018 at 07:50:23PM -0300, Alvaro Herrera wrote:
> I just noticed that using \watch after \gexec does not do what I would
> like it to do, namely re-execute the returned queries.  Instead, it
> executes the returned queries once, then it just returns the queries.
> That is:
> 
> =# select 'select now()' \gexec \watch
>  2018-12-05 19:46:04.928995-03
> 
>  select now()
> 
>  select now()
> 
>  select now()
> 
> (This is under \pset tuples_only)
> 
> I think to be really useful, this combination ought to work like this
> instead:
> 
> =# select 'select now()' \gexec \watch
>  2018-12-05 19:47:51.045574-03
> 
>  2018-12-05 19:47:52.152132-03
> 
>  2018-12-05 19:47:53.099486-03
> 
> Is any psql hacker interested in fixing this?

As far as I can tell, what is happening currently is correct.

\g is a way to say "semicolon," in the sense that it looks backward to
the beginning of an SQL statement, sends it off to the backend, and
returns the results. Once those results are returned, its job is done,
and it releases control to the next psql event along with the memory
of the query it executed, so a following \g (or other "semicolon") can
do something new with it.

\gexec is a slightly different flavor of "semicolon." It starts off
doing what \g does, then before yielding control, it stores the
results and executes those results as a own query. At this point, psql
has forgotten about the results, even though it remembers the query.

\watch is yet another flavor of "semicolon." As with \g, it notices
the previous (or remembered) SQL had been written and executes it.
Unlike \gexec, it doesn't notice the result set. Instead, it repeats
that query in an infinite loop.

There's a bit of a philosophical issue here, or a mathematical one,
whichever way you want to put it.  Does it actually make sense to have
the behavior of one "semicolon" spill onto another?

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 2018-Dec-06, David Fetter wrote:

> There's a bit of a philosophical issue here, or a mathematical one,
> whichever way you want to put it.  Does it actually make sense to have
> the behavior of one "semicolon" spill onto another?

Honestly, I don't see the mathematicality in this.  It either works, or
it doesn't -- and from my POV right now it doesn't.  Are you saying we
need a \gexecwatch for this to work?

I can of course solve my problem with a simple python program, but psql
is so close ...

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

> On 6. Dec 2018, at 09:01, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> On 2018-Dec-06, David Fetter wrote:
>
>> There's a bit of a philosophical issue here, or a mathematical one,
>> whichever way you want to put it.  Does it actually make sense to have
>> the behavior of one "semicolon" spill onto another?
>
> Honestly, I don't see the mathematicality in this.  It either works, or
> it doesn't -- and from my POV right now it doesn't.  Are you saying we
> need a \gexecwatch for this to work?

I’ve been trying to do similar stuff with periodic execution of \gexec (changing the tablespace of all tables in the
givenone and retrying, since some of them could only get a lock on subsequent attempts)  and generally reverted to a
bashloop outside of psql, but having it built-in would be great. 

Perhaps a numeric argument to \gexec, i.e. \gexec 5 to re-execute the output of a query every 5 seconds?

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),
thenthe generation time column will stay constant through all \watch invocations. 

Cheers,
Oleksii

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

On 2018-Dec-06, Pavel Stehule wrote:

> čt 6. 12. 2018 v 12:26 odesílatel Oleksii Kliukin <alexk@hintbits.com>
> napsal:

> > 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.
> 
> It is better to introduce new command like \gexec_repeat with units like
> 5s, or how much 5x -

It is?  \gexec \watch is an elegant construct using two existing atoms
with well-defined semantics.  Can't say I see that in \gexec_repeat --
it seems non-orthogonal to me.

To Oleksii's question, I think if you want to repeat the first query
over and over, you'd use something like this:

select format('select now() as execution_time, %L as generation_time', now()) as query \gset
:query \watch

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

    Alvaro Herrera wrote:

> Honestly, I don't see the mathematicality in this.  It either works, or
> it doesn't -- and from my POV right now it doesn't.  Are you saying we
> need a \gexecwatch for this to work?
>
> I can of course solve my problem with a simple python program, but psql
> is so close ...

\watch reexecutes what's in the query buffer, and \gexec does not
write into the query buffer, so the desired piping does not happen
by design.

I think you could achieve more or less the result with a pre-gexec
hack like that:

postgres=# \pset tuples_only on
postgres=# select 'select now();' \g /tmp/file.sql
postgres=# \setenv EDITOR touch
postgres=# \e /tmp/file.sql
 2018-12-06 13:54:24.915752+01

postgres=# \watch
 2018-12-06 13:54:42.366559+01

 2018-12-06 13:54:44.368962+01

 2018-12-06 13:54:46.3713+01

....

The  "\setenv EDITOR touch" kludge is meant to force \e to
inject the contents of /tmp/file.sql into the query buffer.
It's needed because "\e file" actually checks whether the file has
been modified (per mtime) after $EDITOR returns, and discards it
if it hasn't.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On 2018-Dec-06, Daniel Verite wrote:

> I think you could achieve more or less the result with a pre-gexec
> hack like that:

Hmm, thanks.  AFAICS your hack reexecutes the initial query over and
over, instead of obtaining a fresh query each time.

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

    Alvaro Herrera wrote:

> Hmm, thanks.  AFAICS your hack reexecutes the initial query over and
> over, instead of obtaining a fresh query each time.

I see. That hack is about injecting something programmatically
into the query buffer, but it seems you'd need to do that in a loop.
And if psql had a loop construct you wouldn't need a hack in the
first place I guess!

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Hi Álvaro,

> On 6. Dec 2018, at 13:56, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> To Oleksii's question, I think if you want to repeat the first query
> over and over, you'd use something like this:
>
> select format('select now() as execution_time, %L as generation_time', now()) as query \gset
> :query \watch

Nice one, although it only works if the original query outputs a single row (because of \gset).
I do agree it’s not that useful to reuse the original query instead of executing it anew each time.

Cheers,
Oleksii

On Thu, Dec 06, 2018 at 05:01:26AM -0300, Alvaro Herrera wrote:
> On 2018-Dec-06, David Fetter wrote:
> 
> > There's a bit of a philosophical issue here, or a mathematical one,
> > whichever way you want to put it.  Does it actually make sense to have
> > the behavior of one "semicolon" spill onto another?
> 
> Honestly, I don't see the mathematicality in this.  It either works, or
> it doesn't -- and from my POV right now it doesn't.  Are you saying we
> need a \gexecwatch for this to work?

We could call it something a little shorter, but yes. The part you
trimmed away had descriptions of why the current behavior is correct.
We don't really have ways to compose \ operators. If we're going to
add composition to the psql language, we should think it through
carefully, not just glom it on for a single special case.

> I can of course solve my problem with a simple python program, but psql
> is so close ...

Agreed!

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate