Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <br...@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 <br...@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 address is 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 address is 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)
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers