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....@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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
