preload libraries patch [was: [GENERAL] hexadecimal to decimal] - Mailing list pgsql-patches

Tom Lane wrote:
> It seems entirely sensible to me for the postmaster to choke on invalid
> settings in postgresql.conf.  Better than failing to mention the problem
> at all, anyway.
>
>>2) do you want a patch that exports plperl_init_all() (and I guess
>>similar init functions in pltcl and plpython)?
>
> Yeah, I guess.  Might as well make one in plpgsql too --- even if it
> does nothing today, it might be useful in the future, so the
> documentation ought to recommend "call 'plxxx_init' when preloading plxxx"
> as a general thing.
>

Attached is a patch that:
1) fixes the behavior of preload_libraries
2) adds an exported xxx_init() function to plperl, pltcl, plpython, and
    plpgsql
3) updates the documentation for the changes

Compiles clean, and passes all regression tests with the following line
in postgresql.conf (this probably won't wrap nicely):
preload_libraries =
'$libdir/plperl:plperl_init,$libdir/pltcl:pltcl_init,$libdir/plpython:plpython_init,$libdir/plpgsql:plpgsql_init'

I ran the following both without (one psql session for all four
statements) and with preloading (also all four in one session). The
actual function definitions at the bottom of the email:

without preload:
=====================================================================
regression=# explain analyze select echo_plperl('hello');
  Total runtime: 55.29 msec
regression=# explain analyze select echo_pltcl('hello');
  Total runtime: 23.34 msec
regression=# explain analyze select echo_plpythonu('hello');
  Total runtime: 32.40 msec
regression=# explain analyze select echo_plpgsql('hello');
  Total runtime: 3.09 msec

with preload:
=====================================================================
regression=# explain analyze select echo_plperl('hello');
  Total runtime: 5.14 msec
regression=# explain analyze select echo_pltcl('hello');
  Total runtime: 7.64 msec
regression=# explain analyze select echo_plpythonu('hello');
  Total runtime: 1.91 msec
regression=# explain analyze select echo_plpgsql('hello');
  Total runtime: 1.35 msec

Please apply.

Thanks,

Joe


--test functions
CREATE OR REPLACE FUNCTION echo_plperl(text) RETURNS text AS '
   return $_[0];
' LANGUAGE plperl;

CREATE OR REPLACE FUNCTION echo_pltcl(text) RETURNS text AS '
   return $1
' LANGUAGE pltcl;

CREATE OR REPLACE FUNCTION echo_plpythonu(text) RETURNS text AS '
   return args[0]
' LANGUAGE plpythonu;

CREATE OR REPLACE FUNCTION echo_plpgsql(text) RETURNS text AS '
   begin
     return $1;
   end;
' LANGUAGE plpgsql;

explain analyze select echo_plperl('hello');
explain analyze select echo_pltcl('hello');
explain analyze select echo_plpythonu('hello');
explain analyze select echo_plpgsql('hello');
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.197
diff -c -r1.197 runtime.sgml
*** doc/src/sgml/runtime.sgml    29 Jul 2003 00:03:17 -0000    1.197
--- doc/src/sgml/runtime.sgml    31 Jul 2003 18:09:00 -0000
***************
*** 1008,1032 ****
          can also be optionally specified by adding a colon followed by
          the name of the initialization function after the library
          name. For example
!         <literal>'$libdir/mylib:init_mylib'</literal> would cause
!         <literal>mylib</> to be preloaded and <literal>init_mylib</>
          to be executed. If more than one library is to be loaded, they
          must be delimited with a comma.
         </para>

         <para>
!         If <literal>mylib</> is not found, the server will fail to
!         start.  However, if <literal>init_mylib</> is not found,
!         <literal>mylib</> will still be preloaded without executing
!         the initialization function.
         </para>

         <para>
          By preloading a shared library (and initializing it if
          applicable), the library startup time is avoided when the
          library is first used.  However, the time to start each new
!     server process may increase, even if that process never
!     uses the library.
         </para>
        </listitem>
       </varlistentry>
--- 1008,1037 ----
          can also be optionally specified by adding a colon followed by
          the name of the initialization function after the library
          name. For example
!         <literal>'$libdir/mylib:mylib_init'</literal> would cause
!         <literal>mylib</> to be preloaded and <literal>mylib_init</>
          to be executed. If more than one library is to be loaded, they
          must be delimited with a comma.
         </para>

         <para>
!         If <literal>mylib</> or <literal>mylib_init</> are not found, the
!         server will fail to start.
!        </para>
!
!        <para>
!         PostgreSQL Procedural Language libraries may be preloaded in this way,
!         typically by using the syntax <literal>'$libdir/plXXX:plXXX_init'
!         </literal> where <literal>XXX</literal> is <literal>pgsql</>,
!         <literal>perl</>, <literal>tcl</>, or <literal>python</>.
         </para>

         <para>
          By preloading a shared library (and initializing it if
          applicable), the library startup time is avoided when the
          library is first used.  However, the time to start each new
!         server process may increase, even if that process never
!         uses the library.
         </para>
        </listitem>
       </varlistentry>
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/init/miscinit.c,v
retrieving revision 1.108
diff -c -r1.108 miscinit.c
*** src/backend/utils/init/miscinit.c    28 Jul 2003 00:09:16 -0000    1.108
--- src/backend/utils/init/miscinit.c    31 Jul 2003 16:32:47 -0000
***************
*** 1165,1171 ****
          }

          initfunc = (func_ptr) load_external_function(filename, funcname,
!                                                      false, NULL);
          if (initfunc)
              (*initfunc)();

--- 1165,1171 ----
          }

          initfunc = (func_ptr) load_external_function(filename, funcname,
!                                                      true, NULL);
          if (initfunc)
              (*initfunc)();

Index: src/pl/plperl/plperl.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plperl/plperl.c,v
retrieving revision 1.37
diff -c -r1.37 plperl.c
*** src/pl/plperl/plperl.c    25 Jul 2003 23:37:28 -0000    1.37
--- src/pl/plperl/plperl.c    31 Jul 2003 17:12:45 -0000
***************
*** 101,106 ****
--- 101,107 ----
  static void plperl_init_interp(void);

  Datum        plperl_call_handler(PG_FUNCTION_ARGS);
+ void        plperl_init(void);

  static Datum plperl_func_handler(PG_FUNCTION_ARGS);

***************
*** 128,139 ****
  }

  /**********************************************************************
!  * plperl_init_all()        - Initialize all
   **********************************************************************/
! static void
! plperl_init_all(void)
  {
-
      /************************************************************
       * Do initialization only once
       ************************************************************/
--- 129,141 ----
  }

  /**********************************************************************
!  * plperl_init()            - Initialize everything that can be
!  *                              safely initialized during postmaster
!  *                              startup.
   **********************************************************************/
! void
! plperl_init(void)
  {
      /************************************************************
       * Do initialization only once
       ************************************************************/
***************
*** 168,173 ****
--- 170,195 ----
      plperl_firstcall = 0;
  }

+ /**********************************************************************
+  * plperl_init_all()        - Initialize all
+  **********************************************************************/
+ static void
+ plperl_init_all(void)
+ {
+
+     /************************************************************
+      * Execute postmaster-startup safe initialization
+      ************************************************************/
+     if (plperl_firstcall)
+         plperl_init();
+
+     /************************************************************
+      * Any other initialization that must be done each time a new
+      * backend starts -- currently none
+      ************************************************************/
+
+ }
+

  /**********************************************************************
   * plperl_init_interp() - Create the Perl interpreter
***************
*** 222,231 ****
      Datum        retval;

      /************************************************************
!      * Initialize interpreter on first call
       ************************************************************/
!     if (plperl_firstcall)
!         plperl_init_all();

      /************************************************************
       * Connect to SPI manager
--- 244,252 ----
      Datum        retval;

      /************************************************************
!      * Initialize interpreter
       ************************************************************/
!     plperl_init_all();

      /************************************************************
       * Connect to SPI manager
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.63
diff -c -r1.63 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c    27 Jul 2003 21:49:54 -0000    1.63
--- src/pl/plpgsql/src/pl_comp.c    31 Jul 2003 17:26:31 -0000
***************
*** 106,112 ****
  static void compute_function_hashkey(FmgrInfo *flinfo,
                                       Form_pg_proc procStruct,
                                       PLpgSQL_func_hashkey *hashkey);
! static void plpgsql_HashTableInit(void);
  static PLpgSQL_function *plpgsql_HashTableLookup(PLpgSQL_func_hashkey *func_key);
  static void plpgsql_HashTableInsert(PLpgSQL_function *function,
                                      PLpgSQL_func_hashkey *func_key);
--- 106,112 ----
  static void compute_function_hashkey(FmgrInfo *flinfo,
                                       Form_pg_proc procStruct,
                                       PLpgSQL_func_hashkey *hashkey);
! void plpgsql_HashTableInit(void);
  static PLpgSQL_function *plpgsql_HashTableLookup(PLpgSQL_func_hashkey *func_key);
  static void plpgsql_HashTableInsert(PLpgSQL_function *function,
                                      PLpgSQL_func_hashkey *func_key);
***************
*** 1743,1749 ****
      }
  }

! static void
  plpgsql_HashTableInit(void)
  {
      HASHCTL        ctl;
--- 1743,1749 ----
      }
  }

! void
  plpgsql_HashTableInit(void)
  {
      HASHCTL        ctl;
Index: src/pl/plpgsql/src/pl_handler.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpgsql/src/pl_handler.c,v
retrieving revision 1.15
diff -c -r1.15 pl_handler.c
*** src/pl/plpgsql/src/pl_handler.c    27 Jul 2003 17:10:07 -0000    1.15
--- src/pl/plpgsql/src/pl_handler.c    31 Jul 2003 17:31:15 -0000
***************
*** 44,49 ****
--- 44,86 ----
  #include "utils/builtins.h"
  #include "utils/syscache.h"

+ void plpgsql_init(void);
+ static void plpgsql_init_all(void);
+
+ extern void plpgsql_HashTableInit(void);
+ static int    plpgsql_firstcall = 1;
+
+ /*
+  * plpgsql_init()            - postmaster-startup safe initialization
+  */
+ void
+ plpgsql_init(void)
+ {
+     /* Do initialization only once */
+     if (!plpgsql_firstcall)
+         return;
+
+     plpgsql_HashTableInit();
+
+     plpgsql_firstcall = 0;
+ }
+
+ /*
+  * plpgsql_init_all()        - Initialize all
+  */
+ static void
+ plpgsql_init_all(void)
+ {
+     /* Execute any postmaster-startup safe initialization */
+     if (plpgsql_firstcall)
+         plpgsql_init();
+
+     /*
+      * Any other initialization that must be done each time a new
+      * backend starts -- currently none
+      */
+
+ }

  /* ----------
   * plpgsql_call_handler
***************
*** 60,65 ****
--- 97,105 ----
  {
      PLpgSQL_function *func;
      Datum        retval;
+
+     /* perform initialization */
+     plpgsql_init_all();

      /*
       * Connect to SPI manager
Index: src/pl/plpython/plpython.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/plpython/plpython.c,v
retrieving revision 1.35
diff -c -r1.35 plpython.c
*** src/pl/plpython/plpython.c    25 Jul 2003 23:37:30 -0000    1.35
--- src/pl/plpython/plpython.c    31 Jul 2003 17:12:58 -0000
***************
*** 170,179 ****
  /* function declarations
   */

! /* the only exported function, with the magic telling Postgresql
!  * what function call interface it implements.
   */
  Datum        plpython_call_handler(PG_FUNCTION_ARGS);

  PG_FUNCTION_INFO_V1(plpython_call_handler);

--- 170,181 ----
  /* function declarations
   */

! /* Two exported functions: first is the magic telling Postgresql
!  * what function call interface it implements. Second allows
!  * preinitialization of the interpreter during postmaster startup.
   */
  Datum        plpython_call_handler(PG_FUNCTION_ARGS);
+ void        plpython_init(void);

  PG_FUNCTION_INFO_V1(plpython_call_handler);

***************
*** 329,336 ****

      enter();

!     if (PLy_first_call)
!         PLy_init_all();

      if (SPI_connect() != SPI_OK_CONNECT)
          elog(ERROR, "could not connect to SPI manager");
--- 331,337 ----

      enter();

!     PLy_init_all();

      if (SPI_connect() != SPI_OK_CONNECT)
          elog(ERROR, "could not connect to SPI manager");
***************
*** 2302,2312 ****
  /* language handler and interpreter initialization
   */

  void
! PLy_init_all(void)
  {
      static volatile int init_active = 0;

      enter();

      if (init_active)
--- 2303,2322 ----
  /* language handler and interpreter initialization
   */

+ /*
+  * plpython_init()            - Initialize everything that can be
+  *                              safely initialized during postmaster
+  *                              startup.
+  */
  void
! plpython_init(void)
  {
      static volatile int init_active = 0;

+     /* Do initialization only once */
+     if (!PLy_first_call)
+         return;
+
      enter();

      if (init_active)
***************
*** 2325,2330 ****
--- 2335,2354 ----
      PLy_first_call = 0;

      leave();
+ }
+
+ static void
+ PLy_init_all(void)
+ {
+     /* Execute postmaster-startup safe initialization */
+     if (PLy_first_call)
+         plpython_init();
+
+     /*
+      * Any other initialization that must be done each time a new
+      * backend starts -- currently none
+      */
+
  }

  void
Index: src/pl/tcl/pltcl.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/src/pl/tcl/pltcl.c,v
retrieving revision 1.72
diff -c -r1.72 pltcl.c
*** src/pl/tcl/pltcl.c    25 Jul 2003 23:37:31 -0000    1.72
--- src/pl/tcl/pltcl.c    31 Jul 2003 17:16:32 -0000
***************
*** 149,154 ****
--- 149,155 ----

  Datum        pltcl_call_handler(PG_FUNCTION_ARGS);
  Datum        pltclu_call_handler(PG_FUNCTION_ARGS);
+ void        pltcl_init(void);

  static Datum pltcl_func_handler(PG_FUNCTION_ARGS);

***************
*** 197,208 ****
      fmgr_info_cxt(functionId, finfo, TopMemoryContext);
  }

-
  /**********************************************************************
   * pltcl_init_all()        - Initialize all
   **********************************************************************/
! static void
! pltcl_init_all(void)
  {
      /************************************************************
       * Do initialization only once
--- 198,208 ----
      fmgr_info_cxt(functionId, finfo, TopMemoryContext);
  }

  /**********************************************************************
   * pltcl_init_all()        - Initialize all
   **********************************************************************/
! void
! pltcl_init(void)
  {
      /************************************************************
       * Do initialization only once
***************
*** 244,249 ****
--- 244,274 ----
      return;
  }

+ /**********************************************************************
+  * pltcl_init_all()        - Initialize all
+  **********************************************************************/
+ static void
+ pltcl_init_all(void)
+ {
+     /************************************************************
+      * Execute postmaster-startup safe initialization
+      ************************************************************/
+     if (pltcl_firstcall)
+         pltcl_init();
+
+     /************************************************************
+      * Any other initialization that must be done each time a new
+      * backend starts:
+      * - Try to load the unknown procedure from pltcl_modules
+      ************************************************************/
+     if (SPI_connect() != SPI_OK_CONNECT)
+         elog(ERROR, "SPI_connect failed");
+     pltcl_init_load_unknown(pltcl_norm_interp);
+     pltcl_init_load_unknown(pltcl_safe_interp);
+     if (SPI_finish() != SPI_OK_FINISH)
+         elog(ERROR, "SPI_finish failed");
+ }
+

  /**********************************************************************
   * pltcl_init_interp() - initialize a Tcl interpreter
***************
*** 272,285 ****
      Tcl_CreateCommand(interp, "spi_lastoid",
                        pltcl_SPI_lastoid, NULL, NULL);

-     /************************************************************
-      * Try to load the unknown procedure from pltcl_modules
-      ************************************************************/
-     if (SPI_connect() != SPI_OK_CONNECT)
-         elog(ERROR, "SPI_connect failed");
-     pltcl_init_load_unknown(interp);
-     if (SPI_finish() != SPI_OK_FINISH)
-         elog(ERROR, "SPI_finish failed");
  }


--- 297,302 ----
***************
*** 373,382 ****
      FunctionCallInfo save_fcinfo;

      /************************************************************
!      * Initialize interpreters on first call
       ************************************************************/
!     if (pltcl_firstcall)
!         pltcl_init_all();

      /************************************************************
       * Connect to SPI manager
--- 390,398 ----
      FunctionCallInfo save_fcinfo;

      /************************************************************
!      * Initialize interpreters
       ************************************************************/
!     pltcl_init_all();

      /************************************************************
       * Connect to SPI manager