Re: spurious function execution in prepared statements.

Stephan Szabo wrote:
> On Thu, 30 Sep 2004, Merlin Moncure wrote:
>
> > OK, I have a situation that might be a performance problem, a bug,
or an
> > unavoidable consequence of using prepared statements. The short
version
> > is that I am getting function executions for rows not returned in a
> > result set when they are in a prepared statement.
> An actual boolean expr on t? Or on a column in t?
[...]
> I think a reproducible example would be good. Simple attempts to
duplicate
> this on 8.0b2 have failed for me, unless I'm using order by.

Note: I confirmed that breaking out the 'where' part of the query into
subquery suppresses the behavior.

Here is the actual query:
select lock_cuid(id), *
from data3.wclaim_line_file
where wcl_vin_no >= '32-MHAB-C-X-7243' and
(wcl_vin_no > '32-MHAB-C-X-7243' or wcl_claim_no >=
001) and
(wcl_vin_no > '32-MHAB-C-X-7243' or wcl_claim_no >
001 or id > 2671212)
order by wcl_vin_no, wcl_claim_no, id
limit 1

Here is the prepared statement declaration:
prepare data3_read_next_wclaim_line_file_1_lock (character varying,
numeric, int8, numeric)
as select lock_cuid(id), *
from data3.wclaim_line_file
where wcl_vin_no >= $1 and
(wcl_vin_no > $1 or wcl_claim_no >= $2) and
(wcl_vin_no > $1 or wcl_claim_no > $2 or id > $3)
order by wcl_vin_no, wcl_claim_no, id limit $4

Here is the plan when it runs lock_cuid repeatedly (aside: disabling
seqscans causes an index plan, but that's not the point):

esp=# explain execute data3_read_next_wclaim_line_file_1_lock
('32-MHAB-C-X-7243', 001, 2671212, 1);

QUERY PLAN

------------------------------------------------------------------------
----------------------------
------------------------------------------------------------------------
----------------------------
--------------------------------
Limit (cost=13108.95..13162.93 rows=21592 width=260)
-> Sort (cost=13108.95..13162.93 rows=21592 width=260)
Sort Key: wcl_vin_no, wcl_claim_no, id
-> Seq Scan on wclaim_line_file (cost=0.00..11554.52
rows=21592 width=260)
Filter: (((wcl_vin_no)::text >= ($1)::text) AND
(((wcl_vin_no)::text > ($1)::text) OR
((wcl_claim_no)::numeric >= $2)) AND (((wcl_vin_no)::text > ($1)::text)
OR ((wcl_claim_no)::numeric
> $2) OR ((id)::bigint > $3)))
(5 rows)