Re: [RFC] Add an until-0 loop in psql

On Mon, Apr 30, 2018 at 7:05 AM Pierre Ducroquet <
pierre(dot)ducroquet(at)people-doc(dot)com> wrote:

> On Monday, April 30, 2018 1:01:25 PM CEST Daniel Verite wrote:
> > Corey Huinker wrote:
> > > As of v11, DO blocks can do transactions. I think this will meet your
> > > needs.
> > They do support COMMIT and ROLLBACK in the current
> > development tree, but not VACUUM as in Pierre's example.
> >
> > postgres=# \echo :SERVER_VERSION_NAME
> > 11devel
> >
> > postgres=# do ' begin vacuum; end ';
> > ERROR: VACUUM cannot be executed from a function
> > CONTEXT: SQL statement "vacuum"
> > PL/pgSQL function inline_code_block line 1 at SQL statement
> >
> >
> > Best regards,
>
> Indeed, vacuum is going to be the biggest offender here, sadly.
> One could work around this of course (on top of my head, using notify to
> wake-
> up another client that would launch the required vacuums…)
> Being able to do transactions in DO blocks is a great new feature of v11 I
> was
> not aware of. But psql saw the addition of \if recently, so why not having
> loops in there too ? (Something better than this hack of course, it was
> just a
> 10 minutes hack-sprint for a demo)
>
> Regards
>
> Pierre
>

Bummer about vacuum.

If you dig into the very long discussion about \if (which, incidentally,
started off as a 20-line command patch called \quit-if, so don't discount
that your idea could take off), you'll see some of the problems with
looping discussed, mostly about the issues I already alluded to (no concept
of reading backwards on STDIN, scoping outside the current "file", ability
of psql vars to contain executable \commands), you'll have a pretty good
grasp of the places where psql would need changes.

In the mean time, if you believe the table won't get much larger during the
operation, you could use \gexec as a finite loop iterator

SELECT count(*)::bigint / 1000 FROM big_table as num_iters
\gset
SELECT
'BEGIN',
'DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad
= true LIMIT 1000)',
'VACUUM big_table',
'COMMIT'
from generate_series(1,:num_iters) g
\gexec

If the number of rows increases, then your finite loop will fall short, and
if something else deletes a bunch of rows, your loop will spin it's wheels
a few times at the end, but it would do most of what you want.