Re: Postgres 7.4.6 hang in async_notify

In message <20050425205541.GC29834@dcc.uchile.cl>, Alvaro Herrera writes:

    On Mon, Apr 25, 2005 at 03:42:35PM -0400, pgsql-bugs@counterstorm.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';