Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement

2006-08-31 Thread Victor B. Wagner
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

2006-08-31 Thread Stefan Kaltenbrunner

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

2006-08-31 Thread Gregory Stark

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

2006-08-31 Thread Victor B. Wagner
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Victor B. Wagner
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

2006-08-31 Thread Teodor Sigaev
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

2006-08-31 Thread Bernd Helmle
--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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Martijn van Oosterhout
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

2006-08-31 Thread Csaba Nagy
 - 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

2006-08-31 Thread Stefan Kaltenbrunner

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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Teodor Sigaev

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

2006-08-31 Thread Csaba Nagy
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

2006-08-31 Thread Merlin Moncure

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

2006-08-31 Thread zhou bo
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

2006-08-31 Thread Jeroen T. Vermeulen
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

2006-08-31 Thread Andreas Pflug
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Andreas Pflug
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Zeugswetter Andreas DCP SD

  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

2006-08-31 Thread 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;

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?

2006-08-31 Thread stark

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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Csaba Nagy
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread AgentM


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?

2006-08-31 Thread Chris Browne
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Phil Frost
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Theo Schlossnagle


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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Tom Lane
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?

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Lukas Kahwe Smith

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] GUC settings with units broken?

2006-08-31 Thread Joshua D. Drake

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: 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] Prepared statements considered harmful

2006-08-31 Thread mark
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.
 ...
 Comments?

Hello.

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.

This suggests to me that you are doing either:

   1) Maintaining prepared statements for weeks or months at a time.

   2) Churning your tables up into a froth.

I'm guessing, as you mentioned JDBC, that you might be hitting 1), in
the context of JDBC being used from a Web Application, where the
application server holds a connection open for weeks or months at a
time. If so, it does sound as if JDBC is doing wrong by keeping
prepared queries around for that long. A time limit of an hour, or
even a few minutes would make sense.

My experience does not match yours. Prepared queries have always
significantly improved my execution times. They do have a place.
Whatever the scenarios you are hitting should be dealt with, possibly
in JDBC.

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 6: explain analyze is your friend


Re: [HACKERS] Prepared statements considered harmful

2006-08-31 Thread mark
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?

2006-08-31 Thread elein
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

2006-08-31 Thread mark
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread AgentM


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

2006-08-31 Thread Andrew Dunstan

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

2006-08-31 Thread Andrew - Supernews
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

2006-08-31 Thread mark
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Csaba Nagy
 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

2006-08-31 Thread AgentM


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

2006-08-31 Thread Martijn van Oosterhout
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

2006-08-31 Thread Lukas Kahwe Smith

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

2006-08-31 Thread Lukas Kahwe Smith

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

2006-08-31 Thread Peter Eisentraut
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.

2006-08-31 Thread Chris Browne
[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

2006-08-31 Thread Chris Browne
[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

2006-08-31 Thread Jaime Casanova

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

2006-08-31 Thread Martijn van Oosterhout
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

2006-08-31 Thread Merlin Moncure

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

2006-08-31 Thread Gregory Stark

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?

2006-08-31 Thread Magnus Hagander
 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.

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Peter Eisentraut
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

2006-08-31 Thread Martijn van Oosterhout
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

2006-08-31 Thread Andrew Dunstan

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

2006-08-31 Thread Andrew Dunstan

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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Jeff Davis
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

2006-08-31 Thread Bruce Momjian
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Martijn van Oosterhout
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

2006-08-31 Thread Martijn van Oosterhout
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

2006-08-31 Thread Dave Cramer


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

2006-08-31 Thread Hannu Krosing
Ü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

2006-08-31 Thread Victor B. Wagner
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Bruce Momjian
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Joshua D. Drake

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

2006-08-31 Thread Gregory Stark

[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

2006-08-31 Thread Tom Lane
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?

2006-08-31 Thread Chris Browne
[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

2006-08-31 Thread Joshua D. Drake

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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Gregory Stark
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

2006-08-31 Thread Joshua D. Drake

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

2006-08-31 Thread Dave Cramer


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

2006-08-31 Thread Alvaro Herrera
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

2006-08-31 Thread Joshua D. Drake


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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Tom Lane
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

2006-08-31 Thread Joshua D. Drake

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

2006-08-31 Thread Joshua D. Drake

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

2006-08-31 Thread Tom Lane
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


  1   2   >