share lock when only one user connected?

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: share lock when only one user connected?
Date: 2010-10-28 17:28:15
Message-ID: 20101028192815.14e991e4@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A.M. 2010-10-28 17:52:30 Re: Should PQconsumeInput/PQisBusy be expensive to use?
Previous Message Ivan Sergio Borgonovo 2010-10-28 17:06:13 Re: exceptionally large UPDATE