Forum OpenACS Development: Re: PREPARE considered beneficial

Collapse
Posted by Jeff Davis on
The difficulty with prepared statements is that you need to provide types when you prepare. We could declare them all TEXT, but I am not entirely sure if when you do that whether the resulting query plan is any good (when you give explicit text strings as we do in our fake bind vars it might be smarter than it would be w/o knowing anything).

prepared statements are per db session but for us you would not want to prepare everything since the session would get pretty big, also things like dynamic sort statements and restrictions like you get with listbuilder couldn't be prepared (well, they could but you would need to generate a synthetic key like a hash to identify them rather than using the statement name).

Interestingly, I think you could do it all withough any changes to the db driver itself. Inside db_exec you would take a statement like dbqd.acs-tcl.tcl.file.func.stmnt

select ... from foo, goo, ... where foo.x = :val and goo.y = :val2 ...
change it to first do a
PREPARE dbqd.acs-tcl.tcl.file.func.stmnt (TEXT,TEXT) AS
select ... from foo, goo, ... where foo.x = $1 and goo.y = $2 ...
and have a nsv or something to save the that that statement was prepared on that pool (since PREPARES are per backend) and save the execute statement (which would be static). Then for that and all subsequent calls for that pool you would issue:
EXECUTE dbqd.acs-tcl.tcl.file.func.stmnt (:val, :val2)
to actually retrieve data.

The problem with a blanket system like this is that if you do ddl, the prepared statement does not pick it up. eg:

xarg=# create table tmp(x integer);
CREATE TABLE
xarg=# insert into tmp values(1);
INSERT 254417 1
xarg=# prepare xp as select * from tmp;
PREPARE
xarg=# execute xp;
 x
---
 1
(1 row)

xarg=# alter table tmp add column y integer;
ALTER TABLE
xarg=# execute xp;
 x
---
 1
(1 row)
Also, if you watch files and the query changes you would need to flush prepared queries for those that change (well, probably just mark them dirty and then flush if used).