Thread: Is 9.1 considered more stable/robust than 9.0.4 ?

http://blog.2ndquadrant.com/en/2011/09/limitations-removed-in-postgre.html


On Thu, 2011-09-22 at 02:36 -0500, Abraham, Danny wrote:
> Thanks
> Danny


--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

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

On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> 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

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> but I get:
> ERROR:  ALTER ROLE is not allowed in a non-volatile function
> Why???

Because non-volatile means, among other things, "this function has no
side effects".

            regards, tom lane

On Thu, 2011-09-22 at 07:50 -0500, Merlin Moncure wrote:
> On Thu, Sep 22, 2011 at 5:28 AM, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:
>
> if you change the state of the database, including (and especially)
> system catalogs, your function is volatile, period.

Hmmm. To quote from the online documentation:

"VOLATILE indicates that the function value can change even within a
single table scan"

this funciton return value does not change. Further we read:

"any function that has side-effects must be classified volatile, even if
its result is quite predictable, to prevent calls from being optimized
away;"

Well. In this caase, I'd like it being optimised away. This is the
expected result. And the above documentation fragment states it as only
a precausion, not an ERROR.

Are there other reasons for that ERROR (e.g. not a warrning)?

On Thu, 2011-09-22 at 10:05 -0400, Tom Lane wrote:
Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > but I get:
> > ERROR:  ALTER ROLE is not allowed in a non-volatile function
> > Why???
>
> Because non-volatile means, among other things, "this function has no
> side effects".
>
>             regards, tom lane

Still, those side efects are "stable" - meaning (in a particular case of
that function) for the same input, thay are always the same..... withoun
a transaction could possibly by optimized away.

Then again, with postgres 9.1 I'd write a "RULE ... WITH user (uid) as
(SELECT mypass() as uid) SELECT ... FROM user ..." that would do the
expected optimization explicitly. This would work, right? And the
optimization of a STABLE function within a query, triggers just that,
right? So why to raise and ERROR?


-R

Rafal Pietrak wrote:
> ERROR:  ALTER ROLE is not allowed in a non-volatile function
>
> Why???

See http://www.postgresql.org/docs/8.3/static/xfunc-volatility.html :

A STABLE function cannot modify the database [...]

Yours,
Laurenz Albe

On Thu, 2011-09-22 at 16:57 +0200, Szymon Guz wrote:
>
>
> On 22 September 2011 16:29, Rafal Pietrak <rafal@zorro.isa-geek.com>
> wrote:
[----------]
>
>         Well. In this caase, I'd like it being optimised away. This is
>         the
>         expected result. And the above documentation fragment states
>         it as only
>         a precausion, not an ERROR.
>
>         Are there other reasons for that ERROR (e.g. not a warrning)?
>
>
> Yes, the reason is that caching that function could be a problem.

I only could guess here, that caching is taking the function fingerprint
(e.g. name+arg-types) as key with result as value ... saved after first
call when function is stable. This does not sound inapropiate or
difficult in such cases. But naturally, I don't know the real internals,
so I admit, there may be a problem here.
>
> And why do you want to optimize that?

Currently it's 10k users, I have to plan for 100k. Haveing system table
updated this many times for every password change is not too friendly to
a database.


> Does it take too much time, or do you want to call that thousands of
> times?

...but on the other hand, passwords changes don't happen that often....
so performence will not suffer much.

> Have you checked how much the function lasts?

No, It's just that I don't like to build a systems that issues database
updates when those are not really necesary.
>
>
But I undestand that the overall conclusion is that sideeffects are
volatile no matter what (and the more relaxed statements in the
documentation should not be taken as and invitation to experiment :).

Thank you all for the feedback

-R

On Thu, 2011-09-22 at 02:36 -0500, Abraham, Danny wrote:
> Thanks
>
> Danny

If you are considering migrating from 9.0.X to 9.1.Y, then perhaps wait
for a couple patch releases before going into production. However, I
highly recommend that you migrate your development environment now, at
least as a test. That will give you advance warning of any problems.

However, if you are developing a new application, you might as well
develop against 9.1, because it will stabilize while you develop and do
your own testing.

Regards,
    Jeff Davis