Thread: Pl/tcl auto-load problem
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
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: > 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's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Thanks for the response Jan. I was *appending* my code to the unknown
proc; when I *inserted* it, I can can access variables via upvar.
I'm not sure this is the right way to do it though:
proc unknown {proname args} {
upvar #0 __appver p_appver
upvar #0 __PLTcl_unknown_support_plan_modname p_mod
upvar #0 __PLTcl_unknown_support_plan_modsrc p_src
spi_exec [spi_prepare "SELECT appver FROM t_mach_global"]
set p_appver $appver
...unknown.pltcl continues...
}
At this point I can access __appver in another module and subsequently
I can access it from a pltcl stored procedure. I'd like to access
__appver directly from the stored procedure. Also, I don't think
this is "auto-loading", which is my objective: auto-load several
widely-used variables and query plans for our application. I am trying
to minimize table access for a performance gain.
Any help will be greatly appreciated!
Craig Addleman
DBA
www.sharechive.com
-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: Friday, January 30, 2004 9:39 AM
To: Craig Addleman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Pl/tcl auto-load problem
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:
> 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's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #