Re: [HACKERS] language handlers in public schema? - Mailing list pgsql-patches

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
pronsFROM 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
pronsFROM 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);