Re: Avoiding bad prepared-statement plans.

On Tue, Feb 16, 2010 at 8:17 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Incidentally, can you have two active anonymous portals at the same time?
>
> No, the first one is deleted when the second is created, i.e., our docs
> have:
>
>        An unnamed prepared statement lasts only until the next Parse statement
>        specifying the unnamed statement as destination is issued.  (Note that a
>        simple Query message also destroys the unnamed statement.)

I had to reread it myself but I think you've confused portals with
prepared statements. You can have an unnamed prepared statement --
which is planned at Bind time -- but execute it with a named portal
allowing you to keep it active while you execute a second query.

If you have code like:

$sth = $dbh->execute('select * from tab');
while ($sth->fetch) {
$dbh->execute('insert into tab2');
}

The first execute needs to prepare and execute the first statement. It
doesn't need to keep a named prepared statement handle around because
it'll never be able to re-execute it anyways. But the resulting portal
does need to be a named portal because otherwise the driver will be up
a creek when it comes to the second execute.

The case that's fairly awkward at the moment -- but still not
impossible to handle -- is when the driver sees a prepare and bind but
no execute for a while. Coding like:

$sth1 = $dbh->prepare('select * from tab where id = ?');
$sth2 = $dbh->prepare('select * from tab2 where id = ?');
$sth1->bind(1);
$sth2->bind(2);
$sth1->execute();
$sth2->execute();

In that case the driver is kind of stuck. It can't use the unnamed
prepared statement when the prepare() calls are done. If it wants the
plan-at-bind semantics then It would have to create a "fake" prepared
statement which it doesn't actually send the prepare message for until
the bind arrives.

--
greg