Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres trigger issue with update statement in it.

Postgres trigger issue with update statement in it.

From
Kaleeswaran Velu
Date:

 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/pgSQL
SQL state: 0A000
Hint: Use a BEGIN block with an EXCEPTION clause instead.
Context: PL/pgSQL function func_update_payment() line 53 at SQL statement

Line 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 Regards
Kaleeswaran Velu

Re: Postgres trigger issue with update statement in it.

From
Wolfe Whalen
Date:
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()
    WHERE survey_records.name = NEW.name AND survey_records.obsoleted IS NULL;
  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/pgSQL
SQL state: 0A000
Hint: Use a BEGIN block with an EXCEPTION clause instead.
Context: PL/pgSQL function func_update_payment() line 53 at SQL statement
 
Line 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 Regards
Kaleeswaran Velu

Re: Postgres trigger issue with update statement in it.

From
Adrian Klaver
Date:
On 04/03/2013 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/pgSQL
> SQL state: 0A000
> Hint: Use a BEGIN block with an EXCEPTION clause instead.
> Context: PL/pgSQL function func_update_payment() line 53 at SQL statement
>
> Line 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.

Without seeing the actual code it is a guess, but here it goes.
From this:

ERROR: cannot begin/end transactions in PL/pgSQL

I would say you tried to create a transaction in the function. Could be 
you wrote BEGIN; instead of BEGIN ... END;

>
> Thanks and Regards
> Kaleeswaran Velu


-- 
Adrian Klaver
adrian.klaver@gmail.com

Re: Postgres trigger issue with update statement in it.

From
Kaleeswaran Velu
Date:
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()
    WHERE survey_records.name = NEW.name AND survey_records.obsoleted IS NULL;
  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/pgSQL
SQL state: 0A000
Hint: Use a BEGIN block with an EXCEPTION clause instead.
Context: PL/pgSQL function func_update_payment() line 53 at SQL statement
 
Line 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 Regards
Kaleeswaran Velu