Search Postgresql Archives

Re: why VOLATILE attribute is required?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak <rafal@xxxxxxxxxxxxxxxxxx> wrote:
> Hi All,
>
> I have this function:
> CREATE FUNCTION mypass(newpass text) returns text ....
> EXECUTE 'ALTER USER ' || quote_ident(session_user) || ' PASSWORD ' ||
> quote_literal(newpass); return session_user::text;
>
> to varify user passwords before allowing a change.
>
> I've put that function in a RULE that some housekeeping, like updating
> user state (last pass change, etc):
>
> CREATE RULE pass AS  ON UPDATE TO myself  WHERE old.pass <> new.pass DO
> INSTEAD UPDATE people SET ....  WHERE username=mypass(new.username)
>
> but I get:
> ERROR:  ALTER ROLE is not allowed in a non-volatile function
>
> Why???
>
> 1. The function is "obviously STABLE", since it's outcome will not
> change enything in datatables (I think) - and I can arrange for its
> output being stable within a transaction (if I don't do SET
> AUTHORIZATION within the transation, right?).
> 2. for the purpose I need, the function could/should be "computted
> once", and result used "meny times" (for filtering PEOPLE rows). Having
> it get evaluated for every row is a signifficant unnecesary cost
> panelty.

if you change the state of the database, including (and especially)
system catalogs, your function is volatile, period.

merlin

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux