Thread: Is function atomic?
If it is not, is it possible to acquire a lock on a row ? how about a lock on a table? Thanks a lot! -- Wei Weng Network Software Engineer KenCast Inc.
Wei Weng writes: > If it is not, is it possible to acquire a lock on a row ? how about a > lock on a table? SQL statements issued by server-side functions operate with the same transaction isolation semantics as "normal" SQL statements. However, whatever the function does outside the database may obviously have different semantics. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Wei Weng writes: > Does that mean if I used > DECLARE > ... > BEGIN > DO_STUFF > END; > > the DO_STUFF will not be interrupted (maintain atomicity) even when > multiple threads use the function concurrently? Interruption, atomicity, and concurrency are separate issues. The function could of course be interrupted if there's an error. (That error may be related to concurrency, such as a serialization failure.) The database interactions of the function will be atomic in the sense that rollback will work. Concurrent execution of a function is permitted, but there may be issues if you modify global state or there is a serialization failure. These are the same issues that you have to deal with in any programming environment. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Does that mean if I used DECLARE ... BEGIN DO_STUFF END; the DO_STUFF will not be interrupted (maintain atomicity) even when multiple threads use the function concurrently? On 06 Jul 2001 18:13:37 +0200, Peter Eisentraut wrote: > Wei Weng writes: > > > If it is not, is it possible to acquire a lock on a row ? how about a > > lock on a table? > > SQL statements issued by server-side functions operate with the same > transaction isolation semantics as "normal" SQL statements. However, > whatever the function does outside the database may obviously have > different semantics. > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter > > -- Wei Weng Network Software Engineer KenCast Inc.
From: "Wei Weng" <wweng@kencast.com> > If it is not, is it possible to acquire a lock on a row ? how about a > lock on a table? All functions take place within a transaction, but since PG doesn't support nested transactions yet you can't roll back the effects of a nested function. - Richard Huxton
What I wanted is simple: I don't want other processes that run the function at the same time have read/write access from the tables this function accesses(select and insert/update). Is it possible, say, let other proccess blocked on this function? On 06 Jul 2001 18:49:49 +0200, Peter Eisentraut wrote: > Wei Weng writes: > > > Does that mean if I used > > DECLARE > > ... > > BEGIN > > DO_STUFF > > END; > > > > the DO_STUFF will not be interrupted (maintain atomicity) even when > > multiple threads use the function concurrently? > > Interruption, atomicity, and concurrency are separate issues. The > function could of course be interrupted if there's an error. (That error > may be related to concurrency, such as a serialization failure.) The > database interactions of the function will be atomic in the sense that > rollback will work. Concurrent execution of a function is permitted, but > there may be issues if you modify global state or there is a serialization > failure. These are the same issues that you have to deal with in any > programming environment. > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Wei Weng Network Software Engineer KenCast Inc.
Wei Weng writes: > What I wanted is simple: > I don't want other processes that run the function at the same time have > read/write access from the tables this function accesses(select and > insert/update). Is it possible, say, let other proccess blocked on this > function? See LOCK command. But note that this would lock out process in this function or any other access to these tables. Maybe you should take a look at the userlock module in contrib. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Richard Huxton writes: > All functions take place within a transaction, but since PG doesn't > support nested transactions yet you can't roll back the effects of a > nested function. Do you have any idea when it will? -- John Hasler john@dhh.gt.org (John Hasler) Dancing Horse Hill Elmwood, WI
From: "John Hasler" <john@dhh.gt.org> > Richard Huxton writes: > > All functions take place within a transaction, but since PG doesn't > > support nested transactions yet you can't roll back the effects of a > > nested function. > > Do you have any idea when it will? Check the "todo" list in the developers' area on the website - that'll show what's planned for 7.2 Might be more hands available now though, with the RedHat announcement. - Richard Huxton
I wrote: > Do you have any idea when [nested transactions] will [be added]? Richard Huxton writes: > Check the "todo" list in the developers' area on the website - that'll > show what's planned for 7.2 It's listed there: that's why I asked. Is everything on that list planned for 7.2? -- John Hasler john@dhh.gt.org Dancing Horse Hill Elmwood, Wisconsin
John Hasler writes: > > Do you have any idea when [nested transactions] will [be added]? > > Richard Huxton writes: > > Check the "todo" list in the developers' area on the website - that'll > > show what's planned for 7.2 > > It's listed there: that's why I asked. Is everything on that list planned > for 7.2? No. The TODO list is just a list of random ideas, some better than others. Most seasoned developers have their own private lists of things that they would like to get done. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter