[GENERAL] Discerning when functions had execute revoked from public

[Todd Kover]

I am trying to write something that will enumerate grants/revokes on
functions to make sure they are adjusted properly after said function is
drop/recreated, should that happen.  This will also be used to validate
that permissions are what they should be.

According to:

http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

 } Another point to keep in mind is that by default, execute privilege
 } is granted to PUBLIC for newly created functions (see GRANT for
 } more information). Frequently you will wish to restrict use of a
 } security definer function to only some users. To do that, you must
 } revoke the default PUBLIC privileges and then grant execute privilege
 } selectively. To avoid having a window where the new function is
 } accessible to all, create it and set the privileges within a single
 } transaction.

This revocation from public happens in our environment.  Trouble is, I
can not find where an indiciation that execute has been revoked from
public in pg_catalog.pg_proc (or any other table for that matter).  Is
there a way to find this somewhere in the catalog?

Apologies if this should be obvious.  I'm sure I will find it as soon as
I hit send.  :-)

thanks,
-Todd


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] aggregate functions on massive number of rows

[Todd Kover]

I have an aggregate function setup (attached) that I'm calling over a
massive amount of data and am running into:

ERROR:  cannot have more than 2^32-1 commands in a transaction
CONTEXT:  PL/pgSQL function "float8_jitter_add" line 16 at if

error.  Since I'm able to do count() and avg() over the same values
without this aggregate function, it's theoretically possible.

Something was making me think that it was the extract() that was doing
it (I used to have 'select extract(milliseconds from v_rtt_in) into
v_rtt' and something leaded me to believe the error was with that), but
I don't recall what that is, so it looks like it's just the if like it
says.

Since I'm not really doing anything transaction-oriented in
float8_jitter_add, is there a way to get around this?  Or is this a
limitation in pl/pgsql?

thanks in advance,
-Todd

--
create or replace function float8_jitter_add(float8[], interval)
returns float8[] as '
declare
v_old_state ALIAS FOR $1;
v_rtt_in ALIAS FOR $2;
v_state float8[];
v_rtt float8;
BEGIN
v_state := v_old_state;
v_rtt := extract(milliseconds from v_rtt_in);

IF v_old_state is NULL THEN
v_state := ''{0,0,0,0}'';
v_state[1] = 0;
v_state[2] = 0;
v_state[3] = v_rtt;
v_state[4] = 1;
ELSIF v_rtt IS NOT NULL THEN
if v_old_state[4] = 1 THEN
v_state[1] := v_old_state[2] + (v_old_state[3] - v_rtt);
v_state[2] := v_old_state[2] + 1;
END IF;
v_state[3] := v_rtt;
v_state[4] := 1;
ELSE
v_state[4] := 0;
END IF;
return v_state;
END;
' language 'plpgsql';

create or replace function float8_jitter_sum(float8[])
returns float8 as '
declare
v_state ALIAS FOR $1;
v_avg float8;
BEGIN
v_avg := NULL;
if v_state[1] != 0 THEN
v_avg := v_state[1] / v_state[2];
END IF;
return v_avg;
END;
' language 'plpgsql';

create aggregate jitter (
basetype = interval,
sfunc = float8_jitter_add,
stype = float8[],
finalfunc = float8_jitter_sum
);


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings