Re: share lock when only one user connected?

On 28 Oct 2010, at 19:28, Ivan Sergio Borgonovo wrote:

> I'm running this query when I'm the only user and this should be the
> only thing running.
>
> update catalog_items
...
>  from (
>    select a.id, a.codice, a.codicealt,
...
>    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:

You left out the actual trigger definitions ;)

Could it be possible that you accidentally call the wrong trigger on update of catalog_items?

Another possibility is that the trigger on catalog_items has a side-effect of updating catalog_brands - which in turn
updatescatalog_items again, causing your situation. 

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4cca678310291669837610!