X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9F28ED1CCAE for ; Sun, 25 Jan 2004 20:21:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 38731-08 for ; Sun, 25 Jan 2004 16:21:05 -0400 (AST) Received: from gw02.mail.saunalahti.fi (gw02.mail.saunalahti.fi [195.197.172.116]) by svr1.postgresql.org (Postfix) with ESMTP id 3A89FD1CC84 for ; Sun, 25 Jan 2004 16:21:03 -0400 (AST) Received: from [10.0.0.19] (crash.claymountain.com [62.142.127.138]) by gw02.mail.saunalahti.fi (Postfix) with ESMTP id 52CC7EE935E for ; Sun, 25 Jan 2004 22:21:03 +0200 (EET) Subject: Procedures, triggers and transactions... From: Mauri Sahlberg To: "'pgsql-admin@postgresql.org'" Content-Type: text/plain Organization: Claymountain Solutions Oy Message-Id: <1075062062.3217.29.camel@fault> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Date: Sun, 25 Jan 2004 22:21:02 +0200 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/203 X-Sequence-Number: 12066 Hi, After a long period of developing we have managed to conceive several huge procedures that take forever to run and make up absolutely too large transactions. The framework is that a c++ application transforms xml-input data to sql-procedure calls with appropriate parameters who in turn call another procedures who in turn call another procedures... And as all procedures are run in the transaction of the outermost select (in this case the c++ application's select that called the initial procedure) we have huge problems with concurrency. There of course can be any number of instances of that c++-application running. We tried another approach that propagates calculation and invocations of other procedures through triggers but this probably will not help us either as all procedures are still in the same transaction and no locks will be released until the outermost select that caused the chain has ended? Our current working hypothesis is to build a procedure that writes procedure calls to a table and then let the c++-application to go through that table and call the necessary procedures one by one - each procedure in it's own transaction. Any comments or suggestions? Regards, -- Mauri Sahlberg Claymountain Solutions Oy