Re: Postgres 7.4.6 hang in async_notify

In message <20050425205541(dot)GC29834(at)dcc(dot)uchile(dot)cl>, Alvaro Herrera writes:

On Mon, Apr 25, 2005 at 03:42:35PM -0400, pgsql-bugs(at)counterstorm(dot)com wrote:

> The way the notify gets called is a bit convoluted. Our C program
> performs an insert in table T. Some triggers run doing all sorts of
> stuff in the background involving changes to other tables. Under some
> circumstances, a row is inserted by the trigger into table W (a
> trigger off of that is executed) and the parent trigger performs a
> notify. This, while expensive, normally all works. However, at a
> customer site we saw the postgres process hang out in async_notify and
> refuse to allow more inserts.

What do you mean by "in the background"? Do you fork() the backend
process?

Sorry, I was being imprecise. It just does a lot of dynamic queries,
inserts, updates, lookups, etc.

IIRC there have been bugs in listen/notify by which a backend could hang
forever. Some were fixed, but others main remain. Can you post more
details of the involved functions and triggers?

Here is the top layer. If you need the second level down of functions,
let me know. Yes, this function does imply you could get multiple
notifies generated in a single trigger.

Thanks,

----------------------------------------------------------------------
psql=> \d tagset_1
Table "tagset_1"
Column | Type | Modifiers
--------------------------+--------------------------------+-------------------------------------------------------
internal_dataset_key | text | not null
internal_record_id | integer | not null default nextval(tagset_1_seq'::text)
internal_eventin_ulink | text |
internal_revid | text |
event_timestamp | timestamp(6) without time zone |
code_all_start | timestamp(6) without time zone |
code_all_end | timestamp(6) without time zone |
code_language_list | smallint[] |
code_src_id_list | integer[] |
code_dst_id_list | integer[] |
code_dst_transport_list | integer[] |

Indexes:
"tagset_1_pkey" primary key, btree (internal_dataset_key, internal_record_id)
Triggers:
zzz_tagset_1_table_trigger AFTER INSERT ON tagset_1 FOR EACH ROW EXECUTE PROCEDURE munch_2k4_entry()

create or replace function munch_2k4_entry()
returns trigger as '
declare
srcid integer;
lang smallint;
dsttranss int[];
dstids int[];
dsttrans int;
ev_start timestamp;
ev_last timestamp;
thres int;
hitids int[];
hits int;
id bigint;
pred_id int;
scen text;
entry_tag text;
ev_hit boolean := false;
alarm_hit boolean := false;
begin
lang := new.code_language_list[1];
srcid := new.code_src_net_list[1];
dstids := new.code_dst_net_list;
dsttranss := new.code_dst_transport_list;
if ((array_size(dsttranss) is null) or (array_size(dsttranss)<1)) then
dsttrans := null;
else
dsttrans := dsttranss[1];
end if;
entry_tag := get_entry_tag(new.internal_record_id, new.internal_dataset_key);
ev_start := new.code_all_start;
ev_last := new.code_all_end;

hitids := get_first_hitids(dstids);
hits := array_size(hitids);
if is_ndt_id(srcid) then
if hits>0 then
pred_id := 1;
scen := ''WMI-NDT'';
ev_hit := massage_evidence(srcid, dsttrans, lang, pred_id, hits, entry_tag, ev_start, ev_last);
if ev_hit then
alarm_hit := maybe_raise_alarm(srcid, dsttrans, lang, scen);
if alarm_hit then
notify alarm;
end if;
end if;
end if;
return new;
end if;

if hits > 0 then
pred_id := 2;
scen := ''WMI-EEDT'';
ev_hit := massage_evidence(srcid, dsttrans, lang, pred_id, hits, entry_tag, ev_start, ev_last);
if ev_hit then
alarm_hit := maybe_raise_alarm(srcid, dsttrans, lang, scen);
if alarm_hit then
notify alarm;
end if;
end if;
end if;

hitids := get_inside_hitids(dstids);
hits := array_size(hitids);
if hits>0 then
pred_id := 3;
scen := ''WMI-EIDT'';
ev_hit := massage_evidence(srcid, dsttrans, lang, pred_id, hits, entry_tag, ev_start, ev_last);
if ev_hit then
alarm_hit := maybe_raise_alarm(srcid, dsttrans, lang, scen);
if alarm_hit then
notify alarm;
end if;
end if;
end if;

hitids := dstids;
hits := array_size(dstids);
if hits>0 then
pred_id := 4;
scen := ''WMI-EADT'';
ev_hit := massage_evidence(srcid, dsttrans, lang, pred_id, hits, entry_tag, ev_start, ev_last);
if ev_hit then
alarm_hit := maybe_raise_alarm(srcid, dsttrans, lang, scen);
if alarm_hit then
notify alarm;
end if;
end if;
end if;

return new;
end; '
language 'plpgsql';