Andrew Dunstan wrote:
Why --- what else is needed beyond the addition of those clauses to the
one query?
There are tests for both the function and the handler based on
finfo->dobj.namespace->dump that inhibit output if we're in the
catalog schema.
If we go down this path ISTM the simplest thing would be to add a
field to the FuncInfo object to allow it to be marked as a a
handler/validator.
demo patch attached. I need to fix up comments, but I think it works.
Comments welcome
Also, I think pg_dump *never* quotes the handler name or qualifies it
with a schema name - that looks like it might be a bug, regardless of
this.
I was wrong about this.
cheers
andrew
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.410
diff -c -r1.410 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 21 Jun 2005 20:45:44 -0000 1.410
--- src/bin/pg_dump/pg_dump.c 26 Jun 2005 21:58:00 -0000
***************
*** 2146,2151 ****
--- 2146,2152 ----
int i_proargtypes;
int i_prorettype;
int i_proacl;
+ int i_is_pl_handler;
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
***************
*** 2158,2168 ****
"SELECT tableoid, oid, proname, prolang, "
"pronargs, proargtypes, prorettype, proacl, "
"pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename "
"FROM pg_proc "
"WHERE NOT proisagg "
! "AND pronamespace != "
! "(select oid from pg_namespace where nspname = 'pg_catalog')");
}
else if (g_fout->remoteVersion >= 70100)
{
--- 2159,2175 ----
"SELECT tableoid, oid, proname, prolang, "
"pronargs, proargtypes, prorettype, proacl, "
"pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename, "
! "CASE WHEN oid in (select lanplcallfoid from pg_language where lanplcallfoid != 0) THEN true "
! " WHEN oid in (select lanvalidator from pg_language where lanplcallfoid != 0) THEN true "
! " ELSE false END AS is_pl_handler "
"FROM pg_proc "
"WHERE NOT proisagg "
! "AND (pronamespace != "
! " (select oid from pg_namespace where nspname = 'pg_catalog')"
! " OR oid in (select lanplcallfoid from pg_language where lanplcallfoid != 0) "
! " OR oid in (select lanvalidator from pg_language where lanplcallfoid != 0))"
! );
}
else if (g_fout->remoteVersion >= 70100)
{
***************
*** 2171,2177 ****
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
--- 2178,2185 ----
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename, "
! "false AS is_pl_handler "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
***************
*** 2185,2191 ****
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
--- 2193,2200 ----
"pronargs, proargtypes, prorettype, "
"'{=X}' as proacl, "
"0::oid as pronamespace, "
! "(select usename from pg_user where proowner = usesysid) as usename, "
! "false AS is_pl_handler "
"FROM pg_proc "
"where pg_proc.oid > '%u'::oid",
g_last_builtin_oid);
***************
*** 2210,2215 ****
--- 2219,2225 ----
i_proargtypes = PQfnumber(res, "proargtypes");
i_prorettype = PQfnumber(res, "prorettype");
i_proacl = PQfnumber(res, "proacl");
+ i_is_pl_handler = PQfnumber(res,"is_pl_handler");
for (i = 0; i < ntups; i++)
{
***************
*** 2225,2230 ****
--- 2235,2241 ----
finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
+ finfo[i].isProlangFunc = strcmp(PQgetvalue(res, i, i_is_pl_handler), "t") == 0;
if (finfo[i].nargs == 0)
finfo[i].argtypes = NULL;
else
***************
*** 4937,4943 ****
if (funcInfo == NULL)
return;
! if (!funcInfo->dobj.namespace->dump)
return;
if (OidIsValid(plang->lanvalidator))
--- 4948,4954 ----
if (funcInfo == NULL)
return;
! if (!funcInfo->isProlangFunc && !funcInfo->dobj.namespace->dump)
return;
if (OidIsValid(plang->lanvalidator))
***************
*** 5136,5144 ****
char **argnames = NULL;
/* Dump only funcs in dumpable namespaces */
! if (!finfo->dobj.namespace->dump || dataOnly)
return;
query = createPQExpBuffer();
q = createPQExpBuffer();
delqry = createPQExpBuffer();
--- 5147,5156 ----
char **argnames = NULL;
/* Dump only funcs in dumpable namespaces */
! if ((!finfo->isProlangFunc && !finfo->dobj.namespace->dump) || dataOnly)
return;
+
query = createPQExpBuffer();
q = createPQExpBuffer();
delqry = createPQExpBuffer();
Index: src/bin/pg_dump/pg_dump.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v
retrieving revision 1.115
diff -c -r1.115 pg_dump.h
*** src/bin/pg_dump/pg_dump.h 31 Dec 2004 22:03:08 -0000 1.115
--- src/bin/pg_dump/pg_dump.h 26 Jun 2005 21:58:00 -0000
***************
*** 131,136 ****
--- 131,137 ----
Oid *argtypes;
Oid prorettype;
char *proacl;
+ bool isProlangFunc;
} FuncInfo;
/* AggInfo is a superset of FuncInfo */
Index: src/bin/scripts/createlang.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/scripts/createlang.c,v
retrieving revision 1.17
diff -c -r1.17 createlang.c
*** src/bin/scripts/createlang.c 22 Jun 2005 16:45:50 -0000 1.17
--- src/bin/scripts/createlang.c 26 Jun 2005 21:58:02 -0000
***************
*** 260,279 ****
if (!handlerexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION \"%s\" () RETURNS language_handler AS '%s/%s' LANGUAGE C;\n",
handler, pglib, object);
if (!validatorexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION \"%s\" (oid) RETURNS void AS '%s/%s' LANGUAGE C;\n",
validator, pglib, object);
appendPQExpBuffer(&sql,
! "CREATE %sLANGUAGE \"%s\" HANDLER \"%s\"",
(trusted ? "TRUSTED " : ""), langname, handler);
if (validator)
! appendPQExpBuffer(&sql, " VALIDATOR \"%s\"", validator);
appendPQExpBuffer(&sql, ";\n");
--- 260,279 ----
if (!handlerexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION pg_catalog.\"%s\" () RETURNS language_handler AS '%s/%s' LANGUAGE C;\n",
handler, pglib, object);
if (!validatorexists)
appendPQExpBuffer(&sql,
! "CREATE FUNCTION pg_catalog.\"%s\" (oid) RETURNS void AS '%s/%s' LANGUAGE C;\n",
validator, pglib, object);
appendPQExpBuffer(&sql,
! "CREATE %sLANGUAGE \"%s\" HANDLER pg_catalog.\"%s\"",
(trusted ? "TRUSTED " : ""), langname, handler);
if (validator)
! appendPQExpBuffer(&sql, " VALIDATOR pg_catalog.\"%s\"", validator);
appendPQExpBuffer(&sql, ";\n");
Index: src/bin/scripts/droplang.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/scripts/droplang.c,v
retrieving revision 1.15
diff -c -r1.15 droplang.c
*** src/bin/scripts/droplang.c 14 Jun 2005 02:57:45 -0000 1.15
--- src/bin/scripts/droplang.c 26 Jun 2005 21:58:02 -0000
***************
*** 52,57 ****
--- 52,59 ----
Oid lanvalidator;
char *handler;
char *validator;
+ char *handler_ns;
+ char *validator_ns;
bool keephandler;
bool keepvalidator;
***************
*** 212,224 ****
*/
if (!keephandler)
{
! printfPQExpBuffer(&sql, "SELECT proname FROM pg_proc WHERE oid = %u;", lanplcallfoid);
result = executeQuery(conn, sql.data, progname, echo);
handler = strdup(PQgetvalue(result, 0, 0));
PQclear(result);
}
else
handler = NULL;
/*
* Check that the validator function isn't used by some other language
--- 214,230 ----
*/
if (!keephandler)
{
! printfPQExpBuffer(&sql, "SELECT proname, (SELECT nspname FROM pg_namespace ns WHERE ns.oid = pronamespace) AS prons FROM pg_proc WHERE oid = %u;", lanplcallfoid);
result = executeQuery(conn, sql.data, progname, echo);
handler = strdup(PQgetvalue(result, 0, 0));
+ handler_ns = strdup(PQgetvalue(result, 0, 1));
PQclear(result);
}
else
+ {
handler = NULL;
+ handler_ns = NULL;
+ }
/*
* Check that the validator function isn't used by some other language
***************
*** 241,262 ****
*/
if (!keepvalidator)
{
! printfPQExpBuffer(&sql, "SELECT proname FROM pg_proc WHERE oid = %u;", lanvalidator);
result = executeQuery(conn, sql.data, progname, echo);
validator = strdup(PQgetvalue(result, 0, 0));
PQclear(result);
}
else
validator = NULL;
/*
* Drop the language and the functions
*/
printfPQExpBuffer(&sql, "DROP LANGUAGE \"%s\";\n", langname);
if (!keephandler)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\" ();\n", handler);
if (!keepvalidator)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\" (oid);\n", validator);
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
--- 247,272 ----
*/
if (!keepvalidator)
{
! printfPQExpBuffer(&sql, "SELECT proname, (SELECT nspname FROM pg_namespace ns WHERE ns.oid = pronamespace) AS prons FROM pg_proc WHERE oid = %u;", lanvalidator);
result = executeQuery(conn, sql.data, progname, echo);
validator = strdup(PQgetvalue(result, 0, 0));
+ validator_ns = strdup(PQgetvalue(result, 0, 1));
PQclear(result);
}
else
+ {
validator = NULL;
+ validator_ns = NULL;
+ }
/*
* Drop the language and the functions
*/
printfPQExpBuffer(&sql, "DROP LANGUAGE \"%s\";\n", langname);
if (!keephandler)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\".\"%s\" ();\n", handler_ns, handler);
if (!keepvalidator)
! appendPQExpBuffer(&sql, "DROP FUNCTION \"%s\".\"%s\" (oid);\n", validator_ns, validator);
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
