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
