Thread: Postgres Triggers issue

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();

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;$$
languageplpgsql; 
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
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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!