Postgres Triggers issue

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

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

-----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

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

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�

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!

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

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

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!