Deadlock problem - Mailing list pgsql-sql

Hallo,

I have the following problem ( I hope somebody will have the nerves to 
read it )

here is the DB schema (sorry it is only part of the schema, but it's 
still large, I've tried to isolate only the things I think are important 
in the case)

CREATE TABLE viewables (advert int4 not null PRIMARY KEY REFERENCES advert on delete cascade,views int4 not null,tviews
int4not null,clicks int4 not null default 0,paused bool not null default 'f',payed bool not null default 'f',start date
notnull
 
);

CREATE TABLE periodables (advert int4 not null PRIMARY KEY REFERENCES advert on delete cascade,finish date not
null,tviewsint4 not null default 0,clicks int4 not null default 0,paused bool not null default 'f',payed bool not null
default'f',start date not null
 
);

CREATE OR REPLACE FUNCTION check_parent(text) RETURNS name
AS 'SELECT p.relname FROM pg_class AS p, pg_class AS c, pg_inherits AS r 
WHERE c.relname = $1 AND r.inhrelid = c.relfilenode AND p.relfilenode = 
inhparent;
'
LANGUAGE 'sql' IMMUTABLE;

CREATE TABLE bazar (bazar int4[] not null
) INHERITS (periodables);
CREATE INDEX bazar_advert ON bazar(advert);
CREATE INDEX bazar_rdtree_idx ON bazar using gist (bazar gist__int_ops);

CREATE TABLE dir (dir int4[] not null,city int4[] not null
) INHERITS (viewables);
CREATE INDEX dir_advert ON dir(advert);
CREATE INDEX dir_rdtree_idx ON dir USING gist ( dir gist__int_ops);

CREATE TABLE magazine (magazine int4[] not null
) INHERITS (viewables);
CREATE INDEX magazine_advert ON magazine(advert);
CREATE INDEX magazine_rdtree_idx ON magazine USING gist ( magazine 
gist__int_ops);

CREATE OR REPLACE FUNCTION ch_payed_viewable() RETURNS trigger
AS '
beginif tg_op = ''UPDATE'' then    if old.payed = ''t'' then        new.views = old.views;        new.payed = ''t'';
else       if new.payed = ''t'' then            new.paused = ''f'';        else            new.paused = ''t'';
endif;        new.clicks = 0;        new.tviews = new.views;    end if;else    new.paused = ''t'';    new.clicks = 0;
new.tviews = new.views;end if;return new;
 
end;
'
LANGUAGE 'plpgsql' STRICT;

CREATE TRIGGER biu_dirpayed BEFORE UPDATE OR INSERT ON dirFOR EACH ROW EXECUTE PROCEDURE ch_payed_viewable();
CREATE TRIGGER biu_magazinepayed BEFORE UPDATE OR INSERT ON magazineFOR EACH ROW EXECUTE PROCEDURE
ch_payed_viewable();

CREATE OR REPLACE FUNCTION ch_payed_periodable() RETURNS trigger
AS '
beginif tg_op = ''UPDATE'' then    if old.payed = ''t'' then        new.finish = old.finish;        new.payed = ''t'';
 else        if new.payed = ''t'' then            new.paused = ''f'';        else            new.paused = ''t'';
endif;        new.clicks = 0;    end if;else    new.paused = ''t'';    new.clicks = 0;end if;return new;
 
end;
'
LANGUAGE 'plpgsql' STRICT;

CREATE TRIGGER biu_bazarpayed BEFORE UPDATE OR INSERT ON bazarFOR EACH ROW EXECUTE PROCEDURE ch_payed_periodable();

CREATE TABLE advert_logs(advert int4 NOT NULL REFERENCES advert on delete cascade,service varchar(8) NOT NULL,date date
NOTNULL default CURRENT_DATE,clicks int4 not null,views int4 not null,PRIMARY KEY (advert, service, date)
 
);

CREATE OR REPLACE FUNCTION log_advert_view(int4, text) RETURNS bool
AS '
declarep_advert_id alias for $1;p_service alias for $2;affected_rows int;p_service_type text;
beginp_service_type = check_parent(p_service);
if p_service_type = ''periodables'' then    EXECUTE ''UPDATE '' || p_service || '' SET tviews = tviews + 1 WHERE 
advert = '' || p_advert_id::text;else    EXECUTE ''UPDATE '' || p_service || '' SET tviews = tviews - 1 WHERE 
advert = '' || p_advert_id::text;end if;UPDATE advert_logs SET views = views + 1 WHERE advert = p_advert_id AND 
service = p_service AND date = CURRENT_DATE;
GET DIAGNOSTICS affected_rows = ROW_COUNT;
if affected_rows = 0 then    INSERT INTO advert_logs (advert, service, clicks, views) VALUES 
(p_advert_id, p_service, 0, 1);end if;
return ''t'';
end;
'
LANGUAGE 'plpgsql' STRICT;


here is a php code, which defenetly under hi load makes something like 
deadlock (i do not exactly know, where - only it is in function 
log_advert_view())

first $query is something like for example c.bazar && '{3}' AND finish >= CURRENT_DATE
or c.magazine && '{2}' AND c.tviews > 0
depending on what is going to be viewed (which service and which values 
are being interested in, service is something like magazine, dir, bazar)

$query = "SELECT a.* FROM advert AS a, $service AS c WHERE a.id = 
c.advert AND a.approved AND c.payed AND ".$query." AND NOT c.paused AND 
c.start <= CURRENT_DATE ORDER BY random() LIMIT $limit";
$data = false;
pg_query($connection, 'BEGIN WORK');
$result = array();
$res = @pg_query($connection, $query);
if ($num_rows = @pg_num_rows($res)) {for ($ii=0; $ii<$num_rows; ++$ii) {    $data = pg_fetch_array($res, $ii,
PGSQL_ASSOC);   if (substr($_SERVER['REMOTE_ADDR'], 0, 11) <> '194.145.63.'){        pg_query($connection, "SELECT
log_advert_view('{$data['id']}'::int,
 
'$service')");    }    ob_start();    include '/var/www/reklama.dir.bg/adv/templates/'.$data['template'].'.php';
$result[$ii]= ob_get_contents();    ob_end_clean();}@pg_free_result($res);pg_query($connection, 'COMMIT WORK');
 
} else {@pg_free_result($res);pg_query($connection, 'ROLLBACK WORK');
}

so as I mentioned when a deadlock situation occurred there are a lot of 
SELECT log_advert_view() statment waiting in pg_stat_activity
I tried without transactions -> no difference
tried to remove intarray indexes -> no difference

the solution I found was this function log_advert_view() to be 
implemented in php -> many pg_query statements etc. AND without 
transactions AND without intarray indexes bazar_rdtree_idx, 
dir_rdtree_idx, magazine_rdtree_idx (all other combinations ended in 
deadlocks)

so my question is is there any explanation of why is it happening, and 
is there something I'm missing

maybe it is not deadlock, but what else it is ? (hundred processes got 
stuck)

if some other info would be valuable for the situation I will provide it

thanx
ceco