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.klaver@aklaver.com
