function side effects
in [PgSql]
|
From: Jaime Casanova on 23 Feb 2010 16:54 On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > > There may be some value in inventing a "has no side effects" marker, but > that should not be confused with IMMUTABLE/STABLE. > a READONLY function? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 23 Feb 2010 17:21 On Mon, 2010-02-22 at 23:49 -0500, Tom Lane wrote: > Tatsuo Ishii <ishii(a)postgresql.org> writes: > > I'm wondering if we could detect a funcion has a side effect, > > i.e. does a write to database. > > > Currently we have three properties of functions: IMMUTABLE, STABLE and > > VOLATILE. According to docs IMMUTABLE or STABLE functions do not write > > to database. > > Those classifications are meant as planner directives; they are NOT > meant to be bulletproof. You make them sound like "hints". (I thought we frowned on those?) That isn't true, they don't just change the optimal plan in the way the enable_* parameters do. Immutable functions are reduced in ways that would give the wrong answer if the function is actually volatile. Referring to function properties as "planner directives" hides their critical importance to the output of a query that calls such functions. > Hanging database integrity guarantees on > whether a "non volatile" function changes anything is entirely unsafe. > To give just one illustration of the problems, a nonvolatile function > is allowed to call a volatile one. So wrongly marking a function as something other than volatile *is* a data integrity issue. Why is that OK? ISTM that this should work the way Tatsuo wants it to work. Immutability should be passed down through the call stack to ensure we can't get this wrong. If people have been advising clients to set things immutable when they are not that seems fairly questionable. We shouldn't avoid fixing an integrity loophole just simply to preserve a planner backdoor, especially since other backdoors are specifically avoided. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 23 Feb 2010 17:35 On Tue, 2010-02-23 at 12:51 +0900, Tatsuo Ishii wrote: > I'm wondering if we could detect a funcion has a side effect, > i.e. does a write to database. This is neccessary for pgpool to decide > if a qeury should to be sent to all of databases or not. If a query > includes functions which do writes to database, it should send the > query to all of databases, otherwise the contents of databases go into > inconsistent state. > > Currently we have three properties of functions: IMMUTABLE, STABLE and > VOLATILE. According to docs IMMUTABLE or STABLE functions do not write > to database. VOLATILE functions *may* do writes to database. Maybe I > could regard VOLATILE functions always do write, but priblem is, > VOLATILE qfunctions such as random() and timeofday() apparently do not > write and sending those queries that include such functions is > overkill. > > Can we VOLATILE property divide into two categories, say, VOLATILE > without write, and VOLATILE with write? pgpool parses the query before deciding how to route it, yes? Why not mark random() and timeofday() as stable in the pgpool catalog, yet leave them as volatile on the database servers? It will "just work" then. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 23 Feb 2010 18:04 Simon Riggs <simon(a)2ndQuadrant.com> writes: > So wrongly marking a function as something other than volatile *is* a > data integrity issue. Why is that OK? ISTM that this should work the way > Tatsuo wants it to work. Please read the rest of the thread. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tatsuo Ishii on 23 Feb 2010 19:56
> > I'm wondering if we could detect a funcion has a side effect, > > i.e. does a write to database. This is neccessary for pgpool to decide > > if a qeury should to be sent to all of databases or not. If a query > > includes functions which do writes to database, it should send the > > query to all of databases, otherwise the contents of databases go into > > inconsistent state. > > > > Currently we have three properties of functions: IMMUTABLE, STABLE and > > VOLATILE. According to docs IMMUTABLE or STABLE functions do not write > > to database. VOLATILE functions *may* do writes to database. Maybe I > > could regard VOLATILE functions always do write, but priblem is, > > VOLATILE qfunctions such as random() and timeofday() apparently do not > > write and sending those queries that include such functions is > > overkill. > > > > Can we VOLATILE property divide into two categories, say, VOLATILE > > without write, and VOLATILE with write? > > pgpool parses the query before deciding how to route it, yes? Right. > Why not mark random() and timeofday() as stable in the pgpool catalog, > yet leave them as volatile on the database servers? It will "just work" > then. Please note that random() and timeofday() are just examples. What I'm thinking about was, a function which directly or indirectly cause write to database (thus lead writing to log). Consider a function that calls those has-side-effect functions. We need a property which is inherited to child function to parent function. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |