Thread: avoid pulling up subquerys that contain volatile functions?
the comments fot contain_volatile_functions in clauses.c says... src/backend/optimizer/util/clauses.c:** XXX we do not examine sub-selects to see if they contain uses of* volatile functions. It's not real clear if that is correct or not...*/ but this example seems to clarify (or at least i think) that we have to avoid pulling up subquerys containing volatile functions: --- BEGIN SQL --- create view vfoo_random as select alu_codigo, is_truefrom (select alu_codigo, (random() * 5) as is_true from rec_m_alumno) as t_tmpwhere is_true> 1; select count(*) from vfoo_random where is_true < 1; drop view vfoo_random; --- END SQL --- i thought it was just calling contain_volatile_function from is_simple_subquery() in src/backend/optimizer/prep/prepjointree.c but it doesn't work for me. what i miss? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
Jaime Casanova <systemguards@gmail.com> writes:
> but this example seems to clarify (or at least i think) that we have to avoid
> pulling up subquerys containing volatile functions:
This is exactly the same example discussed in previous threads on this
issue. Do you think it will change anyone's mind?
regards, tom lane
On 10/8/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jaime Casanova <systemguards@gmail.com> writes: > > but this example seems to clarify (or at least i think) that we have to > avoid > > pulling up subquerys containing volatile functions: > > This is exactly the same example discussed in previous threads on this > issue. Do you think it will change anyone's mind? > > regards, tom lane > you are right, i haven't internet all day this week so i'm reading mails for parts... in any case, i still think that is better to get bad performance because i forgot to correctly mark a function that to get incorrect data from a correct query because a "gotcha"... there is a precedent for this in postgres??? BTW, i still wanna get a patch for my postgres... so i will keep trying... but i don't understand why when i add the function contain_volatile_functions in the is_simple_subquery function i got the same results... :) -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
Jaime Casanova <systemguards@gmail.com> writes:
> On 10/8/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This is exactly the same example discussed in previous threads on this
>> issue. Do you think it will change anyone's mind?
> in any case, i still think that is better to get bad performance
> because i forgot to correctly mark a function that to get incorrect
> data from a correct query because a "gotcha"... there is a precedent
> for this in postgres???
Just to be clear, I'm in favor of changing it; but the majority opinion
in the previous discussion seemed to be against.
> ... but i don't understand why when i add the function
> contain_volatile_functions in the is_simple_subquery function i got
> the same results... :)
You should only be enforcing the restriction against the subquery's
target list anyway. The expression_returns_set test is the model to
follow. BTW, you'll also need to make some fixes in allpaths.c, else
you'll still get bit by qual pushdown; again, look for
expression_returns_set.
regards, tom lane
On 10/9/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jaime Casanova <systemguards@gmail.com> writes: > > On 10/8/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> This is exactly the same example discussed in previous threads on this > >> issue. Do you think it will change anyone's mind? > > > in any case, i still think that is better to get bad performance > > because i forgot to correctly mark a function that to get incorrect > > data from a correct query because a "gotcha"... there is a precedent > > for this in postgres??? > > Just to be clear, I'm in favor of changing it; but the majority opinion > in the previous discussion seemed to be against. > >[snipped some interesting explanation about this] > > regards, tom lane > Maybe, document it? even with an example? and the workaround of course -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)