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

Поиск
Список
Период
Сортировка
От Emi Lu
Тема Re: in Pl/PgSQL, do commit every 5000 records
Дата
Msg-id 4411A3EC.3080309@encs.concordia.ca
обсуждение исходный текст
Ответ на Re: in Pl/PgSQL, do commit every 5000 records  (Bruno Wolff III <bruno@wolff.to>)
Ответы Re: in Pl/PgSQL, do commit every 5000 records  (Emi Lu <emilu@encs.concordia.ca>)
Re: in Pl/PgSQL, do commit every 5000 records  ("Florian G. Pflug" <fgp@phlo.org>)
Список pgsql-general
Hi Bruno,

>You can't do commits inside of a function.
>

The example I have is:

CREATE OR REPLACE function test() returns boolean AS $$
DECLARE
... ...
   counter                INTEGER := 0;
BEGIN
... ...
   query_value := ' .....' ;
   OPEN curs1 FOR EXECUTE query_value;
   LOOP
      FETCH curs1 INTO studid;
      EXIT WHEN NOT FOUND;

      query_value := ' INSERT INTO ... ...';

      EXECUTE query_value  ;

      counter := counter + 1 ;
      IF counter%5000 = 0 THEN
         counter := 0;
         COMMIT;
      END IF;

   END LOOP;


   CLOSE curs1;
...
END;

... ...

The above function works ok.

"can't do commits inside of a function " , do you mean although the
function complied ok and run successfully, but it did not really commit
insertion actions at every 5000 records?

>I think you are misremembering advice about not do inserts with a transaction per row which will have
>a lot of overhead for all of the commits.
>



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

Предыдущее
От: Bruno Wolff III
Дата:
Сообщение: Re: in Pl/PgSQL, do commit every 5000 records
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: About updates