Thread: function side effects
Hi, 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? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
"Tatsuo Ishii" <ishii@postgresql.org> wrote: > VOLATILE functions 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? I think it's possible. We might borrow words and semantics from unctional programming languages for functions with side effects. How do they handle the issue? BTW, random() *writes* the random seed, though no one will mind it. Regards, --- Takahiro Itagaki NTT Open Source Software Center
Tatsuo Ishii <ishii@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. 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.
regards, tom lane
Tatsuo Ishii wrote: > Hi, > > 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. I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> > 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. > > I was talking about this to someone in Cuba and one conclusion we > reached was that this was a fairly difficult task -- consider that > someone may choose to define an innocent-looking operator using a > volatile function. If you only examine things that look like functions > in the query you will miss those. The only way to figure out whether a > query has a write effect is to ask the server about the whole query. In general you are right. However in most database application systems, it is possible that all functions are properly designed and implemented (at least they want so). In this world, more or less PostgreSQL functions are just a part of their applications. If they trust their client side applications, why they cannot trust PostgreSQL custom functions as well? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Those classifications are meant as planner directives; they are > NOT meant to be bulletproof. 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. Could it work to store a flag in each process to indicate when it is executing a non-volatile function, and throw an error on any attempt to call a volatile function or modify the database? -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Those classifications are meant as planner directives; they are
>> NOT meant to be bulletproof. 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.
> Could it work to store a flag in each process to indicate when it is
> executing a non-volatile function, and throw an error on any attempt
> to call a volatile function or modify the database?
It's *not an error* for a nonvolatile function to call a volatile one.
At least it's never been in the past, and I'm sure you'd break some
applications if you made it so in the future.
regards, tom lane
> > I was talking about this to someone in Cuba and one conclusion we > > reached was that this was a fairly difficult task -- consider that > > someone may choose to define an innocent-looking operator using a > > volatile function. If you only examine things that look like functions > > in the query you will miss those. The only way to figure out whether a > > query has a write effect is to ask the server about the whole query. > > In general you are right. However in most database application > systems, it is possible that all functions are properly designed and > implemented (at least they want so). In this world, more or less > PostgreSQL functions are just a part of their applications. If they > trust their client side applications, why they cannot trust PostgreSQL > custom functions as well? Still there could be "none honest functions" such as calling volatile functions from non volatile function in the PostgreSQL system(I have not made any investigation. But it's possible). Or vendor provided functions (for example embedded in closed source packages) might fall into this category. Probably it's enough for pgpool to have a "black list" of such that function. Maintaining such a list is a boring task but I cannot think of any good way at this point. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Tue, Feb 23, 2010 at 11:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > It's *not an error* for a nonvolatile function to call a volatile one. it should be considered an error i think, someone think there is a use cas for calling volatile functions inside stable ones but i can see what that reason could be... > At least it's never been in the past, and I'm sure you'd break some > applications if you made it so in the future. > i'm sure of that too, but in this case seems reasonable to do so -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> throw an error on any attempt to call a volatile function or >> modify the database? > It's *not an error* for a nonvolatile function to call a volatile > one. Right, we all know it currently doesn't throw an error, but I can't think of anywhere I'd like to have someone do that in a database for which I have any responsibility. Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? -Kevin
On Tue, Feb 23, 2010 at 4:52 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Right, we all know it currently doesn't throw an error, but I can't > think of anywhere I'd like to have someone do that in a database for > which I have any responsibility. Does anyone have a sane use case > for a non-volatile function to call a volatile one or to update the > database? So consider for example a function which explicitly sets the timezone and then uses timestamp without timezone functions (which are volatile only because the GUC variable might change between calls). Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. -- greg
Greg Stark <gsstark@mit.edu> wrote: > Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Does anyone have a sane use case for a non-volatile function to >> call a volatile one or to update the database? > > So consider for example a function which explicitly sets the > timezone and then uses timestamp without timezone functions (which > are volatile only because the GUC variable might change between > calls). OK, I can see where that would be sane, but it seems more fragile than using timestamp with time zone. But, OK, something sane and functional could break on that. > Or somebody who uses the tsearch functions because they're > planning to not change their dictionaries. I didn't realize tsearch functions were volatile. Should they really be so? > Or builds a hash function by calling random after setting the seed > to a specific value -- this is actually a fairly popular strategy > for building good hash functions. I'd never seen that. I'm not sure I understand where that comes in useful, but if you've seen it enough to call it "fairly popular" I guess I have to accept it. Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) -Kevin
On Tue, Feb 23, 2010 at 6:39 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Or somebody who uses the tsearch functions because they're >> planning to not change their dictionaries. > > I didn't realize tsearch functions were volatile. Should they > really be so? Uhm, my mistake. They're stable. Ok, for that one I'll substitute a function which uses pg_read_file knowing that the file in question won't be changed. Perhaps it's a per-machine key or something like that. >> Or builds a hash function by calling random after setting the seed >> to a specific value -- this is actually a fairly popular strategy >> for building good hash functions. > > I'd never seen that. I'm not sure I understand where that comes in > useful, but if you've seen it enough to call it "fairly popular" I > guess I have to accept it. http://en.wikipedia.org/wiki/Universal_hashing They have the useful property that it's hard for an attacker to contrive data which has poor collision behaviour. > Thanks for the examples. They did make me consider a real-life type > of process which isn't currently implemented as a PostgreSQL > function, but conceivably could be -- randomizing a pool of jurors > to facilitate jury selection. My eyes are opened. :-) I'm not actually sure I follow what you're picturing. -- greg
Greg Stark <gsstark@mit.edu> writes:
> On Tue, Feb 23, 2010 at 6:39 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>> I didn't realize tsearch functions were volatile. �Should they
>> really be so?
> Uhm, my mistake. They're stable.
IMMUTABLE/STABLE/VOLATILE is not really about side effects, it is about
how long the function value can be expected to hold still for.
There are quite a lot of cases of functions that are marked
conservatively as stable (or even volatile) but could be considered
immutable in particular queries, because the application developer is
prepared to assume that values such as GUCs won't change in his usage.
The traditional way to deal with that is to wrap them in an immutable
wrapper function. There's actually code in the planner to make that
work --- we have to suppress inlining to avoid exposing the not-immutable
guts, else the planner will not do what's wanted.
There may be some value in inventing a "has no side effects" marker, but
that should not be confused with IMMUTABLE/STABLE.
regards, tom lane
On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl@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. Yeah, that's what I was thinking, too.... ...Robert
Greg Stark <gsstark@mit.edu> wrote: > Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Thanks for the examples. They did make me consider a real-life >> type of process which isn't currently implemented as a PostgreSQL >> function, but conceivably could be -- randomizing a pool of >> jurors to facilitate jury selection. My eyes are opened. :-) > > I'm not actually sure I follow what you're picturing. Well, to facilitate people's rights to a jury of their peers, we obtain lists of people in each county based on having a drivers license or state ID, being registered to vote, etc., then (after eliminating duplicates and those who have served on juries in recent years) we randomly select a subset, who get questionnaires, from which (at a later date) we randomly pick people to summon for jury a juror panel, from which (on each day they appear) we randomly select people for particular juries. Any flaw in the randomness of selection could constitute grounds for an appeal of the outcome of a case, so we have to be careful about process. (Randomness being defined as the properties that nobody with an interest in the case can control or predict who will be selected from one group into the next, and there is no bias on anything related to demographics, like age or last name [which could correlate with ethnicity]). Sounds like fun, eh? -Kevin
On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl@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
On Mon, 2010-02-22 at 23:49 -0500, Tom Lane wrote: > Tatsuo Ishii <ishii@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
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
Simon Riggs <simon@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
> > 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
> IMMUTABLE/STABLE/VOLATILE is not really about side effects, it is about > how long the function value can be expected to hold still for. > > There are quite a lot of cases of functions that are marked > conservatively as stable (or even volatile) but could be considered > immutable in particular queries, because the application developer is > prepared to assume that values such as GUCs won't change in his usage. > The traditional way to deal with that is to wrap them in an immutable > wrapper function. There's actually code in the planner to make that > work --- we have to suppress inlining to avoid exposing the not-immutable > guts, else the planner will not do what's wanted. "IMMUTABLE indicates that the function cannot modify the database..." "STABLE indicates that the function cannot modify the database..." Apparently IMMUTABLE/STABLE should not write to database according to docs. Are you saying that in the real world these are ignored? If so, this is an important database intergrity issue as Simon pointed out. > It's *not an error* for a nonvolatile function to call a volatile one. > At least it's never been in the past, and I'm sure you'd break some > applications if you made it so in the future. If this is true, at least the docs should be corrected IMO. > There may be some value in inventing a "has no side effects" marker, but > that should not be confused with IMMUTABLE/STABLE. It seems it is neccessary to invent new marker for not only pgpool but HOT/SR (and may be Slony). They need to know if a query including functions do writes or not *before* sending to backend. Otherwise they got error because they sent a write query to standby. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Tatsuo Ishii <ishii@postgresql.org> writes:
> Apparently IMMUTABLE/STABLE should not write to database according to
> docs. Are you saying that in the real world these are ignored? If so,
> this is an important database intergrity issue as Simon pointed out.
One more time: these markings are not about whether the function writes
to the database. They are about whether its result value can be
presumed to be unchanging in various circumstances. Trying to redefine
them for another purpose is going to lead to nothing but trouble.
And no, there is not an "integrity issue" here. If the planner thinks
something is stable or immutable, it might evaluate it fewer times than
the user would wish, but that doesn't render the database inconsistent.
It just means the user doesn't get the behavior he wanted. That's no
different from any other erroneously-written query.
> It seems it is neccessary to invent new marker for not only pgpool but
> HOT/SR (and may be Slony). They need to know if a query including
> functions do writes or not *before* sending to backend. Otherwise they
> got error because they sent a write query to standby.
Well, that's something we can consider adding in 9.1, but it's far too
late for 9.0. Personally I find that goal rather suspect anyway.
I think the chances of determining this reliably in pgpool are
negligible, even if functions were marked like that. You would need to
duplicate *all* of the backend's parsing and all of its state (eg schema
search path) in order to discover anything.
regards, tom lane
On Tue, Feb 23, 2010 at 10:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Personally I find that goal rather suspect anyway. > I think the chances of determining this reliably in pgpool are > negligible, even if functions were marked like that. You would need to > duplicate *all* of the backend's parsing and all of its state (eg schema > search path) in order to discover anything. > i agree with that, as Alvaro suggested maybe a way to ask the server about the whole query is the way to go -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: > On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl@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? SQL standard: <SQL-data access indication> ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Notice also that this is separate from <deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC which is the SQL standard's variant of volatility. So someone has already had the idea that these two should exist separately.
On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: >> On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl@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? > > SQL standard: > > <SQL-data access indication> ::= > NO SQL > | CONTAINS SQL > | READS SQL DATA > | MODIFIES SQL DATA > good! > Notice also that this is separate from > > <deterministic characteristic> ::= > DETERMINISTIC > | NOT DETERMINISTIC > so IMMUTABLE = DETERMINISTIC NO SQL, STABLE = DETERMINISTIC READS SQL DATA VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA > which is the SQL standard's variant of volatility. > > So someone has already had the idea that these two should exist > separately. > seems something we should implement -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Peter Eisentraut <peter_e@gmx.net> writes:
> SQL standard:
> <SQL-data access indication> ::=
> NO SQL
> | CONTAINS SQL
> | READS SQL DATA
> | MODIFIES SQL DATA
Huh. I understand three of those, but what is the use of CONTAINS SQL?
Seems like that would have to be the same as the last one, or maybe
the next-to-last one if you're prepared to assume it's read-only SQL.
regards, tom lane
Jaime Casanova írta: > On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > >> On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: >> >>> On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl@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? >>> >> SQL standard: >> >> <SQL-data access indication> ::= >> NO SQL >> | CONTAINS SQL >> | READS SQL DATA >> | MODIFIES SQL DATA >> >> > > good! > > >> Notice also that this is separate from >> >> <deterministic characteristic> ::= >> DETERMINISTIC >> | NOT DETERMINISTIC >> >> > > so IMMUTABLE = DETERMINISTIC NO SQL, > STABLE = DETERMINISTIC READS SQL DATA > VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA > > >> which is the SQL standard's variant of volatility. >> >> So someone has already had the idea that these two should exist >> separately. >> >> > > seems something we should implement > At least the combinations to recognize the current IMMUTABLE/STABLE/VOLATILE features. By definition, READS SQL DATA and MODIFIES SQL DATA cannot be DETERMINISTIC. But I can imagine some C and PL/Perl functions that are NOT DETERMINISTIC NO SQL. And what does "CONTAINS SQL" mean? Is it distinct from the other two READS/MODIFIES SQL DATA markers? "SELECT CURRENT_TIMESTAMP" may be an example but it doesn't seem to be significantly different from $$SELECT $1 || $2;$$ LANGUAGE SQL or the same written in PL/Perl or C. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics ---------------------------------- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/
On Mon, Mar 1, 2010 at 4:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter_e@gmx.net> writes: >> SQL standard: > >> <SQL-data access indication> ::= >> NO SQL >> | CONTAINS SQL >> | READS SQL DATA >> | MODIFIES SQL DATA > > Huh. I understand three of those, but what is the use of CONTAINS SQL? > Seems like that would have to be the same as the last one > i guess the safer asumption is: treat it as MODIFIES SQL DATA -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter_e@gmx.net> writes: >> SQL standard: > >> <SQL-data access indication> ::= >> NO SQL >> | CONTAINS SQL >> | READS SQL DATA >> | MODIFIES SQL DATA > > Huh. I understand three of those, but what is the use of CONTAINS > SQL? Seems like that would have to be the same as the last one, > or maybe the next-to-last one if you're prepared to assume it's > read-only SQL. On a quick search of the spec, the best I was able to tell was that you are required to use "CONTAINS SQL" if the language is SQL. Perhaps it figures that the database engine can determine the read/write behavior directly if the language is SQL, and you tell it what it does if you're coding in some other language. -Kevin
On mån, 2010-03-01 at 15:30 -0500, Jaime Casanova wrote: > so IMMUTABLE = DETERMINISTIC NO SQL, > STABLE = DETERMINISTIC READS SQL DATA > VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA It might be tempting to create such a mapping, but there could be a number of pitfalls, especially if you define it as a commutative equivalence rather than say logical implications. For example, MODIFIES SQL DATA ought to imply VOLATILE, but the reverse is not true. When the volatility attribute was introduced, we briefly looked at the standard "deterministic" attribute, but concluded that it would be better to create settings that describe how the PostgreSQL planner/executor works instead of some abstract setting that is descriptive but doesn't actually help optimizing the query. We might actually end up with all three groups of settings at some point.
On mån, 2010-03-01 at 16:29 -0500, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > SQL standard: > > > <SQL-data access indication> ::= > > NO SQL > > | CONTAINS SQL > > | READS SQL DATA > > | MODIFIES SQL DATA > > Huh. I understand three of those, but what is the use of CONTAINS SQL? My reading is that CONTAINS SQL allows/indicates the use of non-data statements such as CREATE or ALTER, whereas READS SQL DATA and MODIFIES SQL DATA specifically refer to reading or writing table data.