Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
On 2006.08.31 at 00:09:56 +0200, Peter Eisentraut wrote: Victor B. Wagner wrote: First one is useful if for some reason some ciphers supported by OpenSSL is not permitted to use in the particular network, or if there is need to use ciphersuites which are not included into default ciphersuite list, now compiled into PostgreSQL. Do you have specific examples where that might be the case? One example which can be tested with stock OpenSSL without national cryptography modules is - usage of NULL ciphers. They are not enabled by default, but use of them provides cryptographically strong authentication with client certificates and data consistency checking with MAC algorithm, but avoids overhead of encryption. Consider situation when data are public anyway, but data modification should be properly authorized. Second one can be used for taking cryptography load from server into special hardware chip, which can be useful for loaded servers. Also, upcoming OpenSSL 0.9.9 allows to add entirely new cryptographic algorithms via engines, so engine support allows to use algorithms, ISTM that that should be in a system-wide OpenSSL configuration, not to be hacked into each SSL-using application separately. Is that possible? Really this is possible. Just make PostgreSQL call OPENSSL_config(NULL). This function reads default OpenSSL configuration file and perform neccessary initialization. Note that OpenSSL authors haven't put this code into SSL_library_init, but provide additional API function instead. We take this approach in our libpq patch (which is not submitted yet). But we choose another approach for backend patch. Reason is that database server is more-or-less self-contained thing, and may need another cryptography configuration then end-user applications or other servers running on the same machine. It even can be that they are administered by different people. So, we think that it is better to have all server configuration in the same place, and avoid dependencies on system-wide library configuration. Really, it is possible to have separate OpenSSL configuration files for different applications, and use environment variable to point to correct one. PostgreSQL server typically run as special user, and in most cases there are special provisions to set up specific environment for backend. So, goal of ssl_engine configuration directive can be possibly achieved by simplier patch, which just calls OpenSSL function to read configuration file. But, to make things clear for DBA, we should write a section in administration guide which describe consequences of reading system-wide openssl.cnf, ways to find default location of this file, and method of specifing location of alternate openssl configuration file, if it is required. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
Peter Eisentraut wrote: Victor B. Wagner wrote: First one is useful if for some reason some ciphers supported by OpenSSL is not permitted to use in the particular network, or if there is need to use ciphersuites which are not included into default ciphersuite list, now compiled into PostgreSQL. Do you have specific examples where that might be the case? this is btw. something that is available in most daemons utilizing openssl - one can disable weak ciphers (which might not even be known as weak at the time the defaults where set) or ciphers not authorized for certain usage scenarios by this means. Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
Victor B. Wagner [EMAIL PROTECTED] writes: One example which can be tested with stock OpenSSL without national cryptography modules is - usage of NULL ciphers. They are not enabled by default, but use of them provides cryptographically strong authentication with client certificates and data consistency checking with MAC algorithm, but avoids overhead of encryption. Consider situation when data are public anyway, but data modification should be properly authorized. I'm not sure that's a particularly good use case. There are attacks in the wild that hijack existing TCP connections. If you only authenticate connections and then even with the MAC checks I think you would have a chance of being able to take over the connection. That said it doesn't mean there aren't valid use cases. If for example you wanted to do some initial data load without encryption but didn't want to have to reconfigure your network to allow connections on different ports. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
On 2006.08.31 at 08:52:08 +0100, Gregory Stark wrote: Victor B. Wagner [EMAIL PROTECTED] writes: One example which can be tested with stock OpenSSL without national cryptography modules is - usage of NULL ciphers. They are not enabled by default, but use of them provides cryptographically strong authentication with client certificates and data consistency checking with MAC algorithm, but avoids overhead of encryption. Consider situation when data are public anyway, but data modification should be properly authorized. I'm not sure that's a particularly good use case. There are attacks in the wild that hijack existing TCP connections. If you only authenticate connections and then even with the MAC checks I think you would have a chance of being able to take over the connection. If you are hijacking TCP connection, you have no way to get shared secret, negotiated between client and server during SSL handshake. So, you have no way to generate correct MAC. That said it doesn't mean there aren't valid use cases. If for example you wanted to do some initial data load without encryption but didn't want to have to reconfigure your network to allow connections on different ports. This is not a case for PostgreSQL, which uses same port for SSL and non-SSL connection. Initial handshake with client certificates is much stronger point when comparing SSL with NULL ciphers with non-SSL connection. Also, SSL, even without client certificates, guarantees that you are connecting to the right server. So, using SSL with NULL cipher at least prevents clients from getting wrong data from malicious server due to DNS spoofing attack. Although I don't think that it is widespread attack scenario. Point made by Stefan is much better - it is very probably that somewhen in the future vulnerability in the some cipher would be discovered. If cipher list is configurable, DBA would be able to quickly fix the problem by editing configuration file, instead of recompiling PostgreSQL or OpenSSL. If this is mathematical vulnerability in the algorithm, rather than implementation bug, there would be even no need to upgrade OpenSSL. All that OpenSSL developers can do - mark this cipher as weak according to newly discovered strength. Note that current PostgreSQL cipherlist already contains such a hack: It contains !MD5 element, because MD5 digest algorithm was broken about year ago, and PostgreSQL expected to work with versions of OpenSSL which still consider it strong. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Updatable views
Am Mittwoch, 30. August 2006 18:01 schrieb Tom Lane: This is the first time I've actually looked at this patch, and I am dismayed. viewUpdate.c looks like nothing so much as a large program with a small program struggling to get out. What is all the stuff about handling multiple base rels? SQL92, at least, does not say that a join is updatable, and AFAICT this patch is rejecting that too ... But later SQL versions allow some of that, so at least it shouldn't hurt to have some parts of the code to be more general in preparation of that. I'm unclear as to why you've got DO INSTEAD NOTHING rules in there --- You need to have one unconditional rule if you have a bunch of conditional ones. The system does not see through the fact that the conditional ones cover all cases. The pg_dump changes seem pretty odd too. Why wouldn't you just ignore implicit rules during a dump, expecting the system to regenerate them when the view is reloaded? Right. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
Am Donnerstag, 31. August 2006 11:29 schrieb Stefan Kaltenbrunner: this is btw. something that is available in most daemons utilizing openssl - one can disable weak ciphers (which might not even be known as weak at the time the defaults where set) or ciphers not authorized for certain usage scenarios by this means. In that case I'd expect to edit some central openssl configuration file to turn off the offending methods in one central place. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] [PATCHES] Backend SSL configuration enhancement
On 2006.08.31 at 10:34:02 +0200, Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 11:29 schrieb Stefan Kaltenbrunner: this is btw. something that is available in most daemons utilizing openssl - one can disable weak ciphers (which might not even be known as weak at the time the defaults where set) or ciphers not authorized for certain usage scenarios by this means. In that case I'd expect to edit some central openssl configuration file to turn off the offending methods in one central place. There is no such functionality in OpenSSL configuration file. Moreover, other SSL applications such as Apache, use more fine-grained apporoach - use different ciphersuite settings for virtual hosts and even particular web pages. Cipher strength is quantitive characteristic. In some cases same cipher can be strong enough, and in some - not. I can imagine scenarios where different databases or even different roles in the same database would require different strength of cipher. For example, user with read-only access to tables (say web server, visualizing data) can connect without encryption at all, user with update/insert permissions - with 128-bit encryption, and database superuser - only with 256-bit. But I don't think that implementation of such flexibility would be neccessary until there would be certificate based database authentication. ---(end of broadcast)--- TIP 1: 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: [HACKERS] GIN FailedAssertions on Itanium2 with Intel compiler
Huh, it's a over-optimization by icc on Itanium. With -00 or -02 there is no any
problem, only -O2 produces such effect. The problem is in code at lines 125-172
in ginutils.c:
static bool needUnique = false;
int cmpFunc(...) {
...
if (...) needUnique = true;
...
}
...
needUnique = false;
qsort(, cmpFunc);
if (needUnique)
And, needUnique was setted to true in last call of cmpFunc (by accident, in
fact), so between last call and checking of needUnique there isn't any call of
function. Insertion after qsort() any call (elog, for example) solves the problem.
If needUnique is marked as volatile, all is ok too. But this way doesn't seem
to me as reasonable, because there is a lot of places with potentially the same
problem... and thats may cause unpredictable failures. May be, better way is
limiting optimization level on Itanium with icc.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Updatable views
--On Mittwoch, August 30, 2006 12:01:25 -0400 Tom Lane [EMAIL PROTECTED] wrote: Bernd Helmle [EMAIL PROTECTED] writes: [ latest views patch ] This is the first time I've actually looked at this patch, and I am dismayed. viewUpdate.c looks like nothing so much as a large program with a small program struggling to get out. What is all the stuff about handling multiple base rels? SQL92, at least, does not say that a join is updatable, and AFAICT this patch is rejecting that too ... though it's hard to tell with the conditions for allowing the join to be updatable scattered through a lot of different functions. And some of the code seems to be expecting multiple implicit rules and other parts not. I get the impression that a lot of this code is left over from a more ambitious first draft and ought to be removed in the name of readability/maintainability. I not sure what parts of the code you are refering to exactly, but I admit that there are code parts that could deal with multiple base relations and rules. get_base_base_relation() is an example, it is used to create lookup tables for reversed columns so we could break them down to the correct position in their base tables. Restricting that to only one base relation wouldn't make any difference. Furthermore, SQL99 allows at least updatable views with joined relations which preserve their keys in the view definition. So i don't think it's that bad to leave parts of the code that way for future improvements. I'm unclear as to why you've got DO INSTEAD NOTHING rules in there --- the spec says that a WITH CHECK OPTION violation results in an error, not in nothing happening, so it doesn't seem to me that we should need any NOTHING rules to implement the spec. It would probably help if Well, instead of something like ERROR: cannot insert into a view HINT: You need an unconditional ON INSERT DO INSTEAD rule. you will get ERROR: view update commands violates rule condition with the correct error code set, because the view update check function is fired before. The first one isn't very useful for someone who simply wants to insert data into the view which isn't allowed to get in. You never get the view update check function fired without the DO INSTEAD rule applied to a view created with a check option. there were some header documentation that explained exactly how the module intends to transform a SELECT to create the various action rules. I agree with you, maybe it's a good to add a README to src/backend/rewrite? The pg_dump changes seem pretty odd too. Why wouldn't you just ignore implicit rules during a dump, expecting the system to regenerate them when the view is reloaded? Uhm, you're right. It's easier to exclude them in the SELECT query directly instead of selecting them, iterating over and filter them out. I'll fix that. (Looks like this is a cannot see the wood for the trees-mistake) -- Thanks Bernd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Prepared statements considered harmful
With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd say, in the majority of cases the time you save parsing and planning is irrelevant compared to the possibly disastrous effects of wrong or suboptimal plans. I wonder if other people have similar experiences. I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. A couple of actions to consider: - Never use prepared statements unless the user has turned them on. (This is the opposite of the current behavior.) - Transparently invalidate and regenerate prepared plans more often. This could be tied to the transaction count, update activity obtained from the statistics collector, etc. - Redefine prepared to mean parsed rather than parsed and planned. Each of these or similar changes would only solve a subset of the possible problems. Possibly, we need more knobs to adjust these things. But something needs to be done. Comments? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 01:56:29PM +0200, Peter Eisentraut wrote: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd say, in the majority of cases the time you save parsing and planning is irrelevant compared to the possibly disastrous effects of wrong or suboptimal plans. I wonder if other people have similar experiences. Yeah, it seems to me that many of the benefits of not planning are overrun by the effects of bad plans. - Redefine prepared to mean parsed rather than parsed and planned. I think this is the best. Some way to specify that you don't want planning to take place immediately would be good. One question though: there is a function PQexecParams(). Does this suffer from the same problem? I imagine most interfaces like out-of-band parameters (no escaping issues), why do they not use this? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Prepared statements considered harmful
- Redefine prepared to mean parsed rather than parsed and planned. How about prepared means really prepared... in the sense of parsed, analyzed all sensible plans, and save a meta-plan which based on current statistics and parameter values chooses one of the considered (and cached) plans ? That would be immune both to statistics changes and parameter value changes in certain limits. It would be also a lot more complex too than a simple plan... Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Prepared statements considered harmful
Peter Eisentraut wrote: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd say, in the majority of cases the time you save parsing and planning is irrelevant compared to the possibly disastrous effects of wrong or suboptimal plans. I wonder if other people have similar experiences. I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. A couple of actions to consider: - Never use prepared statements unless the user has turned them on. (This is the opposite of the current behavior.) - Transparently invalidate and regenerate prepared plans more often. This could be tied to the transaction count, update activity obtained from the statistics collector, etc. well this sounds like being best done with the central plan cache idea that is floating around(I think neilc once worked on that) - once we have something like that I would expect we can easily invalidate/regenerate plans there based on certain criteria (from obvious things like DDL-changes to more subtile ones like maybe age of the plan or statistics changed significantly on table foo or regenerate plan everytime when the table bla is involved) Most of that is pure speculation - but something like that would be a very powerful thing to have. Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 14:11 schrieb Csaba Nagy: How about prepared means really prepared... in the sense of parsed, analyzed all sensible plans, and save a meta-plan which based on current statistics and parameter values chooses one of the considered (and cached) plans ? I don't think this could solve one particularly frequent problem which is that pattern matching queries don't get along with prepared plans if the search pattern isn't known at planning time. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GIN FailedAssertions on Itanium2 with Intel compiler
Simple illustration:
#include stdio.h
#include stdlib.h
static char SI = 0;
static int
cmp(const void *a, const void *b) {
puts(CALL cmp);
if ( *(int*)a == *(int*)b ) {
puts(SET SI = 1);
SI = 1;
return 0;
}
return ( *(int*)a *(int*)b ) ? 1 : -1;
}
int
main(int argn, char *argv[]) {
int a[]={43,43};
SI = 0;
qsort(a, sizeof(a)/sizeof(int), sizeof(int), cmp);
if ( SI )
puts(OK);
else
puts(BUG: SI==0);
return 0;
}
% icc -O2 -o 1 1.c ./1
CALL cmp
SET SI = 1
BUG: SI==0
% icc -O1 -o 1 1.c ./1
CALL cmp
SET SI = 1
OK
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
On Thu, 2006-08-31 at 14:32, Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 14:11 schrieb Csaba Nagy: How about prepared means really prepared... in the sense of parsed, analyzed all sensible plans, and save a meta-plan which based on current statistics and parameter values chooses one of the considered (and cached) plans ? I don't think this could solve one particularly frequent problem which is that pattern matching queries don't get along with prepared plans if the search pattern isn't known at planning time. Why not ? I specifically said you would prepare a few sensible plans based on statistics/expected variations of the statistics, and parameter value ranges which would trigger different plans. So for the like query case you could save 2 plans, one for the indexable case, one for the not indexable case. Then at runtime you choose the proper one based on the pattern value. The meta-plan I mentioned would be a collection of plans with rules to choose the right one at run time based on parameter values and perhaps the current statistics. This of course would need a lot more preparation time than just prepare one plan, but that's why you want to do it upfront and then cache the results. A central plan repository mentioned in other posts would fit nicely here... and you could use prepared plans for non-parameterized queries too by simply considering the constants as parameters, to increase the chances for a prepared plan reuse - this of course for complex enough queries. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
On 8/31/06, Peter Eisentraut [EMAIL PROTECTED] wrote: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd say, in the majority of cases the time you save parsing and planning is irrelevant compared to the possibly disastrous effects of wrong or suboptimal plans. I wonder if other people have similar experiences. I have to respectfully disagree. I have used them to great effect in many of my projects. In the most extreme case, prepared statements can provide a 50% reduction or greater in overall query time...this is too good a benefit to simply discard. I worked on converted isam projects which would not have been possbile to make efficient without prepared statements. However you are correct that the planner does often create wacky plans which can cause disasterous results in some cases. My major issue is that you cannot supply hints to the query engine. For example one of my favorite tricks is to paramterize the limit clause in a query which creates a sliding window over the table for progressive readahead. Unfortunately the planner assumes 10% which borks the plan. My work around is to turn off bitmap, seqscan before plan and turn them on after the prepare. The proposal to supply hints to statements and functions has been voted down several times due to the argument that it is better to fix the planner. I think supplying hints does fix the planner, and is a balanced solution. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
hello everyone , i has been add to you guys' mail list by accident, i don't how to refuse to receive your mails, would you please help me to remove my mail address form mail group [EMAIL PROTECTED] i appreciatewhat you will do for me. (my mail address: [EMAIL PROTECTED]) thanks . From: Csaba Nagy [EMAIL PROTECTED] To: Peter Eisentraut [EMAIL PROTECTED] CC: postgres hackers pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Prepared statements considered harmful Date: Thu, 31 Aug 2006 14:52:05 +0200 On Thu, 2006-08-31 at 14:32, Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 14:11 schrieb Csaba Nagy: How about prepared means really prepared... in the sense of parsed, analyzed all sensible plans, and save a meta-plan which based on current statistics and parameter values chooses one of the considered (and cached) plans ? I don't think this could solve one particularly frequent problem which is that pattern matching queries don't get along with prepared plans if the search pattern isn't known at planning time. Why not ? I specifically said you would prepare a few sensible plans based on statistics/expected variations of the statistics, and parameter value ranges which would trigger different plans. So for the like query case you could save 2 plans, one for the indexable case, one for the not indexable case. Then at runtime you choose the proper one based on the pattern value. The meta-plan I mentioned would be a collection of plans with rules to choose the right one at run time based on parameter values and perhaps the current statistics. This of course would need a lot more preparation time than just prepare one plan, but that's why you want to do it upfront and then cache the results. A central plan repository mentioned in other posts would fit nicely here... and you could use prepared plans for non-parameterized queries too by simply considering the constants as parameters, to increase the chances for a prepared plan reuse - this of course for complex enough queries. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
On Thu, August 31, 2006 18:56, Peter Eisentraut wrote: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. Is there any kind of pattern at all to this problem? Anything recognizable? A few typical pitfalls? Without knowing much of the internals, I could imagine [waves hands in vague gestures] other options--something like recognizing major changes that upset the cost functions that went into generating a plan, and invalidating the plan based on those; or noting bad estimates somehow as they become apparent during execution, and annotating the plan with a this assumption was a bad idea marker so you'll do better next time. I guess you can't go far wrong if you re-define prepared to mean merely pre-parsed, but it sounds like such a waste of opportunity... Jeroen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Peter Eisentraut wrote: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd say, in the majority of cases the time you save parsing and planning is irrelevant compared to the possibly disastrous effects of wrong or suboptimal plans. I wonder if other people have similar experiences. I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. A couple of actions to consider: - Never use prepared statements unless the user has turned them on. (This is the opposite of the current behavior.) - Transparently invalidate and regenerate prepared plans more often. This could be tied to the transaction count, update activity obtained from the statistics collector, etc. - Redefine prepared to mean parsed rather than parsed and planned. Each of these or similar changes would only solve a subset of the possible problems. Possibly, we need more knobs to adjust these things. But something needs to be done. Not to mention problems with outdated plans after schema changes. Using views unplanned (replanned) when used in joins could lead to improved resulting plans (e.g. if the view contains outer joins itself). Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure: The proposal to supply hints to statements and functions has been voted down several times due to the argument that it is better to fix the planner. I think supplying hints does fix the planner, and is a balanced solution. Planner hints are a way to address a deficient planner. But neither a manually hinted planner nor a perfectly good planner will help if the planning decisions are based on outdated information. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 15:06 schrieb Jeroen T. Vermeulen: Is there any kind of pattern at all to this problem? Anything recognizable? A few typical pitfalls? If data is not distributed evenly, then any old WHERE foo = $1 is prone to be the wrong plan for half of the possible values of $1. The more data you have and the more it changes, the worse this gets. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 14:52 schrieb Csaba Nagy: So for the like query case you could save 2 plans, one for the indexable case, one for the not indexable case. Then at runtime you choose the proper one based on the pattern value. OK, why don't you work out an example. Let's look at this query: SELECT * FROM t1 WHERE a LIKE $1; What two plans would you prepare? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] Prepared statements considered harmful
Merlin Moncure wrote: On 8/31/06, Peter Eisentraut [EMAIL PROTECTED] wrote: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. On some days, it seems like half the performance problems in PostgreSQL-using systems are because a bad plan was cached somewhere. I'd say, in the majority of cases the time you save parsing and planning is irrelevant compared to the possibly disastrous effects of wrong or suboptimal plans. I wonder if other people have similar experiences. I have to respectfully disagree. I have used them to great effect in many of my projects. Peter doesn't propose to remove prepared statements as such. They are certainly of great value, if used carefully and specifically, as in your case. The problems he's addressing stem from plans _implicitly_ created and stored. In the most extreme case, prepared statements can provide a 50% reduction or greater in overall query time...this is too good a benefit to simply discard. I worked on converted isam projects which would not have been possbile to make efficient without prepared statements. However you are correct that the planner does often create wacky plans which can cause disasterous results in some cases. My major issue is that you cannot supply hints to the query engine. I don't believe extending this thread to the we-need-hints issue is a good idea. Regards, Andreas ---(end of broadcast)--- TIP 1: 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: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 15:18 schrieb Andreas Pflug: Not to mention problems with outdated plans after schema changes. Using views unplanned (replanned) when used in joins could lead to improved resulting plans (e.g. if the view contains outer joins itself). Views don't contain execution plans. I don't see how this is relevant. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
How about prepared means really prepared... in the sense of parsed, analyzed all sensible plans, and save a meta-plan which based on current statistics and parameter values chooses one of the considered (and cached) plans ? I don't think this could solve one particularly frequent problem which is that pattern matching queries don't get along with prepared plans if the search pattern isn't known at planning time. I think what we would actually want is knowledge about how much difference different parameters actually make in plan decision. (the stats show an even distribution and join correlation) Then we could prepare the plan when there is not much difference and postpone planning until we know the parameters when the difference is big. OLTP workload typically benefits from prepared plans, and the one plan is good for all possible inputs, so imho we cannot just assume all plans need replanning for different parameters. Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote: OK, why don't you work out an example. Let's look at this query: SELECT * FROM t1 WHERE a LIKE $1; What two plans would you prepare? if substring($1 from 1 for 1) != '%' then use plan 1 (see below); else use plan 2 (see below); end if; Save both plans from below with the meta-plan from above, and call it a prepared plan. cnagy=# create table t1 (a text); CREATE TABLE cnagy=# insert into t1 select round(1000 * random()) from generate_series(1,1); INSERT 0 1 cnagy=# create index idx_t1_a on t1 (a); CREATE INDEX cnagy=# analyze verbose t1; INFO: analyzing public.t1 INFO: t1: scanned 55 of 55 pages, containing 1 live rows and 0 dead rows; 3000 rows in sample, 1 estimated total rows ANALYZE cnagy=# explain select a from t1 where a like '121%'; QUERY PLAN Bitmap Heap Scan on t1 (cost=2.06..27.63 rows=10 width=10) Filter: (a ~~ '121%'::text) - Bitmap Index Scan on idx_t1_a (cost=0.00..2.06 rows=10 width=0) Index Cond: ((a = '121'::text) AND (a '122'::text)) (4 rows) cnagy=# explain select a from t1 where a like '%121'; QUERY PLAN -- Seq Scan on t1 (cost=0.00..180.00 rows=80 width=10) Filter: (a ~~ '%121'::text) (2 rows) Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] GUC settings with units broken?
This doesn't look right to me: postgres=# set work_mem='1GB'; SET postgres=# show work_mem; work_mem -- 1MB (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 15:36 schrieb Csaba Nagy: On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote: OK, why don't you work out an example. Let's look at this query: SELECT * FROM t1 WHERE a LIKE $1; What two plans would you prepare? if substring($1 from 1 for 1) != '%' then use plan 1 (see below); else use plan 2 (see below); end if; Note that plan 1 can only be created if you know the actual value for $1. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] Prepared statements considered harmful
On Thu, 2006-08-31 at 15:49, Peter Eisentraut wrote: Note that plan 1 can only be created if you know the actual value for $1. Why would that be so ? The plan can contain functions of $1 (both constants in plan 1 are a function of $1). Cheers, Csaba ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Updatable views
Peter Eisentraut [EMAIL PROTECTED] writes: Am Mittwoch, 30. August 2006 18:01 schrieb Tom Lane: This is the first time I've actually looked at this patch, and I am dismayed. viewUpdate.c looks like nothing so much as a large program with a small program struggling to get out. But later SQL versions allow some of that, so at least it shouldn't hurt to have some parts of the code to be more general in preparation of that. If it bloats the code to unreadability, it's bad. I'm unclear as to why you've got DO INSTEAD NOTHING rules in there --- You need to have one unconditional rule if you have a bunch of conditional ones. The system does not see through the fact that the conditional ones cover all cases. AFAICS, for the cases we are able to implement within the existing rule mechanism, there should be exactly one unconditional rule. If you propose more, then you are going to have insurmountable problems with the usual sorts of multiple-evaluation risks. The proposed WITH CHECK OPTION implementation is unworkable for exactly this reason --- it will give the wrong answers in the presence of volatile functions such as nextval(). I believe that we cannot implement WITH CHECK OPTION as a rule. It's a constraint, instead, and will have to be checked the way the executor presently checks constraints, ie after forming the finished new tuple(s). (Someday we're going to have to look into redesigning the rule system so that it can cope better with the kinds of situations that give rise to multiple-evaluation problems. But today is not that day.) It's possible that if we strip the patch down to SQL92-equivalent functionality (no multiple base rels) without WITH CHECK OPTION, we would have something that would work reliably atop the existing rule mechanism. It's getting mighty late in the 8.2 cycle to be doing major rework though. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Prepared statements considered harmful
On Aug 31, 2006, at 8:52 , Csaba Nagy wrote: This of course would need a lot more preparation time than just prepare one plan, but that's why you want to do it upfront and then cache the results. A central plan repository mentioned in other posts would fit nicely here... and you could use prepared plans for non-parameterized queries too by simply considering the constants as parameters, to increase the chances for a prepared plan reuse - this of course for complex enough queries. If prepared statements become more expensive to create, then it would make more sense for them to persist across sessions. All of an application's prepared statements could be cached. -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] gBorg status?
What's up there? It has been down all week. We're trying to get the Slony-I 1.2 release out, so we can then migrate over to pgFoundry. But that doesn't working terribly well when gBorg's down... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/emacs.html ...Yet terrible as Unix addiction is, there are worse fates. If Unix is the heroin of operating systems, then VMS is barbiturate addiction, the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your sinuses with lucite and letting it set.) You owe the Oracle a twelve-step program. --The Usenet Oracle ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Updatable views
Am Donnerstag, 31. August 2006 15:55 schrieb Tom Lane: I'm unclear as to why you've got DO INSTEAD NOTHING rules in there --- You need to have one unconditional rule if you have a bunch of conditional ones. The system does not see through the fact that the conditional ones cover all cases. AFAICS, for the cases we are able to implement within the existing rule mechanism, there should be exactly one unconditional rule. If you propose more, then you are going to have insurmountable problems with the usual sorts of multiple-evaluation risks. I'm not sure what you are saying here ... The implementation creates, for each of the three actions INSERT, UPDATE, DELETE, one conditional rule that redirects the action from the view into the unterlying table, conditional on the view condition being fulfilled. The unconditional DO INSTEAD NOTHING rule then catches the cases where the view condition is not fulfilled. So there is, for each action, exactly one conditional and one unconditional rule. Which is consistent with what you said above, so I don't see the problem. The proposed WITH CHECK OPTION implementation is unworkable for exactly this reason --- it will give the wrong answers in the presence of volatile functions such as nextval(). I'm not sure why anyone would want to define a view condition containing a volatile function. At least it wouldn't put a major dent into this feature if such views were decreed not updatable. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 08:06:57PM +0700, Jeroen T. Vermeulen wrote:
On Thu, August 31, 2006 18:56, Peter Eisentraut wrote:
With time, it becomes ever clearer to me that prepared SQL
statements are just a really bad idea. On some days, it seems like
half the performance problems in PostgreSQL-using systems are
because a bad plan was cached somewhere.
Is there any kind of pattern at all to this problem? Anything
recognizable? A few typical pitfalls?
Frequently I have found preplanning will result in a horrible plan
because it is assumed parameters may be volatile while in practice they
are literals. Here is a function from my database:
CREATE FUNCTION nullorblank(character varying) RETURNS boolean
AS $_$ select $1 is null or trim($1) = '' $_$
LANGUAGE sql IMMUTABLE;
This is used in stored procedures that answer search queries. For
example, let's consider one that searches products, filtered on any
number of part number, manufacturer, or name. If one of these is
not specified, it does not restrict the query. One might write that
query so:
-- $1: part number
-- $2: manufacturer
-- $3: name
SELECT * FROM product WHERE
(nullorblank($1) OR lower(partnumber) = lower($1))
AND (nullorblank($2) OR manufacturername = $2)
AND (nullorblank($3) OR name = $3)
The parameters will always be literal strings, taken from some form
presented to the user. If one does the parameter subsitution manually,
the plans are quite reasonable:
EXPLAIN ANALYZE
SELECT * FROM product WHERE
(nullorblank('int2100/512') OR lower(partnumber) = lower('int2100/512'))
AND (nullorblank('') OR manufacturername = '')
AND (nullorblank('') OR name = '');
QUERY PLAN
Result (cost=15.54..4494.71 rows=1867 width=254) (actual time=43.502..43.507
rows=1 loops=1)
- Bitmap Heap Scan on product (cost=15.54..4494.71 rows=1867 width=254)
(actual time=43.161..43.162 rows=1 loops=1)
Recheck Cond: (lower((partnumber)::text) = 'int2100/512'::text)
- Bitmap Index Scan on product_partnumber_loweridx
(cost=0.00..15.54 rows=1867 width=0) (actual time=43.022..43.022 rows=1 loops=1)
Index Cond: (lower((partnumber)::text) = 'int2100/512'::text)
Total runtime: 51.626 ms
(7 rows)
The 'manufacturername' and 'name' disjuncts have been removed by
simplification, since the expression is known to be true.
However, if prepared, it's horrible:
PREPARE to_be_slow(text, text, text) AS
SELECT * FROM product WHERE
(nullorblank($1) OR lower(partnumber) = lower($1))
AND (nullorblank($2) OR manufacturername = $2)
AND (nullorblank($3) OR name = $3);
explain analyze execute to_be_slow('int2100/512', NULL, NULL);
QUERY PLAN
--
Result (cost=0.00..22317.13 rows=1 width=254) (actual time=1115.167..1579.535
rows=1 loops=1)
- Seq Scan on product (cost=0.00..22317.12 rows=1 width=254) (actual
time=1114.845..1579.211 rows=1 loops=1)
Filter: $1)::character varying IS NULL) OR (btrim(($1)::text) =
''::text) OR (lower((partnumber)::text) = lower($1))) AND ((($2)::character
varying IS NULL) OR (btrim(($2)::text) = ''::text) OR (manufacturername = $2))
AND ((($3)::character varying IS NULL) OR (btrim(($3)::text) = ''::text) OR
((name)::text = $3)))
Total runtime: 1580.006 ms
(5 rows)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 16:09 schrieb Theo Schlossnagle: I don't chime in very often, but I do think the refusal to incorporate hints into the planner system is fantastically stubborn and nonsensical. What is actually fantastically nonsensical about this is that the issues I outlined about prepared statements would merely become worse if planner hints were used. Then, you wouldn't only have to worry about plans that were created earlier during the session, you would be faced with plans that were created earlier during the application's development. In general, the solutions to the prepared statement issues need to effect that the plans are created more often, not less often. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
On Aug 31, 2006, at 9:25 AM, Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure: The proposal to supply hints to statements and functions has been voted down several times due to the argument that it is better to fix the planner. I think supplying hints does fix the planner, and is a balanced solution. Planner hints are a way to address a deficient planner. But neither a manually hinted planner nor a perfectly good planner will help if the planning decisions are based on outdated information. I don't chime in very often, but I do think the refusal to incorporate hints into the planner system is fantastically stubborn and nonsensical. I whole-heartedly agree that it is _better_ to fix the planner, but many of us have production systems and can't just go check out CVS HEAD to address our day-to-day issues and we suffer from this decision. There are many databases out there with better planners than PostgreSQL -- likely there will always be. Even those databases have query planner hints. Why? Because the authors of those database had the humility to realize that the planner they designed wasn't perfect and that people _still_ need their database to perform well despite a non-optimal query plan here and there. A good query planner hint system would act as a catalyst to the improvement of the current query planner as users could share their complex queries and associated improved query plans through hinting. I like Postgres a lot, I think the people that work on it are very very sharp. I do feel that the consistent refusal to allow query hinting to be introduced demonstrates an unhealthy amount of hubris that, in the end, negatively impacts users. While Postgres is missing a ton of other needed features, I rarely see the attitude that they are _unwanted_. Instead I see the if it is important to you, go build it attitude which is what I would expect in an open source project. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
Am Donnerstag, 31. August 2006 16:26 schrieb Andrew Dunstan: Cached plans etc. might have an impact, but please do not overlook the benefits of parameterized queries in avoiding SQL injection attacks, as well as often being much cleaner to code. That might be part of the confusion. Composing queries with the variable parameters out of line is a very nice feature. But that concept is totally independent of the question whether the execution plan should be cached. The APIs (and their documentations) just don't convey that very well. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] [PATCHES] Updatable views
Peter Eisentraut [EMAIL PROTECTED] writes:
Am Donnerstag, 31. August 2006 15:55 schrieb Tom Lane:
The proposed WITH CHECK OPTION implementation is unworkable for exactly
this reason --- it will give the wrong answers in the presence of
volatile functions such as nextval().
I'm not sure why anyone would want to define a view condition containing a
volatile function. At least it wouldn't put a major dent into this feature
if such views were decreed not updatable.
The problem is not with the view condition. Consider
CREATE TABLE data (id serial primary key, ...);
CREATE VIEW only_new_data AS SELECT * FROM data WHERE id 12345
WITH CHECK OPTION;
INSERT INTO only_new_data VALUES(nextval('data_id_seq'), ...);
The proposed implementation will execute nextval twice (bad), and will
apply the WITH CHECK OPTION test to the value that isn't the one stored
(much worse). It doesn't help if the id is defaulted.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] GUC settings with units broken?
Am Donnerstag, 31. August 2006 15:43 schrieb stark: This doesn't look right to me: postgres=# set work_mem='1GB'; SET postgres=# show work_mem; work_mem -- 1MB (1 row) Fixed. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 14:52 schrieb Csaba Nagy: So for the like query case you could save 2 plans, one for the indexable case, one for the not indexable case. Then at runtime you choose the proper one based on the pattern value. OK, why don't you work out an example. Let's look at this query: SELECT * FROM t1 WHERE a LIKE $1; What two plans would you prepare? Well I guess for the case that none of the expected plans fit you can always fallback to generating a new plan on the fly. Anyways it would of course be cool if pgsql could set an invalid flag if it detects that a certain plan performed badly (maybe even automatically cause a fresh table analysis) or some DDL/DML was executed that likely invalidated the plan. I am not sure if there is any philosphie that pgsql tries to adhere to. Does it want to leave the job of tuning to the DBA or does it want to do things automatically (which always means that in some situations it will do the wrong thing). tweak planner vs. planner hints manually analyze vs. automatically analyze manual vaccum vs autovaccum Hmm actually its probably not a black and white thing and the ultimate goal would be to offer both with maybe some installer checkbox to default everything to DBA-less automode. Anyways I never liked the idea of planner hints. I think it makes much more sense to give people direct access to plans in that case. Meaning they can partially hardcode (parameterized) plans they want. I have mentioned before that Sybase seems to have such a feature (you can dump plans, tweak them and remove pieces that should be done on the fly and associate them with stored procedures - not sure if you also do that for prepared statements). regards, Lukas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 03:36:25PM +0200, Csaba Nagy wrote: On Thu, 2006-08-31 at 15:19, Peter Eisentraut wrote: OK, why don't you work out an example. Let's look at this query: SELECT * FROM t1 WHERE a LIKE $1; What two plans would you prepare? if substring($1 from 1 for 1) != '%' then use plan 1 (see below); else use plan 2 (see below); end if; It would be cool if PostgreSQL did this - but I think it is also true that anybody (or JDBC) who tried to prepare a plan in the cases that are known to cause problems, is making a mistake. While on the 'it would be cool' subject - I think it might be cool if the prepare statement took sample arguments that could be used to prepare the plans with. Prepare a plan that would work best with these arguments. Then JDBC could prepare both plans for you - if it was smart enough... :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] gBorg status?
Also people trying to download slony have to do some hunting to find things. The source only tar is not available on pgfoundry. one of them, elein On Thu, Aug 31, 2006 at 10:33:36AM -0400, Chris Browne wrote: What's up there? It has been down all week. We're trying to get the Slony-I 1.2 release out, so we can then migrate over to pgFoundry. But that doesn't working terribly well when gBorg's down... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/emacs.html ...Yet terrible as Unix addiction is, there are worse fates. If Unix is the heroin of operating systems, then VMS is barbiturate addiction, the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your sinuses with lucite and letting it set.) You owe the Oracle a twelve-step program. --The Usenet Oracle ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 10:09:10AM -0400, Theo Schlossnagle wrote: There are many databases out there with better planners than PostgreSQL -- likely there will always be. Even those databases have query planner hints. Why? Because the authors of those database had the humility to realize that the planner they designed wasn't perfect and that people _still_ need their database to perform well despite a non-optimal query plan here and there. A good query planner hint system would act as a catalyst to the improvement of the current query planner as users could share their complex queries and associated improved query plans through hinting. Would a hint system allow the planner to execute quicker? Eliminate plans from consideration early, without evaluation how long they might take to execute? Sort of possible today with toggling of the 'seqscan' and other such options... :-) I like Postgres a lot, I think the people that work on it are very very sharp. I do feel that the consistent refusal to allow query hinting to be introduced demonstrates an unhealthy amount of hubris that, in the end, negatively impacts users. Hubris isn't always bad. If hints were provided, the need for the fully automatic planner to improve would be reduced. But yes, they do seem to be competing goals, disenfranchising the user. While Postgres is missing a ton of other needed features, I rarely see the attitude that they are _unwanted_. Instead I see the if it is important to you, go build it attitude which is what I would expect in an open source project. There is also what you submit should be maintainable because we know you might disappear at any time. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Prepared statements considered harmful
Peter Eisentraut [EMAIL PROTECTED] writes: With time, it becomes ever clearer to me that prepared SQL statements are just a really bad idea. That's an overstatement, but I'll agree that they have strong limitations. I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. AFAIK those are the only two places where preparation is the default ... what else were you thinking of? - Transparently invalidate and regenerate prepared plans more often. This could be tied to the transaction count, update activity obtained from the statistics collector, etc. FWIW, I've assumed right along that once we have a plan-invalidation mechanism, any ANALYZE stats update would invalidate affected plans. - Redefine prepared to mean parsed rather than parsed and planned. For plan-inval to work in all cases, we'll have to store either the source query string or the raw grammar's output tree, before even parse analysis. Is that what you are thinking of? It's hardly prepared at all if you do that. As noted downthread, we've confused out-of-line parameter value shipping with prepared statements. It might be worth rejiggering the FE/BE protocol to separate those things better. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote: I'm attempting to understand why prepared statements would be used for long enough for tables to change to a point that a given plan will change from 'optimal' to 'disastrous'. Wouldn't this require that the tables are completely re-written, or that their data is drastically updated? For my own tables, most of the data remains static for months on end. Data is accumulated. Small changes are made. I don't see why a prepared statement used over a 24 hour period would ever become disastrous. Scenario: A web application maintains a pool of connections to the database. If the connections have to be regularly restarted due to a postgres implementation detail (stale plans), then that is a database deficiency. -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prepared statements considered harmful
Tom Lane wrote: As noted downthread, we've confused out-of-line parameter value shipping with prepared statements. It might be worth rejiggering the FE/BE protocol to separate those things better. Well, that's surely not going to happen in a hurry, is it? Maybe a quick fix would be a way to allow the user to turn plan caching on and off. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
On 2006-08-31, Tom Lane [EMAIL PROTECTED] wrote: I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. AFAIK those are the only two places where preparation is the default RI triggers. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: 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: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 11:36:36AM -0400, AgentM wrote: On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote: I'm attempting to understand why prepared statements would be used for long enough for tables to change to a point that a given plan will change from 'optimal' to 'disastrous'. Wouldn't this require that the tables are completely re-written, or that their data is drastically updated? For my own tables, most of the data remains static for months on end. Data is accumulated. Small changes are made. I don't see why a prepared statement used over a 24 hour period would ever become disastrous. Scenario: A web application maintains a pool of connections to the database. If the connections have to be regularly restarted due to a postgres implementation detail (stale plans), then that is a database deficiency. Or a JDBC deficiency. Nobody is forcing JDBC to automatically reuse a prepared plan indefinately. If automatically prepared, it can regenerate them whenever it wishes. Does Oracle automatically regenerate prepared plans on occasion? I don't consider it a deficiency. It is doing exactly what you are asking it to do. That it isn't second guessing you isn't a deficiency. For all PostgreSQL knows, your tables are not changing such that a query a week later is suddenly disastrous because the consistency of your data has changed drastically, and what you prepared a week ago, and chose to execute today, is still the optimal plan. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
AgentM [EMAIL PROTECTED] writes: On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote: I'm attempting to understand why prepared statements would be used for long enough for tables to change to a point that a given plan will change from 'optimal' to 'disastrous'. Scenario: A web application maintains a pool of connections to the database. If the connections have to be regularly restarted due to a postgres implementation detail (stale plans), then that is a database deficiency. The two major complaints that I've seen are * plpgsql's prepared plans don't work at all for scenarios involving temp tables that are created and dropped in each use of the function. Then, the plan needs to be regenerated on every successive call. Right now we tell people they have to use EXECUTE, which is painful and gives up unnecessary amounts of performance (because it might well be useful to cache a plan for the lifespan of the table). * for parameterized queries, a generic plan gives up too much performance compared to one generated for specific constant parameter values. Neither of these problems have anything to do with statistics getting stale. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: As noted downthread, we've confused out-of-line parameter value shipping with prepared statements. It might be worth rejiggering the FE/BE protocol to separate those things better. Well, that's surely not going to happen in a hurry, is it? Maybe a quick fix would be a way to allow the user to turn plan caching on and off. There aren't any quick fixes here (at least nothing that's likely to appear in 8.2). But I didn't mean the above suggestion as our only response to Peter's criticism --- more that that is one of several areas we ought to think about. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prepared statements considered harmful
Neither of these problems have anything to do with statistics getting stale. ... and the second one would benefit from a meta-plan facility which puts some meta-plan nodes on top of specific plans to dispatch based on parameter values at runtime. Incidentally, the dispatch could check the statistics assumptions too. If you don't need to do the planning for each execution, you could afford to check the assumptions for each execution instead... Cheers, Csaba. ---(end of broadcast)--- TIP 1: 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: [HACKERS] Prepared statements considered harmful
On Aug 31, 2006, at 12:04 , Tom Lane wrote: The two major complaints that I've seen are snip Neither of these problems have anything to do with statistics getting stale. Not stats-- plans. Plan invalidation has been discussed before, no? -M ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 11:27:18AM -0400, Tom Lane wrote: I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. AFAIK those are the only two places where preparation is the default ... what else were you thinking of? Perl DBI (DBD::Pg) defaults to prepared plans when connecting to a version 8.0 or higher server. Or at least, that's the way I read the documentation. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Prepared statements considered harmful
Martijn van Oosterhout wrote: On Thu, Aug 31, 2006 at 11:27:18AM -0400, Tom Lane wrote: I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. AFAIK those are the only two places where preparation is the default ... what else were you thinking of? Perl DBI (DBD::Pg) defaults to prepared plans when connecting to a version 8.0 or higher server. Or at least, that's the way I read the documentation. AFAIK this is also the case for PHP PDO extension, which is bundled since PHP 5.1. regards, Lukas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Lukas Kahwe Smith wrote: Martijn van Oosterhout wrote: On Thu, Aug 31, 2006 at 11:27:18AM -0400, Tom Lane wrote: I'd wish that we reconsider when and how prepared statements are used. The JDBC interface and PL/pgSQL are frequently noticed perpetrators, but the problem is really all over the place. AFAIK those are the only two places where preparation is the default ... what else were you thinking of? Perl DBI (DBD::Pg) defaults to prepared plans when connecting to a version 8.0 or higher server. Or at least, that's the way I read the documentation. AFAIK this is also the case for PHP PDO extension, which is bundled since PHP 5.1. BTW: PDO has gotten a switch to force client side placeholder replacement in favor of using server side prepared statements due to the fact that prepared statements side-step the MySQL query cache. http://netevil.org/node.php?uuid=444a6017-0548-2459-2943-44a601714d58 BTW: I am not posting this to solicit MySQL bashing. The main reason why PDO pushes prepared statements is the fact that they offer good protection against SQL injection. However obviously in shared nothing architectures like PHP, which does not yet have any sort of connection/statement-pooling solution, the danger of prepared statements becoming stale over time is small. However the problem of running the same statements with two different parameters that require different plans is still quite real. regards, Lukas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
AgentM wrote: On Aug 31, 2006, at 12:04 , Tom Lane wrote: The two major complaints that I've seen are snip Neither of these problems have anything to do with statistics getting stale. Not stats-- plans. Plan invalidation has been discussed before, no? Plan invalidation helps with schema changes and data changes but not with parametrized queries. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path changes.
[EMAIL PROTECTED] (Tom Lane) writes: Chris Browne [EMAIL PROTECTED] writes: If I touch preproc.y and pgc.l, the .c files get regenerated, and all is well. If I don't, they get left alone, and I see compilation errors. It seems to me you need to rebuild the C files and commit them. No, because those derived files are not in CVS at all. What you are describing sounds to me like a clock skew problem. Is your machine's system clock showing the correct date? Odd, odd. NOT a clock problem. The .c files were sitting in my buildfarm's CVS repository for HEAD. And yes, indeed, the derived files shouldn't have been there at all. I'm not quite sure how they got there in the first place. At any rate, after comprehensively looking for yacc-derived files, that clears this problem, as well as regression failures with last night's commit of COPY (SELECT) TO, which is no bad thing. -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://www.ntlug.org/~cbbrowne/linux.html Rules of the Evil Overlord #155. If I know of any heroes in the land, I will not under any circumstance kill their mentors, teachers, and/or best friends. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Slony-I 1.1.5 binaries at pgFoundry.org
[EMAIL PROTECTED] (elein) writes: Also people trying to download slony have to do some hunting to find things. The source only tar is not available on pgfoundry. The source tarball for version 1.1.5 is now in place: http://pgfoundry.org/frs/download.php/1063/slony1-1.1.5.tar.bz2 We may as well have at least that bit of backup. I didn't bother putting up the documentation tarball; it is better to grab a newer version of the docs. -- cbbrowne,@,ntlug.org http://cbbrowne.com/info/slony.html You can lead a horse to water, but if you can get him to swim on his back, you've got something. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] updatable views and default values
Hi, now that you're reviewing the updatable view patch, i think we must decide what you're position will be about if the updatable view should inherit the base table default values... or if we want to create default values for every view if we want they match with the base table ones... (fwiw, informix does the former) now the problems... i had a hack in the code to do that but it gives an error now because some refactoring of the code when the multiple values for insert patch was applied... the hack was inside the build_column_default() function and the problem seems to be because we are calling that function from rewriteValuesRTE(), and idea is to put a flag in build_column_default()'s arguments and try to get the base table's default only when that flag is set... but bernd and alvaro's advice was to get your opinions before wasting time coding something that could be rejected... so, opinions? ;) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 06:34:45PM +0200, Lukas Kahwe Smith wrote: BTW: PDO has gotten a switch to force client side placeholder replacement in favor of using server side prepared statements due to the fact that prepared statements side-step the MySQL query cache. Perl DBD:Pg also has a switch to force one way or the other. However (as has been stated already) people are confusing prepared statements with out-of-line parameters. Even DBI uses the phrase prepare for setting up statements, whereas this doesn't actually require server-side prepare, all it needs is out-of-line parameters. I see from the source that DBD::Pg does use PQexecParams() sometimes so maybe it does support out-of-line parameters... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Prepared statements considered harmful
On 8/31/06, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure: The proposal to supply hints to statements and functions has been voted down several times due to the argument that it is better to fix the planner. I think supplying hints does fix the planner, and is a balanced solution. Planner hints are a way to address a deficient planner. But neither a manually hinted planner nor a perfectly good planner will help if the planning decisions are based on outdated information. right, anyways it's clearer now what you are suggesting and I think your idea regarding impicitly generated plans has some merit. the major annoyance for me is I have to force disconnect anytime there is a schema search_path change. query hints, which I still think would make my life much easier, do not have much to do with the thrust of your argument. I think, maybe to add some intelligence to implicit plan generation parhaps guarded by GUC: implicit_plan_generation=[none, smart, all] with smart meaning some defined events including perhaps: * creation or deletion of temp table * duration of time * user invocation * manipulation of search_path just thinking out loud here, merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
Peter Eisentraut [EMAIL PROTECTED] writes: - Redefine prepared to mean parsed rather than parsed and planned. Then you would be going very much against the user's expectations. Driver interfaces expose very clearly to the user an explicit interface to prepare and execute a query separately. What your proposing is to go behind the user's back and do what he's gone out of his way to tell you not to do. You can always choose to prepare your queries immediately before use. Most drivers even supply an interface to do so in a single step for convenience. If you've gone to the trouble of saving the prepared query handle you very much do NOT want the database spontaneously deciding to change the behaviour of that query (even just the performance behaviour) without warning. In fact somewhere down the list of my personal wishlist for Postgres is plan stability which would let the DBA control exactly when plans could change. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] gBorg status?
Also people trying to download slony have to do some hunting to find things. The source only tar is not available on pgfoundry. All gborg *downloads* are available on: http://www.postgresql.org/ftp/projects/gborg/ Seems Slony hasn't released files using the gborg file release system, perhaps? Because for some reason Slony stuff isn't there. But I figured it'd be a good idea t oget that pointer in for people looking for anything else off gborg that didn't know we mirrored those. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path changes.
Chris Browne [EMAIL PROTECTED] writes: [EMAIL PROTECTED] (Tom Lane) writes: No, because those derived files are not in CVS at all. What you are describing sounds to me like a clock skew problem. Is your machine's system clock showing the correct date? Odd, odd. NOT a clock problem. The .c files were sitting in my buildfarm's CVS repository for HEAD. And yes, indeed, the derived files shouldn't have been there at all. I'm not quite sure how they got there in the first place. At any rate, after comprehensively looking for yacc-derived files, that clears this problem, as well as regression failures with last night's commit of COPY (SELECT) TO, which is no bad thing. I'll bet the way they got there is you did a build in the CVS repository tree, and then cleaned up with make distclean not make maintainer-clean. The buildfarm script is supposed to complain about unexpected files in the repository --- I wonder if it is fooled by the .cvsignore entries for these files? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Prepared statements considered harmful
Gregory Stark wrote: Then you would be going very much against the user's expectations. Driver interfaces expose very clearly to the user an explicit interface to prepare and execute a query separately. What your proposing is to go behind the user's back and do what he's gone out of his way to tell you not to do. You can always choose to prepare your queries immediately before use. Most drivers even supply an interface to do so in a single step for convenience. Let's verify that. JDBC and PL/pgSQL have been mentioned. The JDBC documentation merely contains statements of the sort A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. There is absolutely no indication that the execution plan of the statement is computed at the time of preparation. In fact, it doesn't say what pre-compiled means at all. For PL/pgSQL, you simply write a query and all the preparing action happens implicitly. There is nothing explicit about that interface. So if users have certain expectations here, they're just making them up. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 06:43:38PM +0100, Gregory Stark wrote: Peter Eisentraut [EMAIL PROTECTED] writes: - Redefine prepared to mean parsed rather than parsed and planned. Then you would be going very much against the user's expectations. Driver interfaces expose very clearly to the user an explicit interface to prepare and execute a query separately. What your proposing is to go behind the user's back and do what he's gone out of his way to tell you not to do. You can always choose to prepare your queries immediately before use. Most drivers even supply an interface to do so in a single step for convenience. Is that really so? Under Perl DBI, the only way to get a statement handle is to prepare it. Yet I don't want to use server-side prepares because I know of the problems it causes. The single-step approach provides no statement handle at all, which has several drawbacks. People are encouraged to use prepared stataments for clarity and security reasons, not speed. I would really like an option to choose between: - slightly more planning time but always good plans - plan once and be unforgiving if the plan doesn't work with the parameters I'd take the first option anyday, but that's just the types of queries I'm doing. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [COMMITTERS] pgsql: Second try committing the path
Tom Lane wrote: Chris Browne [EMAIL PROTECTED] writes: [EMAIL PROTECTED] (Tom Lane) writes: No, because those derived files are not in CVS at all. What you are describing sounds to me like a clock skew problem. Is your machine's system clock showing the correct date? Odd, odd. NOT a clock problem. The .c files were sitting in my buildfarm's CVS repository for HEAD. And yes, indeed, the derived files shouldn't have been there at all. I'm not quite sure how they got there in the first place. At any rate, after comprehensively looking for yacc-derived files, that clears this problem, as well as regression failures with last night's commit of COPY (SELECT) TO, which is no bad thing. I'll bet the way they got there is you did a build in the CVS repository tree, and then cleaned up with make distclean not make maintainer-clean. The buildfarm script is supposed to complain about unexpected files in the repository --- I wonder if it is fooled by the .cvsignore entries for these files? regards, tom lane Yes, we do. A patch made in July 2005 has this comment: ignore files listed in cvsignore files - this will stop inappropriate triggering of vpath builds. Perhaps I should only do that for vpath builds. Or perhaps I should even remove them at the end of a build, since we don't expect any of those files in a clean repo, do we? Also, in case anyone has not got the message yet: Don't ever build by hand in the buildfarm repo. Ever. I mean it. Use a copy. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Martijn van Oosterhout wrote:
On Thu, Aug 31, 2006 at 06:43:38PM +0100, Gregory Stark wrote:
Peter Eisentraut [EMAIL PROTECTED] writes:
- Redefine prepared to mean parsed rather than parsed and planned.
Then you would be going very much against the user's expectations.
Driver interfaces expose very clearly to the user an explicit interface to
prepare and execute a query separately. What your proposing is to go behind
the user's back and do what he's gone out of his way to tell you not to do.
You can always choose to prepare your queries immediately before use. Most
drivers even supply an interface to do so in a single step for convenience.
Is that really so? Under Perl DBI, the only way to get a statement
handle is to prepare it. Yet I don't want to use server-side prepares
because I know of the problems it causes. The single-step approach
provides no statement handle at all, which has several drawbacks.
People are encouraged to use prepared stataments for clarity and
security reasons, not speed. I would really like an option to choose
between:
- slightly more planning time but always good plans
- plan once and be unforgiving if the plan doesn't work with the
parameters
I'd take the first option anyday, but that's just the types of queries
I'm doing.
Have a nice day,
According to the docs you can actually choose between server side
prepare or not on a per call basis. It contains this example:
$sth-{pg_server_prepare} = 1;
$sth-execute(22);
$sth-{pg_server_prepare} = 0;
$sth-execute(44);
$sth-{pg_server_prepare} = 1;
$sth-execute(66);
cheers
andrew
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Bruce Momjian [EMAIL PROTECTED] writes: OK, here is a much nicer patch. The fix is to do no rounding, but to find the number of days before applying the factor adjustment. You have forgotten the problem of the factor not being exactly representable (eg, things like '10 days' * 0.1 not giving the expected result). Also, as coded this is subject to integer-overflow risks that weren't there before. That could be fixed, but it's still only addressing a subset of the problems. I don't think you can fix them all without rounding somewhere. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
Peter Eisentraut [EMAIL PROTECTED] writes: In that case I'd expect to edit some central openssl configuration file to turn off the offending methods in one central place. I concur with this in the abstract: it would be better design to submit something to the OpenSSL project to allow setting engine choices and such site-wide. In the short term, though, it's hard to deny that our code if (SSL_CTX_set_cipher_list(SSL_context, ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH) != 1) is pretty ad-hoc and looks exactly like the sort of thing someone might want to adjust. I'm willing to accept the part of the patch that makes that string into a GUC variable, until such time as OpenSSL provides a way to configure itself site-wide so that we can remove this code entirely. I'm not eager to accept the other part of the patch. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
On Thu, 2006-08-31 at 10:09 -0400, Theo Schlossnagle wrote: On Aug 31, 2006, at 9:25 AM, Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 15:05 schrieb Merlin Moncure: The proposal to supply hints to statements and functions has been voted down several times due to the argument that it is better to fix the planner. I think supplying hints does fix the planner, and is a balanced solution. There are many databases out there with better planners than PostgreSQL -- likely there will always be. Even those databases have query planner hints. Why? Because the authors of those database had the humility to realize that the planner they designed wasn't perfect and that people _still_ need their database to perform well despite a non-optimal query plan here and there. You can see a related discussion here: http://archives.postgresql.org/pgsql-hackers/2006-08/msg00463.php What I understood from that thread was that the concept of planner hints was not completely rejected. I think the most likely outcome (if any planning system is implemented) is some mechanism to state the hint in a separate SQL declaration rather than inside the query itself. This can still result in potentially stale (or very stale) plans, but at least you don't have to change your application every time you modify the hints. However, as far as I know, this has not progressed beyond the brainstorming stage. I think many people are still very skeptical of various implementations of planner hints, but there is some reasonable level of discussion. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, here is a much nicer patch. The fix is to do no rounding, but to find the number of days before applying the factor adjustment. You have forgotten the problem of the factor not being exactly representable (eg, things like '10 days' * 0.1 not giving the expected result). Also, as coded this is subject to integer-overflow risks that weren't there before. That could be fixed, but it's still only addressing a subset of the problems. I don't think you can fix them all without rounding somewhere. Well, the patch only multiplies by 30, so the interval would have to span +5 million years to overflow. I don't see any reason to add rounding until we get an actual query that needs it (and because rounding is arbitrary). I think the proposed fix is the best we can do at this time. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Prepared statements considered harmful
Peter Eisentraut [EMAIL PROTECTED] writes: Gregory Stark wrote: Driver interfaces expose very clearly to the user an explicit interface to prepare and execute a query separately. The JDBC documentation merely contains statements of the sort A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. There is absolutely no indication that the execution plan of the statement is computed at the time of preparation. The key word there is efficiently. I think it is a reasonable presumption on the user's part that a query done this way will have less overhead than just resubmitting the raw query each time. The important thing I see here is that JDBC allows use of IN parameters with or without a PreparedStatement (no?). So they've separated the concepts of out-of-line parameters and preparing a statement. That's the distinction we have unfortunately fudged in the V3 protocol. The protocol does let you use OOL parameters without retaining a prepared plan, thanks to the hack introduced later to not plan the unnamed statement at Parse time, but that's definitely a bit of a wart on the original protocol design. Maybe it's good enough, or maybe not. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 02:58:48PM -0400, Tom Lane wrote: The protocol does let you use OOL parameters without retaining a prepared plan, thanks to the hack introduced later to not plan the unnamed statement at Parse time, but that's definitely a bit of a wart on the original protocol design. Maybe it's good enough, or maybe not. Urk, so it was a hack. Unfortunatly it seems something you can't really change without changing the protocol. So what are the options now? A GUC like so: prepare_means_plan = [true|false] So then a prepare will always parse straightaway, but you can choose whether or not you want to plan straightaway or at bind time. Would this be acceptable? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Prepared statements considered harmful
On Thu, Aug 31, 2006 at 02:16:32PM -0400, Andrew Dunstan wrote: According to the docs you can actually choose between server side prepare or not on a per call basis. It contains this example: Yeah, but it also contains this: Using prepared statements is in theory quite a bit faster: not only does the PostgreSQL backend only have to prepare the query only once, but DBD::Pg no longer has to worry about quoting each value before sending it to the server. Which just continues the misconception: you can not worry about quoting each value and still not use server-side prepares. There's a third option which is not made clear (and it's not clear if it's available via DBI). Basically, unnamed prepares are not planned until bind time, named statements are planned at prepare time. The question is, do you want to be able to defer planning for named statements also? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Prepared statements considered harmful
On 31-Aug-06, at 2:58 PM, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Gregory Stark wrote: Driver interfaces expose very clearly to the user an explicit interface to prepare and execute a query separately. The JDBC documentation merely contains statements of the sort A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. There is absolutely no indication that the execution plan of the statement is computed at the time of preparation. The key word there is efficiently. I think it is a reasonable presumption on the user's part that a query done this way will have less overhead than just resubmitting the raw query each time. The important thing I see here is that JDBC allows use of IN parameters with or without a PreparedStatement (no?). No, not that I am aware of. You can create a statement, and execute it, but you need a PreparedStatement to set IN parameters So they've separated the concepts of out-of-line parameters and preparing a statement. That's the distinction we have unfortunately fudged in the V3 protocol. The protocol does let you use OOL parameters without retaining a prepared plan, thanks to the hack introduced later to not plan the unnamed statement at Parse time, but that's definitely a bit of a wart on the original protocol design. Maybe it's good enough, or maybe not. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Ühel kenal päeval, N, 2006-08-31 kell 20:01, kirjutas Peter Eisentraut: For PL/pgSQL, you simply write a query and all the preparing action happens implicitly. There is nothing explicit about that interface. So if users have certain expectations here, they're just making them up. Or basing them on experience. I for one would not like it at all if all my queries (select * from plpgsqlfunc()) just magically become slower by 10-50% If there will be an option not to plan/optimise prepared statemants, I would certainly expect it to be off by default. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
On 2006.08.31 at 14:36:28 -0400, Tom Lane wrote: I concur with this in the abstract: it would be better design to submit something to the OpenSSL project to allow setting engine choices and such site-wide. In the short term, though, it's hard to deny that our code if (SSL_CTX_set_cipher_list(SSL_context, ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH) != 1) is pretty ad-hoc and looks exactly like the sort of thing someone might want to adjust. I'm willing to accept the part of the patch that makes that string into a GUC variable, until such time as OpenSSL provides a way to configure itself site-wide so that we can remove this code entirely. I'm not eager to accept the other part of the patch. OK, I'll remove ssl_engine part and add code to read global OpenSSL configuration file, so everything which can be configured in OpenSSL site-wide can be configured so in PostgreSQL backend, and cipherlist which are considered per-application in OpenSSL can be configured via postgresql.conf. I also have patch for libpq which adds following functionality 1. Read site-wide Openssl configuration file 2. Allow to specify alternate key location in the environment variable PGSSLKEY in the form engine:key_id where key_id is something engine specific. This allow to use hardware cryptographic tokens to store certificate private key. Idea is that each user has smart card or other piece of hardware and computer is equipped with appropriate reader. In order to connect to the server user inserts his token into reader. Typically such tokens (called HSM - Hardware Security Modules) never let secret key out of token. Instead they handle cryptographic operations inside the token and appropriate OpenSSL engines delegate these operations to token instead of performing them programmatically. Although interface to storage-only things such as Dallas touch memory can be implemented as OpenSSL engine module. Such setups are quite common in shops or malls. For instance, McDonalds uses such smart cards to identify which employee operates particular cash register. Current version of patch has following drawbacks 1. Certificates for all tokens must be stored on the computer (this is limitation of current OpenSSL engine API - it doesn't allow to get certificate from token) 2. Something external to libpq (i.e. application, which works as client to database) have to find out which token is inserted and put correct certificate into postgresql.crt and correct key_id into PGSSLKEY environment variable. Really, patch can be enhanced by allowing several certificates to be stored in the postgresql.crt and cycling through them until one matching specified secret key is found. What is better - send these patches (for client and for server) separately or combine them in the one patch? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Bruce Momjian [EMAIL PROTECTED] writes: Well, the patch only multiplies by 30, so the interval would have to span +5 million years to overflow. I don't see any reason to add rounding until we get an actual query that needs it Have you tried your patch against the various cases that have been discussed in the past? In particular there were several distinct examples of this behavior posted at the beginning of the thread, and I'd not assume that a fix for one handles them all. BTW, while trolling for examples I came across this: http://archives.postgresql.org/pgsql-bugs/2005-10/msg00307.php pointing out some issues that still haven't been addressed. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Interval aggregate regression failure
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, the patch only multiplies by 30, so the interval would have to span +5 million years to overflow. I don't see any reason to add rounding until we get an actual query that needs it Have you tried your patch against the various cases that have been discussed in the past? In particular there were several distinct examples of this behavior posted at the beginning of the thread, and I'd not assume that a fix for one handles them all. Yes, it fixes all posted examples, except one that displays 23:60. I cannot reproduce that failure from Powerpc so am waiting for Michael to test it. BTW, while trolling for examples I came across this: http://archives.postgresql.org/pgsql-bugs/2005-10/msg00307.php pointing out some issues that still haven't been addressed. Yea, that is a bunch of issues. They are already on the TODO list. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared statements considered harmful
Martijn van Oosterhout kleptog@svana.org writes: So what are the options now? A GUC like so: prepare_means_plan = [true|false] So then a prepare will always parse straightaway, but you can choose whether or not you want to plan straightaway or at bind time. That seems like just a kluge, as you'd typically want query-by-query control, and a GUC setting isn't convenient for that. It's entirely possible that the current protocol definition is Good Enough, assuming that client-library designers are aware of the implications of using named vs unnamed statements (which I bet not all of 'em are). You *can* have either behavior today, so far as client-issued queries go. The area that seems to need work more drastically is controlling what happens with queries inside plpgsql. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Win32 hard crash problem
Hello, Dave Cramer and I have dealt with a company today running 8.1.4 on Windows 2003. The application is a web app that runs via JDBC/Hibernate. The application will function perfectly for about 2/3 weeks and then we will receive a: server sent data (\D\ message) without prior row description (\T\ message)); (not escaped of course). Subsequent connections to the database will fail (such as pgAdmin) and Windows must be completely rebooted. I did ask if they were able to kill the process via the task manager. Instead they opt to use the service options and when that fails (which is always) they reboot the machine entirely. PostgreSQL will also not recover on its own (e.g; auto restart and roll through the logs). The good news is at that on reboot the problem goes away for 2/3 weeks. I have verified that they are doing all requisite routine maintenance. I currently have the customer running hardware checks to verify validity of the hardware but... Any thoughts? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prepared statements considered harmful
[EMAIL PROTECTED] writes: Does Oracle automatically regenerate prepared plans on occasion? Not due to statistics changes, only if your schema changes. (caveat: I last used Oracle back at 8i) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Win32 hard crash problem
Joshua D. Drake [EMAIL PROTECTED] writes: Dave Cramer and I have dealt with a company today running 8.1.4 on Windows 2003. The application is a web app that runs via JDBC/Hibernate. The application will function perfectly for about 2/3 weeks and then we will receive a: server sent data (\D\ message) without prior row description (\T\ message)); That sounds suspiciously close to the time from boot to wraparound of GetTickCount: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sysinfo/base/gettickcount.asp M$ list this as 49 days but that's the time to wrap clear around to zero; the value overflows and goes negative in 24.85 days if I've done the math correctly. My bet is something depending on GetTickCount to measure elapsed time (and no, it's not used in the core Postgres code, but you've got plenty of other possible culprits in that stack). BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. Any thoughts? I suppose get a real operating system won't go over well? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] gBorg status?
[EMAIL PROTECTED] (Magnus Hagander) writes: Also people trying to download slony have to do some hunting to find things. The source only tar is not available on pgfoundry. All gborg *downloads* are available on: http://www.postgresql.org/ftp/projects/gborg/ Seems Slony hasn't released files using the gborg file release system, perhaps? Because for some reason Slony stuff isn't there. But I figured it'd be a good idea t oget that pointer in for people looking for anything else off gborg that didn't know we mirrored those. In the past, binaries got hosted on Jan Wieck's downloads area which probably lives somewhere nearby there. Apparently it wasn't terribly convenient to add/drop files from the gBorg downloads area. For 1.2, I have been putting release candidate files over at pgFoundry, which is certainly still accessible. -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://www3.sympatico.ca/cbbrowne/finances.html Recursion is the root of computation since it trades description for time. -- Alan J. Perlis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Win32 hard crash problem
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Dave Cramer and I have dealt with a company today running 8.1.4 on Windows 2003. The application is a web app that runs via JDBC/Hibernate. The application will function perfectly for about 2/3 weeks and then we will receive a: server sent data (\D\ message) without prior row description (\T\ message)); That sounds suspiciously close to the time from boot to wraparound of GetTickCount: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sysinfo/base/gettickcount.asp M$ list this as 49 days but that's the time to wrap clear around to zero; the value overflows and goes negative in 24.85 days if I've done the math correctly. My bet is something depending on GetTickCount to measure elapsed time (and no, it's not used in the core Postgres code, but you've got plenty of other possible culprits in that stack). BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. The error is server side. I was just describing the environment. Any thoughts? I suppose get a real operating system won't go over well? Tried that, I got nervous laughter on the other end ;) Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Win32 hard crash problem
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. The error is server side. I was just describing the environment. I can entirely assure you that that error message is not present in the server code. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [HACKERS] Prepared statements considered harmful
Peter Eisentraut [EMAIL PROTECTED] writes: Gregory Stark wrote: Let's verify that. JDBC and PL/pgSQL have been mentioned. The JDBC documentation merely contains statements of the sort A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times. There is absolutely no indication that the execution plan of the statement is computed at the time of preparation. In fact, it doesn't say what pre-compiled means at all. I didn't say you were violating the technical definition in the specification. I said you're going against expectations. This is the problem with being dogmatic about abstraction boundaries. Obviously someone who doesn't know what's going on under the hood has no specific expectations about what pre-compiling might mean. But the reality is that you can't effectively use a database without understanding what query plans are and users do have expectations about behaviour below the abstraction barrier. If you don't think pre-compiled and efficiently execute multiple times doesn't translate into generates a query plan so it doesn't have to go through that process to execute the query I think you're in a very small minority. For PL/pgSQL, you simply write a query and all the preparing action happens implicitly. There is nothing explicit about that interface. Well that's sort of the inherent problem with PLpgSQL and the way it mixes up the procedural language with SQL. I guess the natural extension of questioning PL/pgSQL would be to wonder why subqueries in SQL queries don't get replanned every time they're executed. The data distribution could certainly change partway though. So if users have certain expectations here, they're just making them up. Well, that's what makes them expectations rather than promises. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 hard crash problem
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. The error is server side. I was just describing the environment. I can entirely assure you that that error message is not present in the server code. Ok let me be more clear. The message is being throw via PostgreSQL. I am getting per the message I posted.. http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/src/interfaces/libpq/fe-protocol2.c?rev=22194 http://projects.commandprompt.com/public/pgsql/browser/trunk/pgsql/src/interfaces/libpq/fe-protocol3.c?rev=25989 It is in libpq and the protocol not the backend that is giving me the message. When I said server, I as referring to postgresql inclusively, not the driver that was actually connecting. Sincerely, Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Win32 hard crash problem
On 31-Aug-06, at 6:01 PM, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. The error is server side. I was just describing the environment. I can entirely assure you that that error message is not present in the server code. Well that's even more interesting because it doesn't exist in the jdbc driver either. Dave regards, tom lane ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Win32 hard crash problem
Dave Cramer wrote: On 31-Aug-06, at 6:01 PM, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. The error is server side. I was just describing the environment. I can entirely assure you that that error message is not present in the server code. Well that's even more interesting because it doesn't exist in the jdbc driver either. Conclusion: they are using libpq in some form, so you should investigate that. Is there a way to alter the tick counter, so that a test run does not need to take the full 3 weeks? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32 hard crash problem
That sounds suspiciously close to the time from boot to wraparound of GetTickCount: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sysinfo/base/gettickcount.asp M$ list this as 49 days but that's the time to wrap clear around to zero; the value overflows and goes negative in 24.85 days if I've done the math correctly. My bet is something depending on GetTickCount to measure elapsed time (and no, it's not used in the core Postgres code, but you've got plenty of other possible culprits in that stack). This doesn't quite make sense. The only reason we have to reboot is because PostgreSQL no longer responds. The system itself is fine. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] updatable views and default values
Jaime Casanova [EMAIL PROTECTED] writes: now that you're reviewing the updatable view patch, i think we must decide what you're position will be about if the updatable view should inherit the base table default values... or if we want to create default values for every view if we want they match with the base table ones... (fwiw, informix does the former) I'm inclined to agree that if we create an automatic INSERT rule for a view, we ought to automatically copy the base table's defaults up to the view as well. Otherwise the user will certainly want to do that by hand, and the whole point of this feature is to eliminate the need to do obvious things by hand. I think that this would require adding a flag to pg_attrdef entries to show whether they were created implicitly or not, so that pg_dump would know not to dump them. But wait, what if the user does ALTER SET DEFAULT on the base table --- shouldn't that then propagate to the view? Perhaps it would be better if the implementation didn't explicitly store a default expression attached to the view, but were willing to drill down to the base table and grab its default. You could still override that by explicitly attaching a different default to the view, but 90% of the time this would be the right thing. For backwards compatibility we should probably say that this automatic lifting of base-table defaults happens only if the INSERT rule is implicitly generated ... if you write a manual INSERT rule you need manual defaults too. Or should propagate default values become an explicit attribute of ON INSERT rules? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Win32 hard crash problem
Joshua D. Drake [EMAIL PROTECTED] writes: My bet is something depending on GetTickCount to measure elapsed time (and no, it's not used in the core Postgres code, but you've got plenty of other possible culprits in that stack). This doesn't quite make sense. The only reason we have to reboot is because PostgreSQL no longer responds. The system itself is fine. The Windows kernel may still work, but that doesn't mean that everything Postgres depends on still works. I'm wondering about (a) the TCP stack (and that includes 3rd party firewalls and such, not only the core Windows code); (b) timing or threading stuff inside the application that's using libpq, which the only thing we know about so far is that it's *not* JDBC/Hibernate. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Win32 hard crash problem
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: My bet is something depending on GetTickCount to measure elapsed time (and no, it's not used in the core Postgres code, but you've got plenty of other possible culprits in that stack). This doesn't quite make sense. The only reason we have to reboot is because PostgreSQL no longer responds. The system itself is fine. The Windows kernel may still work, but that doesn't mean that everything Postgres depends on still works. I'm wondering about (a) the TCP stack (and that includes 3rd party firewalls and such, not only the core Windows code); (b) timing or threading stuff inside the application that's using libpq, which the only thing we know about so far is that it's *not* JDBC/Hibernate. /me grumbles in a not so polite way about Windows. Which means we need to start stripping it down. Gah, I actually argued *for* this port to. Next time slap me. Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Win32 hard crash problem
Alvaro Herrera wrote: Dave Cramer wrote: On 31-Aug-06, at 6:01 PM, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: BTW, are you sure this is coming from JDBC? I see the exact same message text in libpq: libpq_gettext(server sent data (\D\ message) without prior row description (\T\ message)\n)); Maybe the JDBC driver uses the identical message wording but my thought is to look for something going through libpq. The error is server side. I was just describing the environment. I can entirely assure you that that error message is not present in the server code. Well that's even more interesting because it doesn't exist in the jdbc driver either. Conclusion: they are using libpq in some form, so you should investigate that. Is there a way to alter the tick counter, so that a test run does not need to take the full 3 weeks? Sure it is a registry entry... so we could (in theory) shrink that quite a bit.. However I am confused, if we don't use it, what that is connecting to libpq would trigger it? I know they are using pgAAdmin... Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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: [HACKERS] Win32 hard crash problem
Joshua D. Drake [EMAIL PROTECTED] writes: Which means we need to start stripping it down. Gah, I actually argued *for* this port to. Next time slap me. Well, before you invest a lot of time barking up what might be the wrong tree, there is a very easy test you can use to check the GetTickCount theory: keep closer track of time-since-boot on the affected systems. If that idea is right, it won't be two or three weeks between boot and problems appearing, it'll be 24.85 days on the nose. It shouldn't take much except waiting to either falsify the theory or make it look pretty convincing. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Prepared statements considered harmful
Martijn van Oosterhout kleptog@svana.org writes: Then you would be going very much against the user's expectations. Driver interfaces expose very clearly to the user an explicit interface to prepare and execute a query separately. What your proposing is to go behind the user's back and do what he's gone out of his way to tell you not to do. You can always choose to prepare your queries immediately before use. Most drivers even supply an interface to do so in a single step for convenience. Is that really so? Under Perl DBI, the only way to get a statement handle is to prepare it. Sure, but you can prepare it right before you use it and throw it away instead of keeping it around. The server has to prepare the query sometime. The v3 protocol just gives you control over when that happens, but it doesn't force you to do it at any particular time. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Win32 hard crash problem
On 31/8/06 23:34, Joshua D. Drake [EMAIL PROTECTED] wrote: Sure it is a registry entry... so we could (in theory) shrink that quite a bit.. However I am confused, if we don't use it, what that is connecting to libpq would trigger it? I know they are using pgAAdmin... Are they using pgAgent? That's the only part of pgAdmin that doesn't any sort of timing I can think of offhand (other than the query tool timer which only runs whilst a query is running). Even then it's done indirectly through wxWidgets so I'm not familiar with how it's implemented at the win32 API level. If it were pgAdmin (or any other client) though, how would that lock up the entire PostgreSQL instance, but not the rest of the server? Regards, Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
