Начальная страница › Форумы › PostgreSQL › Установка и настройка PostgreSQL

Функция работает долго(больше 3 часов)

Изображение Izver
Posted Июнь 15th, 2010 by Izver
in

Есть функция обновляющая данные из одной таблицы в другую,диапазоне дат.
p222i - исходник
p222 - таблица которую надо обновить.
в

CREATE OR REPLACE FUNCTION "public"."updatep222v3" () RETURNS text AS
$body$
DECLARE
cur_rec_ record;
cur_rec2_ record;
cur_rec3_ record;
c_datn text:='';
c_datk text:='';
BEGIN
--определение диапазона дат 
SELECT datd INTO  c_datn  FROM p222i  GROUP BY datd ORDER BY datd LIMIT 1;--
SELECT datd INTO  c_datk  FROM p222i  GROUP BY datd ORDER BY datd DESC LIMIT 1;
--удаляем данные из p222, которых нет в p222i
CREATE TEMPORARY TABLE PND AS 
SELECT
 p222.datd,p222.datv,p222.kolr,p222.kpot, p222.kvod,p222.kvpr,p222.moroz,p222.na,
 p222.nd,p222.nkom,p222.nmas,p222.op,p222.pf,p222.pk,p222.ptp,p222.serd,p222.sklad,
p222.tip_nakl,sum(p222.kol) AS p22kol,sum(p222.sumo)  AS p22sumo,
sum(p222.sum_nds) AS p22sum_nds, sum(p222i.kol) AS p22ikol, sum(p222i.sumo) AS p22isumo,
sum(p222i.sum_nds) AS p22isum_nds 
 FROM
  p222
  LEFT JOIN public.p222i ON (public.p222.datd = public.p222i.datd)
  AND (public.p222.datv = public.p222i.datv)
  AND (public.p222.kolr = public.p222i.kolr)
  AND (public.p222.kpot = public.p222i.kpot)
  AND (public.p222.kvod = public.p222i.kvod)
  AND (public.p222.kvpr = public.p222i.kvpr)
  AND (public.p222.moroz = public.p222i.moroz)
  AND (public.p222.na = public.p222i.na)
  AND (public.p222.nd = public.p222i.nd)
  AND (public.p222.nkom = public.p222i.nkom)
  AND (public.p222.nmas = public.p222i.nmas)
  AND (public.p222.op = public.p222i.op)
  AND (public.p222.pf = public.p222i.pf)
  AND (public.p222.pk = public.p222i.pk)
  AND (public.p222.ptp = public.p222i.ptp)
  AND (public.p222.serd = public.p222i.serd)
  AND (public.p222.sklad = public.p222i.sklad)
  AND (public.p222.tip_nakl = public.p222i.tip_nakl) 
  WHERE p222.datd>=c_datn AND p222.datd<=c_datk
  GROUP BY p222.datd,p222.datv,p222.kolr,p222.kpot,p222.kvod,p222.kvpr,p222.moroz,p222.na,
 p222.nd,p222.nkom,p222.nmas,p222.op,p222.pf,p222.pk,p222.ptp,p222.serd,p222.sklad,
 p222.tip_nakl;
 
FOR cur_rec_ IN SELECT * FROM PND  LOOP
IF cur_rec_.p22ikol IS NULL  OR cur_rec_.p22isumo IS NULL  OR cur_rec_.p22isum_nds IS NULL
 then 
 DELETE FROM p222 WHERE p222.datd=cur_rec_.datd AND p222.datv=cur_rec_.datv AND
    p222.kolr=cur_rec_.kolr AND p222.kpot=cur_rec_.kpot AND p222.kvod=cur_rec_.kvod AND 
    p222.kvpr=cur_rec_.kvpr AND p222.moroz=cur_rec_.moroz AND p222.na=cur_rec_.na AND 
    p222.nd=cur_rec_.nd AND p222.nkom=cur_rec_.nkom AND p222.nmas=cur_rec_.nmas AND 
    p222.op=cur_rec_.op AND p222.pf=cur_rec_.pf AND p222.pk=cur_rec_.pk AND 
    p222.ptp=cur_rec_.ptp AND p222.serd=cur_rec_.serd AND p222.sklad=cur_rec_.sklad AND 
    p222.tip_nakl=cur_rec_.tip_nakl;
end IF;
end loop;
 
---если параметры  kol,sumo,sum_nds из p222 для определенного nd не совпадают с параметрами p222i,
удаляем строку в p222 и ставим из  p222i
 CREATE TEMPORARY TABLE PNDv2 AS 
SELECT
 p222.datd,p222.datv,p222.kolr,p222.kpot, p222.kvod,p222.kvpr,p222.moroz,p222.na,
 p222.nd,p222.nkom,p222.nmas,p222.op,p222.pf,p222.pk,p222.ptp,p222.serd,p222.sklad,
p222.tip_nakl,sum(p222.kol) AS p22kol,sum(p222.sumo)  AS p22sumo,
sum(p222.sum_nds) AS p22sum_nds, sum(p222i.kol) AS p22ikol, sum(p222i.sumo) AS p22isumo,
sum(p222i.sum_nds) AS p22isum_nds 
 FROM
  p222
  LEFT JOIN public.p222i ON (public.p222.datd = public.p222i.datd)
  AND (public.p222.datv = public.p222i.datv)
  AND (public.p222.kolr = public.p222i.kolr)
  AND (public.p222.kpot = public.p222i.kpot)
  AND (public.p222.kvod = public.p222i.kvod)
  AND (public.p222.kvpr = public.p222i.kvpr)
  AND (public.p222.moroz = public.p222i.moroz)
  AND (public.p222.na = public.p222i.na)
  AND (public.p222.nd = public.p222i.nd)
  AND (public.p222.nkom = public.p222i.nkom)
  AND (public.p222.nmas = public.p222i.nmas)
  AND (public.p222.op = public.p222i.op)
  AND (public.p222.pf = public.p222i.pf)
  AND (public.p222.pk = public.p222i.pk)
  AND (public.p222.ptp = public.p222i.ptp)
  AND (public.p222.serd = public.p222i.serd)
  AND (public.p222.sklad = public.p222i.sklad)
  AND (public.p222.tip_nakl = public.p222i.tip_nakl) 
  WHERE p222.datd>=c_datn AND p222.datd<=c_datk
  GROUP BY p222.datd,p222.datv,p222.kolr,p222.kpot,p222.kvod,p222.kvpr,p222.moroz,p222.na,
  p222.nd,p222.nkom,p222.nmas,p222.op,p222.pf,p222.pk,p222.ptp,p222.serd,p222.sklad,
  p222.tip_nakl;
 
 FOR cur_rec2_ IN SELECT * FROM PNDv2  LOOP
   IF cur_rec2_.p22kol!=cur_rec2_.p22ikol OR cur_rec2_.p22sumo!=cur_rec2_.p22isumo 
   OR cur_rec2_.p22sum_nds!=cur_rec2_.p22isum_nds  then 
   DELETE FROM p222 WHERE p222.datd=cur_rec2_.datd AND p222.datv=cur_rec2_.datv AND
   p222.kolr=cur_rec2_.kolr AND p222.kpot=cur_rec2_.kpot AND p222.kvod=cur_rec2_.kvod AND 
   p222.kvpr=cur_rec2_.kvpr AND p222.moroz=cur_rec2_.moroz AND p222.na=cur_rec2_.na AND 
   p222.nd=cur_rec2_.nd AND p222.nkom=cur_rec2_.nkom AND p222.nmas=cur_rec2_.nmas AND 
   p222.op=cur_rec2_.op AND p222.pf=cur_rec2_.pf AND p222.pk=cur_rec2_.pk AND 
   p222.ptp=cur_rec2_.ptp AND p222.serd=cur_rec2_.serd AND p222.sklad=cur_rec2_.sklad AND 
   p222.tip_nakl=cur_rec2_.tip_nakl;
   INSERT INTO p222(datd,datv,kol,kolr,kpot,kvod,kvpr,moroz,na,nd,nkom,nmas,op,pf,pk,ptp,serd,
    sklad,sumo,sum_nds,tip_nakl) 
    VALUES (cur_rec2_.datd,cur_rec2_.datv,cur_rec2_.p22ikol,cur_rec2_.kolr,cur_rec2_.kpot,
    cur_rec2_.kvod,cur_rec2_.kvpr,cur_rec2_.moroz,cur_rec2_.na,cur_rec2_.nd,cur_rec2_.nkom,
    cur_rec2_.nmas,cur_rec2_.op,cur_rec2_.pf,cur_rec2_.pk,cur_rec2_.ptp,cur_rec2_.serd,
    cur_rec2_.sklad,cur_rec2_.p22isumo,cur_rec2_.p22isum_nds,cur_rec2_.tip_nakl);   
   end IF;
 end loop;
 
--добавляем новые данные  из  p222i в р222 
CREATE TEMPORARY TABLE PIND AS 
SELECT p222i.datd,p222i.datv,p222i.kolr,p222i.kpot,p222i.kvod,p222i.kvpr,p222i.moroz,
  p222i.na,p222i.nd,p222i.nkom,p222i.nmas,p222i.op,p222i.pf,p222i.pk,p222i.ptp,p222i.serd,
  p222i.sklad,p222i.tip_nakl,sum(p222i.kol) AS p22kol,sum(p222i.sumo)  AS p22sumo,
sum(p222i.sum_nds) AS p22sum_nds,sum(p222.kol) AS p22ikol, sum(p222.sumo) AS p22isumo,
sum(p222.sum_nds) AS p22isum_nds 
FROM
  public.p222i
  LEFT OUTER JOIN public.p222 ON (public.p222i.datd = public.p222.datd)
  AND (public.p222i.datv = public.p222.datv)
  AND (public.p222i.kolr = public.p222.kolr)
  AND (public.p222i.kpot = public.p222.kpot)
  AND (public.p222i.kvod = public.p222.kvod)
  AND (public.p222i.kvpr = public.p222.kvpr)
  AND (public.p222i.moroz = public.p222.moroz)
  AND (public.p222i.na = public.p222.na)
  AND (public.p222i.nd = public.p222.nd)
  AND (public.p222i.nkom = public.p222.nkom)
  AND (public.p222i.nmas = public.p222.nmas)
  AND (public.p222i.op = public.p222.op)
  AND (public.p222i.pf = public.p222.pf)
  AND (public.p222i.pk = public.p222.pk)
  AND (public.p222i.ptp = public.p222.ptp)
  AND (public.p222i.serd = public.p222.serd)
  AND (public.p222i.sklad = public.p222.sklad)
  AND (public.p222i.tip_nakl = public.p222.tip_nakl)
WHERE
  p222i.datd>=c_datn AND p222i.datd<=c_datk
GROUP BY 
  p222i.datd,p222i.datv,p222i.kolr,p222i.kpot,p222i.kvod,p222i.kvpr,p222i.moroz,p222i.na,
  p222i.nd,p222i.nkom,p222i.nmas,p222i.op,p222i.pf,p222i.pk,p222i.ptp,p222i.serd,p222i.sklad,
  p222i.tip_nakl;
 
 FOR cur_rec3_ IN SELECT * FROM PIND  LOOP
  IF cur_rec3_.p22ikol IS NULL  OR cur_rec3_.p22isumo IS NULL  OR cur_rec3_.p22isum_nds IS NULL
  then  
   INSERT INTO p222(datd,datv,kol,kolr,kpot,kvod,kvpr,moroz,na,nd,nkom,nmas,op,pf,pk,ptp,serd,
    sklad,sumo,sum_nds,tip_nakl) 
    VALUES (cur_rec3_.datd,cur_rec3_.datv,cur_rec3_.p22kol,cur_rec3_.kolr,cur_rec3_.kpot,
    cur_rec3_.kvod,cur_rec3_.kvpr,cur_rec3_.moroz,cur_rec3_.na,cur_rec3_.nd,cur_rec3_.nkom,
    cur_rec3_.nmas,cur_rec3_.op,cur_rec3_.pf,cur_rec3_.pk,cur_rec3_.ptp,cur_rec3_.serd,
    cur_rec3_.sklad,cur_rec3_.p22sumo,cur_rec3_.p22sum_nds,cur_rec3_.tip_nakl);   
  end IF;
 end loop;
RETURN NULL; 
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

запускаю функицю
набираю

SELECT * FROM pg_stat_activity   WHERE datname='base' AND client_addr='ip'  AND current_query!='<IDLE>' ORDER BY client_addr

отображается обращение к базе
в колонке current_query отображается
"select updatep222v3()"
через 3 часа снова ваполняю запрос()
SELECT * FROM pg_stat_activity   WHERE datname='base' AND client_addr='ip'  AND current_query!='<IDLE>' ORDER BY client_addr

процесса выполнения функции нету (хотя счетчик времени в окне выполнения работает).
в чем прикол?

‹ pg_ctl: не удалось найти исполняемый файл postgres'а скорость работы приложеня с postgresql меняется ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

да еще в p222i 57225 строк

Изображение Izver
On Июнь 15th, 2010 Izver says:

да еще в p222i 57225 строк

Что за "окно времени

On Июнь 16th, 2010 admin says:

Что за "окно времени выполнения"? В том же psql такового нет. Запускайте оттуда и смотрите.
И ещё есть сильное подозрение, что всё что вы наворотили функцией делается либо с помощью partitioning, либо с помощью триггеров.

Не удивительно. Удивительно, что оно вообще работает

On Июнь 16th, 2010 eugeni says:

Уважаемый Izver, из всех возможных способов сделать такое вы выбрали самый -извра- (*зачеркнуто*) экзотический.
Чтобы получить минимальную/максимальную дату таблицы, сворачивать ее (GROUP BY), затем всю сортировать и затем выбрать ОДИН "верхний" элемент!? И так два раза!!
Обычно делается так:

    SELECT min(datd), max(datd) INTO c_datn, c_datk  FROM p222i

Удалять строки через курсор в цикле - моветон.
WITH PND AS делает то же самое, что и CREATE TEMPORARY ... AS, но без курсоров и циклов.

Искать проблемы производительности в Postgres'е не нужно - если они там и есть, то это не тот случай.
Пожалуйста, опишите задачу словесно, возможно, она решается 1-2 SQL-командами.

  • удаляем данные из р2, которых нет в р2i - !!!
  • если параметры kol,sumo,sum_nds из p222 для определенного nd не совпадают с параметрами p222i, удаляем строку в p222 и ставим из p222i. А если совпадают? Нужна ли проверка вообще - может просто заменить все (учитывая п.1)?
  • добавляем новые данные из p222i в р222

Почему нельзя просто "убить" весь диапазон c_datn..c_datk в таблице р222 и загрузить все из р222i, сворачивая результат по kol,sumo,sum_nds?
Объясните суть доп. проверок в циклах, почему они вынесены из условий запросов WHERE "наружу"?

Задача. Есть таблицы: p222 и

Изображение Izver
On Июнь 16th, 2010 Izver says:

Задача.
Есть таблицы: p222 и p222i
в p222 хранятся данные за весь год,
p222i-шаблон(данные перекачиваются через перл из дбф за 5 дней каждую ночь).
нужно что данные из p222 совпадали с данными из p222i
ключевой реквизит kol(количество),sumo(сумма без ндс),sum_nds(сумма ндс)
этап первый:
из p222 удаляем данные которых нет в p222i
например:
в p222 для nd(№ документа)='2562615' и kvpr(код продукции)='2525' - kol=20,sumo=16,sum_nds=4
а в p222i нет этих данных.
то удаляем строку из p222 с реквизитами nd(№ документа)='2562615' и kvpr(код продукции)='2525' - kol=20,sumo=16,sum_nds=4
этап второй:
проверяются данные после завершения первого этапа.
например:
в p222 для nd(№ документа)='2562714' и kvpr(код продукции)='2524' - kol=5,sumo=4,sum_nds=1
а в p222i для nd(№ документа)='2562714' и kvpr(код продукции)='2524' - kol=6,sumo=4.8,sum_nds=1.2
то удаляем строку из p222 с реквизитами nd(№ документа)='2562714' и kvpr(код продукции)='2524'
и добавляем строку из p222i в p222 с реквизитами nd(№ документа)='2562714' и kvpr(код продукции)='2524'
этап третий:
проверяются данные после завершения второго этапа.
в p222 добавляем новые данные из p222i
в p222i для nd(№ документа)='256275' и kvpr(код продукции)='5254' - kol=10,sumo=8,sum_nds=2
а в p222 нет этих данных.
то добавляем строку из p222i в p222 с реквизитами nd(№ документа)='256275' и kvpr(код продукции)='5254'

всё это делается, чтобы обновлять только измененную информацию.

Можно к, примеру, в этом

On Июнь 16th, 2010 Alarm says:

Можно к, примеру, в этом направлении попробовать
1.

DELETE FROM p222
WHERE nd = (SELECT p.nd 
                      FROM p222 p LEFT JOIN p222i pi ON pi.nd=pnd AND pi.kvpr=p.kvpr AND pi.kol=p.kol AND pi.sumo=p.sumo
WHERE pi IS NULL LIMIT 1);

2.
INSERT INTO p222 (SELECT pi.nd, pi.kvpr, pi.kol, pi.sumo, pi.sum_nds FROM p222i pi LEFT JOIN p222 p ON pi.nd=pnd AND pi.kvpr=p.kvpr AND pi.kol=p.kol AND pi.sumo=p.sumo
WHERE p.nd IS NULL)

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023