Thread: Is function atomic?

Is function atomic?

From
Wei Weng
Date:
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.




Re: Is function atomic?

From
Peter Eisentraut
Date:
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



Re: Is function atomic?

From
Peter Eisentraut
Date:
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



Re: Is function atomic?

From
Wei Weng
Date:
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.




Re: Is function atomic?

From
"Richard Huxton"
Date:
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



Re: Is function atomic?

From
Wei Weng
Date:
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.




Re: Is function atomic?

From
Peter Eisentraut
Date:
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



Re: Is function atomic?

From
John Hasler
Date:
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


Re: Is function atomic?

From
"Richard Huxton"
Date:
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



Re: Is function atomic?

From
John Hasler
Date:
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


Re: Is function atomic?

From
Peter Eisentraut
Date:
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