update by one transaction

Поиск
Список
Период
Сортировка
От Lendvary Gyorgy
Тема update by one transaction
Дата
Msg-id 3580E719.3222E4C3@prolan.hu
обсуждение исходный текст
Ответы Re: [HACKERS] update by one transaction  (dg@illustra.com (David Gould))
Список pgsql-hackers
Hi,

I have a table for instance boci (row_number int, col2 float, col3
float). The boci table has about 10000 rows.
I want to update col3 column of the table in every row with different
values. Desired values are in an array has 10000 elements. I want to be
the value of the col3 column in the first row equal to the first element
of the array and so on.
I've done it with a 'for' cycle, but it is very slow. I want to do
theese updates with one transaction.

Here is a little piece of my program:

PQexec(conn, "BEGIN");
for (i=0; i<10000; i++)
{
    sprintf(buff, "UPDATE boci SET col3 = %f WHERE row_number=%d",
array[i], i);
    PQexec(conn, buff);
}
PQexec(conn, "END");

I can't solve this problem with COPY command becouse I have to update
col3 column in every minute, but I don't want to change row_number and
col2 columns. My problem is the updating and not the creation of the
table. Creation is fast enough.
This program is very, very slow. Is there any way making this program
much faster (for instance with CURSOR or 'block write' or something
else)? Please write me a little program that describes your ideas!

Thanks for your help in advance!

Please help me!!!

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Vadim Mikheev
Дата:
Сообщение: Re: AW: [HACKERS] now 6.4
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: [HACKERS] now 6.4