Thread: Partial commit within the trasaction
Hello, I have read that 7.x version had a model "all or nothing" in transactions. Thus I have upgraded to version 8 and would like to do the following: plpgsq code does time intensive data manipulation + database vacuuming. This data manipulation is splitted logically into several steps. After each step I would like to give a message to the status table, what the procedure is performing. Currently I pass the information to the table via insert, but this is also the limitation. I would like to allow another user see the progress of the current pgplsql procedure, but no insert is commited, till procedure ends. How this can be solved? Regards, Bohdan
On Thu, Sep 08, 2005 at 01:23:56PM +0200, Bohdan Linda wrote: > plpgsq code does time intensive data manipulation + database vacuuming. > This data manipulation is splitted logically into several steps. After > each step I would like to give a message to the status table, what the > procedure is performing. > > Currently I pass the information to the table via insert, but this is also > the limitation. I would like to allow another user see the progress of the > current pgplsql procedure, but no insert is commited, till procedure ends. > > How this can be solved? One way would be to use contrib/dblink to open another connection to the database so the status messages could be inserted in a separate transaction. -- Michael Fuhr
On Thu, Sep 08, 2005 at 02:53:47PM +0200, Michael Fuhr wrote: > One way would be to use contrib/dblink to open another connection > to the database so the status messages could be inserted in a > separate transaction. This could do the trick for logging, even writting of a package that would do all the stuff should not be hard. But what if you want to flush something processed to db. Consider you are doing massive updates/deletes. Again in logical blocks. You as a programmer may decide: "ok so far I am done and even if I crash I want to preserve these changes." It happened me, that db aborted processing such huge updates with out of memory message. Would calling stored procedure from stored procedure solved this? Or if parent procedure is not commited then even called procedure will not commit?
On Thu, Sep 08, 2005 at 03:39:50PM +0200, Bohdan Linda wrote: > On Thu, Sep 08, 2005 at 02:53:47PM +0200, Michael Fuhr wrote: > > One way would be to use contrib/dblink to open another connection > > to the database so the status messages could be inserted in a > > separate transaction. > > This could do the trick for logging, even writting of a package that would > do all the stuff should not be hard. But what if you want to flush > something processed to db. Consider you are doing massive updates/deletes. > Again in logical blocks. You as a programmer may decide: "ok so far I am > done and even if I crash I want to preserve these changes." It happened > me, that db aborted processing such huge updates with out of memory > message. Would calling stored procedure from stored procedure solved > this? Or if parent procedure is not commited then even called procedure > will not commit? Functions are executed in the context of an outer transaction, so if that outer transaction fails then the function's changes will be rolled back. Another implication of this is that functions can't start or commit/rollback transactions because they're already inside a transaction, although in 8.x they can use exception handlers to do partial rollbacks (i.e., functions can use savepoints, albeit not directly with a SAVEPOINT statement). If you want changes to survive a database or system crash then you'll have to commit them. Since server-side functions can't start or commit transactions, you'll have to do those commits with client code, either from a client application or by using dblink or something similar from a server-side function, effectively making the server-side function a client application. This might not be what you're after, but 8.1 will have two-phase commit, which is a way to tell the database "get ready to commit, but don't actually do it until I tell you." Those prepared commits will survive a crash, so after you recover you can say, "Remember that transaction that you prepared before the crash? Go ahead and commit it now." You have to do some extra bookkeeping and you can't commit several prepared transactions atomically (as far as I know), but that's one way you could make changes durable without actually committing them until later. -- Michael Fuhr
On Thu, Sep 08, 2005 at 04:35:51PM +0200, Michael Fuhr wrote: > On Thu, Sep 08, 2005 at 03:39:50PM +0200, Bohdan Linda wrote: > commit it now." You have to do some extra bookkeeping and you can't > commit several prepared transactions atomically (as far as I know), > but that's one way you could make changes durable without actually > committing them until later. In case of durable transactions, would they be released from memory? Thus could the transaction be more respectfull to the HW when processing too much data? And what about nested transactions? Are they planned? The point is connected to my previous question of the secured access to stored procedures. If I move part of database logic to the client, I will have to introduce parameters to the procedures. This may be potentialy abusable. If I try to use dblink from server to server (both are the same), is there some perfromance penalty? How big? Regards, Bohdan
On Thu, Sep 08, 2005 at 05:35:40PM +0200, Bohdan Linda wrote: > On Thu, Sep 08, 2005 at 04:35:51PM +0200, Michael Fuhr wrote: > > On Thu, Sep 08, 2005 at 03:39:50PM +0200, Bohdan Linda wrote: > > commit it now." You have to do some extra bookkeeping and you can't > > commit several prepared transactions atomically (as far as I know), > > but that's one way you could make changes durable without actually > > committing them until later. > > In case of durable transactions, would they be released from memory? Thus > could the transaction be more respectfull to the HW when processing too > much data? I'll defer comments on the memory usage of transactions to the developers, but in general transactions shouldn't have memory problems. In an earlier message you said that the "db aborted processing such huge updates with out of memory message" -- can you elaborate on that? What were you doing that you think caused the out of memory error? That sounds like the underlying problem that needs to be solved. > And what about nested transactions? Are they planned? The point is > connected to my previous question of the secured access to stored > procedures. If I move part of database logic to the client, I will have to > introduce parameters to the procedures. This may be potentialy abusable. What exactly do you mean by "nested transactions"? PostgreSQL 8.x has savepoints but I'm guessing you mean something else, like perhaps the ability to begin and end transactions within a function. The developers' TODO list has an item that says "Add capability to create and call PROCEDURES," which might be what you're really after. > If I try to use dblink from server to server (both are the same), is there > some perfromance penalty? How big? That depends on how much you do over the dblink connection. If you execute many statements that each do only a little work then you'll have a lot of overhead. On the other hand, if you execute statements that each do a lot of work then the overhead will be minimal. -- Michael Fuhr