Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function Comit

Function Comit

From
"Aspire Something"
Date:
Hi,
 
Permit me to recive your geeky knowledge,
 
==============================================
function func_1  calls other two function func_2 and func_3 i.e
==
<code>
All blah blah for create function func_1() Return as blahblah '
DECLARE
SOME INT := 0 ;
SOME2 INT :=12 ;
BEGIN
PERFORM     func_2();
PERFORM func_3() ;
END ;
some more blah blah libpgsql
</code>
=============================================
In the above code
the succesful completion and execution of the
func_2()
IS
provides data for
func_3()
 
In other words the latest data func_2() provides is accesed by func_3() within func_1()
 
Though it has been found func_3() does not work on the latest data produced by func_2() .
Though on other hand if i exec function seperately (Ouside func_1) func_3 does work on
the latest data of func_2;
 

Please point out any mistake or blunder by me this may be logical .
 
Any input is whole heartedly welcomed
Regards
Aspire
 
======= My Sys Looks as ==========
Red Hat 7.2 Kernel  2.4.7-10,
PHP 4.3.0,
GCC 3.02,
Apache 1.3.27 ,
Postgresql 7.3.2,
Intel arch i686    .
================================

Re: Function Comit

From
Josh Berkus
Date:
Aspire,

> Permit me to recive your geeky knowledge,

<grin>  Can I quote that?

> Though it has been found func_3() does not work on the latest data produced
> by func_2() . Though on other hand if i exec function seperately (Ouside
> func_1) func_3 does work on the latest data of func_2;

Hmmm ... this may be related to a bug which Robert Treat reported a few weeks
ago.  Can you provide a sample set of functions, which is reproducable, so
that I can test this issue?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Function Comit

From
Aspire Something
Date:
Dear Josh,

I dont think it is a bug !!!

Let me explain :

While we are working in the function func_1()
It calls func_2() and data reproduced is accesed by func_3()

diagramaticlly it is something like this

                              _____________________func_1()
                              |
                              |___ func_2()_____
                              |                             |
                              |_______________|__func_3()
                              |
                              |
                                end of func_1)

Here is the catch since func_1()'s block is not finisied any data will not
be reflected / commited  in the database
untill succesfull completion of the func_1()
(This is what my intution say)
If I am wrong please and kindly revert back , mean while i am drafting a
sample set of functions, which is reproducable.

Regards,
V Kashyap




----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Aspire Something" <aspire420@hotpop.com>; "pgsql-novice"
<pgsql-novice@postgresql.org>
Sent: Thursday, February 27, 2003 10:45 PM
Subject: Re: [NOVICE] Function Comit


> Aspire,
>
> > Permit me to recive your geeky knowledge,
>
> <grin>  Can I quote that?
>
> > Though it has been found func_3() does not work on the latest data
produced
> > by func_2() . Though on other hand if i exec function seperately (Ouside
> > func_1) func_3 does work on the latest data of func_2;
>
> Hmmm ... this may be related to a bug which Robert Treat reported a few
weeks
> ago.  Can you provide a sample set of functions, which is reproducable, so
> that I can test this issue?
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

Re: Function Comit

From
"Josh Berkus"
Date:
Aspire,

> While we are working in the function func_1()
> It calls func_2() and data reproduced is accesed by func_3()
>
> diagramaticlly it is something like this
>
>                               _____________________func_1()
>                               |
>                               |___ func_2()_____
>                               |                             |
>                               |_______________|__func_3()
>                               |
>                               |
>                                 end of func_1)
>
> Here is the catch since func_1()'s block is not finisied any data
> will not
> be reflected / commited  in the database
> untill succesfull completion of the func_1()

But it is supposed to be visible.  Function2() and Function3() are
child transactions of Function1().   As such, any changes made be
Function1() ro Function2() should be visible to Function3(), even
though they are not visible to any other connection until Function1()
commits.  That's how MVCC works.

However, there have been some problems with this visibility issue in
long-running PL/pgSQL functions before.  So I'd like to see if you've
discovered a new problem; if I can reproduce it, I'll post it to BUGS.

-Josh

Re: Function Comit

From
Tom Lane
Date:
"Josh Berkus" <josh@agliodbs.com> writes:
> However, there have been some problems with this visibility issue in
> long-running PL/pgSQL functions before.  So I'd like to see if you've
> discovered a new problem; if I can reproduce it, I'll post it to BUGS.

Since these functions are all executing within the same transaction,
it's not really a transactional issue.  What it could be is a question
of where CommandCounterIncrement() gets done --- that has to happen
between func2() and func3() else func2's changes won't be considered
done yet.  I'd expect that to happen in most cases, but with a
sufficiently weird flow of control maybe it doesn't happen?  Anyway,
let's see the test case.

            regards, tom lane