Skip site navigation (1) Skip section navigation (2)

Re: Avoiding bad prepared-statement plans.

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, Bart Samwel <bart(at)samwel(dot)tk>, Jeroen Vermeulen <jtv(at)xs4all(dot)nl>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Avoiding bad prepared-statement plans.
Date: 2010-02-17 03:12:50
Message-ID: 407d949e1002161912o58e60b0fu390eb0ebcc6b2b7b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Rayson HoDate: 2010-02-17 03:14:03
Subject: Re: OpenVMS?
Previous:From: Tom LaneDate: 2010-02-17 03:12:26
Subject: Re: bug? autovacuum is not launched even if autovacuum_freeze_max_age is reached

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group