Skip site navigation (1) Skip section navigation (2)

Re: Postgres Triggers issue

From: u235sentinel <u235sentinel(at)gmail(dot)com>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Postgres Triggers issue
Date: 2010-02-11 19:36:26
Message-ID: 4B745C3A.2060407@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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!



In response to

pgsql-novice by date

Next:From: Marcin KrolDate: 2010-02-12 17:09:54
Subject: db size and VACUUM ANALYZE
Previous:From: Tim LandscheidtDate: 2010-02-11 16:48:25
Subject: Re: Using xpath queries against XML Datatype

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group