Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UNeb8-0007SN-N0 for pgsql-sql@arkaria.postgresql.org; Thu, 04 Apr 2013 07:28:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UNeb8-0002fH-5R for pgsql-sql@arkaria.postgresql.org; Thu, 04 Apr 2013 07:28:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UNeb6-0002fB-V2 for pgsql-sql@postgresql.org; Thu, 04 Apr 2013 07:28:37 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UNeay-0005Ej-Gs for pgsql-sql@postgresql.org; Thu, 04 Apr 2013 07:28:35 +0000 Received: from compute5.internal (compute5.nyi.mail.srv.osa [10.202.2.45]) by gateway1.nyi.mail.srv.osa (Postfix) with ESMTP id 6818820B48; Thu, 4 Apr 2013 03:28:26 -0400 (EDT) Received: from web1.nyi.mail.srv.osa ([10.202.2.211]) by compute5.internal (MEProxy); Thu, 04 Apr 2013 03:28:26 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=message-id:from:to:cc:mime-version :content-transfer-encoding:content-type:subject:date:in-reply-to :references; s=smtpout; bh=VipT2v+MNG2A0O0SOe9kIcOLzhM=; b=jntqt cMLW4SmLB114qIF/5FmuClv2r+XVTydFl61lcr1CGeNvp4HqV4UOHAqViwIPMED2 TA3wHByR5PKdocQqZx9FxHjK0QETcasJhTHNWZsVuwIPHMfzV3QrJgVOHT3RLQpb grPYFRKuVrpqBLzvMQJBocrIQ9BI4i7PVFX3OA= Received: by web1.nyi.mail.srv.osa (Postfix, from userid 99) id 403B4B80003; Thu, 4 Apr 2013 03:28:26 -0400 (EDT) Message-Id: <1365060506.15519.140661213164229.7F345F5D@webmail.messagingengine.com> X-Sasl-Enc: xJiPo5H+GmFtgulajOixZ/wv3MzHzOqpCFEqlGv8kuRM 1365060506 From: Wolfe Whalen To: Kaleeswaran Velu Cc: Postgres SQL List MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary="_----------=_1365060506155190"; charset="utf-8" X-Mailer: MessagingEngine.com Webmail Interface - ajax-f4d0590a Subject: Re: Postgres trigger issue with update statement in it. Date: Thu, 04 Apr 2013 00:28:26 -0700 In-Reply-To: <1365048506.78628.YahooMailNeo@web163902.mail.gq1.yahoo.com> References: <1365048506.78628.YahooMailNeo@web163902.mail.gq1.yahoo.com> X-Pg-Spam-Score: -2.6 (--) 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 This is a multi-part message in MIME format. --_----------=_1365060506155190 Content-Transfer-Encoding: 7bit Content-Type: text/plain 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 --_----------=_1365060506155190 Content-Transfer-Encoding: 7bit Content-Type: text/html
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
--_----------=_1365060506155190--