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