Thread: Recursion and SPI
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? 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.'' 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? Are there any restrictions on when SPI_connect may be called? The executor can presumably call a function at any time during the execution of a plan if the function is used in a WHERE clause. If that function turns around and calls the executor via SPI, are there any possibilities for deadlock? Ian
> 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
"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">
Ian Lance Taylor <ian@airs.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?
Looks to me like it should work.
> 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.''
The documentation could be out of date. Why don't you test it out and
let us know?
regards, tom lane
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
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 insteadelse 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.
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.
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
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 ableto 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 #
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.
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.