Re: share lock when only one user connected?

On Fri, 29 Oct 2010 08:19:27 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

I've to amend the "one user connected" assumption.
But I'm really sure there is no other process writing on
catalog_items.

There is a process that read catalog_items and write on another
table.

create catalog_items {
  itemid bigint primary key
}
create catalog_related {
  itemid bigint references catalog_items (itemid) on delete cascade,
  itemid_related bigint references catalog_items (itemid) on delete
  cascade
}

To add some more details the update is running inside a larger
transaction that update other tables.
There are no "on update cascade" and no other triggers.
I'm using 8.3.4

I admit I've a very naïve knowledge of locks and maybe I'm making
assumptions that aren't valid.
I've been able to find:
http://www.postgresql.org/docs/7.2/static/locking-tables.html
but no equivalent for 8.3
I assume the same is valid for 8.3 so since there are no explicit
LOCK TABLE on catalog_items what's left are the INDEX.
I've been experiencing the same problem even dropping the gin index
on the FT1IDX column but there are other btree index on that table.

How can I get more information in the logs to know which statement
were producing the lock?
One for sure was the update.

> 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 ;)

create trigger FT1IDX_catalog_items_update_trigger
  after insert or update on catalog_items for each
  row execute procedure FT1IDX_catalog_items_update();
create trigger FT1IDX_catalog_brands_update_trigger
  after update or delete on catalog_brands for each
  row execute procedure FT1IDX_catalog_brands_update();

> 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.

Is this the case?
My intention was that when catalog_brands.brandid get changed
catalog_items.FT1IDX get updated

> > 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?


thanks

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