VACUUM cannot be executed from multi-command string - Mailing list pgsql-sql

From Sabin Coanda
Subject VACUUM cannot be executed from multi-command string
Date
Msg-id gg1b88$1oco$1@news.hub.org
Whole thread Raw
Responses Re: VACUUM cannot be executed from multi-command string
List pgsql-sql
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





pgsql-sql by date:

Previous
From: "Ryan Wells"
Date:
Subject: Re: Left Join Question
Next
From: Richard Huxton
Date:
Subject: Re: VACUUM cannot be executed from multi-command string