Обсуждение: \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
čt 6. 12. 2018 v 12:26 odesílatel Oleksii Kliukin <alexk@hintbits.com> napsal:
> 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 given one and retrying, since some of them could only get a lock on subsequent attempts) and generally reverted to a bash loop 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?
looks not intuitive :)
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 -
Regards
Pavel
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
čt 6. 12. 2018 v 13:56 odesílatel Alvaro Herrera <alvherre@2ndquadrant.com> napsal:
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.
Maybe I am wrong, but currently is not possible to compose \ commands.
So you should to introduce new pattern. There is enough long command buffer to implement it.
Regards
Pavel
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