I know that I can use fcinfo->flinfo->fn_extra to determine if a 
particular function has been previously called within a given SQL 
statement execution, but what is the best way to determine that the 
current statement has or has not changed?

Specifically I'd like to reset a variable within my PL/R interpreter 
each time a new SQL statement begins.

Joe


Thu, 24 Jul 2003 16:41:39 -0700
Joe Conway <mail@joeconway.com>
best way to determine start of new statement within a function?
Joe Conway <mail@joeconway.com> writes:
> Specifically I'd like to reset a variable within my PL/R interpreter 
> each time a new SQL statement begins.

Define "new SQL statement".  In particular, what of a PL function
executing multiple SQL statements inside an outer SQL statement that
invoked the function?  Unless you've got a clear idea of the semantics
you want, it's not going to be very profitable to discuss
implementations ...

			regards, tom lane

Fri, 25 Jul 2003 00:53:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: best way to determine start of new statement within a function? 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>Specifically I'd like to reset a variable within my PL/R interpreter 
>>each time a new SQL statement begins.
> 
> Define "new SQL statement".  In particular, what of a PL function
> executing multiple SQL statements inside an outer SQL statement that
> invoked the function?  Unless you've got a clear idea of the semantics
> you want, it's not going to be very profitable to discuss
> implementations ...
> 

Well, I guess in a situation like this:
a)  SELECT * FROM myfunc1()...
where myfunc1() executes via spi
b)      SELECT * FROM myfunc2()...

I'd be interested in being able to detect when outer statement (a) 
starts, as well as the fact that it is at nesting level 0. And similarly 
it would be nice to be able to detect when statement (b) starts along 
with the fact that it is at nesting level 1. But for the moment, I'd be 
happy with just the former.

Joe


Fri, 25 Jul 2003 08:31:14 -0700
Joe Conway <mail@joeconway.com>
Re: best way to determine start of new statement within
The semantics of this conceptually is not too
bad.  The implementation could be tricky.

For any given DML or sub-DML (select, eg) the scope
should be for that DML. The DML is the "parent"
of the function.  The DML is the statement 
context and the function is the function 
context.  

statement --> function
					|
					+ --> statement --> function
												|
												+ --> statement
statement --> function1, function2
					|				|
					|				+--> statement -> function3
					|											|
					|											+--> statement
					|
					|
					|
					+ --> statement --> function4
												|
												+ --> statement

For example:

select myfunc() from foo; -- 1 statement

select myfunc() from  (select myfunc2() from foo);
	-- subselect ( select ): both actually
		have the same memory duration, but have different
		statement contexts and different function contexts.
	-- the scope of each matches its select

select myfunc() from foo; 
	--where myfunc invokes select myfunc2() from bar;
The scope of myfunc is the parent select.
The scope of myfunc2 resets at each iteration of
myfunc(), but is available for each iteration of
itself.  Note that these things can nest very
deeply.

select myfunc() from foo where myfunc3() = 't';
	-- both myfunc() and myfunc3() have the same
		statement parent.
	-- if myfunc3() invoked select * from myfunc4()
		then each iteration of myfunc3() resets the
		statement context of that select.

The new statement corresponds to a single invocation
of one DML. Memory should last through the whole
invocation loop.

Am I making sense here?  

Where does the function context info memory get
available for allocation and where does it
get deallocated?  (It does get deallocated
via garbage collection, right?) I think the scope
of this is correct.

We implemented this at informix and got a lot of
stuff wrong.  Memory pool hell, but we had to deal
with a multi-threaded server.  Illustra did
it right so I think postgres already has the handles to
the hook the memory into the right scope.

Do you want me to try to write this up into
more formal definitions?

elein

On Fri, Jul 25, 2003 at 08:31:14AM -0700, Joe Conway wrote:
> Tom Lane wrote:
> >Joe Conway <mail@joeconway.com> writes:
> >>Specifically I'd like to reset a variable within my PL/R interpreter 
> >>each time a new SQL statement begins.
> >
> >Define "new SQL statement".  In particular, what of a PL function
> >executing multiple SQL statements inside an outer SQL statement that
> >invoked the function?  Unless you've got a clear idea of the semantics
> >you want, it's not going to be very profitable to discuss
> >implementations ...
> >
> 
> Well, I guess in a situation like this:
> a)  SELECT * FROM myfunc1()...
> where myfunc1() executes via spi
> b)      SELECT * FROM myfunc2()...
> 
> I'd be interested in being able to detect when outer statement (a) 
> starts, as well as the fact that it is at nesting level 0. And similarly 
> it would be nice to be able to detect when statement (b) starts along 
> with the fact that it is at nesting level 1. But for the moment, I'd be 
> happy with just the former.
> 
> Joe
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

Fri, 25 Jul 2003 10:19:02 -0700
elein <elein@varlena.com>
Re: best way to determine start of new statement within
The semantics of this conceptually is not too
bad.  The implementation could be tricky.

For any given DML or sub-DML (select, eg) the scope
should be for that DML. The DML is the "parent"
of the function.  The DML is the statement
context and the function is the function
context.

statement --> function
               |
               + --> statement --> function
                                    |
                                    + --> statement
statement --> function1, function2
               |           |
               |           +--> statement -> function3
               |                                |
               |                                +--> statement
               |
               |
               |
               + --> statement --> function4
                                    |
                                    + --> statement

For example:

select myfunc() from foo; -- 1 statement


select myfunc() from  (select myfunc2() from foo);
   -- subselect ( select ): both actually
      have the same memory duration, but have different
      statement contexts and different function contexts.
   -- the scope of each matches its select

select myfunc() from foo;
   --where myfunc invokes select myfunc2() from bar;
The scope of myfunc is the parent select.
The scope of myfunc2 resets at each iteration of
myfunc(), but is available for each iteration of
itself.  Note that these things can nest very
deeply.

select myfunc() from foo where myfunc3() = 't';
   -- both myfunc() and myfunc3() have the same
      statement parent.
   -- if myfunc3() invoked select * from myfunc4()
      then each iteration of myfunc3() resets the
      statement context of that select.

The new statement corresponds to a single invocation
of one DML. Memory should last through the whole
invocation loop.

Am I making sense here?

Where does the function context info memory get
available for allocation and where does it
get deallocated?  (It does get deallocated
via garbage collection, right?) I think the scope
of this is correct.

We implemented this at informix and got a lot of
stuff wrong.  Memory pool hell, but we had to deal
with a multi-threaded server.  Illustra did
it right so I think postgres already has the handles to
the hook the memory into the right scope.

Do you want me to try to write this up into
more formal definitions?

elein


On Fri, Jul 25, 2003 at 12:53:11AM -0400, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > Specifically I'd like to reset a variable within my PL/R interpreter 
> > each time a new SQL statement begins.
> 
> Define "new SQL statement".  In particular, what of a PL function
> executing multiple SQL statements inside an outer SQL statement that
> invoked the function?  Unless you've got a clear idea of the semantics
> you want, it's not going to be very profitable to discuss
> implementations ...
> 
>    		regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

Fri, 25 Jul 2003 10:24:08 -0700
elein <elein@norcov.com>
Re: best way to determine start of new statement within a function?
	(resend)
elein wrote:
> Do you want me to try to write this up into
> more formal definitions?
> 

I think it would be a useful starting point for future discussions, but 
it goes way beyond what I was looking for at the moment.

Joe



Mon, 28 Jul 2003 09:58:42 -0700
Joe Conway <mail@joeconway.com>
Re: best way to determine start of new statement within
I was starting to wonder if my message got there.
Tom had asked for a clear definition of "what is a
statement" hence the wide description.  

If I wrote this up more formally where would I put
it so we could talk about it when the various issues
come up? Maybe a friend-of-the-court unapproved functional
spec in the dev docs?  Noting, of course, that
this is functional not implementation specifications.

I'm surprised these issues did not come
up with who ever is working on nested transactions
since that is in the same realm.

elein

On Mon, Jul 28, 2003 at 09:58:42AM -0700, Joe Conway wrote:
> elein wrote:
> >Do you want me to try to write this up into
> >more formal definitions?
> >
> 
> I think it would be a useful starting point for future discussions, but 
> it goes way beyond what I was looking for at the moment.
> 
> Joe
> 
> 

Mon, 28 Jul 2003 10:23:57 -0700
elein <elein@varlena.com>
Re: best way to determine start of new statement within a function?
	(resend)