Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UNoNN-0001uj-Vu for pgsql-sql@arkaria.postgresql.org; Thu, 04 Apr 2013 17:55:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UNoNN-0004ny-CJ for pgsql-sql@arkaria.postgresql.org; Thu, 04 Apr 2013 17:55:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UNoNL-0004mH-GH for pgsql-sql@postgresql.org; Thu, 04 Apr 2013 17:55:03 +0000 Received: from nm11-vm6.bullet.mail.gq1.yahoo.com ([98.136.218.173]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UNoNF-0007I7-Q5 for pgsql-sql@postgresql.org; Thu, 04 Apr 2013 17:55:03 +0000 Received: from [98.137.12.59] by nm11.bullet.mail.gq1.yahoo.com with NNFMP; 04 Apr 2013 17:54:55 -0000 Received: from [98.137.12.209] by tm4.bullet.mail.gq1.yahoo.com with NNFMP; 04 Apr 2013 17:54:55 -0000 Received: from [127.0.0.1] by omp1017.mail.gq1.yahoo.com with NNFMP; 04 Apr 2013 17:54:55 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 214351.56290.bm@omp1017.mail.gq1.yahoo.com Received: (qmail 52268 invoked by uid 60001); 4 Apr 2013 17:54:55 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1365098095; bh=NhQ6sUW0ziMWVCw+mJvfDCXGGuQ5JN2bnN9Cg2vncsU=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=PYoRnXoGbEPBT9EBYUENuJmVX6Yr/HJh+dnWJzydvzl6SwhGEaOT+l6B8Ll6vHMaIxqd+5LJ5ZdVXVwAVr0mTNOnOz5N/a6cRs5NTwZ01ScSQ6wdGlikBX0mfZX9i+g0+tXvrhydnfOuiiCIAWOvFtJcRUIXkdSz7UxOHL3cLXM= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=QIxC07KO9ZWwknBdUkkf1BYPESwxse9cvgJg/IGxN8RPnjaOu3djFfu3oZdR5YqCiLilVIvQGKK3DXLFmkrJmt2Z/nsfUwqvlqVT7k8ek0pVn3GqyQAwGAUquqbIfwYysiZDksIhw83VysCynU7fmFi3Ybyq4r3FvljVrVerYwo=; X-YMail-OSG: I6Kn4NIVM1kUUyJBnVV5_f7kkrPbbM250GRm_4KMYwNhSpu Aw.o8jKCwgus5TDlP98.syv749K1ejLvWKVGtNzOuegORq2E_.FErG6d2H_L T1_MiLa.p.JgAkgziAQ4kqP9pmA1mxDBNSQMVV7YLHIRFSpx1Yr1QBQmOoqm BCKsv5fe01jl8h_newT9.ITguCeUXhr341YoFhjSgWWyzdKM9_4zDqZMG5lA Qjj8tKgSE5lpWnSU.lyfCy4.Nhg2p6BjkcCREsUTbXRBak30TbL0xG3Dujys Vv2_LduVp6DJFsKmK9pu8h6UlErx5CTOEmQnifTAvXULrKRKy0Pf2PFPY0XT AK.OkHjIwrvfNo2xd4K5LvFFQt9NtWLC4z.XqIBUxbwBxi3d8GDGmVVibiLg iOmDdfkZfsKJIXOmv05d5H4Y5ler.aZOq6fJX7jBOXB5PzIdJ0CCpSbutiPF IJjfcpfC6zuuxVnd.xjgVgO1X09dRe1IXstD17h.tP6wFc1VStQCHZlT05na aJiinEyiOpZlgzfj3KLcg4FfuBF1Iu4clFVxszj.pJa9.dla9qal99FNkUPQ I1u3wH8jD4LJkNWFxLW_k9xtJGQ9g0QIb.cGd831w9Iu_gpMg_yy7GVitz8k MJ_ZizuNwQydtd3Ou Received: from [223.186.255.182] by web163906.mail.gq1.yahoo.com via HTTP; Thu, 04 Apr 2013 10:54:54 PDT X-Rocket-MIMEInfo: 002.001, SGkgTXIuIFdvbGZlLApUaGFua3MgZm9yIHlvdXIgcmVzcG9uc2UuIEkgaGF2ZSByZWNlaXZlZCBlLW1haWxzIGZyb20gc2V2ZXJhbCBvdGhlcnMuIFRoYW5rcyBmb3IgZXZlcnlvbmUgYW5kIGFwcHJlY2lhdGUgeW91ciBoZWxwLgoKTXIuIFdvbGZlIHNlbnQgYSBzYW1wbGUgY29kZS4gSSB0b29rIHRoYXQgYXMgdGhlIGJhc2UgYW5kIGRlYnVnZ2VkIG15IGNvZGUgYW5kIGlkZW50aWZpZWQgdGhlIGlzc3VlLiBOb3cgbXkgY29kZSBpcyB3b3JraW5nIGZpbmUuIEkgaGF2ZSBpZGVudGlmaWVkIHRoZSByZWFsIGMBMAEBAQE- X-Mailer: YahooMailWebService/0.8.140.532 References: <1365048506.78628.YahooMailNeo@web163902.mail.gq1.yahoo.com> <1365060506.15519.140661213164229.7F345F5D@webmail.messagingengine.com> Message-ID: <1365098094.12263.YahooMailNeo@web163906.mail.gq1.yahoo.com> Date: Thu, 4 Apr 2013 10:54:54 -0700 (PDT) From: Kaleeswaran Velu Reply-To: Kaleeswaran Velu Subject: Re: Postgres trigger issue with update statement in it. To: Wolfe Whalen Cc: Postgres SQL List In-Reply-To: <1365060506.15519.140661213164229.7F345F5D@webmail.messagingengine.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="561220625-291372781-1365098094=:12263" X-Pg-Spam-Score: -4.4 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --561220625-291372781-1365098094=:12263 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi Mr. Wolfe,=0AThanks for your response. I have received e-mails from seve= ral others. Thanks for everyone and appreciate your help.=0A=0AMr. Wolfe se= nt a sample code. I took that as the base and debugged my code and identifi= ed the issue. Now my code is working fine. I have identified the real culpr= it.=0A=0AEarlier my trigger was as like=A0=0A=0ACREATE OR REPLACE FUNCTION = fun_update_payments() RETURNS TRIGGER AS $trg_update_payments$=0ADECLARE=0A= BEGIN=0A=0A=A0 =A0 UPDATE jl=0A=A0 =A0 SET jl.outstanding =3D jl.outstandin= g - new.Principle_Amount=0A=A0 =A0 WHERE jl.jl_id=3Dnew.jl_id;=0A=A0 =A0 = =A0 =A0=A0=0A=0A=A0 =A0 RETURN new;=0A=A0 =A0=A0=0A=A0 =A0 EXCEPTION WHEN O= THERS THEN=0A=A0 =A0 =A0 =A0ROLLBACK;=0A=A0 =A0 =A0 =A0RAISE NOTICE 'fun_up= date_payment() Failed...';=0A=A0 =A0=0AEND=0A$trg_update_payments$ LANGUAGE= plpgsql;=0A=0A=0AAfter debugging I found my Update statement is wrong, I s= hould not have prefix as (Oracle accepts this.). I then chang= ed that to as below and stared working.=0A=0A=A0 =A0 UPDATE jl=0A=A0 =A0 SE= T outstanding =3D outstanding - new.Principle_Amount=0A=A0 =A0 WHERE jl_id= =3Dnew.jl_id;=0A=A0=0ASomehow Postgres is not capturing this at the compila= tion time.=A0=0ABut at run time, instead of throwing syntax error, it was t= rowing some transactional error as "ERROR: cannot begin/end transactions in= PL/pgSQL". The reason for that is the EXCEPTION =A0block that I had at the= end.=0A=0AI then removed below block from the trigger, then it was throwin= g expected syntax error at run time.=A0=0A=A0 =A0 EXCEPTION WHEN OTHERS THE= N=0A=A0 =A0 =A0 =A0ROLLBACK;=0A=A0 =A0 =A0 =A0RAISE NOTICE 'fun_update_paym= ent() Failed...';=0A=0AHowever it works now. Again thanks to Mr. Wolfe.=0A= =0AThanks and Regards=0AKaleeswaran Velu=0A=0A=0A__________________________= ______=0A From: Wolfe Whalen =0ATo: Kaleeswaran Velu =0ACc: Postgres SQL List =0ASent= : Thursday, April 4, 2013 12:58 PM=0ASubject: Re: [SQL] Postgres trigger is= sue with update statement in it.=0A =0A=0A =0AHi Kaleeswaran,=0A=0A=A0=0AWe= 're glad to have you on the mailing list. =A0I don't know enough about your= trigger function to know exactly where it's going wrong, but I threw toget= her a quick example that has an insert trigger on a child table that update= s a row on the parent table. =A0I'm hoping this might help. =A0If it doesn'= t help, maybe you could give us a little more information about your functi= on or tables. =A0I'd be happy to help in any way that I can.=0A=0A=A0=0ACRE= ATE TABLE survey_records (=0A=0A=A0 name varchar(100),=0A=0A=A0 obsoleted t= imestamp DEFAULT NULL=0A=0A);=0A=0A=A0=0ACREATE TABLE geo_surveys (=0A=0A= =A0 measurement integer=0A=0A) INHERITS (survey_records);=0A=0A=A0=0ACREATE= OR REPLACE FUNCTION obsolete_old_surveys() RETURNS trigger AS $$=0A=0ABEGI= N=0A=0A=A0 UPDATE survey_records SET obsoleted =3D clock_timestamp()=0A=0A= =A0 =A0 WHERE survey_records.name =3D NEW.name AND survey_records.obsoleted= IS NULL;=0A=0A=A0 RETURN NEW;=0A=0AEND;=0A=0A$$ LANGUAGE plpgsql;=0A=0A=A0= =0ACREATE TRIGGER obsolete_old_surveys_tr=0A=0ABEFORE INSERT ON geo_surveys= =0A=0AFOR EACH ROW EXECUTE PROCEDURE obsolete_old_surveys();=0A=0A=A0=0AINS= ERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 5);=0A= =0AINSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide', 10= );=0A=0AINSERT INTO geo_surveys (name, measurement) VALUES ('Carbon Dioxide= ', 93);=0A=0A=A0=0AYou'd wind up with something like this:=0A=0A=A0=0ASELEC= T * FROM survey_records;=0A=0A=A0 =A0 =A0 name =A0 =A0 =A0| =A0 =A0 =A0 =A0= obsoleted =A0 =A0 =A0 =A0 =A0=0A=0A----------------+----------------------= ------=0A=0A=A0Carbon Dioxide | 2013-04-03 23:59:44.228225=0A=0A=A0Carbon D= ioxide | 2013-04-03 23:59:53.66243=0A=0A=A0Carbon Dioxide |=A0=0A=0A(3 rows= )=0A=0A=A0=0ASELECT * FROM geo_surveys;=0A=0A=A0 =A0 =A0 name =A0 =A0 =A0| = =A0 =A0 =A0 =A0 obsoleted =A0 =A0 =A0 =A0 =A0| measurement=A0=0A=0A--------= --------+----------------------------+-------------=0A=0A=A0Carbon Dioxide = | 2013-04-03 23:59:44.228225 | =A0 =A0 =A0 =A0 =A0 5=0A=0A=A0Carbon Dioxide= | 2013-04-03 23:59:53.66243 =A0| =A0 =A0 =A0 =A0 =A010=0A=0A=A0Carbon Diox= ide | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 = =A0 =A093=0A=0A(3 rows)=0A=0A=A0=0AThe parent survey_records is actually up= dating the child table rows when you do an update. =A0Parent tables can alm= ost seem like a view in that respect. =A0You would have to be a bit careful= if you're going to have an update trigger on a child that updated the pare= nt table. It's easy to wind up with a loop like this:=0A=0A=A0=0AChild: Upd= ate row 1 -> Trigger function -> Update Row 1 on parent=0A=0A->Parent: Let'= s see... =A0Row 1 is contained in this child table, so let's update it ther= e.=0A=0A->Child:=A0Update row 1 -> Trigger function -> Update Row 1 on pare= nt=0A->Parent: Let's see... =A0Row 1 is contained in this child table, so l= et's update it there.=0A=0A... etc etc.=0A=0A=A0=0A=A0=0ABest Regards,=0A= =0A=A0=0AWolfe=0A=A0=0A-- =0A=0AWolfe Whalen=0A=0Awolfe@quios.net=0A=0A=A0= =0A=A0=0A=A0=0AOn Wed, Apr 3, 2013, at 09:08 PM, Kaleeswaran Velu wrote:=0A= =0A=A0=0A>=A0=0A>=A0Hello Friends,=0A>=0A>I am new to=A0Postgres=A0DB. Rece= ntly installed Postgres 9.2.=A0=0A>=0A>Facing an issue with very simple tri= gger, tried to resolve myself by reading documents or google search but no = luck.=0A>=0A>=A0=0A>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 u= pdate statement in it. This update statement should update a respective rec= ord in table A when ever there is any insert/update happen in table B. =A0T= he issue here is where ever I insert/update record in table B, getting an e= rror as below :=0A>=0A>=A0=0A>********** Error **********=0A>=0A>ERROR: can= not begin/end transactions in PL/pgSQL=0A>=0A>SQL state: 0A000=0A>=0A>Hint:= Use a BEGIN block with an EXCEPTION clause instead.=0A>=0A>Context: PL/pgS= QL function func_update_payment() line 53 at SQL statement=0A>=0A>=A0=0A>Li= ne no 53 in the above error message is an update statement. If I comment ou= t the update statement, trigger works fine.=0A>=0A>=A0=0A>=A0=0A>Can anyone= =A0shed=A0some lights on this? Your help is appreciated.=0A>=0A>=A0=0A>Than= ks and Regards=0A>=0A>Kaleeswaran Velu=0A> --561220625-291372781-1365098094=:12263 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
= Hi Mr. Wolfe,
Thanks for your response. I hav= e received e-mails from several others. Thanks for everyone and appreciate = your help.

Mr. W= olfe 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 r= eal culprit.

Earlier my trigger was = as like 

CREATE OR REPLACE FUNCTION fun_update_payments() RE= TURNS TRIGGER AS $trg_update_payments$
DECLARE
BEGIN

    UPDATE j= l
    SET jl.o= utstanding =3D jl.outstanding - new.Principle_Amount
    WHERE jl.jl_id=3Dnew.jl_id;
   =     
    RETURN new;<= /div>
    
    EXCEPTI= ON WHEN OTHERS THEN
       ROLLBACK;
       RAISE NOTICE 'fun_update_payment= () Failed...';
   
END
$trg_update_payments$ LANGUAGE plpgsql;


After debugging I f= ound my Update statement is wrong, I should not have prefix as <table_na= me.> (Oracle accepts this.). I then changed that to as below and stared = working.

    UPDATE jl
    SET ou= tstanding =3D outstanding - new.Principle_Amount
    WHERE jl_id=3Dnew.jl_i= d;
 
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 t= he end.
I = then removed below block from the trigger, then it was throwing expected sy= ntax error at run time. 
    = EXCEPTION WHEN OTHERS THEN
       ROLLBACK;
       RAISE NOTICE 'fun_update_payment= () Failed...';

Ho= wever it works now. Again thanks to Mr. Wolfe.

Thanks and Regards
K= aleeswaran 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.
=0A
=0A=0A=0A=0A=0A
Hi Ka= leeswaran,
=0A
 
=0A
We're glad to have you on t= he mailing list.  I don't know enough about your trigger function to k= now exactly where it's going wrong, but I threw together a quick example th= at 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 yo= u could give us a little more information about your function or tables. &n= bsp;I'd be happy to help in any way that I can.
=0A
 =0A
CREATE TABLE survey_records (
=0A
  name varcha= r(100),
=0A
  obsoleted timestamp DEFAULT NULL
= =0A
);
=0A
 
=0A
CREATE TABLE geo_surveys (<= br>
=0A
  measurement integer
=0A
) INHERITS (su= rvey_records);
=0A
 
=0A
CREATE OR REPLACE FUNCT= ION obsolete_old_surveys() RETURNS trigger AS $$
=0A
BEGIN
=
=0A
  UPDATE survey_records SET obsoleted =3D clock_timestam= p()
=0A
    WHERE survey_records.name =3D NEW.name AND survey_records.obsoleted IS NULL;<= br>
=0A
  RETURN NEW;
=0A
END;
=0A
= $$ LANGUAGE plpgsql;
=0A
 
=0A
CREATE TRIGGER ob= solete_old_surveys_tr
=0A
BEFORE INSERT ON geo_surveys
=0A
FOR EACH ROW EXECUTE PROCEDURE obsolete_old_surveys();
= =0A
 
=0A
INSERT INTO geo_surveys (name, measurement) VAL= UES ('Carbon Dioxide', 5);
=0A
INSERT INTO geo_surveys (name, = measurement) VALUES ('Carbon Dioxide', 10);
=0A
INSERT INTO ge= o_surveys (name, measurement) VALUES ('Carbon Dioxide', 93);
=0A 
=0A
You'd wind up with something like this:
=0A=
 
=0A
SELECT * FROM survey_records;
=0A
&nb= sp;     name      |         ob= soleted          
=0A
---------------= -+----------------------------
=0A
 Carbon Dioxide | 2013= -04-03 23:59:44.228225
=0A
 Carbon Dioxide | 2013-04-03 2= 3:59:53.66243
=0A
 Carbon Dioxide | 
=0A(3 rows)
=0A
 
=0A
SELECT * FROM geo_surveys;<= br>
=0A
      name      |   &nb= sp;     obsoleted          | measurement=  
=0A
----------------+----------------------------+-----= --------
=0A
 Carbon Dioxide | 2013-04-03 23:59:44.228225= |           5
=0A
 Carbon Dioxi= de | 2013-04-03 23:59:53.66243  |          10=
=0A
 Carbon Dioxide |          =                  |   &nb= sp;      93
=0A
(3 rows)
=0A
 = ;
=0A
The parent survey_records is actually updating the child tab= le rows when you do an update.  Parent tables can almost seem like a v= iew in that respect.  You would have to be a bit careful if you're goi= ng to have an update trigger on a child that updated the parent table. It's= easy to wind up with a loop like this:
=0A
 
=0AChild: Update row 1 -> Trigger function -> Update Row 1 on parent
=0A
->Parent: Let's see...  Row 1 is contained in this = child table, so let's update it there.
=0A
->Child: Up= date row 1 -> Trigger function -> Update Row 1 on parent
=0A->Parent: Let's see...  Row 1 is contained in this child table, so= let's update it there.
=0A
... etc etc.
=0A
&nbs= p;
=0A
 
=0A
Best Regards,
=0A
 =0A
Wolfe
=0A
 
=0A
--
=0A
Wolfe Whalen
=0A
wolfe@quios.net
=0A
 
=0A
=0A
 
=0A
 
=0AOn Wed, Apr 3, 2013, at 09:08 PM, Kaleeswaran Velu wrote:
=0A
 
=0A
 
=0A
 Hello Friends,
=0A
I am new to&n= bsp;Postgres DB. Recently installed Postgres 9.2. 
=0A
Facing an issue= with very simple trigger, tried to resolve myself by reading documents or = google search but no luck.
=0A
 
=0A
I have a table A(parent) and table B (child). There is a BEFORE INSE= RT OR UPDATE trigger attached in table B. This trigger has a update stateme= nt in it. This update statement should update a respective record in table = A when ever there=0A is any insert/update happen in table B.  <= span class=3D"yiv1012255988highlight" style=3D"background-color:transparent= ;">The issue here is where ever I insert/update record in table B, getting = an error as below :
=0A
 
=0A
********** Error **********
=0A
ERROR: cannot begin/end tr= ansactions in PL/pgSQL
=0A
SQL state: 0A000
=0A
Hint: Use a BEGIN block with an EXCEPTION clause instead.
=0A<= div style=3D"background-color:transparent;">Context: PL/pgSQL function func= _update_payment() line 53 at SQL statement
=0A
 
=0A
Line no 53 in the above error message is an update statement. If = I comment out the update=0A statement, trigger works fine.
=0A 
=0A
=0A
 
=0A
Can anyone shed some lights on this? Your h= elp is appreciated.
=0A
 = ;
=0A
Thanks and Regards
= =0A
Kaleeswaran Velu
=0A
= =0A
=0A=0A


--561220625-291372781-1365098094=:12263--