On Sun, May 27, 2012 at 11:49:31AM -0700, Adrian Klaver wrote:
> >
> > If you can help me find out how these got defined this way, I might be
> > able to prevent this problem for the next person.
> >
>
> After reading the above thread here is what the queries mentioned return:
>
> production=# SELECT nspname,proname,probin FROM pg_proc,pg_namespace
> WHERE probin LIKE '%python%' and pg_proc.pronamespace=pg_namespace.oid;
>
> nspname | proname | probin
> ------------+-------------------------+------------------
> pg_catalog | plpython_call_handler | $libdir/plpython
> pg_catalog | plpython_inline_handler | $libdir/plpython
> public | plpython_call_handler | $libdir/plpython
> (3 rows)
You are the third person to have this problem, and this was the
information I needed to properly find the cause. I believe it was done
in this 8.1 commit:
e0dedd0559f005d60c69c9772163e69c204bac69
Implement a preliminary 'template' facility for procedural languages,
as per my recent proposal. For now the template data is hard-wired
in proclang.c --- this should be replaced later by a new shared
system catalog, but we don't want to force initdb during 8.1 beta.
This change lets us cleanly load existing dump files even if they
contain outright wrong information about a PL's support functions, such
as a wrong path to the shared library or a missing validator function.
Also, we can revert the recent kluges to make pg_dump dump PL support
functions that are stored in pg_catalog. While at it, I removed the
code in pg_regress that replaced $libdir with a hardcoded path for
temporary installations. This is no longer needed given our support
for relocatable installations.
This moved the helper functions into pg_catalog, but the author probably
didn't realize that public schema helper functions would continue to be
dumped by pg_dump. These helper functions continued to be
dumped/restored until the rename. There are certainly helper functions
for other languages that are still duplicated in the public schema ---
there is nothing unique about plpython. We are only seeing problems
because of the plpython.so rename.
In normal use, a pg_dumpall restore would throw an error about a missing
helper function shared objects, but the pg_catalog entry would continue
to work just fine. Odd we have not heard complaints from users seeing
that error on restore --- odds are, they are just ignoring the error,
which pg_upgrade does not do.
The attached pg_upgrade patch adds checks for this plpython helper
function and reports a proper error, suggesting how to fix the problem:
Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
The old cluster has a "plpython_call_handler" function defined
in the "public" schema which is a duplicate of the one defined
in the "pg_catalog" schema. You can confirm this by executing
in psql:
\df *.plpython_call_handler
The "public" schema version of this function was created by a
pre-8.1 install of plpython, and must be removed for pg_upgrade
to complete because it references a now-obsolete "plpython"
shared object file. You can remove the "public" schema version
of this function by running the following command:
DROP FUNCTION public.plpython_call_handler()
in each affected database:
test
test3
Remove the problem functions from the old cluster to continue.
Failure, exiting
We could do the same for other PL languages if they are ever renamed. I
suppose we don't care about fixing the duplicate schema entries.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/function.c b/contrib/pg_upgrade/function.c
new file mode 100644
index 90e8a9f..58b341a
*** a/contrib/pg_upgrade/function.c
--- b/contrib/pg_upgrade/function.c
*************** get_loadable_libraries(void)
*** 132,138 ****
PGresult **ress;
int totaltups;
int dbnum;
!
ress = (PGresult **) pg_malloc(old_cluster.dbarr.ndbs * sizeof(PGresult *));
totaltups = 0;
--- 132,139 ----
PGresult **ress;
int totaltups;
int dbnum;
! bool found_public_plpython_handler = false;
!
ress = (PGresult **) pg_malloc(old_cluster.dbarr.ndbs * sizeof(PGresult *));
totaltups = 0;
*************** get_loadable_libraries(void)
*** 157,165 ****
--- 158,224 ----
FirstNormalObjectId);
totaltups += PQntuples(ress[dbnum]);
+ /*
+ * Systems that install plpython before 8.1 have
+ * plpython_call_handler() defined in the "public" schema, causing
+ * pg_dumpall to dump it. However that function still references
+ * "plpython" (no "2"), so it throws an error on restore. This code
+ * checks for the problem function, reports affected databases to the
+ * user and explains how to remove them.
+ * 8.1 git commit: e0dedd0559f005d60c69c9772163e69c204bac69
+ * http://archives.postgresql.org/pgsql-hackers/2012-03/msg01101.php
+ * http://archives.postgresql.org/pgsql-bugs/2012-05/msg00206.php
+ */
+ if (GET_MAJOR_VERSION(old_cluster.major_version) < 901)
+ {
+ PGresult *res;
+
+ res = executeQueryOrDie(conn,
+ "SELECT 1 "
+ "FROM pg_catalog.pg_proc JOIN pg_namespace "
+ " ON pronamespace = pg_namespace.oid "
+ "WHERE proname = 'plpython_call_handler' AND "
+ "nspname = 'public' AND "
+ "prolang = 13 /* C */ AND "
+ "probin IS NOT NULL AND "
+ "pg_proc.oid >= %u;",
+ FirstNormalObjectId);
+ if (PQntuples(res) > 0)
+ {
+ if (!found_public_plpython_handler)
+ {
+ pg_log(PG_WARNING,
+ "\nThe old cluster has a \"plpython_call_handler\" function defined\n"
+ "in the \"public\" schema which is a duplicate of the one defined\n"
+ "in the \"pg_catalog\" schema. You can confirm this by executing\n"
+ "in psql:\n"
+ "\n"
+ " \\df *.plpython_call_handler\n"
+ "\n"
+ "The \"public\" schema version of this function was created by a\n"
+ "pre-8.1 install of plpython, and must be removed for pg_upgrade\n"
+ "to complete because it references a now-obsolete \"plpython\"\n"
+ "shared object file. You can remove the \"public\" schema version\n"
+ "of this function by running the following command:\n"
+ "\n"
+ " DROP FUNCTION public.plpython_call_handler()\n"
+ "\n"
+ "in each affected database:\n"
+ "\n");
+ }
+ pg_log(PG_WARNING, " %s\n", active_db->db_name);
+ found_public_plpython_handler = true;
+ }
+ PQclear(res);
+ }
+
PQfinish(conn);
}
+ if (found_public_plpython_handler)
+ pg_log(PG_FATAL,
+ "Remove the problem functions from the old cluster to continue.\n");
+
totaltups++; /* reserve for pg_upgrade_support */
/* Allocate what's certainly enough space */
*************** check_loadable_libraries(void)
*** 245,256 ****
* For this case, we could check pg_pltemplate, but that only works
* for languages, and does not help with function shared objects,
* so we just do a general fix.
- *
- * Some systems have plpython_call_handler() that references
- * "plpython" defined in the "public" schema, causing pg_dump to
- * dump it an generate an error on pg_dumpall restore; not sure
- * on the cause, see:
- * http://archives.postgresql.org/pgsql-hackers/2012-03/msg01101.php
*/
if (GET_MAJOR_VERSION(old_cluster.major_version) < 901 &&
strcmp(lib, "$libdir/plpython") == 0)
--- 304,309 ----
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
