Lists: | pgsql-hackers |
---|
From: | Jaime Casanova <systemguards(at)gmail(dot)com> |
---|---|
To: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | avoid pulling up subquerys that contain volatile functions? |
Date: | 2005-10-08 15:51:14 |
Message-ID: | c2d9e70e0510080851h3396e2b3hd1b21f83fe6f281c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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_true
from (select alu_codigo, (random() * 5) as is_true
from rec_m_alumno) as t_tmp
where 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 ;)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jaime Casanova <systemguards(at)gmail(dot)com> |
Cc: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: avoid pulling up subquerys that contain volatile functions? |
Date: | 2005-10-08 18:10:00 |
Message-ID: | 29438.1128795000@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Jaime Casanova <systemguards(at)gmail(dot)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
From: | Jaime Casanova <systemguards(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: avoid pulling up subquerys that contain volatile functions? |
Date: | 2005-10-09 07:35:13 |
Message-ID: | c2d9e70e0510090035i6ecd0b92s4d85d10b243a62a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 10/8/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jaime Casanova <systemguards(at)gmail(dot)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 ;)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jaime Casanova <systemguards(at)gmail(dot)com> |
Cc: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: avoid pulling up subquerys that contain volatile functions? |
Date: | 2005-10-09 16:24:29 |
Message-ID: | 6755.1128875069@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Jaime Casanova <systemguards(at)gmail(dot)com> writes:
> On 10/8/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From: | Jaime Casanova <systemguards(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PG Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: avoid pulling up subquerys that contain volatile functions? |
Date: | 2005-10-11 20:34:15 |
Message-ID: | c2d9e70e0510111334p5b969dbdt5ba6072dc3546c15@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On 10/9/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jaime Casanova <systemguards(at)gmail(dot)com> writes:
> > On 10/8/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 ;)