Re: in Pl/PgSQL, do commit every 5000 records

Hi Merlin,

>> >> In general, if you have the choice between looping over a large result
>> >> in a stored procedure (or, even worse, in a client app) and letting the
>> >> backend do the looping, then letting the backend handle it is nearly
>> >> always
>> >> faster.
>>
>>
>
>There are different reasons why a large query might not always be the
>best approach. Unfortunately it is the only approach on the server
>side.
>
>Large queries tend to become less and less practical when the database
>becomes really big. Just as a 'for example', it would be nice to be
>able to do part of a large complex job, stop it, and continue it again
>later.
>
>
Also combined the suggestions from Florian,
>> use the exception support in plpgsql to prevent the whole
transaction from rolling back in case of an error.
>> Only the statements _inside_ the block where you caught the error
would roll back.

I will try separate my huge data computation into several pieces
something like:

declare
...
begin
...
-- step1

BEGIN
...
insert into (select ... ... from ... where ... )

EXCEPTION WHEN ...... THEN
-- do nothing
END;

-- step2

BEGIN
...
UPDATE tableA from ... WHERE ... ;

EXCEPTION WHEN ...... THEN
-- do nothing
END;

...
...
end;

If I understood correctly, "begin ... exception when .. then ... end"
can work the same way as commit. In another way, if commands in the
sub-block (such as step1) run successfully, data in this part (step1) is
committed. Then step2, step3... stepN that are all under "begin..
exception.. end" sub-blocks will be run and "committed" one by one.

>stored procedures (not functions) are suppoesed to give you this power
>and allow you to do things which are non-transactional like vacuum.
>
>
"To define a procedure, i.e. a function that returns nothing, just
specify RETURNS VOID. "
Copied from
http://www.postgresql.org/docs/8.1/interactive/sql-createfunction.html

So, a stored procedure is "a void function", right?

Thanks,
Ying