The problem with client code processing a function is that unless you
are using threads (my client application is not in a multi-threaded
environment), the client has to wait for the server to return from the
end of the function. I don't want the client to wait and the result
doesn't affect the user at all, so there is no reason why he should wait.
Kenneth Downs wrote:
> Rafal Pietrak wrote:
>
>> A plain INSERT of batch takes 5-10minutes on desktop postgresql (800MHz
>> machine, ATA disks). When I attach trigger (*Very* simple funciton) to
>> update the accounts, the INSERT take hours (2-4). But when I make just
>> one single update of all accounts at the end of the batch insert, it
>> takes 20-30min.
>>
>>
>>
> Why not have the INSERT go to an "inbox" table, a table whose only job
> is to receive the data for future processing.
>
> Your client code should mark all rows with a batch number as they go
> in. Then when the batch is loaded, simply invoke a stored procedure to
> process them. Pass the stored procedure the batch number.
>
> IOW, have your "background trigger" be a stored procedure that is
> invoked by the client, instead of trying to get the server to do it.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match