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