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 updates catalog_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(at)postgresql(dot)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!