Here is a patch with a fix outlined by Tom:
test=> CREATE OR REPLACE FUNCTION runMaintenance()
test-> RETURNS BOOL AS '
test'> VACUUM;
test'> SELECT TRUE;
test'> ' LANGUAGE sql;
CREATE
test=>
test=> select runMaintenance();
ERROR: VACUUM cannot be executed from a function
Looks good. Will commit after typical delay.
---------------------------------------------------------------------------
Tom Lane wrote:
> "Rod Taylor" <[EMAIL PROTECTED]> writes:
> > 7.2 crashes with the below function:
> > CREATE OR REPLACE FUNCTION runMaintenance()
> > RETURNS BOOL AS '
> > VACUUM;
> > SELECT TRUE;
> > ' LANGUAGE sql;
>
> AFAICS there is no way that we can support VACUUM inside a function;
> the forced transaction commits that VACUUM performs will recycle any
> memory allocated by the function executor, leading to death and
> destruction upon return from VACUUM.
>
> Accordingly, what we really need is a way of preventing VACUUM from
> executing in the above scenario. The IsTransactionBlock() test it
> already has isn't sufficient.
>
> I have thought of something that probably would be sufficient:
>
> if (!MemoryContextContains(QueryContext, vacstmt))
> elog(ERROR, "VACUUM cannot be executed from a function");
>
> This is truly, horribly ugly ... but it'd get the job done, because only
> interactive queries will generate parsetrees in QueryContext.
>
> Can someone think of a better way?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
Bruce Momjian | http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.223
diff -c -r1.223 vacuum.c
*** src/backend/commands/vacuum.c 12 Apr 2002 20:38:25 -0000 1.223
--- src/backend/commands/vacuum.c 14 Apr 2002 16:41:37 -0000
***************
*** 181,186 ****
--- 181,189 ----
if (IsTransactionBlock())
elog(ERROR, "%s cannot run inside a BEGIN/END block", stmttype);
+ if (!MemoryContextContains(QueryContext, vacstmt))
+ elog(ERROR, "VACUUM cannot be executed from a function");
+
/*
* Send info about dead objects to the statistics collector
*/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
