Re: Problem with volatile function - Mailing list pgsql-general

From Craig Ringer
Subject Re: Problem with volatile function
Date
Msg-id 4859FFF8.3030908@postnewspapers.com.au
Whole thread Raw
In response to Re: Problem with volatile function  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
>> Personally I'd expect that to only evaluate once. It's saying "where
>> f_name.counter in this row is equal to some single random value
>> generated at the start of the query". The parameters of the random()
>> function do not depend on the input, so Pg evaluates it once rather than
>> with each row.
>
> No, that's not the model that we use.  In my view, the semantics
> specified by the SQL standard are that
>     SELECT ... FROM a, b, c WHERE ...
> are that you notionally form the Cartesian product of a X b X c
> and then evaluate the WHERE condition at each row of the product
> to find out which rows make it into the SELECT result.  Obviously,
> the DBMS is going to try like heck to optimize the execution of that
> model, but its optimizations should not change the results.

Thanks for explaining that; I'd completely misunderstood the way Pg
handles evaluation of expressions that don't explicitly reference fields
in the records being scanned.

I think the workarounds I suggested should still work, despite the
incorrect reasoning on which they were based.

--
Craig Ringer

pgsql-general by date:

Previous
From: Artacus
Date:
Subject: Re: Problem with volatile function
Next
From: "Scott Marlowe"
Date:
Subject: Re: PITR base backup -- stop server or not?