Re: [HACKERS] Prepared statements considered harmful

On Thu, Aug 31, 2006 at 07:04:07PM -0400, Gregory Stark wrote: > 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. Not really. All named prepares are planned straight away, all

Re: [HACKERS] Roadmaps 'n all that

Josh Berkus writes: >> I propose a modest experiment: for the 8.3 development cycle, let's try >> to agree (in the next month or so) on a roadmap of what major features >> should be in 8.3 and who will make each one happen. > Well, I think the what is more important that the who -- we can switch

Re: [HACKERS] Roadmaps 'n all that

Tom, > I propose a modest experiment: for the 8.3 development cycle, let's try > to agree (in the next month or so) on a roadmap of what major features > should be in 8.3 and who will make each one happen. Well, I think the what is more important that the who -- we can switch "whos" if that's w

Re: [HACKERS] Roadmaps 'n all that

we will know whether this is a great thing we should continue, or we should stick to our traditional laissez-faire style of project management. I figure that even if it really sucks, it wouldn't kill us to try it for one release cycle --- at the very worst, we'd make up lost time in future by no

Re: [HACKERS] massive speedup on temp table creation/destruction?

"Merlin Moncure" <[EMAIL PROTECTED]> writes: > On 8/31/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> What I found surprising is that I get numbers like 430 tps from HEAD >> and 220 tps from REL8_1_STABLE. This test case is of course not about >> performance, but I'm not quite sure what we changed tha

Re: [HACKERS] Roadmaps 'n all that

On Aug 31, 2006, at 8:47 PM, Tom Lane wrote: [ hijacking this thread over to where the developers hang out ] Alvaro Herrera <[EMAIL PROTECTED]> writes: Tom Lane wrote: It's pointless to suppose that individual developers would really be answerable to any project-wide management, since that's

Re: [HACKERS] massive speedup on temp table creation/destruction?

On 8/31/06, Tom Lane <[EMAIL PROTECTED]> wrote: I was just trying (unsuccessfully so far) to replicate Csaba Nagy's report of a strange failure with temp table creation. I made use of pgbench's recent improvements to be able to push random scripts at a collection of backends: $ cat ttscript.sql

[HACKERS] Roadmaps 'n all that

[ hijacking this thread over to where the developers hang out ] Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> It's pointless to suppose that individual developers would really be >> answerable to any project-wide management, since that's not who they're >> paid by. So I tend to

Re: [PATCHES] [HACKERS] Interval aggregate regression failure

Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I am unclear about this report. The patch was not meant to fix every > > interval issue, but merely to improve multiplication and division > > computations. Does it do that? > > According to Michael's last report, your patch fails u

Re: [PATCHES] [HACKERS] Interval aggregate regression failure

I am unclear about this report. The patch was not meant to fix every interval issue, but merely to improve multiplication and division computations. Does it do that? I think the 23:60 is a time rounding issue that isn't covered in this patch. I am not against fixing it, but does the submitted

Re: [PATCHES] [HACKERS] Interval aggregate regression failure

Bruce Momjian <[EMAIL PROTECTED]> writes: > I am unclear about this report. The patch was not meant to fix every > interval issue, but merely to improve multiplication and division > computations. Does it do that? According to Michael's last report, your patch fails under --enable-integer-dateti

[HACKERS] massive speedup on temp table creation/destruction?

I was just trying (unsuccessfully so far) to replicate Csaba Nagy's report of a strange failure with temp table creation. I made use of pgbench's recent improvements to be able to push random scripts at a collection of backends: $ cat ttscript.sql create temp table foo (f1 int); drop table foo; $

Re: [HACKERS] [PATCHES] Interval month, week -> day

Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Sep 1, 2006, at 9:12 , Tom Lane wrote: >> I agree that this seems like an oversight in the original >> months/days/seconds patch, rather than behavior we want to keep. >> But is DecodeInterval the only place with the problem? > I'll check on this

Re: [HACKERS] [PATCHES] Interval month, week -> day

On Sep 1, 2006, at 9:12 , Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: I came across some behavior that seems counterintuitive to me: test=# select '1.5 mon'::interval; interval - 1 mon 360:00:00 (1 row) With the time/day/month interval struct intro

Re: [HACKERS] [PATCHES] Interval month, week -> day

Michael Glaesemann <[EMAIL PROTECTED]> writes: > I came across some behavior that seems counterintuitive to me: > test=# select '1.5 mon'::interval; > interval > - > 1 mon 360:00:00 > (1 row) > With the time/day/month interval struct introduced in 8.1, I'd expect > this to

Re: [HACKERS] GRANT role docs inconsistency

"Magnus Hagander" <[EMAIL PROTECTED]> writes: > The manual says: > GRANT role [, ...] > TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH ADMIN > OPTION ] It doesn't say that anymore: http://archives.postgresql.org/pgsql-committers/2006-08/msg00034.php Perhaps we ought to start thin

Re: [PATCHES] [HACKERS] Interval aggregate regression failure

On Sep 1, 2006, at 5:05 , Bruce Momjian wrote: 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

Re: [HACKERS] python / 7.4 / FC5 / x86_64

Andrew Dunstan <[EMAIL PROTECTED]> writes: > Peter Eisentraut wrote: >> Ideally, we would get Python to tell us the right location, because "use >> lib64 >> if it exists" isn't the right solution. >> >> Is this fixed somewhere post 7.4? > Yes, but it was never backported. See: > http://develope

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 us

Re: [HACKERS] Prepared statements considered harmful

Martijn van Oosterhout 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 wh

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 GetTick

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 pr

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

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 t

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 t

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.

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 p

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"))

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

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 PreparedStat

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

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: "serv

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 re

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

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 ---(

[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 desc

Re: [HACKERS] Prepared statements considered harmful

Martijn van Oosterhout 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

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 t

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 b

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_

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 bas

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 SQ

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

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 desig

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 parame

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 li

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 argu

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 settin

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 givin

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.

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? Od

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 clea

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

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 proble

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, perhap

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 sepa

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

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 sw

[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, in

[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: We may

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 the

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 > > > > > 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 wi

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 p

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

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

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 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)-

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

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

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 appl

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'. >

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 prep

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

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,

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. T

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 r

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.

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) != '%'

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 somewh

Re: [HACKERS] GUC settings with units broken?

Peter Eisentraut wrote: Am Donnerstag, 31. August 2006 15:43 schrieb stark: This doesn't look right to me: It's the whole metric system thing ;) postgres=# set work_mem='1GB'; SET postgres=# show work_mem; work_mem -- 1MB (1 row) Fixed. -- === The PostgreSQL Company:

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 ex

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

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 s

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. Composin

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 f

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

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 i

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 cov

[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/~cbb

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 p

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 l

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 broadca

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)

[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'

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);

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 particularl

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 execut

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 p

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

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

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. Plann

  1   2   >