Обсуждение: pg_cron for vacuum - dynamic table set
Hi, I am trying to schedule pg_cron to generate a set of commands and then run them. E.g
Since vacuum doesn't work inside a transaction block
Any guidance to make it work or an alternative?
select 'vacuum freeze '||table_name from inventory_for_vacuum \gexec . This works well at command line, but when scheduled in pg_cron, it fails with syntax error ERROR: syntax error at or near "\" .Since vacuum doesn't work inside a transaction block
ERROR: VACUUM cannot run inside a transaction block, I cannot use a function directly to run over a loop.Any guidance to make it work or an alternative?
On 2/3/22 1:29 PM, saket bansal wrote: > Hi, I am trying to schedule pg_cron to generate a set of commands and > then run them. E.g |select 'vacuum freeze '||table_name from > inventory_for_vacuum \gexec| . This works well at command line, but when > scheduled in pg_cron, it fails with syntax error |ERROR: syntax error at > or near "\"| . \gexec is a psql metacommand and will only run in that client. I'm guessing that is not the case in the pg_cron task? > Since vacuum doesn't work inside a transaction block |ERROR: VACUUM > cannot run inside a transaction block|, I cannot use a function directly > to run over a loop. > Any guidance to make it work or an alternative? > > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Feb 3, 2022 at 2:29 PM saket bansal <saket.tcs@gmail.com> wrote:
Hi, I am trying to schedule pg_cron to generate a set of commands and then run them. E.gselect 'vacuum freeze '||table_name from inventory_for_vacuum \gexec. This works well at command line, but when scheduled in pg_cron, it fails with syntax errorERROR: syntax error at or near "\".
Right, because you don't get to use psql meta-commands if you are not using psql.
Since vacuum doesn't work inside a transaction blockERROR: VACUUM cannot run inside a transaction block, I cannot use a function directly to run over a loop.
Any guidance to make it work or an alternative?
Have a pg_cron job that dynamically creates other pg_cron jobs that only run once?
David J.
Can't you use a do script to construct and execute the statement?
On 2/3/22 3:29 PM, saket bansal wrote:
What about running the command from regular cron?
Hi, I am trying to schedule pg_cron to generate a set of commands and then run them. E.gselect 'vacuum freeze '||table_name from inventory_for_vacuum \gexec. This works well at command line, but when scheduled in pg_cron, it fails with syntax errorERROR: syntax error at or near "\".
Since vacuum doesn't work inside a transaction blockERROR: VACUUM cannot run inside a transaction block, I cannot use a function directly to run over a loop.
Any guidance to make it work or an alternative?
What about running the command from regular cron?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.