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

> Florian G. Pflug wrote:
> < snipped code of stored procedure >
>
>>>
>>> Are you aware of the "insert into <table> (<field1>, ..., <fieldn>)
>>> select <val1>, .., <valn> from ...."
>>> command? It'd be much faster to use that it it's possible...
>>>
>>> greetings, Florian Pflug
>>
>>
>> It did faster. Thank you Florian. Could you hint me why "insert into
>> .. select " is faster than a cursor transaction please?
>
> Well, you're avoiding a lot of overhead. "insert into ... select from .."
> is just one sql-statement. Of course, postgres internally does
> something similar to your stored procedure, but it's all compiled
> C code now (instead of interpreted plpgsql). Additionally, postgres
> might be able to optimize this more than you could from plpgsql, because
> you're restricted to the api that is exposed to plpgsql, while the
> backend-code
> might be able to "pull a few more tricks".
>
> 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.


The information are very helpful! Thank you again Florian.

If now, I have a series of queries to be run:

1. "insert into t1... (select .. from ...left join ... .. where ....) "
2. "insert into t2 ... the same sub-query as in 1 "
3. "update t3 set ... from ( the same sub-query as in 1) AS X where
t3.pk = X.pk "
4. "update t4 set ... from ( the same sub-query as in 1) AS X where
t4.pk = X.pk"

. the subquery (select .. from ...left join ... .. where ....) is two
big tables doing left join

Will there be a better way between

a. put all there 4 queries into one function
    in perl or java, just call this function

b. in perl / java, write and run the 4 queries independently

The pl/pgsql function does not allow commit. So, in the function , if
any step went wrong, all 4 steps rollback. While in java, after every
query, I can do commit. May java speed up all four updates?


- Ying