Installing PL/pgSQL by default - Mailing list pgsql-general

Tom Lane wrote:
> But actually I thought we had more or less concluded that CREATE OR
> REPLACE LANGUAGE would be acceptable (perhaps only if it's given
> without any extra args?).  Or for that matter there seems to be enough
> opinion on the side of just installing plpgsql by default.  CINE is
> a markedly inferior alternative to either of those.

Based on research done as part of this thread, it seems plpgsql has
similar risks to recursive queries, so the idea of installing plpgsql by
default now makes more sense.

The attached patch installs plpgsql language by default, as well as the
three plpgsql helper functions.  The language is installed just like it
was before, but now automatically, e.g. still a separate shared object.
One problem is that because system oids are used, it isn't possible to
drop the language:

    $ droplang plpgsql test
    droplang: language removal failed: ERROR:  cannot drop language plpgsql
    because it is required by the database system

I assume we still want to allow the language to be uninstalled, for
security purposes.  Right?  Any suggestions?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/installation.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/installation.sgml,v
retrieving revision 1.328
diff -c -c -r1.328 installation.sgml
*** doc/src/sgml/installation.sgml    2 Dec 2009 14:07:25 -0000    1.328
--- doc/src/sgml/installation.sgml    3 Dec 2009 23:09:59 -0000
***************
*** 2257,2270 ****
       is <command>createlang</command> failing with unusual errors.
       For example, running as the owner of the PostgreSQL installation:
  <screen>
! -bash-3.00$ createlang plpgsql template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plpgsql.so": A memory
addressis not in the address space for the process. 
  </screen>
      Running as a non-owner in the group posessing the PostgreSQL
      installation:
  <screen>
! -bash-3.00$ createlang plpgsql template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plpgsql.so": Bad
address
  </screen>
       Another example is out of memory errors in the PostgreSQL server
       logs, with every memory allocation near or greater than 256 MB
--- 2257,2270 ----
       is <command>createlang</command> failing with unusual errors.
       For example, running as the owner of the PostgreSQL installation:
  <screen>
! -bash-3.00$ createlang plperl template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plperl.so": A memory
addressis not in the address space for the process. 
  </screen>
      Running as a non-owner in the group posessing the PostgreSQL
      installation:
  <screen>
! -bash-3.00$ createlang plperl template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plperl.so": Bad
address
  </screen>
       Another example is out of memory errors in the PostgreSQL server
       logs, with every memory allocation near or greater than 256 MB
Index: src/include/catalog/pg_language.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_language.h,v
retrieving revision 1.35
diff -c -c -r1.35 pg_language.h
*** src/include/catalog/pg_language.h    22 Sep 2009 23:43:41 -0000    1.35
--- src/include/catalog/pg_language.h    3 Dec 2009 23:09:59 -0000
***************
*** 75,79 ****
--- 75,82 ----
  DATA(insert OID = 14 ( "sql"        PGUID f t 0 0 2248 _null_ ));
  DESCR("SQL-language functions");
  #define SQLlanguageId 14
+ DATA(insert OID = 9 ( "plpgsql"        PGUID t t 2995 2996 2997 _null_ ));
+ DESCR("SQL-language functions");
+

  #endif   /* PG_LANGUAGE_H */
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.554
diff -c -c -r1.554 pg_proc.h
*** src/include/catalog/pg_proc.h    29 Nov 2009 18:14:30 -0000    1.554
--- src/include/catalog/pg_proc.h    3 Dec 2009 23:10:03 -0000
***************
*** 4722,4727 ****
--- 4722,4734 ----
  DATA(insert OID = 3114 (  nth_value        PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_
_null_window_nth_value _null_ _null_ _null_ )); 
  DESCR("fetch the Nth row value");

+ /* PL/pgSQL support functions */
+ DATA(insert OID = 2995 (  plpgsql_call_handler    PGNSP PGUID 13 1 0 0 f f f f f v 0 0 2280 "" _null_ _null_ _null_
_null_plpgsql_call_handler "$libdir/plpgsql" _null_ _null_ )); 
+ DESCR("PL/pgSQL function/trigger manager");
+ DATA(insert OID = 2996 (  plpgsql_inline_handler    PGNSP PGUID 13 1 0 0 f f f t f v 1 0 2278 2281 _null_ _null_
_null__null_ plpgsql_inline_handler "$libdir/plpgsql" _null_ _null_ )); 
+ DESCR("PL/pgSQL anonymous code block executor");
+ DATA(insert OID = 2997 (  plpgsql_validator    PGNSP PGUID 13 1 0 0 f f f t f v 1 0 2278 26 _null_ _null_ _null_
_null_plpgsql_validator "$libdir/plpgsql" _null_ _null_ )); 
+ DESCR("PL/pgSQL function validator");

  /*
   * Symbolic values for provolatile column: these indicate whether the result
Index: src/test/regress/GNUmakefile
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/GNUmakefile,v
retrieving revision 1.79
diff -c -c -r1.79 GNUmakefile
*** src/test/regress/GNUmakefile    26 Oct 2009 21:11:22 -0000    1.79
--- src/test/regress/GNUmakefile    3 Dec 2009 23:10:03 -0000
***************
*** 138,144 ****
  ## Run tests
  ##

! pg_regress_call = ./pg_regress --inputdir=$(srcdir) --dlpath=. --multibyte=$(MULTIBYTE) --load-language=plpgsql
$(NOLOCALE)

  check: all
      $(pg_regress_call) --temp-install=./tmp_check --top-builddir=$(top_builddir)
--schedule=$(srcdir)/parallel_schedule$(MAXCONNOPT) $(TEMP_CONF) 
--- 138,144 ----
  ## Run tests
  ##

! pg_regress_call = ./pg_regress --inputdir=$(srcdir) --dlpath=. --multibyte=$(MULTIBYTE) $(NOLOCALE)

  check: all
      $(pg_regress_call) --temp-install=./tmp_check --top-builddir=$(top_builddir)
--schedule=$(srcdir)/parallel_schedule$(MAXCONNOPT) $(TEMP_CONF)