Greg,
thanks for the suggestion. looking into other thread on the list, it
looks like setting
$dbh->{pg_server_prepare} = 0;
would solve my problem as well. With this setting will dbd::pg behave
in old-style (i.e. prepare_cached prepared and stored on dbd::pg
side), or it won't cache anything at
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
>> Which is why Vlad should use prepare() instead of prepare_cached().
> in our web application similar SQL queries (like load an object)
> executed over and over again with high frequency. So it's very
> desirable to use prepare_cached. I think we
On May 2, 2005, at 11:51 , Vlad wrote:
in our web application similar SQL queries (like load an object)
executed over and over again with high frequency. So it's very
desirable to use prepare_cached. I think we are going to adjust our
ORM (object relation mapper) to always use full path to referenc
> > The docs for prepare_cached() are littered with "Don't do this unless
> > you understand the implications" warnings, as well as some kludges to
> > differentiate different cases.
>
> Which is why Vlad should use prepare() instead of prepare_cached().
in our web application similar SQL queries
On May 2, 2005, at 09:34 , Tom Lane wrote:
I think you could demonstrate that if the spec is "make it look
like the
original query was retyped as source each time", then *every* DDL
change
in the database potentially requires invalidating every cached plan.
I don't find that a desirable spec.
I
On May 1, 2005, at 23:16 , Steve Atkins wrote:
Isn't this behaving as documented? prepare_cached() is supposed to
return the original statement handle when you pass it the same string
a second time.
Yes.
The docs for prepare_cached() are littered with "Don't do this unless
you understand the implic
David Wheeler <[EMAIL PROTECTED]> writes:
> On May 1, 2005, at 21:30 , Neil Conway wrote:
>> An alternative would be to flush dependent plans when the schema
>> search path is changed. In effect this would mean flushing *all*
>> prepared plans whenever the search path changes: we could perhaps
On May 1, 2005, at 21:30 , Neil Conway wrote:
An alternative would be to flush dependent plans when the schema
search path is changed. In effect this would mean flushing *all*
prepared plans whenever the search path changes: we could perhaps
keep plans that only contain explicit namespace ref
On May 2, 2005, at 06:40 , Andrew Dunstan wrote:
I am not sure this is reasonably fixable. Invalidating the cache is
not a pleasant solution - the query might not be affected by the
change in search path at all. I'd be inclined to say that this is
just a limitation of prepare_cached() which s
On May 2, 2005, at 06:36 , Vlad wrote:
as I understood Tom's message, he's not advising dbd::pg driver to
rely on the fact that earlier prepared query is still valid.
That's not going to change. It's your responsibility, as the
programmer, to know when you need to expire the cache:
$dbh->do("SET
On May 2, 2005, at 06:14 , Neil Conway wrote:
I'm not sure I quite follow you -- in some future version of the
backend in which prepared queries are invalidated, this would be
invisible to the client. The client wouldn't need to explicitly
check for the "liveness" of the prepared query, they
On May 1, 2005, at 22:44 , Tom Lane wrote:
I am not claiming that the backend handles all these cases nicely
today: it certainly doesn't. But we understand in principle how
to fix these problems by invalidating plans inside the backend.
I don't see how the DBD::Pg driver can hope to deal with any
Andrew Dunstan wrote:
Vlad wrote:
i.e. the following perl code won't work correctly with DBD::Pg 1.40+
$dbh->do("SET search_path TO one");
my $sth1 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?");
$sth1->execute("one");
$dbh->do("set search_path to two");
my $sth2 = $dbh->prepare_cach
On 5/2/05, Neil Conway <[EMAIL PROTECTED]> wrote:
> I'm not sure I quite follow you -- in some future version of the backend
> in which prepared queries are invalidated, this would be invisible to
> the client. The client wouldn't need to explicitly check for the
> "liveness" of the prepared query,
Vlad [EMAIL PROTECTED] wrote:
> ok, since there is no gurantee that server-side prepared query is
> still active, pergaps postgresql interface library provide way to
> check if a prepared before query still alive prior runing exec, so
> that dbd::pg driver can make sure it's still there, right befo
Vlad wrote:
i.e. the following perl code won't work correctly with DBD::Pg 1.40+
$dbh->do("SET search_path TO one");
my $sth1 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?");
$sth1->execute("one");
$dbh->do("set search_path to two");
my $sth2 = $dbh->prepare_cached("SELECT * FROM test
Vlad wrote:
ok, since there is no gurantee that server-side prepared query is
still active, pergaps postgresql interface library provide way to
check if a prepared before query still alive prior runing exec
I'm not sure I quite follow you -- in some future version of the backend
in which prepared
ok, since there is no gurantee that server-side prepared query is
still active, pergaps postgresql interface library provide way to
check if a prepared before query still alive prior runing exec, so
that dbd::pg driver can make sure it's still there, right before
executing?
If there is no such fun
On Sun, May 01, 2005 at 11:19:16PM -0400, Tom Lane wrote:
> Vlad <[EMAIL PROTECTED]> writes:
> > i.e. the following perl code won't work correctly with DBD::Pg 1.40+
>
> > $dbh->do("SET search_path TO one");
> > my $sth1 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?");
> > $sth1->execu
Vlad <[EMAIL PROTECTED]> writes:
> so is it possible that a successfully prepared (and possibly a couple
> of times already executed) query will be invalidated by postgresql
> for some reason (like lack of memory for processing/caching other
> queries)? Assuming that no database structure changes
btw, after re-reading the second part of your comment once again, I
have a (clarification) question:
so is it possible that a successfully prepared (and possibly a couple
of times already executed) query will be invalidated by postgresql
for some reason (like lack of memory for processing/caching
yeah, I agree.
perhaps a more correct solution would be to adjust DBD::Pg to detect
changes of active schema and store instances of server side prepared
queries tieing them up with query + current schema, not only a query
as it's now (as I understand)...
On 5/2/05, Neil Conway <[EMAIL PROTECTED]
Neil Conway <[EMAIL PROTECTED]> writes:
> An alternative would be to flush dependent plans when the schema search
> path is changed.
I think this would actually be the Wrong Thing. It's certainly a
debatable point --- but the best analogy we have is the behavior of
plpgsql functions in the face
Tom Lane wrote:
That's what it is supposed to do. It would hardly be possible to
"prepare" a query at all if we had to wait till EXECUTE to find out
which tables it was supposed to use.
An alternative would be to flush dependent plans when the schema search
path is changed. In effect this would m
Vlad <[EMAIL PROTECTED]> writes:
> i.e. the following perl code won't work correctly with DBD::Pg 1.40+
> $dbh->do("SET search_path TO one");
> my $sth1 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?");
> $sth1->execute("one");
> $dbh->do("set search_path to two");
> my $sth2 = $dbh->p
Tom,
thanks for you reply.
> That's what it is supposed to do. It would hardly be possible to
> "prepare" a query at all if we had to wait till EXECUTE to find out
> which tables it was supposed to use.
I understand that from postgresql point of view everything is logical.
From the application
Vlad <[EMAIL PROTECTED]> writes:
> SET search_path TO one;
> PREPARE st( VARCHAR(20) ) AS SELECT * FROM test WHERE item = $1;
> EXECUTE st( 'one' );
> SET search_path TO two;
> -- next statement fails because st selects from one.test, not from two.test
> EXECUTE st( 'two' );
That's what it is
27 matches
Mail list logo
