Re: Pl/tcl auto-load problem - Mailing list pgsql-general

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 #