On Wed, 20 Sep 2006, Bruce Momjian wrote: > > Doesn't creating many temp tables in a transaction do the same thing? > > ---------------------------------------------------------------------------
Like this? jeremyd=# CREATE OR REPLACE FUNCTION testy(n integer) returns integer as $$ BEGIN EXECUTE 'CREATE TEMP TABLE testy_' || n::text || ' (a integer, b text);'; RETURN n; END; $$ LANGUAGE plpgsql VOLATILE STRICT; CREATE FUNCTION jeremyd=# select testy(n) from generate_series(1,1000000) n; WARNING: out of shared memory CONTEXT: SQL statement "CREATE TEMP TABLE testy_3323 (a integer, b text);" PL/pgSQL function "testy" line 2 at execute statement ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. CONTEXT: SQL statement "CREATE TEMP TABLE testy_3323 (a integer, b text);" PL/pgSQL function "testy" line 2 at execute statement > > Josh Berkus wrote: > > All, > > > > > I vote for locking down to superuser access (lets be frank here: I > > > would estimate 90%+ database installatons run with the application as > > > root) so we are not losing much. > > > > Not in my experience. Note that making them superuser-only pretty much > > puts > > them out of the hands of hosted applications. > > > > How simple would it be to limit the number of advisory locks available to a > > single request? That would at least make the DOS non-trivial. Or to put in > > a handle (GUC?) that allows turning advisory locks off? > > > > Hmmm ... I'll bet I could come up with other ways to use generate_series in > > a > > DOS, even without advisory locks ... > > > > -- > > Josh Berkus > > PostgreSQL @ Sun > > San Francisco > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > -- Two percent of zero is almost nothing. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
