Re: Postgres Triggers issue

A. Kretschmer wrote:
> In response to u235sentinel :
>
>> I have a strange problem we noticed the other day with triggers. We're
>> running 8.3.3 on Solaris 10 (intel) and have a feed that comes in
>> regularly to populate a table we're working on. The feed works just
>> fine inserting rows however the following trigger stops the feed until
>> we remove the trigger. Any thoughts on what I'm doing wrong here?
>>
>> Thanks!
>>
>> ---
>>
>> CREATE OR REPLACE FUNCTION r.m_t()
>> RETURNS trigger AS
>> $BODY$
>> BEGIN
>> INSERT INTO temp_m_t VALUES (NEW.*,1+1);
>> RETURN NULL;
>> END;
>> $BODY$
>> LANGUAGE 'plpgsql';
>>
>>
>> CREATE TRIGGER tafter
>> AFTER INSERT OR UPDATE
>> ON r.m_a
>> FOR EACH ROW
>> EXECUTE PROCEDURE r.m_t();
>>
>
> What exactly happens?
>
> Something similar works for me:
>
> test=# create table a(i int);
> CREATE TABLE
> test=*# create table b(i int, other_column int);
> CREATE TABLE
> test=*# create or replace function f() returns trigger as $$begin insert into b values (new.*, 5); return null; end;$$ language plpgsql;
> CREATE FUNCTION
> test=*# create trigger trg1 after insert or update on a for each row execute procedure f();
> CREATE TRIGGER
> test=*# insert into a values (1);
> INSERT 0 1
> test=*# select * from b;
> i | other_column
> ---+--------------
> 1 | 5
> (1 row)
>
>
>
> Regards, Andreas
>

We found the problem. I did some additional digging and learned the
admin in question was trying to trigger on a schema.table that didn't
exist! Yeah I did slap him around a bit ;-)

remembering the schema part of the name can be important!! ::grinz::

One further question, so we're doing inserts from a remote source (it's
a radware system feeding us data). Why would it stop the system from
inserting data when it's an after statement? I noticed a bunch of
'connection time out' messages in our logs.

It is working so I'm good. Still it is interesting the feed just
stopped when the trigger was enabled.

Thanks!