From: Tzvetan Tzankov <ce@noxis.net>
To: pgsql-sql@postgresql.org
Subject: Deadlock problem
Date: Fri, 18 Apr 2003 12:06:10 +0300
Message-ID: <b7of5s$1pr7$1@news.hub.org> (raw)
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 int4 not null,
clicks int4 not null default 0,
paused bool not null default 'f',
payed bool not null default 'f',
start date not null
);
CREATE TABLE periodables (
advert int4 not null PRIMARY KEY REFERENCES advert on delete cascade,
finish date not null,
tviews int4 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 '
begin
if 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'';
end if;
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 dir
FOR EACH ROW EXECUTE PROCEDURE ch_payed_viewable();
CREATE TRIGGER biu_magazinepayed BEFORE UPDATE OR INSERT ON magazine
FOR EACH ROW EXECUTE PROCEDURE ch_payed_viewable();
CREATE OR REPLACE FUNCTION ch_payed_periodable() RETURNS trigger
AS '
begin
if 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'';
end if;
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 bazar
FOR 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 NOT NULL 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 '
declare
p_advert_id alias for $1;
p_service alias for $2;
affected_rows int;
p_service_type text;
begin
p_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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ce@noxis.net
Subject: Re: Deadlock problem
In-Reply-To: <b7of5s$1pr7$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox