Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 all? Besides, why don't you recommend turning pg_server_prepare off? On 5/2/05, Greg Sabino Mullane <[EMAIL PROTECTED]> wrote: > > -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 are going to adjust our > > ORM (object relation mapper) to always use full path to reference DB > > objects in query. > > This is actually a perfect case for prepare (and server-side prepare at > that), and not prepare_cached(). The latter has some overhead as a hash > table has to be searched and the right statement handle produced. One thing > I sometimes do is "pre-prepare" a lot of my frequently used statements at > the top of a long-running script (e.g. mod_perl). Then you simply refer to > the statement handle rather than prepare() or prepare_cached. It also has the > advantage of consolidating most of your SQL calls into one place in your > script. You can even create different handles for changed schemas. > It goes something like this: > > #!pseudo-perl > > BEGIN { > ## mod_perl only runs this once > use DBI; > my %sth; > $dbh = DBI->connect... > > ## Grab a user's information > $SQL = "SELECT * FROM u WHERE status = 2 AND username=?"; > $sth{grabuser} = $dbh->prepare($SQL); > > ## Insert a widget > $SQL = "INSERT INTO widgets(partno, color) VALUES (?,?,?)"; > $sth{addwidget} = $dbh->prepare($SQL); > ## Insert a widget into the jetson schema > $dbh-do("SET search_path TO jetson"); > $sth{addwidget_jetson} = $dbh->prepare($SQL); > > ## (reset search_path, keep going with all common SQL statements) > > } > ## mod_perl runs all this each time: > > ...skip lots of code... > > my $username = $forminput{'username'}; > $sth = $sth{grabuser}; > $count = $sth->execute($username); > > ...and later on... > > for (@widgets) { > if ("jetson" eq $_->{owner}) { > $dbh->do("SET search_path TO jetson"); > $sth{addwidget_jetson}->execute($_->{partnumber}, $_->{color}); > $dbh->do("SET search_path TO public"); > ## Silly example, better to use fully qualified names of course, > ## or perhaps a custom function that inserts for you > } > else { > $sth{addwidget}->execute($_->{partnumber}, $_->{color}); > } > } > > A simplified example, but the take home moral of all this is to be very > careful when using prepare_cached (which is actually a DBI feature, not > a DBD::Pg one). > > - -- > Greg Sabino Mullane [EMAIL PROTECTED] > PGP Key: 0x14964AC8 20050509 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > > -BEGIN PGP SIGNATURE- > > iD8DBQFCdPSrvJuQZxSWSsgRAsntAJ4iqrfqkj/f5Dqc4Ya7Vs4h0XZhGwCgxC15 > mM86zvTH/mXdAACBKPDG//4= > =vZ2+ > -END PGP SIGNATURE- > > > ___ > Dbdpg-general mailing list > [EMAIL PROTECTED] > http://gborg.postgresql.org/mailman/listinfo/dbdpg-general > -- Vlad ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
-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 are going to adjust our > ORM (object relation mapper) to always use full path to reference DB > objects in query. This is actually a perfect case for prepare (and server-side prepare at that), and not prepare_cached(). The latter has some overhead as a hash table has to be searched and the right statement handle produced. One thing I sometimes do is "pre-prepare" a lot of my frequently used statements at the top of a long-running script (e.g. mod_perl). Then you simply refer to the statement handle rather than prepare() or prepare_cached. It also has the advantage of consolidating most of your SQL calls into one place in your script. You can even create different handles for changed schemas. It goes something like this: #!pseudo-perl BEGIN { ## mod_perl only runs this once use DBI; my %sth; $dbh = DBI->connect... ## Grab a user's information $SQL = "SELECT * FROM u WHERE status = 2 AND username=?"; $sth{grabuser} = $dbh->prepare($SQL); ## Insert a widget $SQL = "INSERT INTO widgets(partno, color) VALUES (?,?,?)"; $sth{addwidget} = $dbh->prepare($SQL); ## Insert a widget into the jetson schema $dbh-do("SET search_path TO jetson"); $sth{addwidget_jetson} = $dbh->prepare($SQL); ## (reset search_path, keep going with all common SQL statements) } ## mod_perl runs all this each time: ...skip lots of code... my $username = $forminput{'username'}; $sth = $sth{grabuser}; $count = $sth->execute($username); ...and later on... for (@widgets) { if ("jetson" eq $_->{owner}) { $dbh->do("SET search_path TO jetson"); $sth{addwidget_jetson}->execute($_->{partnumber}, $_->{color}); $dbh->do("SET search_path TO public"); ## Silly example, better to use fully qualified names of course, ## or perhaps a custom function that inserts for you } else { $sth{addwidget}->execute($_->{partnumber}, $_->{color}); } } A simplified example, but the take home moral of all this is to be very careful when using prepare_cached (which is actually a DBI feature, not a DBD::Pg one). - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 20050509 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCdPSrvJuQZxSWSsgRAsntAJ4iqrfqkj/f5Dqc4Ya7Vs4h0XZhGwCgxC15 mM86zvTH/mXdAACBKPDG//4= =vZ2+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 reference DB objects in query. Yes, that will do the trick. Regards, David ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
> > 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 (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 reference DB objects in query. -- Vlad ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 agree. It seems to me that if someone is doing that sort of chicanery, then one should not use prepared statements. IOW, I would view it as an edge case. Regards, David ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 implications" warnings, as well as some kludges to differentiate different cases. Which is why Vlad should use prepare() instead of prepare_cached(). Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 >> keep plans that only contain explicit namespace references, but >> that seems fragile. > Yes, but this would be invisible to DBD::Pg and other clients, no? Depends what you call "invisible" --- Neil is suggesting automatic replanning of already-prepared queries. To the extent that that causes behavioral changes (like following a new search path) it wouldn't be invisible to applications. On the whole I think that the correct semantics of PREPARE is that the objects referred to by the query are determined when the PREPARE is executed, and don't change later on. Compare the following example: PREPARE foobar AS SELECT * FROM foo; EXECUTE foobar; ALTER TABLE foo RENAME TO bar; EXECUTE foobar; ALTER TABLE baz RENAME TO foo; EXECUTE foobar; Should the second EXECUTE fail entirely? Should the third select a perhaps completely different set of columns from the formerly-named baz? I don't think so. But this is exactly equivalent to the idea that already-prepared statements should track later changes in search_path. Here's an even nastier example: SET search_path = s1, s2; CREATE TABLE s2.foo ( ... ); PREPARE foobar AS SELECT * FROM foo; EXECUTE foobar; -- shows contents of s2.foo CREATE TABLE s1.foo ( ... ); EXECUTE foobar; -- shows contents of ?? 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. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 references, but that seems fragile. Yes, but this would be invisible to DBD::Pg and other clients, no? Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 should be documented. I expect that Tim would happily accept a documentation patch. http://svn.perl.org/modules/dbi/trunk/DBI.pm I expect that the same issue comes up for other databases, too. Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 search_path TO one"); my $sth1 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?"); $sth1->execute("one"); $dbh->do("set search_path to two"); @{$dbh->{CachedKids}} = (); # Expire the cache! my $sth2 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?"); $sth2->execute("two"); (Sorry that was stuck in another reply to Neil. Trying to do too many things at once!) Best, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 could just execute it -- if necessary, the backend will re-plan the query before executing it. $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"); @{$dbh->{CachedKids}} = (); # Expire the cache! my $sth2 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?"); $sth2->execute("two"); Regards, David ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 of these situations :-( It can't. So if you need to be able to switch schemas or do any of the evil(tm) things Tom suggest, then I recommend that you use prepare () instead of prepare_cached(). Or do the caching yourself. Regards, David ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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_cached("SELECT * FROM test WHERE item = ?"); $sth2->execute("two"); in the last call $sth1 prepared query will be actually executed, i.e. "one.test" table used, not "two.test" as a programmer would expect! Correctness seems to be in the eye of the beholder. It does what I as a programmer would expect. The behaviour you previously saw was an unfortunate byproduct of the fact that up to now DBD::Pg has emulated proper prepared statements, whereas now it uses them for real. Any application that relies on that broken byproduct is simply erroneous, IMNSHO. If you really need this, then as previously discussed on list, there is a way to turn off use of server-side prepared statements. Oops. I missed that the code used prepare_cached() rather than just prepare(). 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 should be documented. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 'prepare' is not quite schema-safe
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, they could just execute it -- if > necessary, the backend will re-plan the query before executing it. 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. I don't actually care abou the cases when DB structure has been changed and postgtres invalidated prepares because of that. -- Vlad ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 'prepare' is not quite schema-safe
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 before > executing? > > If there is no such function (and I can't find it), then it will be > hard for a driver to make things working right with server-side > prepared queries! You can always use fully qualified class (table) names in your prepared queries, i.e. explicitly specify the schema name. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [Dbdpg-general] Re: [GENERAL] 'prepare' is not quite schema-safe
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 WHERE item = ?"); $sth2->execute("two"); in the last call $sth1 prepared query will be actually executed, i.e. "one.test" table used, not "two.test" as a programmer would expect! Correctness seems to be in the eye of the beholder. It does what I as a programmer would expect. The behaviour you previously saw was an unfortunate byproduct of the fact that up to now DBD::Pg has emulated proper prepared statements, whereas now it uses them for real. Any application that relies on that broken byproduct is simply erroneous, IMNSHO. If you really need this, then as previously discussed on list, there is a way to turn off use of server-side prepared statements. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 'prepare' is not quite schema-safe
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 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 could just execute it -- if necessary, the backend will re-plan the query before executing it. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] 'prepare' is not quite schema-safe
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 function (and I can't find it), then it will be hard for a driver to make things working right with server-side prepared queries! On 5/2/05, Tom Lane <[EMAIL PROTECTED]> wrote: > 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 has been > > performed. > > Well, that assumption is wrong to start with: what if the query plan > uses an index that someone else has chosen to drop? Or the plan > depends on an inlined copy of a SQL function that someone has since > changed? Or the plan was chosen on the basis of particular settings > of planner parameters like random_page_cost, but the user has changed > these via SET? (The last is a pretty close analogy to changing > search_path, I think.) > > 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 of > these situations :-( > -- Vlad ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 'prepare' is not quite schema-safe
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->execute("one"); > > > $dbh->do("set search_path to two"); > > my $sth2 = $dbh->prepare_cached("SELECT * FROM test WHERE item = ?"); > > $sth2->execute("two"); > > > in the last call $sth1 prepared query will be actually executed, i.e. > > "one.test" table used, not "two.test" as a programmer would expect! > > Hmm. The above is arguably a DBD::Pg bug: it should not expect that > it's okay to use the same prepared statement in both cases. I do not > know what the spec is for "prepare_cached", but it sure seems that the > concept is fraught with danger --- the client-side driver has very > little hope of knowing what server-side events might be reasons to > invalidate the query cache. (Not that the server side is presently > all that good about it, but at least the server side is fixable > in principle ;-)) 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. 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. Cheers, Steve ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 'prepare' is not quite schema-safe
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 has been > performed. Well, that assumption is wrong to start with: what if the query plan uses an index that someone else has chosen to drop? Or the plan depends on an inlined copy of a SQL function that someone has since changed? Or the plan was chosen on the basis of particular settings of planner parameters like random_page_cost, but the user has changed these via SET? (The last is a pretty close analogy to changing search_path, I think.) 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 of these situations :-( regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 'prepare' is not quite schema-safe
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 other queries)? Assuming that no database structure changes has been performed. If the answer is YES, then it's important to double check that the dbd::pg driver would try to handle such situation appropriate - like re-prepare query with postgresql. > Hmm. The above is arguably a DBD::Pg bug: it should not expect that > it's okay to use the same prepared statement in both cases. I do not > know what the spec is for "prepare_cached", but it sure seems that the > concept is fraught with danger --- the client-side driver has very > little hope of knowing what server-side events might be reasons to > invalidate the query cache. (Not that the server side is presently > all that good about it, but at least the server side is fixable > in principle ;-)) > > regards, tom lane > -- Vlad ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 'prepare' is not quite schema-safe
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]> wrote: > 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 mean flushing *all* prepared plans > whenever the search path changes: we could perhaps keep plans that only > contain explicit namespace references, but that seems fragile. > > Flushing all plans might well be a cure that is worth than the disease, > at least for a lot of users. > > -Neil -- Vlad ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] 'prepare' is not quite schema-safe
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 of search-path changes, and I think that most people who have thought about that carefully are in favor of changing plpgsql functions to follow a search path frozen at function creation time. The fact that we haven't gotten around to making that happen isn't an argument for breaking PREPARE in the same way that plpgsql is broken ;-) regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 'prepare' is not quite schema-safe
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 mean flushing *all* prepared plans whenever the search path changes: we could perhaps keep plans that only contain explicit namespace references, but that seems fragile. Flushing all plans might well be a cure that is worth than the disease, at least for a lot of users. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 'prepare' is not quite schema-safe
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->prepare_cached("SELECT * FROM test WHERE item = ?"); > $sth2->execute("two"); > in the last call $sth1 prepared query will be actually executed, i.e. > "one.test" table used, not "two.test" as a programmer would expect! Hmm. The above is arguably a DBD::Pg bug: it should not expect that it's okay to use the same prepared statement in both cases. I do not know what the spec is for "prepare_cached", but it sure seems that the concept is fraught with danger --- the client-side driver has very little hope of knowing what server-side events might be reasons to invalidate the query cache. (Not that the server side is presently all that good about it, but at least the server side is fixable in principle ;-)) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] 'prepare' is not quite schema-safe
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 that serves multiple (identical) queries using the same DB connection and switching the schemas depends of the account a query came for it turns into oddity with the switch from DBD::Pg 1.32 (which caches prepared queries internally AFAIK) to DBD::Pg 1.41 wich has postgresql prepare the query... 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 WHERE item = ?"); $sth2->execute("two"); in the last call $sth1 prepared query will be actually executed, i.e. "one.test" table used, not "two.test" as a programmer would expect! -- Vlad ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [Dbdpg-general] 'prepare' is not quite schema-safe
> the first problem you have is that you have a critical production system > that you upgraded without going through proper test first. > That's just bad change control. > In any case, if the new DBD::Pg blew up in your face why did you not > immediately revert to the previous working one? Even if you didn't have it > on disk anymore you can just re-download it explicitly. > If it were no longer on CPAN it is on the 'backpan'. > > Just because its Linux, or Postgresql or Perl, doesn't mean you don't have > to follow proper operational procedures. there were a lot of other things upgraded same time, not only dbd::pg (to minimize total downtime): - our own perl code (with major changes, it's 4mb code size project) - postgresql itself - dbd pg ... all the above has been tested on the special "testing" server prior the upgrade and showed itself stable. Unfortunately it's not always possible to simulate exactly the same situation as in production environment, and as the result the problem didn't show itself up untill we did the upgrade. Because a lot of things were upgraded it wasn't quite obvious right away that it was the result of new DBD::Pg feature, so it took us an hour or two to localize it down to DBD::Pg (which has been downgraded immediately) and then we worked the rest of the time to find out the actual cause and make sure everything else is OK. anyways with all that said, I wasn't blaming anyone here and wasn't actually looking for an analyze on how should've we do the upgrade. Rather I'm interested in list's opinion on possible workaround. Though thanks for your point anyway. -- Vlad ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [Dbdpg-general] 'prepare' is not quite schema-safe
On Sun, 1 May 2005, Vlad wrote: > Hello, > > I'm seeking for an advise to solve the issue that we hit recently > (cost me sleepless night after production server upgrade). the first problem you have is that you have a critical production system that you upgraded without going through proper test first. That's just bad change control. In any case, if the new DBD::Pg blew up in your face why did you not immediately revert to the previous working one? Even if you didn't have it on disk anymore you can just re-download it explicitly. If it were no longer on CPAN it is on the 'backpan'. Just because its Linux, or Postgresql or Perl, doesn't mean you don't have to follow proper operational procedures. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 'prepare' is not quite schema-safe
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 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. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] 'prepare' is not quite schema-safe
Hello, I'm seeking for an advise to solve the issue that we hit recently (cost me sleepless night after production server upgrade). The actual environment is Apache+mod_perl, Postgresql 8.0.2. After upgrading DBD::Pg to the 1.41 version (which supports preparing quries on "server" side) we hit a series of strange issues. After digging the issue for two days I can provide the minimal example to illustrate what happens: CREATE SCHEMA one; SET search_path TO one; CREATE TABLE test ( item VARCHAR(20) ); INSERT INTO test VALUES( 'one' ); CREATE SCHEMA two; SET search_path TO two; CREATE TABLE test ( item VARCHAR(20) ); INSERT INTO test VALUES( 'two' ); 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' ); I'm not sure is it bug, feature or something else, but I'm looking for some solution to either make "prepare" statement bound/apply to "current schema unless specified in the statement" OR advise from the list on how the above situation can be avoided. As a quick workaround we reverted back to DBD::Pg 1.32 with no server-side prepare support. p.s. I'm not cross posting both to pgsql-general and dbd-pg lists, cause not sure from which side dbd::pg or postgrtesql a possible workaround can be found from. -- Vlad ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org