Обсуждение: commit within function
Is there a way to issue a commit within a function and continue processing? I know this is not possible in 8.1..
We have long running function calls...parts of which are independent of each other. The independent functions are called within a "main" outer function. We would like to commit the data modified by each function as a single unit so that the data is freed up for other modules to act on.
Are there any future postgres releases (we use 8.1.9) that possible address this.
We have long running function calls...parts of which are independent of each other. The independent functions are called within a "main" outer function. We would like to commit the data modified by each function as a single unit so that the data is freed up for other modules to act on.
Are there any future postgres releases (we use 8.1.9) that possible address this.
On Wed, Jul 15, 2009 at 3:58 PM, Anj Adu<fotographs@gmail.com> wrote: > Is there a way to issue a commit within a function and continue processing? > I know this is not possible in 8.1.. > > We have long running function calls...parts of which are independent of each > other. The independent functions are called within a "main" outer function. > We would like to commit the data modified by each function as a single unit > so that the data is freed up for other modules to act on. > > Are there any future postgres releases (we use 8.1.9) that possible address > this. What you are looking for are savepoints, most likely.
So..are you implying that we fake an error to force the commit on a savepoint..and just ignore the error and move on..if that works..i guess it will work for me.
On Wed, Jul 15, 2009 at 5:49 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
So, did this not work either?
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
I had just figured that that hadn't worked, so you were looking for
something else.--
On Wed, Jul 15, 2009 at 4:32 PM, Anj Adu<fotographs@gmail.com> wrote:
> No
>
> Savepoints allow a transaction to be committed upto a savepoint "if"
> somthing bad happens after savepoint..but I want an explicit commit even if
> nothing bad happens later..i.e.I need to commit "when" I want to. (just like
> Oracle PLSQL)
>
> On Wed, Jul 15, 2009 at 3:01 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Wed, Jul 15, 2009 at 3:58 PM, Anj Adu<fotographs@gmail.com> wrote:
>> > Is there a way to issue a commit within a function and continue
>> > processing?
>> > I know this is not possible in 8.1..
>> >
>> > We have long running function calls...parts of which are independent of
>> > each
>> > other. The independent functions are called within a "main" outer
>> > function.
>> > We would like to commit the data modified by each function as a single
>> > unit
>> > so that the data is freed up for other modules to act on.
>> >
>> > Are there any future postgres releases (we use 8.1.9) that possible
>> > address
>> > this.
>>
>> What you are looking for are savepoints, most likely.
>
>
When fascism comes to America, it will be intolerance sold as diversity.
Hard to say, since you really haven't told us what you're doing in your transaction. Often the standard error trapping is the simplest way to do that. On Wed, Jul 15, 2009 at 7:36 PM, Anj Adu<fotographs@gmail.com> wrote: > So..are you implying that we fake an error to force the commit on a > savepoint..and just ignore the error and move on..if that works..i guess it > will work for me. > > On Wed, Jul 15, 2009 at 5:49 PM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> So, did this not work either? >> >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING >> >> I had just figured that that hadn't worked, so you were looking for >> something else. >> >> On Wed, Jul 15, 2009 at 4:32 PM, Anj Adu<fotographs@gmail.com> wrote: >> > No >> > >> > Savepoints allow a transaction to be committed upto a savepoint "if" >> > somthing bad happens after savepoint..but I want an explicit commit even >> > if >> > nothing bad happens later..i.e.I need to commit "when" I want to. (just >> > like >> > Oracle PLSQL) >> > >> > On Wed, Jul 15, 2009 at 3:01 PM, Scott Marlowe <scott.marlowe@gmail.com> >> > wrote: >> >> >> >> On Wed, Jul 15, 2009 at 3:58 PM, Anj Adu<fotographs@gmail.com> wrote: >> >> > Is there a way to issue a commit within a function and continue >> >> > processing? >> >> > I know this is not possible in 8.1.. >> >> > >> >> > We have long running function calls...parts of which are independent >> >> > of >> >> > each >> >> > other. The independent functions are called within a "main" outer >> >> > function. >> >> > We would like to commit the data modified by each function as a >> >> > single >> >> > unit >> >> > so that the data is freed up for other modules to act on. >> >> > >> >> > Are there any future postgres releases (we use 8.1.9) that possible >> >> > address >> >> > this. >> >> >> >> What you are looking for are savepoints, most likely. >> > >> > >> >> >> >> -- >> When fascism comes to America, it will be intolerance sold as diversity. > > -- When fascism comes to America, it will be intolerance sold as diversity.
Oh wait, not both things. I'm saying to use error trapping first to accomplish this. OR if that doesn't work, then look at savepoints. On Wed, Jul 15, 2009 at 7:36 PM, Anj Adu<fotographs@gmail.com> wrote: > So..are you implying that we fake an error to force the commit on a > savepoint..and just ignore the error and move on..if that works..i guess it > will work for me. > > On Wed, Jul 15, 2009 at 5:49 PM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> So, did this not work either? >> >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING >> >> I had just figured that that hadn't worked, so you were looking for >> something else. >> >> On Wed, Jul 15, 2009 at 4:32 PM, Anj Adu<fotographs@gmail.com> wrote: >> > No >> > >> > Savepoints allow a transaction to be committed upto a savepoint "if" >> > somthing bad happens after savepoint..but I want an explicit commit even >> > if >> > nothing bad happens later..i.e.I need to commit "when" I want to. (just >> > like >> > Oracle PLSQL) >> > >> > On Wed, Jul 15, 2009 at 3:01 PM, Scott Marlowe <scott.marlowe@gmail.com> >> > wrote: >> >> >> >> On Wed, Jul 15, 2009 at 3:58 PM, Anj Adu<fotographs@gmail.com> wrote: >> >> > Is there a way to issue a commit within a function and continue >> >> > processing? >> >> > I know this is not possible in 8.1.. >> >> > >> >> > We have long running function calls...parts of which are independent >> >> > of >> >> > each >> >> > other. The independent functions are called within a "main" outer >> >> > function. >> >> > We would like to commit the data modified by each function as a >> >> > single >> >> > unit >> >> > so that the data is freed up for other modules to act on. >> >> > >> >> > Are there any future postgres releases (we use 8.1.9) that possible >> >> > address >> >> > this. >> >> >> >> What you are looking for are savepoints, most likely. >> > >> > >> >> >> >> -- >> When fascism comes to America, it will be intolerance sold as diversity. > > -- When fascism comes to America, it will be intolerance sold as diversity.
Oops, I just noticed you took us off list. Don't do that, others might have better ideas than me on this. On Thu, Jul 16, 2009 at 7:59 AM, Anj Adu<fotographs@gmail.com> wrote: > 1. exec Func A > 2. call Func B > 3. Call Func C > > Func A is the outermost control function. > Function B inserts into a set of tables say TableX and TableY > > The inserts into TableX and TableY in Function B are needed before FuncC can > execute. However..if Func C fails..the data in TableX and TableY need not be > rolled back as they are reference tables. We do not want to repeat the > inserts into tableX and tableY if the entire process fails downstream for > some reason. > > Currently..I use PL/Java to simulate an autonomous transaction. But it is a > kludge and PL/Java support for newer JVMs and newer Postgres versions is a > pain to troubleshoot as support is limited. > > > On Wed, Jul 15, 2009 at 10:59 PM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Wed, Jul 15, 2009 at 10:48 PM, Anj Adu<fotographs@gmail.com> wrote: >> > We have function A calling function B...then function A goes on to do >> > other >> > things (call function C,D) >> > >> > The updates/inserts in function B have to be committed for us to achieve >> > scalability in the process that function A accomplishes (i.e have >> > multiple >> > processes calling the A function run in parallel) >> > >> > Hence...the question on how to commit. >> >> So, are you saying they have to commit, even if they break unique >> constraints, or FKs and such? Or they have to be skipped? I'm not >> sure what you're trying to do still. Maybe some pseudo code that lays >> out what the path is you want to follow on these inserts / updates. > > -- When fascism comes to America, it will be intolerance sold as diversity.
On Thu, Jul 16, 2009 at 11:30 AM, Anj Adu<fotographs@gmail.com> wrote: > I need an explicit commit...i.e i need func B's results to be > available to other transactions when I enter func C > > So...what you are saying is..in Func c > > BEGIN > simulate error > EXCEPTION > WHEN condition [ OR condition ... ] THEN > do nothing; > END; > > Will this cause the transaction to be committed upto the savepoint in > the beginning of function C ? The transaction will be committed or not as a whole. That's how transactions work. If you need part of the data so far processed committed then you need to commit the transaction and start a new one.