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.
