Re: Functions too slow, even with iscachable? - Mailing list pgsql-sql

At 17:11 7/08/00 +0800, Ang Chin Han wrote:
>
>BTW, pg_dump doesn't seem to preserve the iscachable attribute. Bug?
>

Here is a patch for 7.0.2 sources which adds support for ischachable to
pg_dump.


-------------------------------------------------
diff -Naur pg_dump/pg_dump.c zzz/pg_dump.c
--- pg_dump/pg_dump.c    Fri Apr 14 11:34:24 2000
+++ zzz/pg_dump.c    Mon Aug  7 21:51:21 2000
@@ -1456,13 +1456,15 @@    int            i_proretset;    int            i_prosrc;    int            i_probin;
+    int            i_iscachable;    int            i_usename;    /* find all user-defined funcs */
appendPQExpBuffer(query,          "SELECT pg_proc.oid, proname, prolang, pronargs, prorettype, "
 
-                      "proretset, proargtypes, prosrc, probin, usename "
+                      "proretset, proargtypes, prosrc, probin, usename, "
+                      "proiscachable "                      "from pg_proc, pg_user "                 "where
pg_proc.oid> '%u'::oid and proowner = usesysid",                      g_last_builtin_oid);
 
@@ -1492,6 +1494,7 @@    i_proretset = PQfnumber(res, "proretset");    i_prosrc = PQfnumber(res, "prosrc");    i_probin
=PQfnumber(res, "probin");
 
+    i_iscachable = PQfnumber(res, "proiscachable");    i_usename = PQfnumber(res, "usename");    for (i = 0; i <
ntups;i++)
 
@@ -1507,6 +1510,7 @@        finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));        finfo[i].lang =
atoi(PQgetvalue(res,i, i_prolang));        finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
 
+        finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable),"t") == 0);        if (finfo[i].nargs < 0 ||
finfo[i].nargs> FUNC_MAX_ARGS)        {            fprintf(stderr, "failed sanity check: %s has %d args\n",
 
@@ -2663,11 +2667,18 @@                          (j > 0) ? "," : "",                          fmtId(typname, false));
}
 
-    appendPQExpBuffer(q, " ) RETURNS %s%s AS '%s' LANGUAGE '%s';\n",
+    appendPQExpBuffer(q, " ) RETURNS %s%s AS '%s' LANGUAGE '%s'",                      (finfo[i].retset) ? " SETOF " :
"",      fmtId(findTypeByOid(tinfo, numTypes, finfo[i].prorettype), false),                      func_def, func_lang);
 
+    if (finfo[i].iscachable) /* OR in new attrs here */
+    {
+        appendPQExpBuffer(q, " WITH (iscachable)");
+    }
+
+    appendPQExpBuffer(q, ";\n");
+     fputs(q->data, fout);    /*** Dump Function Comments ***/
diff -Naur pg_dump/pg_dump.h zzz/pg_dump.h
--- pg_dump/pg_dump.h    Thu Apr 13 03:16:15 2000
+++ zzz/pg_dump.h    Mon Aug  7 21:49:05 2000
@@ -61,6 +61,7 @@    char       *prosrc;    char       *probin;    char       *usename;
+    int            iscachable;        /* Attr */    int            dumped;            /* 1 if already dumped */}
FuncInfo;


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/