share lock when only one user connected?

Поиск
Список
Период
Сортировка
От Ivan Sergio Borgonovo
Тема share lock when only one user connected?
Дата
Msg-id 20101028192815.14e991e4@dawn.webthatworks.it
обсуждение исходный текст
Ответы Re: share lock when only one user connected?  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: share lock when only one user connected?  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Список pgsql-general
I'm running this query when I'm the only user and this should be the
only thing running.

update catalog_items
  set Code=s.codice, CodeAlt=s.codicealt, BrandID=s.marca,
  Name=initcap(s.nome), Description=s.desc_l1,
  qty=coalesce(s.quantita, 0),
  ListPrice=coalesce(s.prezzoListino, 0) ,
  valIva=s.valIva, ivaInc=s.ivaInc,
  StatusID=coalesce(s.stato, 1) ,
  Weight=s.peso, dataInserimento=s.dataInserimento, daPub=s.daPub,
  BestSeller=s.bestSeller, posInMag=s.posInMag , ISBN=s.ISBN,
  dataPub=coalesce(s.dataPubblicazione, datapub),
  updated=now()
  from (
    select a.id, a.codice, a.codicealt,
    a.marca, a.nome, a.desc_l1, a.quantita, a.prezzoListino,
    a.valIva, a.ivaInc, a.stato, a.peso, a.dataInserimento, a.daPub,
    a.bestSeller, a.posInMag, a.ISBN, ip.dataPubblicazione
    from
      import.Articoli a
      left join import.ArticoliPropTipo_1 ip on a.id=ip.idArt
      where a.action=8
    ) as s
    where s.id=catalog_items.ItemID
;

And I get

DETAIL:  Process 7188 waits for ShareLock on transaction 110562621;
blocked by process 7244. Process 7244 waits for ShareLock on
transaction 110562544; blocked by process 7188.

On that table 2 triggers are defined:

create or replace function FT1IDX_catalog_items_update() returns
trigger as
$$
declare
    _Name varchar(64);
begin
  select into _Name Name from catalog_brands
   where BrandID=new.BrandID;
   new.FT1IDX:=
     GetFTIDX('pg_catalog.english', new.Code, new.CodeAlt, new.ISBN,
     new.Name, new.Authors, _Name);
  return new;
end;
$$ language plpgsql volatile;

create or replace function FT1IDX_catalog_brands_update() returns
trigger as
$$
begin
  if(TG_OP='DELETE') then
    update catalog_items set
      FT1IDX=
       GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, '')
        where BrandID=old.BrandID;
    return old;
  else
    if(coalesce(new.Name,'')<>coalesce(old.Name,'')) then
      update catalog_items set
        FT1IDX=
          GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, new.Name) where BrandID=new.BrandID;
    end if;
  end if;
return new;
end;
$$ language plpgsql volatile;

What could it be? how can I fix it?



--
Ivan Sergio Borgonovo
http://www.webthatworks.it


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

Предыдущее
От: Ivan Sergio Borgonovo
Дата:
Сообщение: Re: exceptionally large UPDATE
Следующее
От: "A.M."
Дата:
Сообщение: Re: Should PQconsumeInput/PQisBusy be expensive to use?