> Does the SPI interface support recursion?  That is, can a function
> use SPI to make a query which involves calling another function which
> uses SPI?

 From http://www.postgresql.org/docs/programmer/spi.htm :

"SPI procedures are always called by some (upper) Executor and the SPI
manager
uses the Executor to run your queries. Other procedures may be called by the
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Executor running queries from your procedure."

> The documentation suggests not, saying that if a function which uses
> SPI calls another function which uses SPI, it won't work, and calling
> that ``bad practice.''

 From http://www.postgresql.org/docs/programmer/spi-spiconnect.htm :

"You may get SPI_ERROR_CONNECT error if SPI_connect is called from an
already
connected procedure - e.g. if you *directly call* one procedure from another
                                  ^^^^^^^^^^^^^^^
connected one. Actually, while the child procedure will be able to use SPI,
your parent procedure will not be able to continue to use SPI after the
child
returns (if SPI_finish is called by the child). It's bad practice."

But you are able to run queries which call SPI functions.

> However, in spi.c I note that there is a stack, and a variable
> _SPI_curid, and the undocumented functions SPI_push and SPI_pop.
> If that works to support recursion, then why does the documentation
> recommend against it?

Afair, there were no SPI_push & SPI_pop originally. Someone added them
but forgot to document.

Vadim

Fri, 5 Jan 2001 12:44:17 -0800 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Recursion and SPI
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:

> > Does the SPI interface support recursion?  That is, can a function
> > use SPI to make a query which involves calling another function which
> > uses SPI?
> 
> >From http://www.postgresql.org/docs/programmer/spi.htm :
> 
> "SPI procedures are always called by some (upper) Executor and the SPI
> manager
> uses the Executor to run your queries. Other procedures may be called by the
>                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Executor running queries from your procedure."
> 
> > The documentation suggests not, saying that if a function which uses
> > SPI calls another function which uses SPI, it won't work, and calling
> > that ``bad practice.''
> 
> >From http://www.postgresql.org/docs/programmer/spi-spiconnect.htm :
> 
> "You may get SPI_ERROR_CONNECT error if SPI_connect is called from an
> already
> connected procedure - e.g. if you *directly call* one procedure from another
>                                   ^^^^^^^^^^^^^^^
> connected one. Actually, while the child procedure will be able to use SPI,
> your parent procedure will not be able to continue to use SPI after the
> child
> returns (if SPI_finish is called by the child). It's bad practice."
> 
> But you are able to run queries which call SPI functions.

Thanks for the clarification.

Approaching the current documentation from a position of ignorance, I
find it ambiguous.  I propose something along the lines of the
following patch to clarify it.  Thanks.

(Alternatively, perhaps the code could maintain a count of nested
calls to SPI_connect/SPI_finish.  But I didn't try to write that
patch.)

Ian

Index: spi.sgml
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/spi.sgml,v
retrieving revision 1.8
diff -u -r1.8 spi.sgml
--- spi.sgml	2000/12/22 18:57:50	1.8
+++ spi.sgml	2001/01/05 22:10:13
@@ -34,10 +34,11 @@
 </Para>
 
 <Para>
-<Acronym>SPI</Acronym> procedures are always called by some (upper) 
-Executor and the <Acronym>SPI</Acronym>
-manager uses the Executor to run your queries. Other procedures may be
-called by the Executor running queries from your procedure.
+Procedures which use <Acronym>SPI</Acronym> are called by the
+Executor.  The <Acronym>SPI</Acronym> calls recursively invoke the
+Executor in turn to run queries.  When the Executor is invoked
+recursively, it may itself call procedures which may make
+<Acronym>SPI</Acronym> calls.
 </Para>
 
 <Para>
@@ -146,12 +147,17 @@
    utility SPI functions may be called from un-connected procedures.
 </PARA>
 <PARA>
-   You may get <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error if <Function>SPI_connect</Function> is
-   called from an already connected procedure - e.g. if you directly call one
-   procedure from another connected one.  Actually, while the child procedure
-   will be able to use SPI, your parent procedure will not be able to continue
-   to use SPI after the child returns (if <Function>SPI_finish</Function> is called by the child).
-   It's bad practice.
+   If your procedure is already connected,
+   <Function>SPI_connect</Function> will return an
+   <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error.  Note that this
+   may happen if a procedure which has called
+   <Function>SPI_connect</Function> directly calls another procedure
+   which itself calls <Function>SPI_connect</Function>.  While
+   recursive calls to the <Acronym>SPI</Acronym> manager are permitted
+   when an <Acronym>SPI</Acronym> query invokes another function which
+   uses <Acronym>SPI</Acronym>, directly nested calls to
+   <Function>SPI_connect</Function> and
+   <Function>SPI_finish</Function> are forbidden.
 </PARA>
 </REFSECT1>
 <REFSECT1 ID="R1-SPI-SPICONNECT-2">

05 Jan 2001 14:13:27 -0800
Ian Lance Taylor <ian@airs.com>
Re: Recursion and SPI
Applied.  Thanks.

> "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> 
> > > Does the SPI interface support recursion?  That is, can a function
> > > use SPI to make a query which involves calling another function which
> > > uses SPI?
> > 
> > >From http://www.postgresql.org/docs/programmer/spi.htm :
> > 
> > "SPI procedures are always called by some (upper) Executor and the SPI
> > manager
> > uses the Executor to run your queries. Other procedures may be called by the
> >                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > Executor running queries from your procedure."
> > 
> > > The documentation suggests not, saying that if a function which uses
> > > SPI calls another function which uses SPI, it won't work, and calling
> > > that ``bad practice.''
> > 
> > >From http://www.postgresql.org/docs/programmer/spi-spiconnect.htm :
> > 
> > "You may get SPI_ERROR_CONNECT error if SPI_connect is called from an
> > already
> > connected procedure - e.g. if you *directly call* one procedure from another
> >                                   ^^^^^^^^^^^^^^^
> > connected one. Actually, while the child procedure will be able to use SPI,
> > your parent procedure will not be able to continue to use SPI after the
> > child
> > returns (if SPI_finish is called by the child). It's bad practice."
> > 
> > But you are able to run queries which call SPI functions.
> 
> Thanks for the clarification.
> 
> Approaching the current documentation from a position of ignorance, I
> find it ambiguous.  I propose something along the lines of the
> following patch to clarify it.  Thanks.
> 
> (Alternatively, perhaps the code could maintain a count of nested
> calls to SPI_connect/SPI_finish.  But I didn't try to write that
> patch.)
> 
> Ian
> 
> Index: spi.sgml
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/spi.sgml,v
> retrieving revision 1.8
> diff -u -r1.8 spi.sgml
> --- spi.sgml	2000/12/22 18:57:50	1.8
> +++ spi.sgml	2001/01/05 22:10:13
> @@ -34,10 +34,11 @@
>  </Para>
>  
>  <Para>
> -<Acronym>SPI</Acronym> procedures are always called by some (upper) 
> -Executor and the <Acronym>SPI</Acronym>
> -manager uses the Executor to run your queries. Other procedures may be
> -called by the Executor running queries from your procedure.
> +Procedures which use <Acronym>SPI</Acronym> are called by the
> +Executor.  The <Acronym>SPI</Acronym> calls recursively invoke the
> +Executor in turn to run queries.  When the Executor is invoked
> +recursively, it may itself call procedures which may make
> +<Acronym>SPI</Acronym> calls.
>  </Para>
>  
>  <Para>
> @@ -146,12 +147,17 @@
>     utility SPI functions may be called from un-connected procedures.
>  </PARA>
>  <PARA>
> -   You may get <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error if <Function>SPI_connect</Function> is
> -   called from an already connected procedure - e.g. if you directly call one
> -   procedure from another connected one.  Actually, while the child procedure
> -   will be able to use SPI, your parent procedure will not be able to continue
> -   to use SPI after the child returns (if <Function>SPI_finish</Function> is called by the child).
> -   It's bad practice.
> +   If your procedure is already connected,
> +   <Function>SPI_connect</Function> will return an
> +   <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error.  Note that this
> +   may happen if a procedure which has called
> +   <Function>SPI_connect</Function> directly calls another procedure
> +   which itself calls <Function>SPI_connect</Function>.  While
> +   recursive calls to the <Acronym>SPI</Acronym> manager are permitted
> +   when an <Acronym>SPI</Acronym> query invokes another function which
> +   uses <Acronym>SPI</Acronym>, directly nested calls to
> +   <Function>SPI_connect</Function> and
> +   <Function>SPI_finish</Function> are forbidden.
>  </PARA>
>  </REFSECT1>
>  <REFSECT1 ID="R1-SPI-SPICONNECT-2">
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 9 Jan 2001 09:23:50 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Re: Recursion and SPI
Hi,

Has anyone any input to offer on adding an arbitrary locking feature?

Where
GETLOCK "string" will lock on "string", the lock being only released at the
end of a transaction.

While the lock is held, other processes trying to do GETLOCK "string" will
block until the lock is released.

This feature can allow applications to better serialize things. For
example: inserting unique records. Cooperating applications could just do
something like:

GETLOCK "mytable.key2=1234";
SELECT count(*) from mytable where key2=1234 for update;
if count==0, insert the stuff.
 elsif count==1 update the stuff instead
 else something is wrong!

The lock will thus only affect applications interested in mytable where
key2=1234

In contrast the current alternatives appear to be either LOCK the entire
table (preventing ALL inserts and selects), or to create a UNIQUE
constraint (forcing complete rollbacks and restarts in event of a collision
:( ).

Any comments, suggestions or tips would be welcome. It looks like quite a
complex thing to do - I've only just started looking at the postgresql
internals and the lock manager.

Cheerio,
Link.






Thu, 11 Jan 2001 15:43:39 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Lock on arbitrary string feature
On Thu, Jan 11, 2001 at 03:43:39PM +0800, Lincoln Yeoh wrote:
> Hi,
> 
> Has anyone any input to offer on adding an arbitrary locking feature?
> 
> Where
> GETLOCK "string" will lock on "string", the lock being only released at the
> end of a transaction.
> 
> While the lock is held, other processes trying to do GETLOCK "string" will
> block until the lock is released.
> 
> This feature can allow applications to better serialize things. For
> example: inserting unique records. Cooperating applications could just do
> something like:
> 
> GETLOCK "mytable.key2=1234";
> SELECT count(*) from mytable where key2=1234 for update;
> if count==0, insert the stuff.
>  elsif count==1 update the stuff instead
>  else something is wrong!
> 
> The lock will thus only affect applications interested in mytable where
> key2=1234

	We do something like this with listen/notify pairs.  To syncronize
two clients, we have them each listen for the other's token string,
send a notify, and then block on select(), checking for incoming
notifications.  When they get the notification, they send a notify back
to the other side to un-block it.

	If anything, it would be nice if there were a way to make a LISTEN
block the connection on a specific event tag, which is essentially what
we are doing in our interface library.

-- 
Adam Haberlach            |A cat spends her life conflicted between a
adam@newsnipple.com       |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500                 |profound desire to avoid getting wet.

Thu, 11 Jan 2001 09:38:41 -0800
Adam Haberlach <adam@newsnipple.com>
Re: Lock on arbitrary string feature
Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Has anyone any input to offer on adding an arbitrary locking feature?

> Where
> GETLOCK "string" will lock on "string", the lock being only released at the
> end of a transaction.

> Any comments, suggestions or tips would be welcome. It looks like quite a
> complex thing to do - I've only just started looking at the postgresql
> internals and the lock manager.

A lock is basically an entry in a shared hash table, so you could
implement this just by having a different kind of key (ie, the given
string) for these sorts of locks.

However, the whole thing strikes me as more of an ugly kluge than a
clean solution to the real problem.  If you're not using a UNIQUE
constraint then you're relying on application logic to guarantee
consistency, which is bad.  If you do have a UNIQUE constraint and
want to layer this sort of application lock on top of it, then you
still have the problem of unexpected failures if some instance/portion
of your application does inserts without remembering to get the
application-level lock.  So, as Vadim remarked, doing the insert and
rolling back to a savepoint on failure would be a much better answer.

BTW, you should consider whether you couldn't use the existing USERLOCK
feature as a short-term alternative.  If you can squeeze the key value
you need to insert into a user lock tag, that will do as well as your
proposed general-string-tag locks.

			regards, tom lane

Thu, 11 Jan 2001 13:26:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Lock on arbitrary string feature 
Tom Lane wrote:
> Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> > Has anyone any input to offer on adding an arbitrary locking feature?
>
> > Where
> > GETLOCK "string" will lock on "string", the lock being only released at the
> > end of a transaction.
>
> > Any comments, suggestions or tips would be welcome. It looks like quite a
> > complex thing to do - I've only just started looking at the postgresql
> > internals and the lock manager.
>
> A lock is basically an entry in a shared hash table, so you could
> implement this just by having a different kind of key (ie, the given
> string) for these sorts of locks.
>
> However, the whole thing strikes me as more of an ugly kluge than a
> clean solution to the real problem.  If you're not using a UNIQUE
> constraint then you're relying on application logic to guarantee
> consistency, which is bad.  If you do have a UNIQUE constraint and
> want to layer this sort of application lock on top of it, then you
> still have the problem of unexpected failures if some instance/portion
> of your application does inserts without remembering to get the
> application-level lock.  So, as Vadim remarked, doing the insert and
> rolling back to a savepoint on failure would be a much better answer.

    You're right that it's ugly, but at least it'd be a temporary
    "solution" for the fact that we cannot  catch  exceptions  in
    triggers  yet.  So  the if/else logic will currently not work
    reliable in a trigger without beeing able to lock before  the
    SELECT.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Thu, 11 Jan 2001 14:57:04 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Lock on arbitrary string feature
At 01:26 PM 11-01-2001 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
>> GETLOCK "string" will lock on "string", the lock being only released at the
>> end of a transaction.
>However, the whole thing strikes me as more of an ugly kluge than a
>clean solution to the real problem.  If you're not using a UNIQUE

But doesn't that go well with SQL :). The joys of INSERT vs UPDATE.

And "select .. for update" too! So far I haven't left out any "for
updates", at least I think so ;). 

I did consider using select for update to simulate it but it doesn't work
when the values are very variable.

>application-level lock.  So, as Vadim remarked, doing the insert and
>rolling back to a savepoint on failure would be a much better answer.

Yep, savepoints will allow better consistency. But a getlock feature can be
very handy in lots of other scenarios.

>BTW, you should consider whether you couldn't use the existing USERLOCK
>feature as a short-term alternative.  If you can squeeze the key value
>you need to insert into a user lock tag, that will do as well as your
>proposed general-string-tag locks.

Looks interesting. Probably what it does is similar enough to what I'm
trying to do. Copy from the best :).

But meantime, back to lock table...

Cheerio,
Link.




Fri, 12 Jan 2001 10:37:39 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Lock on arbitrary string feature 
At 09:38 AM 11-01-2001 -0800, Adam Haberlach wrote:
>	We do something like this with listen/notify pairs.  To syncronize
>two clients, we have them each listen for the other's token string,
>send a notify, and then block on select(), checking for incoming
>notifications.  When they get the notification, they send a notify back
>to the other side to un-block it.
>
>	If anything, it would be nice if there were a way to make a LISTEN
>block the connection on a specific event tag, which is essentially what
>we are doing in our interface library.

Actually what you are talking about is almost an inverse of this locking
thing. One is stop until it's ok to go. The other is stop if it's not ok to
go.

You're looking for a WAIT for "notification" feature :). 

I actually was looking for this too, and I thought I was the only one
interested in this. Wow a 100% increase in interest ;). 

I'm also trying to see how this can be done. It looks a lot easier to do
than the getlock feature. But I can't figure out what to select/wait/snooze
on, when the routine is in the inside looking about (async.c:
Async_Wait(char *relname) yeah oxymoronic I know). Rather than outside
looking in (in which case it's select PQsocket or something like that).
Would like to use as little CPU as possible when waiting -  think of
postgresql on battery powered wearable "servers" + wireless LAN.

Cheerio,
Link.


Fri, 12 Jan 2001 11:08:40 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Lock on arbitrary string feature