Re: Re: [webmaster] How to commit/vacuum a batch of delete statements in a postgresql function

On 11/26/2015 08:16 PM, Nava Jyothi wrote:
> /Hi PostGresSQL Team
>
> Could you advise us on how to commit records in a batch when one is
> doing batch wise deletion/. I came across
> http://www.postgresql.org/message-id/60644bymua.fsf@dba2.int.libertyrms.com,

That post references untested pseudo code.

> but use of vacuum is giving an error as follows from function.

> /********** Error **********
>
> ERROR: VACUUM cannot be executed from a function or multi-command string
> SQL state: 25001

Well the underling reason is this:

http://www.postgresql.org/docs/9.4/interactive/sql-vacuum.html

"VACUUM cannot be executed inside a transaction block."

>
> /My stored procedure is of the format:/
> /
>
> /Loop
> exit when <some condition> DELETE from incoming_table where xyz='123';
> VACUUM incoming_table; -- or commit; End Loop;/

Not sure where the above is coming from, but assuming it is in a
Postgres procedural language function, the function will being running
in a transaction block. This is why you are seeing the error. The
solution would be too have a script that calls the batch delete
function, then calls VACUUM. So something like(again untested pseudo code);

SELECT batch_delete_fnc();
VACUUM affected_table;

Note NO transaction block around above.

>
>
> I thank you for the help.
>
> -Nava

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com