How do I use the pltcu unknown module to auto-load a return value from a
select query? I'm trying to boost performance in our databases by
preloading
certain frequently-used variables. Ultimately I'd like to auto-load
prepared queries this way too.

I've figured out how to use pltcu to auto-load a global variable and access
that variable by appending the following lines to unknown.pltcl:
global var1
set var1 abc
Then, in a pltcu procdure, I can access it:
create function get_var1() returns varchar as '
global var1
return $var1
' language 'pltcu';
(returns abc)

What I'd like to do is something like the following in unknown.pltcl:
global ret
spi_exec "select col1 from tablename where col2 = ''xyz''"
set ret $col1
and return the global $ret in pltcu functions.

When I try use spi_exec in unknown.pltcu, I get an error when loading the
module:
New/updated unknown: too many nested calls to Tcl_EvalObj (infinite
loop?)
Abort

I've tried a few other variations, all without success. Any suggestions?
(I'm
using pltcu because I need a trigger function that can write to system
files, and I don't think that can be done with plperl or plpython.
Performance
boosting through hardware upgrades is not a viable option because the
databases are on tablets running Linux with 245 MB ram and damnably limited
battery life.)

Craig Addleman
DBA
www.sharechive.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

It seems that something you added to the unknown handler triggers it to
be called again. Might be that spi_exec isn't defined at the time that
script is evaluated.


Jan


Craig Addleman wrote:[color=blue]
> How do I use the pltcu unknown module to auto-load a return value from a
> select query? I'm trying to boost performance in our databases by
> preloading
> certain frequently-used variables. Ultimately I'd like to auto-load
> prepared queries this way too.
>
> I've figured out how to use pltcu to auto-load a global variable and access
> that variable by appending the following lines to unknown.pltcl:
> global var1
> set var1 abc
> Then, in a pltcu procdure, I can access it:
> create function get_var1() returns varchar as '
> global var1
> return $var1
> ' language 'pltcu';
> (returns abc)
>
> What I'd like to do is something like the following in unknown.pltcl:
> global ret
> spi_exec "select col1 from tablename where col2 = ''xyz''"
> set ret $col1
> and return the global $ret in pltcu functions.
>
> When I try use spi_exec in unknown.pltcu, I get an error when loading the
> module:
> New/updated unknown: too many nested calls to Tcl_EvalObj (infinite
> loop?)
> Abort
>
> I've tried a few other variations, all without success. Any suggestions?
> (I'm
> using pltcu because I need a trigger function that can write to system
> files, and I don't think that can be done with plperl or plpython.
> Performance
> boosting through hardware upgrades is not a viable option because the
> databases are on tablets running Linux with 245 MB ram and damnably limited
> battery life.)
>
> Craig Addleman
> DBA
> www.sharechive.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster[/color]


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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend