Re: advisory locks and permissions

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.