Thread: Postgres Triggers issue

Postgres Triggers issue

From
u235sentinel
Date:
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();


Re: Postgres Triggers issue

From
"Albe Laurenz"
Date:
u235sentinel wrote:
> 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 do you mean "stops the feed"?

Can you describe the behaviour in database terms?
What exactly happens, and how does it differ from what you expect?
Are there error messages? If yes, could you quote them?

Yours,
Laurenz Albe

Re: Postgres Triggers issue

From
Adrian Klaver
Date:
On Thursday 11 February 2010 1:57:39 am Albe Laurenz wrote:
> u235sentinel wrote:
> > 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 do you mean "stops the feed"?
>
> Can you describe the behaviour in database terms?
> What exactly happens, and how does it differ from what you expect?
> Are there error messages? If yes, could you quote them?
>
> Yours,
> Laurenz Albe

In addition to the above I am not quite sure about this:

INSERT INTO temp_m_t VALUES (NEW.*,1+1)

Are you trying to have an incrementing number for the last value? As it stands
you are are always going to get 2 inserted into that field.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Postgres Triggers issue

From
"Igor Neyman"
Date:
> -----Original Message-----
> From: u235sentinel [mailto:u235sentinel@gmail.com]
> Sent: Wednesday, February 10, 2010 11:15 PM
> To: pgsql-general@postgresql.org
> Subject: 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();
>
>

Trigger function for an insert/update trigger should return "NEW", not
NULL (OLD - for "on delete" trigger):

 CREATE OR REPLACE FUNCTION r.m_t()
 RETURNS trigger AS
 $BODY$
 BEGIN
  INSERT INTO temp_m_t VALUES (NEW.*,1+1); RETURN NEW; END;
 $BODY$ LANGUAGE 'plpgsql';


Igor Neyman

Re: Postgres Triggers issue

From
"A. Kretschmer"
Date:
In response to Igor Neyman :
> >
> > CREATE TRIGGER tafter
> > AFTER INSERT OR UPDATE
> > ON r.m_a
> > FOR EACH ROW
> > EXECUTE PROCEDURE r.m_t();
> >
> >
>
> Trigger function for an insert/update trigger should return "NEW", not
> NULL (OLD - for "on delete" trigger):

It's an AFTER TRIGGER, so the RETURN-Value ignored.

It works with NULL, see my other posting (the example there).


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re: Postgres Triggers issue

From
Andreas Kretschmer
Date:
A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:

> In response to Igor Neyman :
> > >
> > > CREATE TRIGGER tafter
> > > AFTER INSERT OR UPDATE
> > > ON r.m_a
> > > FOR EACH ROW
> > > EXECUTE PROCEDURE r.m_t();
> > >
> > >
> >
> > Trigger function for an insert/update trigger should return "NEW", not
> > NULL (OLD - for "on delete" trigger):
>
> It's an AFTER TRIGGER, so the RETURN-Value ignored.

According the doc:

The return value of a BEFORE or AFTER statement-level trigger or an
AFTER row-level trigger is always ignored; it might as well be null.

http://www.postgresql.org/docs/current/static/plpgsql-trigger.html


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Postgres Triggers issue

From
u235sentinel
Date:
Trigger function for an insert/update trigger should return "NEW", not
>>> NULL (OLD - for "on delete" trigger):
>>>
>> It's an AFTER TRIGGER, so the RETURN-Value ignored.
>>
>
> According the doc:
>
> The return value of a BEFORE or AFTER statement-level trigger or an
> AFTER row-level trigger is always ignored; it might as well be null.
>
> http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>
>
> 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!

Re: Postgres Triggers issue

From
Adrian Klaver
Date:
On 02/11/2010 11:08 AM, u235sentinel wrote:
> Trigger function for an insert/update trigger should return "NEW", not
>>>> NULL (OLD - for "on delete" trigger):
>>> It's an AFTER TRIGGER, so the RETURN-Value ignored.
>>
>> According the doc:
>>
>> The return value of a BEFORE or AFTER statement-level trigger or an
>> AFTER row-level trigger is always ignored; it might as well be null.
>>
>> http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
>>
>>
>> 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.


Well that would depend on any number of factors. Without information on
how the feed is being done or more detailed logs it is hard to say for
sure. At a guess though, I would say it is because the 'feed' is being
done wrapped in a transaction and when the trigger errors it aborts the
transaction.

>
> Thanks!
>


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Postgres Triggers issue

From
u235sentinel
Date:
Adrian Klaver wrote:
>
>
> Well that would depend on any number of factors. Without information
> on how the feed is being done or more detailed logs it is hard to say
> for sure. At a guess though, I would say it is because the 'feed' is
> being done wrapped in a transaction and when the trigger errors it
> aborts the transaction.
>

 From my perspective, I only see inserts when I select * from
pg_stat_activity.  I'm told it's a jdbc connection (don't know much
about java myself) but it has been interesting to see that it's working
now.  Still I did find it odd that the inserts stopped when the badly
written trigger was there

I appreciate the help :D

Re: Postgres Triggers issue

From
u235sentinel
Date:
Adrian Klaver wrote:
> On Thursday 11 February 2010 1:57:39 am Albe Laurenz wrote:
>
>> u235sentinel wrote:
>>
>>> 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 do you mean "stops the feed"?
>>
>> Can you describe the behaviour in database terms?
>> What exactly happens, and how does it differ from what you expect?
>> Are there error messages? If yes, could you quote them?
>>
>> Yours,
>> Laurenz Albe
>>
>
> In addition to the above I am not quite sure about this:
>
> INSERT INTO temp_m_t VALUES (NEW.*,1+1)
>
> Are you trying to have an incrementing number for the last value? As it stands
> you are are always going to get 2 inserted into that field.
>
>
Yes this was intentional for testing purposes.  We were trying to see if
we can do it and it worked.  Now we can get into the really fun stuff :-)

Thanks to all for their help!