Thread: VACUUM cannot be executed from multi-command string

VACUUM cannot be executed from multi-command string

From
"Sabin Coanda"
Date:
Hi there,

I upgrade "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)" to "PostgreSQL 8.3.5, compiled by Visual C++
build 1400".

I found the new Postgres version doesn't allowed to run a script file which 
contains multiline statements, which was working at the previous version. 
Trying to do it I get the error:
ERROR:  VACUUM cannot be executed from a function or multi-command string

This cause a bad performance running a script with more than 20000 
statements. I suppose this behavior is required to have transactional update 
on the whole script file, but this is a feature I didn't need in my 
scenario.

In the previous version I made some tests with and without vacuum, and I 
found the following results:

- without vacuum:
1000 rows in 1 min
2000 rows in 3 min
3000 rows in 7,5 min
... and the time rises geometrically, at 20000 getting hours

- with vacuum on every 20 statements
1000 rows in 30 sec
2000 rows in 70 sec
3100 rows in 120 sec
... and it rises lineary with the script length, at 20000 getting about 10 
minutes.

Can anybody help me to find a solution with the new vacuum constraint, 
without loosing the performance ?

TIA,
Sabin





Re: VACUUM cannot be executed from multi-command string

From
Richard Huxton
Date:
Sabin Coanda wrote:
> Hi there,
> 
> I upgrade "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.4.2 (mingw-special)" to "PostgreSQL 8.3.5, compiled by Visual C++
> build 1400".
> 
> I found the new Postgres version doesn't allowed to run a script file which 
> contains multiline statements, which was working at the previous version. 
> Trying to do it I get the error:
> ERROR:  VACUUM cannot be executed from a function or multi-command string

> Can anybody help me to find a solution with the new vacuum constraint, 
> without loosing the performance ?

Not without seeing the bit of the script causing problems.

How/why are you executing vacuum from your function / multi-command
string. I don't see why you have a multi-command string in a script.

--  Richard Huxton Archonet Ltd