| From: | Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | why VOLATILE attribute is required? |
| Date: | 2011-09-22 10:28:39 |
| Message-ID: | 1316687319.31744.50.camel@localhost.localdomain |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
Probably I miss something. So my question is: why the database enforces
the VOLATILE attribute when function contains "ALTER ROLE ..."?
BTW: my postgres is 8.3
-R
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rohan Malhotra | 2011-09-22 11:32:16 | limitby without orderby |
| Previous Message | Rajesh Kumar Mallah | 2011-09-22 09:47:54 | Re: 10GbE / iSCSI storage for postgresql. |