Re: Postgres trigger issue with update statement in it. - Mailing list pgsql-sql
From | Kaleeswaran Velu |
---|---|
Subject | Re: Postgres trigger issue with update statement in it. |
Date | |
Msg-id | 1365098094.12263.YahooMailNeo@web163906.mail.gq1.yahoo.com Whole thread Raw |
In response to | Re: Postgres trigger issue with update statement in it. (Wolfe Whalen <wolfe@quios.net>) |
List | pgsql-sql |
Hi Mr. Wolfe,
Thanks for your response. I have received e-mails from several others. Thanks for everyone and appreciate your help.
Mr. Wolfe sent a sample code. I took that as the base and debugged my code and identified the issue. Now my code is working fine. I have identified the real culprit.
Earlier my trigger was as like
CREATE OR REPLACE FUNCTION fun_update_payments() RETURNS TRIGGER AS $trg_update_payments$
DECLARE
BEGIN
UPDATE jl
SET jl.outstanding = jl.outstanding - new.Principle_Amount
WHERE jl.jl_id=new.jl_id;
RETURN new;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'fun_update_payment() Failed...';
END
$trg_update_payments$ LANGUAGE plpgsql;
After debugging I found my Update statement is wrong, I should not have prefix as <table_name.> (Oracle accepts this.). I then changed that to as below and stared working.
UPDATE jl
SET outstanding = outstanding - new.Principle_Amount
WHERE jl_id=new.jl_id;
Somehow Postgres is not capturing this at the compilation time.
But at run time, instead of throwing syntax error, it was trowing some transactional error as "ERROR: cannot begin/end transactions in PL/pgSQL". The reason for that is the EXCEPTION block that I had at the end.
I then removed below block from the trigger, then it was throwing expected syntax error at run time.
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE NOTICE 'fun_update_payment() Failed...';
However it works now. Again thanks to Mr. Wolfe.
Thanks and Regards
Kaleeswaran Velu
From: Wolfe Whalen <wolfe@quios.net>
To: Kaleeswaran Velu <v_kalees@yahoo.com>
Cc: Postgres SQL List <pgsql-sql@postgresql.org>
Sent: Thursday, April 4, 2013 12:58 PM
Subject: Re: [SQL] Postgres trigger issue with update statement in it.
Hi Kaleeswaran,
We're glad to have you on the mailing list. I don't know enough about your trigger function to know exactly where it's going wrong, but I threw together a quick example that has an insert trigger on a child table that updates a row on the parent table. I'm hoping this might help. If it doesn't help, maybe you could give us a little more information about your function or tables. I'd be happy to help in any way that I can.
CREATE TABLE survey_records (
name varchar(100),
obsoleted timestamp DEFAULT NULL
);
CREATE TABLE geo_surveys (
measurement integer
) INHERITS (survey_records);
CREATE OR REPLACE FUNCTION obsolete_old_surveys() RETURNS trigger AS $$
BEGIN
UPDATE survey_records SET obsoleted = clock_timestamp()
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER obsolete_old_surveys_tr
BEFORE INSERT ON geo_surveys
FOR EACH ROW EXECUTE PROCEDURE obsolete_old_surveys();
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 5);
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 10);
INSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 93);
You'd wind up with something like this:
SELECT * FROM survey_records;
name | obsoleted
----------------+----------------------------
Carbon Dioxide | 2013-04-03 23:59:44.228225
Carbon Dioxide | 2013-04-03 23:59:53.66243
Carbon Dioxide |
(3 rows)
SELECT * FROM geo_surveys;
name | obsoleted | measurement
----------------+----------------------------+-------------
Carbon Dioxide | 2013-04-03 23:59:44.228225 | 5
Carbon Dioxide | 2013-04-03 23:59:53.66243 | 10
Carbon Dioxide | | 93
(3 rows)
The parent survey_records is actually updating the child table rows when you do an update. Parent tables can almost seem like a view in that respect. You would have to be a bit careful if you're going to have an update trigger on a child that updated the parent table. It's easy to wind up with a loop like this:
Child: Update row 1 -> Trigger function -> Update Row 1 on parent
->Parent: Let's see... Row 1 is contained in this child table, so let's update it there.
->Child: Update row 1 -> Trigger function -> Update Row 1 on parent
->Parent: Let's see... Row 1 is contained in this child table, so let's update it there.
... etc etc.
Best Regards,
Wolfe
--
Wolfe Whalen
wolfe@quios.net
On Wed, Apr 3, 2013, at 09:08 PM, Kaleeswaran Velu wrote:
Hello Friends,I am new to Postgres DB. Recently installed Postgres 9.2.Facing an issue with very simple trigger, tried to resolve myself by reading documents or google search but no luck.I have a table A(parent) and table B (child). There is a BEFORE INSERT OR UPDATE trigger attached in table B. This trigger has a update statement in it. This update statement should update a respective record in table A when ever there is any insert/update happen in table B. The issue here is where ever I insert/update record in table B, getting an error as below :********** Error **********ERROR: cannot begin/end transactions in PL/pgSQLSQL state: 0A000Hint: Use a BEGIN block with an EXCEPTION clause instead.Context: PL/pgSQL function func_update_payment() line 53 at SQL statementLine no 53 in the above error message is an update statement. If I comment out the update statement, trigger works fine.Can anyone shed some lights on this? Your help is appreciated.Thanks and RegardsKaleeswaran Velu