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
