Re: [GENERAL] Installing PL/pgSQL by default - Mailing list pgsql-hackers

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > I installed PL/pgSQL by default via initdb with the attached patch.  The
> > > only problem is that pg_dump still dumps out the language creation:
> > >     CREATE PROCEDURAL LANGUAGE plpgsql;
> > >     ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
> > > What is odd is that I used the same process that initdb uses to create
> > > other objects.  Does anyone know why this is happening?
> >
> > I think pg_dump pays attention to what schema the objects are in,
> > and that's most likely creating them in PUBLIC.  Try adding
> > "set search_path = pg_catalog".
> >
> > It's not impossible that we'll have to tweak pg_dump a bit; it's
> > never had to deal with languages that shouldn't be dumped ...
>
> I found that pg_dump tests for pg_language.lanispl == true, which is
> true for all the stored procedure languages.  I can easily special case
> plpgsql, or check for FirstNormalObjectId, though I don't see that used
> in pg_dump currently.
>
> A more difficult issue is whether we should preserve the fact that
> plpgsql was _removed_ in the pg_dump output, i.e, if someone removes
> plpgsql from a database, do we issue a DROP LANGUAGE in pg_dump?  I
> don't remember us having to deal with anything like this before.

OK, the attached patch installs plpgsql by default from initdb, and
supresses the dumping of CREATE LANGUAGE in 8.5 and in 8.3/8.4 if binary
upgrade is used (because you know you are upgrading to a release that
has plpgsql installed by default).  The 8.3/8.4 is necessary so the
schema load doesn't generate any errors and cause pg_migrator to exit.

--
  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.333
diff -c -c -r1.333 installation.sgml
*** doc/src/sgml/installation.sgml    15 Dec 2009 22:59:53 -0000    1.333
--- doc/src/sgml/installation.sgml    17 Dec 2009 23:35:36 -0000
***************
*** 2266,2279 ****
       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
--- 2266,2279 ----
       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/bin/initdb/initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.178
diff -c -c -r1.178 initdb.c
*** src/bin/initdb/initdb.c    11 Dec 2009 03:34:56 -0000    1.178
--- src/bin/initdb/initdb.c    17 Dec 2009 23:35:36 -0000
***************
*** 176,181 ****
--- 176,182 ----
  static void setup_privileges(void);
  static void set_info_version(void);
  static void setup_schema(void);
+ static void load_plpgsql(void);
  static void vacuum_db(void);
  static void make_template0(void);
  static void make_postgres(void);
***************
*** 1894,1899 ****
--- 1895,1925 ----
  }

  /*
+  * load PL/pgsql server-side language
+  */
+ static void
+ load_plpgsql(void)
+ {
+     PG_CMD_DECL;
+
+     fputs(_("loading PL/pgSQL server-side language ... "), stdout);
+     fflush(stdout);
+
+     snprintf(cmd, sizeof(cmd),
+              "\"%s\" %s template1 >%s",
+              backend_exec, backend_options,
+              DEVNULL);
+
+     PG_CMD_OPEN;
+
+     PG_CMD_PUTS("CREATE LANGUAGE plpgsql;\n");
+
+     PG_CMD_CLOSE;
+
+     check_ok();
+ }
+
+ /*
   * clean everything up in template1
   */
  static void
***************
*** 3126,3131 ****
--- 3152,3159 ----

      setup_schema();

+     load_plpgsql();
+
      vacuum_db();

      make_template0();
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.556
diff -c -c -r1.556 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    14 Dec 2009 00:39:11 -0000    1.556
--- src/bin/pg_dump/pg_dump.c    17 Dec 2009 23:35:36 -0000
***************
*** 32,37 ****
--- 32,38 ----

  #include "access/attnum.h"
  #include "access/sysattr.h"
+ #include "access/transam.h"
  #include "catalog/pg_cast.h"
  #include "catalog/pg_class.h"
  #include "catalog/pg_default_acl.h"
***************
*** 4599,4606 ****
                            "(%s lanowner) AS lanowner "
                            "FROM pg_language "
                            "WHERE lanispl "
                            "ORDER BY oid",
!                           username_subquery);
      }
      else if (g_fout->remoteVersion >= 80300)
      {
--- 4600,4609 ----
                            "(%s lanowner) AS lanowner "
                            "FROM pg_language "
                            "WHERE lanispl "
+                           /* do not dump initdb-installed languages */
+                           "AND oid >= %u "
                            "ORDER BY oid",
!                           username_subquery, FirstNormalObjectId);
      }
      else if (g_fout->remoteVersion >= 80300)
      {
***************
*** 4610,4618 ****
                            "lanvalidator,  lanacl, "
                            "(%s lanowner) AS lanowner "
                            "FROM pg_language "
!                           "WHERE lanispl "
                            "ORDER BY oid",
!                           username_subquery);
      }
      else if (g_fout->remoteVersion >= 80100)
      {
--- 4613,4622 ----
                            "lanvalidator,  lanacl, "
                            "(%s lanowner) AS lanowner "
                            "FROM pg_language "
!                           "WHERE lanispl%s"
                            "ORDER BY oid",
!                           username_subquery,
!                           binary_upgrade ? "\nAND lanname != 'plpgsql'" : "");
      }
      else if (g_fout->remoteVersion >= 80100)
      {
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    17 Dec 2009 23:35:38 -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)