Re: [HACKERS] Postgres-R

2008-08-19 Thread K, Niranjan (NSN - IN/Bangalore)
Unfortunately, I'am getting the error as below when I start the gossip. I had 
followed the same steps as you  mentioned.

REFLECT:I'm not in the list of gossip hosts, exiting
  (the hosts are [cluster_1|cluster_2])

cluster_1  cluster_2 are node names  are the in /etc/hosts.

Did you face this?

regards,
Niranjan

-Original Message-
From: ext leiyonghua [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 19, 2008 6:42 AM
To: K, Niranjan (NSN - IN/Bangalore)
Cc: Markus Wanner; pgsql-hackers@postgresql.org
Subject: Re: Postgres-R

hi,
Assume that we have two node
node 0 , 192.168.0.2
node 1 , 192.168.0.3
1. add a host entry in /etc/hosts for hostname resolving.
2. add the host list in configuration 'ensemble.conf' for gossip service:
ENS_GOSSIP_HOSTS=node0:node1
3. set the envrionment variable ENS_CONFIG_FILE export 
ENS_CONFIG_FILE=/xxx/xxx/ensemble.conf
4. start ensemble  gossip
5. try 'c_mtalk' and happy.

this is a simplest case for me, hehe!

leiyonghua



K, Niranjan (NSN - IN/Bangalore) 写道:
 Thanks for the information.
 For Step5 (starting ensemble daemon).- I set the multicast address to 
 both nodes (Node 1 Node 2 eth0: 224.0.0.9/4)  before starting the ensemble. 
 And started the server application mtalk in node 1  node 2 and then client 
 application in node 1  node 2. But the count of members ('nmembers') show as 
 1. This is the output of the client program 'c_mtalk'. Seeing this, I'am 
 assuming that the applications are not merged.
 Could you please let me know how did you proceed with the setup of ensemble?

 regards,
 Niranjan

 -Original Message-
 From: ext leiyonghua [mailto:[EMAIL PROTECTED]
 Sent: Monday, August 18, 2008 2:58 PM
 To: K, Niranjan (NSN - IN/Bangalore); Markus Wanner; 
 pgsql-hackers@postgresql.org
 Subject: Re: Postgres-R

 [EMAIL PROTECTED] 写道:
   
 I wish to set up the Postgres-R environment, could you please let me know 
 the steps for setting it up.
 Thanks.


   
 
 yeah, actually, i have not been successful to set up this, but let me give 
 some information for you.
 1. download the postgresql snapshot source code from here:
 http://www.postgresql.org/ftp/snapshot/dev/
 (this is a daily tarball)

 2. Get the corresponding patch for postgres-r from:
 http://www.postgres-r.org/downloads/

 3. apply the patch for snapshot source, and configure like this:

 ./configure --enable-replication
 make  make install

 4. install the GCS ensemble, according the document : 
 http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html

 5. start ensemble daemon and gossip if neccessary ( yes, make sure the 
 two nodes can 'GCS' each other)

 3. Assume that you have two nodes, start up postgresql and create a database 
 'db', and create a table 'tb' for testing which should be have a primary key 
 for all nodes.

 4. At the origin node, execute the command at psql console:
 alter database db start replication in group gcs; (which means the 
 database 'db' is the origin and the group 'gcs' is the GCS group name)

 5. At the subscriber node, execute the command:
 alter database db accept replication from group gcs;


 Hope information above would be helpful, and keep in touch.

 leiyonghua


   


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Extending varlena

2008-08-19 Thread Jeremy Drake
On Mon, 18 Aug 2008, Tom Lane wrote:

 What would make more sense is to redesign the large-object stuff to be
 somewhat modern and featureful, and provide stream-access APIs (think
 lo_read, lo_seek, etc) that allow offsets wider than 32 bits.

A few years ago, I was working on such a project for a company I used to
work for.  The company changed directions shortly thereafter, and the
project was dropped, but perhaps the patch might still be useful as a
starting point for someone else.

The original patch is
http://archives.postgresql.org/pgsql-hackers/2005-09/msg01026.php, and the
advice I was working on implementing was in
http://archives.postgresql.org/pgsql-hackers/2005-09/msg01063.php

I am attaching the latest version of the patch I found around.  As it was
almost 3 years ago, I am a little fuzzy on where I left off, but I do
remember that I was trying to work through the suggestions Tom Lane gave
in that second linked email.  I would recommend discarding the libpq
changes, since that seemed to not pass muster.

Note that this patch was against 8.0.3.  There only seem to be a few
issues applying it to the current head, but I haven't really dug into them
to see how difficult it would be to update.  Luckily, the large object
code is fairly slow-moving, so there aren't too many conflicts.  One thing
I did notice is that it looks like someone extracted one of the functions
I wrote in this patch and applied it as a 32-bit version.  Good for them.
I'm glad someone got some use out of this project, and perhaps more use
will come of it.



-- 
At the source of every error which is blamed on the computer you will
find at least two human errors, including the error of blaming it on
the computer.diff -Nur postgresql-8.0.3-orig/src/backend/libpq/be-fsstubs.c 
postgresql-8.0.3/src/backend/libpq/be-fsstubs.c
--- postgresql-8.0.3-orig/src/backend/libpq/be-fsstubs.c2004-12-31 
13:59:50.0 -0800
+++ postgresql-8.0.3/src/backend/libpq/be-fsstubs.c 2005-10-03 
11:43:36.0 -0700
@@ -233,6 +233,34 @@
PG_RETURN_INT32(status);
 }
 
+
+Datum
+lo_lseek64(PG_FUNCTION_ARGS)
+{
+   int32   fd = PG_GETARG_INT32(0);
+   int64   offset = PG_GETARG_INT64(1);
+   int32   whence = PG_GETARG_INT32(2);
+   MemoryContext currentContext;
+   int64   status;
+
+   if (fd  0 || fd = cookies_size || cookies[fd] == NULL)
+   {
+   ereport(ERROR,
+   (errcode(ERRCODE_UNDEFINED_OBJECT),
+errmsg(invalid large-object descriptor: %d, 
fd)));
+   PG_RETURN_INT64(-1);
+   }
+
+   Assert(fscxt != NULL);
+   currentContext = MemoryContextSwitchTo(fscxt);
+
+   status = inv_seek(cookies[fd], offset, whence);
+
+   MemoryContextSwitchTo(currentContext);
+
+   PG_RETURN_INT64(status);
+}
+
 Datum
 lo_creat(PG_FUNCTION_ARGS)
 {
@@ -283,6 +311,165 @@
PG_RETURN_INT32(inv_tell(cookies[fd]));
 }
 
+
+Datum
+lo_tell64(PG_FUNCTION_ARGS)
+{
+   int32   fd = PG_GETARG_INT32(0);
+
+   if (fd  0 || fd = cookies_size || cookies[fd] == NULL)
+   {
+   ereport(ERROR,
+   (errcode(ERRCODE_UNDEFINED_OBJECT),
+errmsg(invalid large-object descriptor: %d, 
fd)));
+   PG_RETURN_INT64(-1);
+   }
+
+   /*
+* We assume we do not need to switch contexts for inv_tell. That is
+* true for now, but is probably more than this module ought to
+* assume...
+*/
+   PG_RETURN_INT64(inv_tell(cookies[fd]));
+}
+
+Datum
+lo_length(PG_FUNCTION_ARGS)
+{
+   int32   fd = PG_GETARG_INT32(0);
+   int32   sz = 0;
+   MemoryContext currentContext;
+
+   if (fd  0 || fd = cookies_size || cookies[fd] == NULL)
+   {
+   ereport(ERROR,
+   (errcode(ERRCODE_UNDEFINED_OBJECT),
+errmsg(invalid large-object descriptor: %d, 
fd)));
+   PG_RETURN_INT32(-1);
+   }
+   Assert(fscxt != NULL);
+   currentContext = MemoryContextSwitchTo(fscxt);
+
+   sz = inv_length(cookies[fd]);
+
+   MemoryContextSwitchTo(currentContext);
+
+   PG_RETURN_INT32(sz);
+}
+
+Datum
+lo_length64(PG_FUNCTION_ARGS)
+{
+   int32   fd = PG_GETARG_INT32(0);
+   int64   sz = 0;
+   MemoryContext currentContext;
+
+   if (fd  0 || fd = cookies_size || cookies[fd] == NULL)
+   {
+   ereport(ERROR,
+   (errcode(ERRCODE_UNDEFINED_OBJECT),
+errmsg(invalid large-object descriptor: %d, 
fd)));
+   PG_RETURN_INT64(-1);
+   }
+   Assert(fscxt != NULL);
+   currentContext = MemoryContextSwitchTo(fscxt);
+
+   sz = inv_length(cookies[fd]);
+
+   MemoryContextSwitchTo(currentContext);
+
+   

[HACKERS] possible minor EXPLAIN bug?

2008-08-19 Thread Pavel Stehule
Hello

I thing so Agg node doesn't set width well:

postgres=# explain select a,b from twocol;
QUERY PLAN
--
 Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
(1 row)

postgres=# explain select sum(a) from twocol group by b;
   QUERY PLAN

 HashAggregate  (cost=42.10..44.60 rows=200 width=8) -- wrong should be 4
   -  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
(2 rows)

Agg get width directly from outer plan, what could be wrong.

Regards
Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] WITH RECURSIVE patches 0818

2008-08-19 Thread Tatsuo Ishii
 I think I may have found another bug:
 
 WITH RECURSIVE t(i,j) AS (
 VALUES (1,2)
 UNION ALL
 SELECT t2.i, t.j
 FROM (
 SELECT 2 AS i
 UNION ALL   /* Wrongly getting detected, I think */
 SELECT 3 AS i
 ) AS t2
 JOIN
 t
 ON (t2.i = t.i)
 )
 SELECT * FROM t;
 ERROR:  attribute number 2 exceeds number of columns 1
 
 Is there some way to ensure that in the case of WITH RECURSIVE, the
 query to the right of UNION ALL follows only the SQL:2008 rules about
 not having outer JOINs, etc. in it, but otherwise make it opaque to
 the error-checking code?
 
 I know I didn't explain that well, but the above SQL should work and
 the error appears to stem from the parser's looking at the innermost
 UNION ALL instead of the outermost.

Thanks for the report. I will look into this.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Overhauling GUCS

2008-08-19 Thread Magnus Hagander
Alvaro Herrera wrote:
 Tom Lane escribió:
 Gregory Stark [EMAIL PROTECTED] writes:
 The entire target market for such a thing is DBAs stuck on hosted databases
 which don't have shell access to their machines. Perhaps the overlap between
 that and the people who can write a server-side module which dumps out a
 config file according to some rules is just too small?
 There's a veritable boatload of stuff we do that assumes shell access
 (how many times have you seen cron jobs recommended, for instance?).
 So I'm unconvinced that modify the config without shell access
 is really a goal that is worth lots of effort.
 
 Actually, lots of people are discouraged by suggestions of using cron to
 do anything.  The only reason cron is suggested is because we don't have
 any other answer, and for many people it's a half-solution.  An
 integrated task scheduler in Pg would be more than welcome.

Yes, I hear a lot of people complaining aobut that too. Now that we have
a working autovacuum, some of it goes away though - no need to cron your
VACUUMs in most cases anymore. But there are still backups - but they
are often managed by the scheduler of an enterprise backup software.


 Also, remember that pgAdmin already comes with a pgAgent thing.

Yeah, it's a real life-saver on Windows where the builtin task-scheduler
isn't as readily accessible or easy to use..

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Extending varlena

2008-08-19 Thread Magnus Hagander
Josh Berkus wrote:
 Andrew,
 
 I always find these requests puzzling.  Is it really useful to store the
 data for a jpeg, video file or a 10GB tar ball in a database column?
 
 Some people find it useful.  Because LOs are actually easier to manage in 
 PG than in most other DBMSes, right now that's a significant source of 
 PostgreSQL adoption.  I'd like to encourage those users by giving them 
 more useful LO features.

Given that they don't replicate with most (any?) of the currently
available replication solutions, the fact that they are easy to use
becomes irrelevant fairly quickly to larger installations in my experience.

But the interface *is* nice, so if we could fix that, it would be very good.

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Extending varlena

2008-08-19 Thread Magnus Hagander
Simon Riggs wrote:
 On Mon, 2008-08-18 at 16:22 -0400, Tom Lane wrote:
 David Fetter [EMAIL PROTECTED] writes:
 What would need to happen for the next jump up from where varlena is
 now, to 8 bytes?
 Dealing with upwards-of-4GB blobs as single Datums isn't remotely sane,
 and won't become so in the near (or even medium) future.  So I don't
 see the point of doing all the work that would be involved in making
 this go.

 What would make more sense is to redesign the large-object stuff to be
 somewhat modern and featureful, and provide stream-access APIs (think
 lo_read, lo_seek, etc) that allow offsets wider than 32 bits.  The main
 things I think we'd need to consider besides just the access API are

 - permissions features (more than none anyway)
 - better management of orphaned objects (obsoleting vacuumlo)
 - support  16TB of large objects (maybe partition pg_largeobject?)
 - dump and restore probably need improvement to be practical for such
   large data volumes
 
 Sounds like a good list.
 
 Probably also using a separate Sequence to allocate numbers rather than
 using up all the Oids on LOs would be a good plan.

The ability to partition the large object store would not suck either...
For backup/recovery purposes mainly.

//Magnus


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Asko Oja
Hi

 The reason that this case wasn't covered in 8.3 is that there didn't
 seem to be a use-case that justified doing the extra work.  I still
 haven't seen one.
You just stopped reading the thread where it was discussed after your troll
remark?

 Other than inline-able SQL functions there is no reason to invalidate a
stored plan
 based on the fact that some function it called changed contents.
Isn't it reason enough for this patch?
ERROR:  cache lookup failed for function is normal and good behaviour and
should not be recoverd from because it never happen if you PostgreSQL right
:)

Usecase 1: Inlined functions
postgres=# create or replace function salary_without_income_tax(i_salary in
numeric, salary out numeric ) returns numeric as $$ select $1 * 0.76 as
salary $$ language sql;
postgres=# prepare c2 as select salary, salary_without_income_tax(salary)
from salaries;
postgres=#  execute c2;
 salary | salary_without_income_tax
+---
  1 |   7600.00
postgres=# create or replace function salary_without_income_tax(i_salary in
numeric, salary out numeric ) returns numeric as $$ select $1 * 0.74 as
salary $$ language sql;
postgres=#  execute c2; salary | salary_without_income_tax
+---
  1 |   7600.00

Use case 2: While rewriting existing modules due to changes in business
requirements then in addition to new code we have to refactor lots of old
functions one natural thing to do would be to get rid of return types as
they are even more inconvenient to use than out parameters. Another reason
is keep coding style consistent over modules so future maintenace will be
less painful in the assholes.
postgres=# create type public.ret_status as ( status integer, status_text
text);
CREATE TYPE
postgres=# create or replace function x ( i_param text ) returns
public.ret_status as $$ select 200::int, 'ok'::text; $$ language sql;
CREATE FUNCTION
postgres=# create or replace function x ( i_param text, status OUT int,
status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$
language sql;
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION first

Usecase 3.: Extra out parameters are needed in existing functions. I assure
you if you have 5 years of legacy code that is constantly changing it does
happen (often).
postgres=# create or replace function xl ( i_param text, status OUT int,
status_text OUT text, more_text OUT text ) returns record as $$ select
200::int, 'ok'::text, 'cat'::text; $$ language sql;
ERROR:  cannot change return type of existing function
DETAIL:  Row type defined by OUT parameters is different.
HINT:  Use DROP FUNCTION first.

Usecase 4: Things are even worse when you need to change the type that is
used in functions. You have to drop and recreate the type and all the
functions that are using it. Sometimes type is used in several functions and
only some of them need changes.
postgres=# create type public.ret_status_ext as ( status integer,
status_text text, more_money numeric);

CREATE TYPE
postgres=# create or replace function x ( i_param text ) returns
public.ret_status_ext as $$ select 200::int, 'ok'::text; $$ language sql;
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION first.

And whenever we do drop and create as hinted  then we receive error flood
that won't stop until something is manually done to get rid of it
postgres=# drop function x(text);
DROP FUNCTION
postgres=# create or replace function x ( i_param text ) returns
public.ret_status_ext as $$ select 200::int, $1, 2.3 $$ language sql;
CREATE FUNCTION
postgres=# execute c;
ERROR:  cache lookup failed for function 24598

I hope i have answered your question  Why do you not use CREATE OR REPLACE
FUNCTION? That leaves us to deal with functions in our usual bad, wrong and
stupid ways.
* We create a function with new name and redirect all the calls to it.
(stupid as it creates extra development, testing, code reviewing and
releasing work and leaves around old code).
* We pause pgBouncer and after release let it reconnect all connections (bad
as it creates downtime).
* We invalidate all procedures using update to pg_proc (simply wrong way to
do it but still our best workaround short of restarting postgres).
postgres=# update pg_proc set proname = proname;
UPDATE 2152
postgres=#  execute c2;
 salary | salary_without_income_tax
+---
  1 |   7400.00

 Perhaps Skype needs to rethink how they are modifying functions.
We have had to change the way we use functions to suit PostgreSQL for 5
years now. That creates us quite a lot of extra work both on development
side and DBA side plus the constantly hanging danger of downtime. Our DBA
teams job is to reduce all possible causes for downtime and this patch is
solution to one of them. Sadly we just get trolled into the ground :)

All in all it's not the job of PostgreSQL to tell the 

Re: [HACKERS] Compatibility types, type aliases, and distinct types

2008-08-19 Thread Peter Eisentraut
Am Monday, 18. August 2008 schrieb Tom Lane:
 If the type has no functions of its own, then the only way to make it
 easily usable is to throw in implicit conversions *in both directions*
 between it and the type it's an alias for.  You're going to find that
 that's a problem.

I'm not finding that that's a problem.  We have several cases of that in the 
standard catalogs already.  What kind of problem are you foreseeing?

One direction of the cast could be AS ASSIGNMENT, btw., but that is another 
decision that would have to be worked out.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] possible minor EXPLAIN bug?

2008-08-19 Thread Simon Riggs

On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote:

 postgres=# explain select sum(a) from twocol group by b;
QUERY PLAN
 
  HashAggregate  (cost=42.10..44.60 rows=200 width=8) -- wrong should be 4
-  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
 (2 rows)

Although column b is not displayed it is kept in the HashAgg node to
allow your request to GROUP BY B. I'm happy that it tells me the width
of 8 so I can work out space used by hash, but perhaps it should say 12
(or even 16) to include hash value also, so we include the full cost per
row in the hash table.

If you do 
  explain select sum(a) from twocol
you will see the width is only 4

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Extending varlena

2008-08-19 Thread Peter Eisentraut
Am Monday, 18. August 2008 schrieb Tom Lane:
 - permissions features (more than none anyway)
 - better management of orphaned objects (obsoleting vacuumlo)
 - support  16TB of large objects (maybe partition pg_largeobject?)
 - dump and restore probably need improvement to be practical for such
   large data volumes

If you replace the third point by maybe partition TOAST tables, replace 
large object handle by TOAST pointer, and create an API to work on TOAST 
pointers, how are the two so much different?  And why should they be?  I can 
see that there are going to be needs to access large data with interfaces 
that are not traditional SQL, but at least the storage handling could be the 
same.  That way you would solve the first two points and others for free.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Overhauling GUCS

2008-08-19 Thread Peter Eisentraut
Am Monday, 18. August 2008 schrieb Josh Berkus:
 Right now, if you want to survey
 your databases, tables, approx disk space, query activity, etc., you can
 do that all through port 5432.  You can't manage most of your server
 settings that way, and definitely can't manage the *persistent* settings.  
 When you're trying to manage 1000 PostgreSQL servers, this is not a minor
 issue.

Some of that effort could go into making less settings persistent.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Auto-tuning GUCS

2008-08-19 Thread Michael Nacos
  I do think you and others make it less likely every time you throw up big
  insoluble problems like above though. As a consequence every proposal has
  started with big overly-complex solutions trying to solve all these
 incidental
  issues which never go anywhere instead of simple solutions which directly
  tackle the main problem.


insoluble? overly-complex solution? parsing a text file? I do not think we
understand
each other, or rather we start with totally different assumptions and design
goals.
it was probably a mistake to post keeping the subject line as it is,
considering I have
no interest overhauling GUCS, but this is where the subject of autotuning
was brought
up and this is where I posted.

now, to me, shell access, cron jobs, text config files - or rather, a single
text config
file, these are all good. if you plan to deploy/maintain entire farms or
cloud solutions,
tough! you should be looking into configuration management, such as cfengine
and
puppet already!

you seem to consider ease of use a prerequisite for tuning efficiency, our
design goals
couldn't be more different. what you want is an installer - what I'd like is
DBA support

Coping with user and system-generated comments is one difficult part that
people
normally don't consider, dealing with bad settings the server won't start
with is another.

now, as things stand, I will tinker in this area, simply because I'm
stubborn and this is
part of my job. I have parsed many text files in my professional career,
please do not
think a simple config file should be a problem (even with comments, I think)

The impression I get every time this comes up is that various people
 have different problems they want to solve that (they think) require
 redesign of the way GUC works.  Those complicated solutions arise from
 attempting to satisfy N different demands simultaneously.  The fact that
 many of these goals aren't subscribed to by the whole community to begin
 with doesn't help to ease resolution of the issues.


in this single thread I have identified at least three different development
targets:

* newbie-friendly default-guessing installer
* configuration manager for farms/clouds etc.
* auto-tuning support

this is why I'm posting this with a different subject line. If anyone wants
to discuss the
GUCS auto-tuning part, I'm all ears.

regards,

Michael


Re: [HACKERS] possible minor EXPLAIN bug?

2008-08-19 Thread Pavel Stehule
2008/8/19 Simon Riggs [EMAIL PROTECTED]:

 On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote:

 postgres=# explain select sum(a) from twocol group by b;
QUERY PLAN
 
  HashAggregate  (cost=42.10..44.60 rows=200 width=8) -- wrong should be 4
-  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
 (2 rows)

 Although column b is not displayed it is kept in the HashAgg node to
 allow your request to GROUP BY B. I'm happy that it tells me the width
 of 8 so I can work out space used by hash, but perhaps it should say 12
 (or even 16) to include hash value also, so we include the full cost per
 row in the hash table.

 If you do
  explain select sum(a) from twocol
 you will see the width is only 4

yes, Agg get this value directly, but it wrong

postgres=# explain select * from (select sum(a) from twocol group by b
offset 0) c;
 QUERY PLAN

 Subquery Scan c  (cost=42.10..46.60 rows=200 width=8)
   -  Limit  (cost=42.10..44.60 rows=200 width=8)
 -  HashAggregate  (cost=42.10..44.60 rows=200 width=8)
   -  Seq Scan on twocol  (cost=0.00..31.40 rows=2140 width=8)
(4 rows)

limit, subquery scan has wrong width now.

regards
Pavel

 --
  Simon Riggs   www.2ndQuadrant.com
  PostgreSQL Training, Services and Support



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Overhauling GUCS

2008-08-19 Thread Peter Eisentraut
Am Monday, 18. August 2008 schrieb Tom Lane:
 The impression I get every time this comes up is that various people
 have different problems they want to solve that (they think) require
 redesign of the way GUC works.  Those complicated solutions arise from
 attempting to satisfy N different demands simultaneously.

Which may be the reason that I have been getting the impression that 
the Problems and the proposed resolutions on 
http://wiki.postgresql.org/wiki/GUCS_Overhaul are not really closely related.  
I can agree with the Problems, but then I am lost.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Peter Eisentraut
I seem to recall that there was general support for installing a smaller 
default postgresql.conf file with only, say, a dozen parameters mentioned for 
initial tuning.  The complete file can stay as a sample.  Any objections to 
that?  (Let's not discuss quite yet exactly which parameters are the chosen 
ones.)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Hans-Juergen Schoenig

Peter Eisentraut wrote:
I seem to recall that there was general support for installing a smaller 
default postgresql.conf file with only, say, a dozen parameters mentioned for 
initial tuning.  The complete file can stay as a sample.  Any objections to 
that?  (Let's not discuss quite yet exactly which parameters are the chosen 
ones.)


  


i think this would make sense as long as this small file tells users 
where to find the full story.

generally i would say that this would be a step into the right direction.

alternatively we could use some sort of #include mechanism to split 
most important and not so important.


   hans


--
Cybertec Schönig  Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
 2008/8/18 Hannu Krosing [EMAIL PROTECTED]:
  On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
  2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]:
   Hi,
  
   Le lundi 18 août 2008, Andrew Dunstan a écrit :
On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
This is not the kind of patch we put into stable branches.
  
   So what? That is not the only criterion for backpatching.
  
   I fail to understand why this problem is not qualified as a bug.
  
 
  Does it change of result some queries?
 
  Not in the long run, but not invalidating the functions (current
  behaviour) postpones seeing the results of function change until DBA
  manually restarts the error-producing client.
 
  It is protection to server's hang?
 
  Can't understand this question :(
 
  If you mean, does the change protect against hanging the server, then
  no, currently the server does not actually hang, it just becomes
  unusable until reconnect :(
 
 Hi
 
 I am sorry, but it's really new feature and not bug fix

Could you please explain why you think so ?

As I see it, the patch does not change visible behaviour, except
removing some sonditions where client becomes unusable after some other
backend does some legitimate changes.

Is the current behavior planned or even defined by spec ? 

I agree, that the bug (if it is a bug) could also be circumvented by the
calling function by detecting a failed cache lookup and doing
replan/requery itself, but this would require all PL implementations and
other functions with stored plans to do it independently.

-
Hannu




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Mon, 2008-08-18 at 20:29 -0400, Tom Lane wrote:
 Asko Oja [EMAIL PROTECTED] writes:
  For users of stored procedures it is protection from downtime. For Skype it
  has been around 20% of databse related downtime this year.
 
 Perhaps Skype needs to rethink how they are modifying functions.

Why not suggest they just should stop using functions and move all
business logic into client or 3rd tier ?

(Actually I would not recommend that  as functions are very good way to
abstract database access AND provide better security AND speed up
queries)

 The reason that this case wasn't covered in 8.3 is that there didn't
 seem to be a use-case that justified doing the extra work.  I still
 haven't seen one.  Other than inline-able SQL functions there is no
 reason to invalidate a stored plan based on the fact that some function
 it called changed contents.

Maybe there should be something in postgreSQL docs that warns users against 
using functions in any non-trivial circumstances, as functions are not 
expected to behave like the rest of postgreSQL features and there is 
not plan to fix that ?


Hannu





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Pavel Stehule
2008/8/19 Hannu Krosing [EMAIL PROTECTED]:
 On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
 2008/8/18 Hannu Krosing [EMAIL PROTECTED]:
  On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
  2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]:
   Hi,
  
   Le lundi 18 août 2008, Andrew Dunstan a écrit :
On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
This is not the kind of patch we put into stable branches.
  
   So what? That is not the only criterion for backpatching.
  
   I fail to understand why this problem is not qualified as a bug.
  
 
  Does it change of result some queries?
 
  Not in the long run, but not invalidating the functions (current
  behaviour) postpones seeing the results of function change until DBA
  manually restarts the error-producing client.
 
  It is protection to server's hang?
 
  Can't understand this question :(
 
  If you mean, does the change protect against hanging the server, then
  no, currently the server does not actually hang, it just becomes
  unusable until reconnect :(

 Hi

 I am sorry, but it's really new feature and not bug fix

 Could you please explain why you think so ?

 As I see it, the patch does not change visible behaviour, except
 removing some sonditions where client becomes unusable after some other
 backend does some legitimate changes.

Are you sure, so this behave hasn't any secondary effect? So this
change doesn't breaks any application?

Pavel


 Is the current behavior planned or even defined by spec ?

 I agree, that the bug (if it is a bug) could also be circumvented by the
 calling function by detecting a failed cache lookup and doing
 replan/requery itself, but this would require all PL implementations and
 other functions with stored plans to do it independently.

 -
 Hannu





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Pavel Stehule
2008/8/19 Hannu Krosing [EMAIL PROTECTED]:
 On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
 2008/8/18 Hannu Krosing [EMAIL PROTECTED]:
  On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
  2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]:
   Hi,
  
   Le lundi 18 août 2008, Andrew Dunstan a écrit :
On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
This is not the kind of patch we put into stable branches.
  
   So what? That is not the only criterion for backpatching.
  
   I fail to understand why this problem is not qualified as a bug.
  
 
  Does it change of result some queries?
 
  Not in the long run, but not invalidating the functions (current
  behaviour) postpones seeing the results of function change until DBA
  manually restarts the error-producing client.
 
  It is protection to server's hang?
 
  Can't understand this question :(
 
  If you mean, does the change protect against hanging the server, then
  no, currently the server does not actually hang, it just becomes
  unusable until reconnect :(

 Hi

 I am sorry, but it's really new feature and not bug fix

 Could you please explain why you think so ?

 As I see it, the patch does not change visible behaviour, except
 removing some sonditions where client becomes unusable after some other
 backend does some legitimate changes.

 Is the current behavior planned or even defined by spec ?

 I agree, that the bug (if it is a bug) could also be circumvented by the
 calling function by detecting a failed cache lookup and doing
 replan/requery itself, but this would require all PL implementations and
 other functions with stored plans to do it independently.


I am not against to this patch or this feature. But I am sure, so
isn't well to do not necessary changes in stable version.

Pavel

 -
 Hannu





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Tue, 2008-08-19 at 12:42 +0200, Pavel Stehule wrote:
 2008/8/19 Hannu Krosing [EMAIL PROTECTED]:
  On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
  2008/8/18 Hannu Krosing [EMAIL PROTECTED]:
   On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote:
   2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]:
Hi,
   
Le lundi 18 août 2008, Andrew Dunstan a écrit :
 On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote:
 This is not the kind of patch we put into stable branches.
   
So what? That is not the only criterion for backpatching.
   
I fail to understand why this problem is not qualified as a bug.
   
  
   Does it change of result some queries?
  
   Not in the long run, but not invalidating the functions (current
   behaviour) postpones seeing the results of function change until DBA
   manually restarts the error-producing client.
  
   It is protection to server's hang?
  
   Can't understand this question :(
  
   If you mean, does the change protect against hanging the server, then
   no, currently the server does not actually hang, it just becomes
   unusable until reconnect :(
 
  Hi
 
  I am sorry, but it's really new feature and not bug fix
 
  Could you please explain why you think so ?
 
  As I see it, the patch does not change visible behaviour, except
  removing some sonditions where client becomes unusable after some other
  backend does some legitimate changes.
 
 Are you sure, so this behave hasn't any secondary effect? So this
 change doesn't breaks any application?

I can't think of any.

What it does, is it makes the changed function usable right after
redefining the new function.

Current behaviour is to make the calling function unusable until the
backend is restarted, after which it still will use the new version of
the function.

 Pavel
 
 
  Is the current behavior planned or even defined by spec ?
 
  I agree, that the bug (if it is a bug) could also be circumvented by the
  calling function by detecting a failed cache lookup and doing
  replan/requery itself, but this would require all PL implementations and
  other functions with stored plans to do it independently.
 
  -
  Hannu
 
 
 
 
 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Gregory Stark
Hannu Krosing [EMAIL PROTECTED] writes:

 Maybe there should be something in postgreSQL docs that warns users against 
 using functions in any non-trivial circumstances, as functions are not 
 expected to behave like the rest of postgreSQL features and there is 
 not plan to fix that ?

Now who's trolling :)

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] about postgres-r setup.

2008-08-19 Thread Markus Wanner

Hi leiyonghua,

leiyonghua wrote:

and still same status.


Uh.. do you have debugging enabled? Any logging output of the two 
postmaster processes?


Regards

Markus


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Postgres-R

2008-08-19 Thread Markus Wanner

Hi,

leiyonghua wrote:

./configure --enable-replication
make  make install


You certainly also want --enable-debug and --enable-cassert, maybe also 
additional flags for the C compiler, like -DRMGR_DEBUG, please check the 
source code for these.


4. install the GCS ensemble, according the document : 
http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html


5. start ensemble daemon and gossip if neccessary ( yes, make sure the 
two nodes can 'GCS' each other)


Yeah, either use the gossip process, or make sure IP multicast works for 
your network configuration. I admit that ensemble is quite a beast WRT 
compilation and configuration.


3. Assume that you have two nodes, start up postgresql and create a 
database 'db', and create a table 'tb' for testing which should be have 
a primary key for all nodes.


4. At the origin node, execute the command at psql console:
alter database db start replication in group gcs;
(which means the database 'db' is the origin and the group 'gcs' is the 
GCS group name)


5. At the subscriber node, execute the command:
alter database db accept replication from group gcs;


As recovery doesn't work automatically, you still need to sync the 
complete database from the node which initiated the replication group. 
Then accept replication.


I'm working on automatic recovery.

Regards

Markus Wanner


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Postgres-R

2008-08-19 Thread Markus Wanner
Hi,

K, Niranjan (NSN - IN/Bangalore) wrote:
 Thanks for the information.
 For Step5 (starting ensemble daemon).- 
 I set the multicast address to both nodes (Node 1 Node 2 eth0: 224.0.0.9/4)  
 before starting the ensemble. And started the server application mtalk in 
 node 1  node 2 and then client application in node 1  node 2. But the count 
 of members ('nmembers') show as 1. This is the output of the client program 
 'c_mtalk'. Seeing this, I'am assuming that the applications are not merged.

This sounds like IP multicast does not work properly for your network
(is IP multicast available and enabled for your OS? Maybe you are
running on virtual hosts with a virtual network, which doesn't support
multicasting?). You can either try to fix that or switch to using a
gossip process.

Regards

Markus Wanner


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Improving non-joinable EXISTS subqueries

2008-08-19 Thread Stephen Frost
* Tom Lane ([EMAIL PROTECTED]) wrote:
 So ... I'm wondering if this actually touches anyone's hot-button,
 or if we should just file it in the overflowing pile of Things That
 Might Be Nice To Do Someday.

What bugs me the most about having IN() be faster than EXISTS() in
certain situations is that it ends up being counter-intuitive and not
really what you'd expect to happen.  That being said, we can always tell
people that they can use IN() as a work-around for these situations.  In
the long run, I think it's definitely worth it to spend a bit of extra
time in planning the query for this case.  Not knowing what else is on
your plate for 8.4, I don't know where I'd rank this, but it wouldn't be
at the top.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] possible minor EXPLAIN bug?

2008-08-19 Thread Tom Lane
Pavel Stehule [EMAIL PROTECTED] writes:
 I thing so Agg node doesn't set width well:

The planner doesn't really bother to set the width correctly for any
expression-computing node.  This is partly laziness, but OTOH it is
very hard to estimate a sane width for any function returning a
variable-width data type; eg what are the odds of a useful answer for
select repeat(textcol, intcol) from tab1;
For plan nodes that return just Vars it's easier, since we generally
have got stats about average column widths.

I think Agg just copies the width of its input ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Compatibility types, type aliases, and distinct types

2008-08-19 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes:
 One direction of the cast could be AS ASSIGNMENT, btw., but that is another 
 decision that would have to be worked out.

Making the back-cast be AS ASSIGNMENT would reduce the risks of
ambiguities, for sure.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Improving non-joinable EXISTS subqueries

2008-08-19 Thread Kevin Grittner
 Tom Lane [EMAIL PROTECTED] wrote: 
 
 The examples that Kevin Grittner put up awhile back included several
 uses of EXISTS() in places where it couldn't be turned into a
semijoin,
 eg in the query's targetlist.  I was musing a bit about whether we
could
 improve those scenarios.  I would like to get 8.4 to the point where
we
 could say as a blanket performance recommendation prefer EXISTS
over
 IN.  The semantic gotchas associated with NOT IN make it hard to
 optimize well, not to mention being a perennial bane of novices; so
if
 we could just point people in the other direction without
qualification
 I think we'd be better off.
 
Agreed.
 
 So ... I'm wondering if this actually touches anyone's hot-button,
 or if we should just file it in the overflowing pile of Things That
 Might Be Nice To Do Someday.
 
 Comments?
 
I'm in the position of trying to influence programmers here to write
queries using set logic.  Way too many of the queries here are coded
with a cursor for a primary select, with a bunch of lower level
cursors to navigate around and get the related rows one at a time. 
Results are often stuck into a work table as this progresses, with the
work table massaged a bit here and there in this procedural process,
and the final results selected out.  It should surprise nobody here
that this is not fast to write, easy to maintain, efficient to run, or
generally free from subtle errors.  I point out that they should write
queries which state what they want, regardless of how complex those
rules are, instead of writing how to get it.  The optimizer, I argue,
has tricks available which they don't.
 
Usually, a rewrite into set logic has a fraction of the number of
lines, runs much faster, and loses a bug or two that was hidden within
the procedural spaghetti.  On the other hand, sometimes they write a
perfectly good set logic query (from the point of view of stating
what they want), and the optimizer falls down, and I have to come in
and say Oh, it has trouble with EXISTS; you can use IN here. When I
tell them to use IN instead of EXISTS, then I need to have all these
caveats about the sizes of tables and the possibilities of NULL on the
NOT EXISTS.  At this point I tend to lose a big part of my audience.
 
So I'd be very happy to see this work done, not because I can't find a
workaround, but because trying to teach all the programmers tricky
hand-optimizations is a losing battle, and if I lose that battle the
queries degenerate into spaghetti-land.
 
As with others, I can't say where this fits on a priority list, but I
would hate to see it drift off onto a someday list.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Tom Lane
Hannu Krosing [EMAIL PROTECTED] writes:
 On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote:
 I am sorry, but it's really new feature and not bug fix

 Could you please explain why you think so ?

For the same reasons that plan invalidation itself was a new feature
and not a bug fix; notably, risk vs reward tradeoff and not wanting
to change long-established behavior in stable branches.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Alvaro Herrera
Hans-Juergen Schoenig wrote:

 alternatively we could use some sort of #include mechanism to split  
 most important and not so important.

We already have an include mechanism.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Magnus Hagander
Alvaro Herrera wrote:
 Hans-Juergen Schoenig wrote:
 
 alternatively we could use some sort of #include mechanism to split  
 most important and not so important.
 
 We already have an include mechanism.

Using that to include a file that's full of comments anyway (which is
all that's left in postgresql.conf at this time, I'm sure) just seems.
Well. Sub-optimal.


//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Extending varlena

2008-08-19 Thread Kevin Grittner
 Tom Lane [EMAIL PROTECTED] wrote: 
 
 I was kinda wondering about something closer to the TOAST model,
where
 a blob is only referenceable from a value that's in a table field;
 and that value encapsulates the name of the blob in some way that
 needn't even be user-visible.  This'd greatly simplify the
 cleanup-dead-objects problem, and we could avoid addressing the
 permissions problem at all, since regular SQL permissions on the
table
 would serve fine.  But it's not clear what regular SQL fetch and
update
 behaviors should be like for such a thing.  (Fetching or storing the
 whole blob value is right out, IMHO.)  ISTR hearing of concepts
roughly
 like this in other DBs --- does it ring a bell for anyone?
 
It'd probably be good to have methods parallel to the JDBC API within
the implementation.
 
http://java.sun.com/javase/6/docs/api/java/sql/Blob.html
http://java.sun.com/javase/6/docs/api/java/sql/Clob.html
http://java.sun.com/javase/6/docs/api/java/sql/NClob.html
 
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getBlob(int)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getClob(int)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getNClob(int)
 
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.sql.Blob)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.io.InputStream)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.io.InputStream,%20long)
(similar for CLOB  NCLOB) 
 
Reading a blob value gives you an object which lets you perform the
stream-based operations against the blob.  To set a blob value on an
insert, you prepare a statement and then link the stream to the blob
-- the insertRow method sucks the data from the stream.  To set a blob
on an update, you use an updateable cursor (or maybe a prepared
statement) to do the same.  You can set a lob from another lob
directly in SQL  I assume we'd want to support streams directly inline
in the protocol, as well as support functions to convert between
datums and streams (for, say, tiny little 2MB or 10MB values), and
files and streams (kinda like COPY).
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 17:11:49 +0200
Magnus Hagander [EMAIL PROTECTED] wrote:

 Alvaro Herrera wrote:
  Hans-Juergen Schoenig wrote:
  
  alternatively we could use some sort of #include mechanism to
  split most important and not so important.
  
  We already have an include mechanism.
 
 Using that to include a file that's full of comments anyway (which is
 all that's left in postgresql.conf at this time, I'm sure) just seems.
 Well. Sub-optimal.

Yes but part of this idea is valid. The fact is the majority of the
postgresql.conf parameters don't need to be in there by default. It
just makes the file an intimidating mess for newbies and I am not
talking about just n00bs but also people coming from other environments
such as MSSQL.

I believe we could probably break the conf down to a reasonable 2 dozen
or less parameters. The rest should just be documented in our
documentation and call it good. We even have static URLs for this (I
seem to have dejavu with this as I am pretty sure I have had this
discussion already).

Joshua D Drake


 
 
 //Magnus
 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Dimitri Fontaine
Le mardi 19 août 2008, Tom Lane a écrit :
 For the same reasons that plan invalidation itself was a new feature
 and not a bug fix; 

I'm sorry but that doesn't help me a dime to understand current situation. It 
could well be just me, but... here's how I see it:

 - plan invalidation is a new feature in 8.3
 - previous releases are out of business: new feature against stable code
 - now, we have found a bug in plan invalidation
 - HEAD (to be 8.4) will get some new code to fix it

But 8.3 won't benefit from this bugfix? On the grounds that the feature which 
is now deployed on the field should *maybe* not get used the way it *is*?

Sorry again, I really don't get it.
-- 
dim


signature.asc
Description: This is a digitally signed message part.


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 12:48:06 +0100
Gregory Stark [EMAIL PROTECTED] wrote:

 Hannu Krosing [EMAIL PROTECTED] writes:
 
  Maybe there should be something in postgreSQL docs that warns users
  against using functions in any non-trivial circumstances, as
  functions are not expected to behave like the rest of postgreSQL
  features and there is not plan to fix that ?
 
 Now who's trolling :)

Although I read his remark as sarcastic after reading the entire
thread I have to say it may be a good idea to have the something in
the docs about the limitation. I never think about it anymore
because I am used to the behavior. I can see where and entity
like skype who has I am sure thousands of procedures would have this
as a constant irritant.

Do I think it should be pushed back to 8.3.x; no. It is a feature. I
don't consider the existing behavior a bug. I consider it a limitation
and we don't back patch fixes for limitations. 

Sincerely,

Joshua D. Drake


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Tom Lane
Dimitri Fontaine [EMAIL PROTECTED] writes:
  - now, we have found a bug in plan invalidation

[ shrug... ] You have not found a bug in plan invalidation.  You have
found omitted functionality --- functionality that was *intentionally*
omitted from the 8.3 version.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Tom Lane
Joshua Drake [EMAIL PROTECTED] writes:
 Magnus Hagander [EMAIL PROTECTED] wrote:
 Using that to include a file that's full of comments anyway (which is
 all that's left in postgresql.conf at this time, I'm sure) just seems.
 Well. Sub-optimal.

 Yes but part of this idea is valid. The fact is the majority of the
 postgresql.conf parameters don't need to be in there by default. It
 just makes the file an intimidating mess for newbies and I am not
 talking about just n00bs but also people coming from other environments
 such as MSSQL.

Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?
I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation.  For novices they aren't
even adequately duplicative.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 12:12:16 -0400
Tom Lane [EMAIL PROTECTED] wrote:

 Joshua Drake [EMAIL PROTECTED] writes:
  Magnus Hagander [EMAIL PROTECTED] wrote:

  Yes but part of this idea is valid. The fact is the majority of the
  postgresql.conf parameters don't need to be in there by default. It
  just makes the file an intimidating mess for newbies and I am not
  talking about just n00bs but also people coming from other
  environments such as MSSQL.
 
 Well, why not just make a one-eighty and say that the default
 postgresql.conf is *empty* (except for whatever initdb puts into it)?

I guess it would depend on what initdb puts into it. I don't really
have a problem ripping out all extra stuff as it would help force
people to read the docs but would we still have 150 parameters?. From a
friendly perspective it would make sense to tone it down to the key
parameters such as shared_buffers, listen_address, work_mem etc... 

We don't need (for example) to have autovacuum in there by default as it
is always on and configured reasonably at this point. If they need to
change autovacuum they should be reading the docs about it first;
the same with bgwriter, fsync, async_commit etc...

 I've never thought that the current contents were especially useful
 as documentation; the kindest thing you can say about 'em is that they
 are duplicative of the SGML documentation.  For novices they aren't
 even adequately duplicative.

I can't argue with this. :)

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] Time to get rid of -Winline ?

2008-08-19 Thread Tom Lane
Having recently updated my work machine to Fedora 9, I'm now getting
blessed with all the -Winline warnings that gcc 4.3 likes to emit.
I recall some other folk complaining of that previously.  While I could
suppress the switch in a Makefile.custom, I'm wondering whether it's
really doing anything for us.  The original motivation for putting it in
by default was to find out how many of our inline decorations were
really working, but AFAIR we have never done anything with that issue
anyway...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Dimitri Fontaine
Le mardi 19 août 2008, Tom Lane a écrit :
 [ shrug... ] You have not found a bug in plan invalidation.  You have
 found omitted functionality --- functionality that was *intentionally*
 omitted from the 8.3 version.

Thanks a lot for this clarification, now I understand you viewpoint.

So, the 8.3 fix would be about documenting this intentionnal omit in the 
great manual, maybe in a Limits section of the sql-createfunction page?

Another thing I do not understand well is how people are expected to work in 
8.3 with a function based API, without hitting Skype problems. I'm having a 
project here where the project manager wants a database function API to keep 
data logic at serverside, should I tell him to reconsider this while 8.4 is 
not ready?
We would then have to go live with an 8.3 based solution containing middleware 
code, then port it again to SQL functions when 8.4 is out  stable. Not 
appealing, but I sure understand the no new feature in stable code base 
argument here.

Regards,
-- 
dim


signature.asc
Description: This is a digitally signed message part.


[HACKERS] Adjusting debug_print_plan to be more useful by default

2008-08-19 Thread Tom Lane
Back in April we changed EXPLAIN VERBOSE to not dump the internal plan
tree anymore, on the grounds that non-hackers didn't want that info and
hackers could get it with debug_print_plan and related variables.
Well, now that I've tried to do some planner development work relying on
debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying.
It's not sufficient to set debug_print_plan = true, because the output
comes out at priority DEBUG1, which is to say it doesn't come out at all
in a default configuration.  If you boost up client_min_messages or
log_min_messages so you can see it, you get lots of extraneous debugging
messages too.

I'd like to propose that the messages emitted by debug_print_plan
and friends be given priority LOG rather than DEBUG1.  If you've gone
to the trouble of turning on the variable, then you presumably want the
results, so it seems dumb to print them at a priority that isn't logged
by default.  (Note that this is biased to the assumption that you want
the messages in the postmaster log, not on the console.  Which is
usually what I want, but maybe someone wants to argue for NOTICE?)

I'd also like to propose making debug_pretty_print default to ON.
At least for me, the other formatting is 100% unreadable.

Comments?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Tom Lane
Joshua Drake [EMAIL PROTECTED] writes:
 Tom Lane [EMAIL PROTECTED] wrote:
 Well, why not just make a one-eighty and say that the default
 postgresql.conf is *empty* (except for whatever initdb puts into it)?

 I guess it would depend on what initdb puts into it.

Per the code:

max_connections
shared_buffers
max_fsm_pages   (slated to die anyway in 8.4)
lc_messages
lc_monetary
lc_numeric
lc_time
datestyle
default_text_search_config

The first three of those are derived from probing the SHMMAX setting,
and the rest are from the initdb-time locale settings.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Josh Berkus

Tom,


Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?
I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation.  For novices they aren't
even adequately duplicative.


Well, that doesn't help unless we either provide a .conf generation tool 
(something I favor) or docs somewhere which explain which are the 
variables to be the most concerned with instead of making users read 
through all 218 of them.


Attached is the postgresql.conf.simple I used in my presentaiton.  It 
has an egregious math error in it (see if you can find it) but should 
give you the general idea.


--Josh

# 
# Simple PostgreSQL Configuration File
# 

# This file provides a simple configuration with the most common options
# which most users need to modify for running PostgreSQL in production, 
# including extensive notes on how to set each of these.  If your configuration
# needs are more specific, then use the standard postgresql.conf, or add 
# additional configuration options to the bottom of this file.
#
# This file is re-read when you send a SIGHUP to the server, or on a full
# restart.  Note that on a SIGHUP simply recommenting the settings is not
# enough to reset to default value; the last explicit value you set will
# still be in effect.
#
# AvRAM:  Several of the formulas below ask for AvRAM, which is short for
# Available RAM.  This refers to the amount of memory which is available for
# running PostgreSQL.  On a dedicated PostgreSQL server, you can use the total
# system RAM, but on shared servers you need to estimate what portion of RAM
# is usually available for PostgreSQL.
#
# Each setting below lists one recommended starting setting, followed by
# several alternate settings which are commented out.  If multiple settings
# are uncommented, the *last* one will take effect.

# listen_addresses
# 
# listen_addresses takes a list of network interfaces the Postmaster will
# listen on.  The setting below, '*', listens on all interfaces, and is only
# appropriate for development servers and initial setup.  Otherwise, it 
# should be restrictively set to only specific addresses. Note that most
# PostgreSQL access control settings are in the pg_hba.conf file.

  listen_addresses = '*' # all interfaces 
# listen_addresses = 'localhost'  # unix sockets and loopback only
# listen_addresses = 'localhost,192.168.1.1' # local and one external interface

# max_connections
# 
# An integer setting a limit on the number of new connection processes which 
# PostgreSQL will create.  Should be set to the maximum number of connections 
# which you expect to need at peak load.  Note that each connection uses
# shared_buffer memory, as well as additional non-shared memory, so be careful
# not to run the system out of memory.  In general, if you need more than 1000
# connections, you should probably be making more use of connection pooling.
# 
# Note that by default 3 connections are reserved for autovacuum and 
# administration.

  max_connections = 200  # small server
# max_connections = 700  # web application database
# max_connections = 40   # data warehousing database

# shared_buffers
# 
# A memory quantity defining PostgreSQL's dedicated RAM, which is used
# for connection control, active operations, and more.  However, since
# PostgreSQL also needs free RAM for file system buffers, sorts and 
# maintenance operations, it is not advisable to set shared_buffers to a
# majority of RAM.  
#
# Note that increasing shared_buffers often requires you to increase some 
# system kernel parameters, most notably SHMMAX and SHMALL.  See 
# Operating System Environment: Managing Kernel Resources in the PostgreSQL
# documentation for more details.  Also note that shared_buffers over 2GB is 
# only supported on 64-bit systems.
#
# The setting below is a formula.  Calculate the resulting value, then
# uncomment it.  Values should be expressed in kB, MB or GB.

# shared_buffers = ( AvRAM / 4 )
# shared_buffers = 512MB   # basic 2GB web server
# shared_buffers = 8GB # 64-bit server with 32GB RAM

# work_mem
# 
# This memory quantity sets the limit for the amount of non-shared RAM 
# available for each query operation, including sorts and hashes.  This limit
# acts as a primitive resource control, preventing the server from going
# into swap due to overallocation.  Note that this is non-shared RAM per
# *operation*, which means large complex queries can use multple times
# this amount.  Also, work_mem is allocated by powers of two, so round
# to the nearest binary step.

# The setting below is a formula.  Calculate the resulting value, then  
   

Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 12:48:20 -0400
Tom Lane [EMAIL PROTECTED] wrote:

 Joshua Drake [EMAIL PROTECTED] writes:
  Tom Lane [EMAIL PROTECTED] wrote:
  Well, why not just make a one-eighty and say that the default
  postgresql.conf is *empty* (except for whatever initdb puts into
  it)?
 
  I guess it would depend on what initdb puts into it.
 
 Per the code:
 
 max_connections
 shared_buffers
 max_fsm_pages (slated to die anyway in 8.4)
 lc_messages
 lc_monetary
 lc_numeric
 lc_time
 datestyle
 default_text_search_config
 
 The first three of those are derived from probing the SHMMAX setting,
 and the rest are from the initdb-time locale settings.

When I first started to reply I had a list of another dozen or so we
should add but in reality as I think about it; we need only one more
parameter. If we add listen_addresses and a link to the documention for
the rest, I would +1 this.

I was thinking about the apache conf and it is riddled with
documentation, lots and lots of text. I find that either I am irritated
with how much documentation there is (because I already understand the
directive I am working with) or I am frustrated because it doesn't
adequately explain the dependencies.

If we move to the above route, we end up in an environment with a
single source for official documentation and we can always point to
that.

Sincerely,

Joshua D. Drake


 
   regards, tom lane
 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Tom Lane
Dimitri Fontaine [EMAIL PROTECTED] writes:
 Another thing I do not understand well is how people are expected to work in 
 8.3 with a function based API, without hitting Skype problems.

I could understand this level of complaining if this were a new problem
that'd appeared in 8.3.  But *every PG version that we've ever released*
behaves the same way with respect to function drop/recreate.  If the
Skype folk have developed a way of working that is guaranteed not to
work with any released version, one has to wonder what they were
thinking.

If you need to DROP rather than CREATE OR REPLACE functions, then 8.3
doesn't make things better for you than prior releases did, but it
does't make them worse either.  Making things better for that case is
unequivocally a new feature.  And it's rather a corner case at that,
else there would have been enough prior complaints to put it on the
radar screen for 8.3.

What we've got at this point is a submitted patch for a new feature
that hasn't even been accepted into HEAD yet.  Lobbying to get it
back-patched is entirely inappropriate IMHO.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Kevin Grittner
 Josh Berkus [EMAIL PROTECTED] wrote: 
 
 Attached is the postgresql.conf.simple I used in my presentaiton.  It

 has an egregious math error in it (see if you can find it) but should

 give you the general idea.
 
Well, this sure looks scary:
 
# maintenance_work_mem = 256MB  #webserver with 2GB RAM
 
But I'm amazed by this, too:
 
# max_connections = 700  # web application database
 
How many CPUs and spindles are you assuming there?
 
My testing and experience suggest applications should use no more than
4 per CPU plus 2 per spindle, absolute maximum.  Don't you find that a
connection pool with queuing capability is required for best
performance with a large number of users?
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Tom Lane
Joshua Drake [EMAIL PROTECTED] writes:
 If we move to the above route, we end up in an environment with a
 single source for official documentation and we can always point to
 that.

Yeah, the fundamental point here is whether or not postgresql.conf
should be trying to serve as part of our system documentation.  I'm
inclined to think that any comments in it should be more about why these
particular values have been set, and not here are some values you might
like to twiddle.  So initdb might emit

# Set by initdb from probing kernel limits 2008-08-11
max_connections = 100
shared_buffers = 32MB

# Set by initdb from its locale environment: LANG = en_US
lc_messages = en_US
lc_monetary = en_US
[etc]

I'm really not in favor of having comments in the conf file that try to
tell you about stuff you might want to set, much less why.  That task
properly belongs to some kind of introductory chapter in the SGML docs.
Novice DBAs are unlikely even to *find* the config file, let alone look
inside it, if there's not an introductory chapter telling them about
Things They Ought To Do.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Jonah H. Harris
On Tue, Aug 19, 2008 at 1:17 PM, Kevin Grittner
[EMAIL PROTECTED] wrote:
 Josh Berkus [EMAIL PROTECTED] wrote:
 But I'm amazed by this, too:

 # max_connections = 700  # web application database

 How many CPUs and spindles are you assuming there?

 My testing and experience suggest applications should use no more than
 4 per CPU plus 2 per spindle, absolute maximum.  Don't you find that a
 connection pool with queuing capability is required for best
 performance with a large number of users?

Agreed, with this many concurrent users, I would expect severe lock
contention on the ProcArrayLock.  Similarly, if this were heavily
updated, WAL-related locks would likely become another significant
bottleneck.

-- 
Jonah H. Harris, Senior DBA
myYearbook.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Adjusting debug_print_plan to be more useful by default

2008-08-19 Thread Simon Riggs

On Tue, 2008-08-19 at 12:40 -0400, Tom Lane wrote:
 Back in April we changed EXPLAIN VERBOSE to not dump the internal plan
 tree anymore, on the grounds that non-hackers didn't want that info and
 hackers could get it with debug_print_plan and related variables.
 Well, now that I've tried to do some planner development work relying on
 debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying.
 It's not sufficient to set debug_print_plan = true, because the output
 comes out at priority DEBUG1, which is to say it doesn't come out at all
 in a default configuration.  If you boost up client_min_messages or
 log_min_messages so you can see it, you get lots of extraneous debugging
 messages too.
 
 I'd like to propose that the messages emitted by debug_print_plan
 and friends be given priority LOG rather than DEBUG1.  If you've gone
 to the trouble of turning on the variable, then you presumably want the
 results, so it seems dumb to print them at a priority that isn't logged
 by default.  (Note that this is biased to the assumption that you want
 the messages in the postmaster log, not on the console.  Which is
 usually what I want, but maybe someone wants to argue for NOTICE?)
 
 I'd also like to propose making debug_pretty_print default to ON.
 At least for me, the other formatting is 100% unreadable.

+1

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 13:22:34 -0400
Tom Lane [EMAIL PROTECTED] wrote:
 
 I'm really not in favor of having comments in the conf file that try
 to tell you about stuff you might want to set, much less why.  That
 task properly belongs to some kind of introductory chapter in the
 SGML docs. Novice DBAs are unlikely even to *find* the config file,
 let alone look inside it, if there's not an introductory chapter
 telling them about Things They Ought To Do.

I would be willing to work up a patch that does as you suggest.

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 12:17:46 -0500
Kevin Grittner [EMAIL PROTECTED] wrote:

 Well, this sure looks scary:
  
 # maintenance_work_mem = 256MB  #webserver with 2GB RAM

I would agree. 2GB isn't that much memory as it is and that is a fairly
heft amount of maintenance_work_mem. This isn't the days when vacuum
ran via cron at 2am anymore. Autovacuum will fire at any time.

  
 But I'm amazed by this, too:
  
 # max_connections = 700  # web application database
  
 How many CPUs and spindles are you assuming there?
  
 My testing and experience suggest applications should use no more than
 4 per CPU plus 2 per spindle, absolute maximum.  Don't you find that a
 connection pool with queuing capability is required for best
 performance with a large number of users?

I just did the math on this and I would say you are correct. I had
never really evaluated in the way you just had but based on some of our
larger installs (32cores, 100 spindles) your math works.

Noting that he actually states it is a webserver connecting there
should absolutely be a pool in front of PostgreSQL.

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Asko Oja
Polite answers lead to polite discussions. Caling other people names lead to
flame wars.
It's perfectly ok for Skype to keep our own build of 8.3 with given patch
and make it available for whoever might want it. At least now there is
almost good enough description why the patch was needed althou it would have
been more pleasant if the discussion had been constructive.
We didn't keep close enough watch on the list when 8.3 plan invalidation was
discussed and it came as bad surprise to us that some parts important to us
were left out.

By the way it's real nice what you are doing with in and exists
improvements. Thanks.

regards
Asko

On Tue, Aug 19, 2008 at 8:06 PM, Tom Lane [EMAIL PROTECTED] wrote:

 Dimitri Fontaine [EMAIL PROTECTED] writes:
  Another thing I do not understand well is how people are expected to work
 in
  8.3 with a function based API, without hitting Skype problems.

 I could understand this level of complaining if this were a new problem
 that'd appeared in 8.3.  But *every PG version that we've ever released*
 behaves the same way with respect to function drop/recreate.  If the
 Skype folk have developed a way of working that is guaranteed not to
 work with any released version, one has to wonder what they were
 thinking.

 If you need to DROP rather than CREATE OR REPLACE functions, then 8.3
 doesn't make things better for you than prior releases did, but it
 does't make them worse either.  Making things better for that case is
 unequivocally a new feature.  And it's rather a corner case at that,
 else there would have been enough prior complaints to put it on the
 radar screen for 8.3.

 What we've got at this point is a submitted patch for a new feature
 that hasn't even been accepted into HEAD yet.  Lobbying to get it
 back-patched is entirely inappropriate IMHO.

regards, tom lane

 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers



Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Pavel Stehule
2008/8/19 Dimitri Fontaine [EMAIL PROTECTED]:
 Le mardi 19 août 2008, Tom Lane a écrit :
 [ shrug... ] You have not found a bug in plan invalidation.  You have
 found omitted functionality --- functionality that was *intentionally*
 omitted from the 8.3 version.

 Thanks a lot for this clarification, now I understand you viewpoint.

 So, the 8.3 fix would be about documenting this intentionnal omit in the
 great manual, maybe in a Limits section of the sql-createfunction page?

 Another thing I do not understand well is how people are expected to work in
 8.3 with a function based API, without hitting Skype problems. I'm having a
 project here where the project manager wants a database function API to keep
 data logic at serverside, should I tell him to reconsider this while 8.4 is
 not ready?

You could to use patched 8.3.

 We would then have to go live with an 8.3 based solution containing middleware
 code, then port it again to SQL functions when 8.4 is out  stable. Not
 appealing, but I sure understand the no new feature in stable code base
 argument here.

This problem isn't too hard without pooling. Not all systems are
global - so usually is possible to find some window and recreate
functions and close all user connections.

Regards
Pavel Stehule


 Regards,
 --
 dim


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Asko Oja
 Another thing I do not understand well is how people are expected to work
in
 8.3 with a function based API, without hitting Skype problems.
People are expected to use same workarounds as Skype is using. For us
another unneccessary downtime week ago was what set us moving/thinking :).
When you use software with limitations then you learn to live with them.
Good thing about postgres you can do something yourself to get some of the
limitations removed.
As Pavel said you are probably using your own build anyway so one more patch
should not be a problem.

regards
Asko

On Tue, Aug 19, 2008 at 8:48 PM, Pavel Stehule [EMAIL PROTECTED]wrote:

 2008/8/19 Dimitri Fontaine [EMAIL PROTECTED]:
  Le mardi 19 août 2008, Tom Lane a écrit :
  [ shrug... ] You have not found a bug in plan invalidation.  You have
  found omitted functionality --- functionality that was *intentionally*
  omitted from the 8.3 version.
 
  Thanks a lot for this clarification, now I understand you viewpoint.
 
  So, the 8.3 fix would be about documenting this intentionnal omit in
 the
  great manual, maybe in a Limits section of the sql-createfunction page?
 
  Another thing I do not understand well is how people are expected to work
 in
  8.3 with a function based API, without hitting Skype problems. I'm having
 a
  project here where the project manager wants a database function API to
 keep
  data logic at serverside, should I tell him to reconsider this while 8.4
 is
  not ready?

 You could to use patched 8.3.

  We would then have to go live with an 8.3 based solution containing
 middleware
  code, then port it again to SQL functions when 8.4 is out  stable. Not
  appealing, but I sure understand the no new feature in stable code base
  argument here.

 This problem isn't too hard without pooling. Not all systems are
 global - so usually is possible to find some window and recreate
 functions and close all user connections.

 Regards
 Pavel Stehule

 
  Regards,
  --
  dim
 

 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers



Re: [HACKERS] temporary statistics option at initdb time

2008-08-19 Thread Magnus Hagander
Magnus Hagander wrote:
 Decibel! wrote:
 On Aug 13, 2008, at 4:12 AM, Magnus Hagander wrote:
 Tom Lane wrote:
 Decibel! [EMAIL PROTECTED] writes:
 I disagree. While we don't guarantee stats are absolutely up-to-date,
 or atomic I don't think that gives license for them to just magically
 not exist sometimes.
 Would it really be that hard to have the system copy the file out
 before telling all the other backends of the change?
 Well, there is no (zero, zilch, nada) use-case for changing this setting
 on the fly.  Why not make it a frozen at postmaster start GUC?  Seems
 like that gets all the functionality needed and most of the ease of use.
 Oh, there is a use-case. If you run your system and then only afterwards
 realize the I/O from the stats file is high enough to be an issue, and
 want to change it.

 That said, I'm not sure the use-case is anywhere near common enough to
 put a lot of code into it.

 Something to keep in mind as PG is used to build larger systems 'further
 up the enterprise'... for us to bounce a database at work costs us a LOT
 in lost revenue. I don't want to go into specifics, but it's more than
 enough to buy a very nice car. :) That's why I asked how hard it'd be to
 do this on the fly.
 
 Well, it's doable, but fairly hard.
 
 But you can do it the symlink way without shutting it down, I think. :-)

Actually, I think maybe not so hard. Attached is a patch that fixes
this. It's done by keeping the old filename around. When you change the
path, the stats collector will start writing the new file the next time
it writes something (which should be max 0.5 seconds later if something
is happening). The backends will immediately try to read from the new
filename, but if that one is not found, they will switch to reading the
old filename. This obviously fails if you change the temp directory
twice in less than half a second, but I really don't see a use-case for
that...

Or did I miss something here? :-)

//Magnus
Index: backend/postmaster/pgstat.c
===
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.179
diff -c -r1.179 pgstat.c
*** backend/postmaster/pgstat.c	15 Aug 2008 08:37:39 -	1.179
--- backend/postmaster/pgstat.c	19 Aug 2008 15:24:59 -
***
*** 110,115 
--- 110,116 
   */
  char	   *pgstat_stat_filename = NULL;
  char	   *pgstat_stat_tmpname = NULL;
+ char	   *pgstat_stat_lastname = NULL;
  
  /*
   * BgWriter global statistics counters (unused in other processes).
***
*** 203,208 
--- 204,210 
  
  static volatile bool need_exit = false;
  static volatile bool need_statwrite = false;
+ static volatile bool got_SIGHUP = false;
  
  /*
   * Total time charged to functions so far in the current backend.
***
*** 224,229 
--- 226,232 
  static void pgstat_exit(SIGNAL_ARGS);
  static void force_statwrite(SIGNAL_ARGS);
  static void pgstat_beshutdown_hook(int code, Datum arg);
+ static void pgstat_sighup_handler(SIGNAL_ARGS);
  
  static PgStat_StatDBEntry *pgstat_get_db_entry(Oid databaseid, bool create);
  static void pgstat_write_statsfile(bool permanent);
***
*** 2571,2577 
  	 * Ignore all signals usually bound to some action in the postmaster,
  	 * except SIGQUIT and SIGALRM.
  	 */
! 	pqsignal(SIGHUP, SIG_IGN);
  	pqsignal(SIGINT, SIG_IGN);
  	pqsignal(SIGTERM, SIG_IGN);
  	pqsignal(SIGQUIT, pgstat_exit);
--- 2574,2580 
  	 * Ignore all signals usually bound to some action in the postmaster,
  	 * except SIGQUIT and SIGALRM.
  	 */
! 	pqsignal(SIGHUP, pgstat_sighup_handler);
  	pqsignal(SIGINT, SIG_IGN);
  	pqsignal(SIGTERM, SIG_IGN);
  	pqsignal(SIGQUIT, pgstat_exit);
***
*** 2635,2640 
--- 2638,2652 
  			break;
  
  		/*
+ 		 * Reload configuration if we got SIGHUP from the postmaster.
+ 		 */
+ 		if (got_SIGHUP)
+ 		{
+ 			ProcessConfigFile(PGC_SIGHUP);
+ 			got_SIGHUP = false;
+ 		}
+ 
+ 		/*
  		 * If time to write the stats file, do so.	Note that the alarm
  		 * interrupt isn't re-enabled immediately, but only after we next
  		 * receive a stats message; so no cycles are wasted when there is
***
*** 2834,2839 
--- 2846,2858 
  	need_statwrite = true;
  }
  
+ /* SIGHUP handler for collector process */
+ static void
+ pgstat_sighup_handler(SIGNAL_ARGS)
+ {
+ 	got_SIGHUP = true;
+ }
+ 
  
  /*
   * Lookup the hash table entry for the specified database. If no hash
***
*** 3018,3023 
--- 3037,3059 
  
  	if (permanent)
  		unlink(pgstat_stat_filename);
+ 	else
+ 	{
+ 		/*
+ 		 * If the old filename exists, we need to unlink it now that we have written
+ 		 * the new file. This indicates that we are done, and it also makes it possible
+ 		 * to switch the directory back without getting the old data.
+ 		 *
+ 		 * Also do away with the old name here, so we don't try to delete it again.
+ 		 */
+ 		if (pgstat_stat_lastname != NULL)
+ 		

Re: [HACKERS] Adjusting debug_print_plan to be more useful by default

2008-08-19 Thread daveg
On Tue, Aug 19, 2008 at 06:33:33PM +0100, Simon Riggs wrote:
 
 On Tue, 2008-08-19 at 12:40 -0400, Tom Lane wrote:
  Back in April we changed EXPLAIN VERBOSE to not dump the internal plan
  tree anymore, on the grounds that non-hackers didn't want that info and
  hackers could get it with debug_print_plan and related variables.
  Well, now that I've tried to do some planner development work relying on
  debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying.
  It's not sufficient to set debug_print_plan = true, because the output
  comes out at priority DEBUG1, which is to say it doesn't come out at all
  in a default configuration.  If you boost up client_min_messages or
  log_min_messages so you can see it, you get lots of extraneous debugging
  messages too.
  
  I'd like to propose that the messages emitted by debug_print_plan
  and friends be given priority LOG rather than DEBUG1.  If you've gone
  to the trouble of turning on the variable, then you presumably want the
  results, so it seems dumb to print them at a priority that isn't logged
  by default.  (Note that this is biased to the assumption that you want
  the messages in the postmaster log, not on the console.  Which is
  usually what I want, but maybe someone wants to argue for NOTICE?)
  
  I'd also like to propose making debug_pretty_print default to ON.
  At least for me, the other formatting is 100% unreadable.
 
 +1

+1

-dg

-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Bruce Momjian
Joshua Drake wrote:
 On Tue, 19 Aug 2008 12:48:06 +0100
 Gregory Stark [EMAIL PROTECTED] wrote:
 
  Hannu Krosing [EMAIL PROTECTED] writes:
  
   Maybe there should be something in postgreSQL docs that warns users
   against using functions in any non-trivial circumstances, as
   functions are not expected to behave like the rest of postgreSQL
   features and there is not plan to fix that ?
  
  Now who's trolling :)
 
 Although I read his remark as sarcastic after reading the entire
 thread I have to say it may be a good idea to have the something in
 the docs about the limitation. I never think about it anymore
 because I am used to the behavior. I can see where and entity
 like skype who has I am sure thousands of procedures would have this
 as a constant irritant.
 
 Do I think it should be pushed back to 8.3.x; no. It is a feature. I
 don't consider the existing behavior a bug. I consider it a limitation
 and we don't back patch fixes for limitations. 

The bottom line here is that we don't have the time to explain or
justify our backpatch policy every time someone shows up with a bug that
needs to be fixed.

If you want to create your own version of Postgres, go ahead;  no one is
stopping you.  But if we backpatched everything and we introduced bugs
or change behavior, more people would complain.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 14:29:52 -0400 (EDT)
Bruce Momjian [EMAIL PROTECTED] wrote:


  Do I think it should be pushed back to 8.3.x; no. It is a feature. I
  don't consider the existing behavior a bug. I consider it a
  limitation and we don't back patch fixes for limitations. 
 
 The bottom line here is that we don't have the time to explain or
 justify our backpatch policy every time someone shows up with a bug
 that needs to be fixed.

Is our backpatch policy documented? It does not appear to be in
developer FAQ.

Joshua D. Drake


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] temporary statistics option at initdb time

2008-08-19 Thread Tom Lane
Magnus Hagander [EMAIL PROTECTED] writes:
 Actually, I think maybe not so hard. Attached is a patch that fixes
 this. It's done by keeping the old filename around. When you change the
 path, the stats collector will start writing the new file the next time
 it writes something (which should be max 0.5 seconds later if something
 is happening). The backends will immediately try to read from the new
 filename, but if that one is not found, they will switch to reading the
 old filename. This obviously fails if you change the temp directory
 twice in less than half a second, but I really don't see a use-case for
 that...

I think this is introducing complication and race conditions to solve a
problem that no one will really care about.  Just let people change the
filename at SIGHUP and document that doing that on-the-fly may cause
stats queries to fail for a short interval.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Adjusting debug_print_plan to be more useful by default

2008-08-19 Thread Hannu Krosing
On Tue, 2008-08-19 at 12:40 -0400, Tom Lane wrote:
 Back in April we changed EXPLAIN VERBOSE to not dump the internal plan
 tree anymore, on the grounds that non-hackers didn't want that info and
 hackers could get it with debug_print_plan and related variables.
 Well, now that I've tried to do some planner development work relying on
 debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying.
 It's not sufficient to set debug_print_plan = true, because the output
 comes out at priority DEBUG1, which is to say it doesn't come out at all
 in a default configuration.  If you boost up client_min_messages or
 log_min_messages so you can see it, you get lots of extraneous debugging
 messages too.
 
 I'd like to propose that the messages emitted by debug_print_plan
 and friends be given priority LOG rather than DEBUG1.  If you've gone
 to the trouble of turning on the variable, then you presumably want the
 results, so it seems dumb to print them at a priority that isn't logged
 by default.  (Note that this is biased to the assumption that you want
 the messages in the postmaster log, not on the console.  Which is
 usually what I want, but maybe someone wants to argue for NOTICE?)

what about changing (or adding) values log and notice ?

debug_print_plan = log;

debug_print_plan = notice;

so you could set that on demand ?

 I'd also like to propose making debug_pretty_print default to ON.
 At least for me, the other formatting is 100% unreadable.
 
 Comments?
 
   regards, tom lane
 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Bruce Momjian
Joshua Drake wrote:
 On Tue, 19 Aug 2008 14:29:52 -0400 (EDT)
 Bruce Momjian [EMAIL PROTECTED] wrote:
 
 
   Do I think it should be pushed back to 8.3.x; no. It is a feature. I
   don't consider the existing behavior a bug. I consider it a
   limitation and we don't back patch fixes for limitations. 
  
  The bottom line here is that we don't have the time to explain or
  justify our backpatch policy every time someone shows up with a bug
  that needs to be fixed.
 
 Is our backpatch policy documented? It does not appear to be in
 developer FAQ.

Seems we need to add it.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Adjusting debug_print_plan to be more useful by default

2008-08-19 Thread Tom Lane
Hannu Krosing [EMAIL PROTECTED] writes:
 what about changing (or adding) values log and notice ?
 debug_print_plan = log;
 debug_print_plan = notice;
 so you could set that on demand ?

Well, we could, but it would break existing habits for not much gain.
Really this proposal is to make debug_print_plan and friends work like
all our other logging options, and AFAIR all the rest emit at level LOG.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Peter Eisentraut
On Tuesday 19 August 2008 19:12:16 Tom Lane wrote:
 Well, why not just make a one-eighty and say that the default
 postgresql.conf is *empty* (except for whatever initdb puts into it)?

Well, my original implementation of GUC had an empty default configuration 
file, which was later craptaculated to its current form based on seemingly 
popular demand.  I am very happy to work back toward the empty state, and 
there appears to be growing support for that.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Kevin Grittner
 Joshua Drake [EMAIL PROTECTED] wrote:
 
 Is our backpatch policy documented? It does not appear to be in
 developer FAQ.
 
It's mentioned here:
 
http://www.postgresql.org/support/versioning
 
PostgreSQL minor releases fix only frequently-encountered, security,
and data corruption bugs to reduce the risk of upgrading.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Joshua Drake wrote:
 Is our backpatch policy documented? It does not appear to be in
 developer FAQ.

 Seems we need to add it.

I'm not sure that I *want* a formal written-down backpatch policy.
Whether (and how far) to backpatch has always been a best-judgment call
in the past, and we've gotten along fine with that.  I think having a
formal policy is just likely to lead to even more complaints: either
patching or not patching could result in second-guessing by someone
who feels he can construe the policy to match the result he prefers.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] compilig libpq with borland 5.5

2008-08-19 Thread claudio lezcano
I made tests compiling both sources (from CVS repository and from HTTP), and
i got the next results:

1-) MSVS 2005
*Source from CVS repository: *fatal error U1073: Don't know how to make
'libpq-dist.rc'
*Source from http (ver. 8.3.3): **Successfully generated*

2.) Borland 5.5.1
   * Source from CVS repository:*
Error E2451 ..\..\port\dirent.c 35: Undefined symbol
'INVALID_FILE_ATTRIBUTES' in function opendir
Error E2451 ..\..\port\dirent.c 52: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 53: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 59: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 62: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 64: Undefined symbol 'dirname' in function
opendir
Error E2451 ..\..\port\dirent.c 65: Undefined symbol 'handle' in function
opendir
Error E2451 ..\..\port\dirent.c 66: Undefined symbol 'ret' in function
opendir
Error E2451 ..\..\port\dirent.c 67: Undefined symbol 'ret' in function
opendir
Error E2451 ..\..\port\dirent.c 77: Undefined symbol 'handle' in function
readdir
Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'handle' in function
readdir
Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'dirname' in function
readdir
Error E2451 ..\..\port\dirent.c 80: Undefined symbol 'handle' in function
readdir
Error E2451 ..\..\port\dirent.c 88: Undefined symbol 'handle' in function
readdir
Error E2451 ..\..\port\dirent.c 100: Undefined symbol 'ret' in function
readdir
Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function
readdir
Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function
readdir
Error E2451 ..\..\port\dirent.c 103: Undefined symbol 'ret' in function
readdir
Error E2451 ..\..\port\dirent.c 109: Undefined symbol 'handle' in function
closedir
Error E2451 ..\..\port\dirent.c 110: Undefined symbol 'handle' in function
closedir
Error E2451 ..\..\port\dirent.c 111: Undefined symbol 'dirname' in function
closedir
Error E2228 ..\..\port\dirent.c 111: Too many error or warning messages in
function closedir
*** 26 errors in Compile ***
** error 1 ** deleting .\Release\dirent.obj

*Source from http (ver. 8.3.3):*
Warning: 'win32' not found in library
Warning: 'getaddrinfo' not found in library
Warning: 'pgstrcasecmp' not found in library
Warning: 'thread' not found in library
Warning: 'inet_aton' not found in library
Warning: 'crypt' not found in library
Warning: 'noblock' not found in library
Warning: 'md5' not found in library
Warning: 'ip' not found in library
Warning: 'fe-auth' not found in library
Warning: 'fe-protocol2' not found in library
Warning: 'fe-protocol3' not found in library
Warning: 'fe-connect' not found in library
Warning: 'fe-exec' not found in library
Warning: 'fe-lobj' not found in library
Warning: 'fe-misc' not found in library
Warning: public '_pqFlush' in module 'fe-misc' clashes with prior module
'fe-exec'
Warning: 'fe-print' not found in library
Warning: 'fe-secure' not found in library
Warning: 'pqexpbuffer' not found in library
Warning: 'pqsignal' not found in library
Warning: 'wchar' not found in library
Warning: 'encnames' not found in library
Warning: 'snprintf' not found in library
Warning: 'strlcpy' not found in library
Warning: 'pthread-win32' not found in library
Warning: '' not found in library
Warning: '.OBJ' file not found
brcc32.exe -l 0x409 -iC:\Borland\BCC55\include
-fo.\Release\libpq.res
libpq.rc
Borland Resource Compiler  Version 5.40
Copyright (c) 1990, 1999 Inprise Corporation.  All rights reserved.
ilink32.exe @MAKE.@@@
Turbo Incremental Link 5.00 Copyright (c) 1997, 2000 Borland
Error: Unresolved external '_pgwin32_safestat' referenced from C:\SOURCE
POSTGRES 8.3\SRC\INTERFACES\LIBPQ\REL
EASE\BLIBPQ.LIB|fe-connect

** error 2 ** deleting .\Release\blibpq.dll

He would welcome any suggestions or help

Claudio Lezcano


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Bruce Momjian
Tom Lane wrote:
 Bruce Momjian [EMAIL PROTECTED] writes:
  Joshua Drake wrote:
  Is our backpatch policy documented? It does not appear to be in
  developer FAQ.
 
  Seems we need to add it.
 
 I'm not sure that I *want* a formal written-down backpatch policy.
 Whether (and how far) to backpatch has always been a best-judgment call
 in the past, and we've gotten along fine with that.  I think having a
 formal policy is just likely to lead to even more complaints: either
 patching or not patching could result in second-guessing by someone
 who feels he can construe the policy to match the result he prefers.

OK, agreed.
-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Alvaro Herrera
Asko Oja escribió:
  Another thing I do not understand well is how people are expected to work
 in
  8.3 with a function based API, without hitting Skype problems.
 People are expected to use same workarounds as Skype is using. For us
 another unneccessary downtime week ago was what set us moving/thinking :).
 When you use software with limitations then you learn to live with them.
 Good thing about postgres you can do something yourself to get some of the
 limitations removed.

Make sure you do not live with patches forever, i.e. that it gets into
8.4.  Otherwise it's going to be a pain for everyone.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


 I'm really not in favor of having comments in the conf file that try to
 tell you about stuff you might want to set, much less why.  That task
 properly belongs to some kind of introductory chapter in the SGML docs.
 Novice DBAs are unlikely even to *find* the config file, let alone look
 inside it, if there's not an introductory chapter telling them about
 Things They Ought To Do.

Ugh, you are heading in the wrong direction. The configuration file
should be well documented: moving the documentation further away
from it is the wrong idea, especially if it means firing up a web
browser to do so. As link is fine, and recommended, but a bare
configuration file would be far, far worse than the mess we have today.
I like Josh B's version a lot. It's not perfect (I'd add a URL for
each config for example), but it's a great start. Text space is cheap, and 
having
a consistent, well-documented, easy-to-read conf file is something
worth shooting for.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200808191511
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkirGyEACgkQvJuQZxSWSsgsvwCdH6Hb4KOj47j/Zceb26FgEQUM
J2gAoKE19rLhMpgP17EdJIuUVoKQ7H3u
=//eH
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Tom Lane
Greg Sabino Mullane [EMAIL PROTECTED] writes:
 I like Josh B's version a lot. It's not perfect (I'd add a URL for
 each config for example), but it's a great start.

Josh B's approach is great until people start making changes that are
unrelated to (or perhaps even contradictory to) his comments.  And then
it's just a recipe for confusion.  I would far rather see his text as
part of the SGML docs.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 19:12:47 -
Greg Sabino Mullane [EMAIL PROTECTED] wrote:

 Ugh, you are heading in the wrong direction. The configuration file
 should be well documented: moving the documentation further away
 from it is the wrong idea, especially if it means firing up a web
 browser to do so.

It is impossible to document the postgresql.conf file in a manner that
is truly useful without firing up the reading material in the first
place.

Even with Josh's improvements there are too many variables and we are
just going to have a bunch of people breaking stuff and then
complaining that, well it was suggested in the postgresql.conf. 

 a URL
 for each config for example), but it's a great start. Text space is
 cheap, and having a consistent, well-documented, easy-to-read conf
 file is something worth shooting for.

I have yet to find one; anywhere.

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Dimitri Fontaine

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

Le 19 août 08 à 19:06, Tom Lane a écrit :

Dimitri Fontaine [EMAIL PROTECTED] writes:
Another thing I do not understand well is how people are expected  
to work in

8.3 with a function based API, without hitting Skype problems.


What we've got at this point is a submitted patch for a new feature
that hasn't even been accepted into HEAD yet.  Lobbying to get it
back-patched is entirely inappropriate IMHO.


Well, there's a misunderstanding here. I certainly were lobbying for  
considering a backpatch as I saw it as a bugfix. You told me it's a  
new feature, I say ok for not backpatching, obviously.


This mail was a real attempt at learning some tips to be able to push  
the functions usage as far as Skype is doing, in 8.3 release, and  
avoiding the trap which has always existed in released PostgreSQL  
version. This certainly was a bad attempt at it.


Now, my understanding is that rolling out new versions of functions  
requires forcing dropping all current opened sessions as soon as  
PostgreSQL considers you need to drop any function. I'll think about  
it in next project design meetings.


Regards,
- --
dim


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkirHlEACgkQlBXRlnbh1bk4YQCgswDS1bu+P+N7yKJvwnRAWnL3
FYkAnRZQzqbEoahShh/Qz9mnrIm1e99y
=hIBt
-END PGP SIGNATURE-

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Dimitri Fontaine

Le 19 août 08 à 20:47, Tom Lane a écrit :

I'm not sure that I *want* a formal written-down backpatch policy.
Whether (and how far) to backpatch has always been a best-judgment  
call

in the past, and we've gotten along fine with that.  I think having a
formal policy is just likely to lead to even more complaints: either
patching or not patching could result in second-guessing by someone
who feels he can construe the policy to match the result he prefers.


Agreed.
The problem here (at least for me) was to understand why this (yet to  
be reviewed) patch is about implementing a new feature and not about  
bugfixing an existing one. So we're exactly in the fog around the  
informal backpatch policy, and as long as we're able to continue  
talking nicely about it, this seems the finest solution :)


Keep up the amazing work, regards,
--
dim



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Peter Eisentraut
On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
 moving the documentation further away from it is the wrong idea,
 especially if it means firing up a web browser to do so.

I can see that argument, but I think we can quite simply solve it if we 
provide a plain-text version of the configuration chapter of the 
documentation.  You can easily grep that in a second window and don't have to 
be in-your-face to users who just want to edit the settings.

 Text space is cheap,

I'd offer the alternative theory that anything that is longer than one screen 
is overwhelming and unwieldy.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Robert Haas
 I'm really not in favor of having comments in the conf file that try to
 tell you about stuff you might want to set, much less why.  That task
 properly belongs to some kind of introductory chapter in the SGML docs.
 Novice DBAs are unlikely even to *find* the config file, let alone look
 inside it, if there's not an introductory chapter telling them about
 Things They Ought To Do.

+1.  When I have a question about something PostgreSQL-related, the
first think I do is Read The Fine Manual.  The PostgreSQL
documentation is excellent, and one of the highlights of the project
IMO.

I've read through the postgresql.conf file occasionally, but that's a
really difficult way to try to understand the subject.  I'd much
rather read through that file in a web browser than a shell window -
but the real advantage of putting it in the documentation is that you
can not only document each specific setting, but also give a broad
overview of relevant topics.  A section on Performance Tuning in
Section III: Server Administration would be really great.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 14:47:13 -0400
Tom Lane [EMAIL PROTECTED] wrote:

 Bruce Momjian [EMAIL PROTECTED] writes:
  Joshua Drake wrote:
  Is our backpatch policy documented? It does not appear to be in
  developer FAQ.
 
  Seems we need to add it.
 
 I'm not sure that I *want* a formal written-down backpatch policy.

Then we write a formal guideline. It really isn't fair to new developers
to not have any idea how they are going to be able to get a patch
applied to older branches. Something like:

Generally speaking we adhere to the following guideline for patches.
   * Security fixes are applied to all applicable branches.
   * Bugfixes are applied to all applicable branches
  * Note: A patch that addresses a known limitation is generally
not backpatched
   * New features are always applied to -HEAD only.

This is not a policy as much as a legend for developers to consider
before they submit their patch.

If we do this, we have the opportunity to just point to the FAQ when
there is no ambiguity. It also increases transparency of the process;
which is always a good thing.

Sincerely,

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Alvaro Herrera
Peter Eisentraut wrote:
 On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
  moving the documentation further away from it is the wrong idea,
  especially if it means firing up a web browser to do so.
 
 I can see that argument, but I think we can quite simply solve it if we 
 provide a plain-text version of the configuration chapter of the 
 documentation.  You can easily grep that in a second window and don't have to 
 be in-your-face to users who just want to edit the settings.

Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
you run man postgresql.conf and it gives you this manpage documenting
every option.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Andrew Sullivan
On Tue, Aug 19, 2008 at 02:47:13PM -0400, Tom Lane wrote:

 Whether (and how far) to backpatch has always been a best-judgment call
 in the past, and we've gotten along fine with that.  I think having a
 formal policy is just likely to lead to even more complaints:

I completely agree with this.  If you formalise the back-patch policy,
then it will be necessary to invent classifications for bug severity
to determine whether to back patch.  This will inevitably lead to some
sort of false objectivity measure, where bugs get a severity number
that actually just means we have already decided to back-patch.

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 15:43:11 -0400
Alvaro Herrera [EMAIL PROTECTED] wrote:

 Peter Eisentraut wrote:
  On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:
   moving the documentation further away from it is the wrong idea,
   especially if it means firing up a web browser to do so.
  
  I can see that argument, but I think we can quite simply solve it
  if we provide a plain-text version of the configuration chapter of
  the documentation.  You can easily grep that in a second window and
  don't have to be in-your-face to users who just want to edit the
  settings.
 
 Hmm, let me suggest providing it as a manpage for postgresql.conf,
 i.e., you run man postgresql.conf and it gives you this manpage
 documenting every option.

and native windows help (egad) but yes that would be good.

Joshua D. Drake 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Robert Haas
 Another thing I do not understand well is how people are expected to work in
 8.3 with a function based API, without hitting Skype problems. I'm having a

All database-driven applications have this problem.  Any time you have
a database on the backend and interface code on the front-end, you
need to keep in mind that it won't necessarily be possible to update
the two of them simultaneously, especially if you have multiple
back-ends and multiple front-ends, as you almost certainly do.  Even
if PostgreSQL invalidated plans in the particular situation you're
discussing, there would still be other problems.  You could add a new,
non-NULLable column to a table before updating the code that insert
into that table, or drop an old column that the code still counts on
being able to access.

I handle these problems all the time by ordering the changes
carefully. If I need to change a function API in an incompatible way,
I change the NAME of the function as well as the type signature (eg.
do_important_thing - do_important_thing_v2).  Then I change the code.
 Then I remove the old function once everything that relies on it is
dead.

Maybe in your particular environment plan invalidation for functions
will solve most of the cases you care about, but I respectfully submit
that there's no substitute for good release engineering.  If you don't
know exactly what functions are going to be created, modified, or
dropped on your production servers during each release before you
actually roll that release out...  you probably need to improve your
internal documentation.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Ron Mayer

Peter Eisentraut wrote:

On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote:

Text space is cheap,


I'd offer the alternative theory that anything that is longer than one screen 
is overwhelming and unwieldy.


One more benefit of a small file is that it makes it easier to ask someone
please attach a copy of your postgresql.conf file; rather than please
send the output of grep -v '^[]*#' postgresql.conf  | grep = or worse
Can you recall what you changed?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Dimitri Fontaine

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

Le 19 août 08 à 22:03, Robert Haas a écrit :

All database-driven applications have this problem.  Any time you have
a database on the backend and interface code on the front-end, you
need to keep in mind that it won't necessarily be possible to update
the two of them simultaneously, especially if you have multiple
back-ends and multiple front-ends, as you almost certainly do.  Even
if PostgreSQL invalidated plans in the particular situation you're
discussing, there would still be other problems.  You could add a new,
non-NULLable column to a table before updating the code that insert
into that table, or drop an old column that the code still counts on
being able to access.


Using functions the way Skype uses them means not issuing a single  
insert, update or delete directly from your code, but calling a  
function which takes care about it.
So you use PostgreSQL transactionnal DDL to roll-out new function  
versions at the same time you push the schema modifications, and  
commit it all in one go.



Maybe in your particular environment plan invalidation for functions
will solve most of the cases you care about


When the code only is a client to an SQL functions API, which  
effectively replaces SQL as the way to interact between code and  
database, then I believe plan invalidation at function change is the  
missing piece.



, but I respectfully submit
that there's no substitute for good release engineering.  If you don't
know exactly what functions are going to be created, modified, or
dropped on your production servers during each release before you
actually roll that release out...  you probably need to improve your
internal documentation.


Agreed :)
- --
dim


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkirK2kACgkQlBXRlnbh1bmxvQCgmowpfnZ5nFRml0mNfj2HRE+3
HJEAnR3G6Lhnb7R4+iSze8xGACwyk4D7
=of1o
-END PGP SIGNATURE-

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Andrew Sullivan
On Tue, Aug 19, 2008 at 12:42:29PM -0700, Joshua Drake wrote:
 Generally speaking we adhere to the following guideline for patches.
* Security fixes are applied to all applicable branches.
* Bugfixes are applied to all applicable branches
   * Note: A patch that addresses a known limitation is generally
 not backpatched
* New features are always applied to -HEAD only.
 
 This is not a policy as much as a legend for developers to consider
 before they submit their patch.

But it's meaningless.  Bugfixes are applied to all applicable
branches, is either false or trivially true.  It's trivially true if
you interpret applicable branches to mean the ones that get the
patch.  It's false if you mean bugfix to mean every patch that
fixes a bug.  I can think of bugs that we have lived with in older
releases because fixing them was too risky or because the bug was so
tiny or unusual as to make the risk greater than the reward.

A formal policy that's any more detailed than what's in the FAQ today
is a solution in search of a problem.

A

-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Tue, 2008-08-19 at 21:26 +0200, Dimitri Fontaine wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 Hi,
 
 Le 19 août 08 à 19:06, Tom Lane a écrit :
  Dimitri Fontaine [EMAIL PROTECTED] writes:
  Another thing I do not understand well is how people are expected  
  to work in
  8.3 with a function based API, without hitting Skype problems.
 
  What we've got at this point is a submitted patch for a new feature
  that hasn't even been accepted into HEAD yet.  Lobbying to get it
  back-patched is entirely inappropriate IMHO.
 
 Well, there's a misunderstanding here. I certainly were lobbying for  
 considering a backpatch as I saw it as a bugfix. You told me it's a  
 new feature, I say ok for not backpatching, obviously.
 
 This mail was a real attempt at learning some tips to be able to push  
 the functions usage as far as Skype is doing, in 8.3 release, and  
 avoiding the trap which has always existed in released PostgreSQL  
 version. This certainly was a bad attempt at it.
 
 Now, my understanding is that rolling out new versions of functions  
 requires forcing dropping all current opened sessions as soon as  
 PostgreSQL considers you need to drop any function. I'll think about  
 it in next project design meetings.

I think that another option is to manipulate pg_proc - just do a no-op
update to advance xmin for all functions that may have cached plans.

UPDATE pg_proc SET proname = proname;

then make sure that pg_proc is vacuumed often enough.

It's a bit wasteful, as it forces re-planning of all functions, but
should have similar effect than the patch.

It's also possible that updating pg_proc in bulk introduces some race
conditions which lock up the database.

--
Hannu





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 16:22:43 -0400
Andrew Sullivan [EMAIL PROTECTED] wrote:

 A formal policy that's any more detailed than what's in the FAQ today
 is a solution in search of a problem.

Odd that the problem continues to rear its head though isn't it? This
certainly isn't the first time it has come up.  I have however made my
argument. I also tried to help solve the problem. If we aren't
interested in a solution, oh well. It doesn't make my life any harder.


Sincerely,

Joshua D. Drake


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Alvaro Herrera
Dimitri Fontaine escribió:

 The problem here (at least for me) was to understand why this (yet to be 
 reviewed) patch is about implementing a new feature and not about  
 bugfixing an existing one. So we're exactly in the fog around the  
 informal backpatch policy, and as long as we're able to continue talking 
 nicely about it, this seems the finest solution :)

The actual criterion is not really new user-visible feature versus
bug fix.  It's more an attempt at measuring how large a potential
impact the change has.  The patch I saw was introducing a whole new
message type to go through the shared invalidation queue, which is not
something to be taken lightly (consider that there are three message
types of messages currently.)

It's possible that for the Skype usage this patch introduces the
behavior they want.  But for other people, perhaps this kind of
invalidation causes secondary effects that are completely unforeseen --
what if it breaks their apps and they must carry out a week's work to
fix it?  What if a serious security problem is discovered tomorrow and
they can't update because we've broken backwards compatibility for them?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes:
 The actual criterion is not really new user-visible feature versus
 bug fix.  It's more an attempt at measuring how large a potential
 impact the change has.  The patch I saw was introducing a whole new
 message type to go through the shared invalidation queue, which is not
 something to be taken lightly (consider that there are three message
 types of messages currently.)

I hadn't read it yet, but that makes it wrong already.  There's no need
for any new inval traffic --- the existing syscache inval messages on
pg_proc entries should serve fine.

More generally, if we are to try to invalidate on the strength of
pg_proc changes, what of other DDL changes?  Operators, operator
classes, maybe?  How about renaming a schema?  I would like to see a
line drawn between things we find worth trying to track and things we
don't.  If there is no such line, we're going to need a patch a lot
larger than this one.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Tue, 2008-08-19 at 16:03 -0400, Robert Haas wrote:
  Another thing I do not understand well is how people are expected to work in
  8.3 with a function based API, without hitting Skype problems. I'm having a
 
 All database-driven applications have this problem.  Any time you have
 a database on the backend and interface code on the front-end, you
 need to keep in mind that it won't necessarily be possible to update
 the two of them simultaneously, especially if you have multiple
 back-ends and multiple front-ends, as you almost certainly do.  Even
 if PostgreSQL invalidated plans in the particular situation you're
 discussing, there would still be other problems.  You could add a new,
 non-NULLable column to a table before updating the code that insert
 into that table, or drop an old column that the code still counts on
 being able to access.
 
 I handle these problems all the time by ordering the changes
 carefully. If I need to change a function API in an incompatible way,
 I change the NAME of the function as well as the type signature (eg.
 do_important_thing - do_important_thing_v2).  Then I change the code.
  Then I remove the old function once everything that relies on it is
 dead.

Not having plan invalidation forces you to have do_important_thing_v2
for do_important_thing even with no changes in source code, just for the
fact that do_part_of_important_thing() which it calls has changed.

An example -

 you have functions

A) caller1() to callerN() which includes call to called1() 

B) one of these functions, say callerM() needs one more field returned
from called1(), so you either write a completely new function
called1_v2() with one more field and then update  callerM() to call
called1_v2()

C) now, to get rid of called1() you have to replace called1 with
called1_v2 also in all other functions caller1() to callerN() 

D) then you can drop called1()

if you missed one of callerx() functions (you can drop called1() even if
it is used, as postgreSQL does not check dependencies in functions) then
you have a non-functioning database, where even client reconnect won't
help, only putting called1() back.

If there is plan invalidation then you just change called1() to return
one more field and that's it - no juggling with C) and D) and generally
less things that can go wrong.

 Maybe in your particular environment plan invalidation for functions
 will solve most of the cases you care about, but I respectfully submit
 that there's no substitute for good release engineering. 

Nope, but the amount of release engineering (and deployment-time work)
you need to do depends a lot on fragility of the system.

The more arcane and fragile the system is, the more you need to rely on
external systems and procedures to keep it working.

Imagine how much harder it would be, if there were no transactions and
you had to ensure the right ordering of all changes by release process
only. You probably would end up doing several times more work and
temporary hacks and you would still be out of luck doing _any_
nontrivial updates while the systems are running 24/7.

 If you don't
 know exactly what functions are going to be created, modified, or
 dropped on your production servers during each release before you
 actually roll that release out...  

this is not about knowing this at all - this is about needing to change
less, about optimizing on work that does not need to be done if system
is smarter.

 you probably need to improve your internal documentation.

or improve the database system you use.

if you need to change less functions, you also need less documentation
about changes. if you can prove that select a,b from f() always
returns the same data as select a,b from f_b2() then you don't need to
write f_b2() at all, you just redefine f() and can also skip migrating
all callers of f() to f_v2() just to satisfy your databases quirks.

---
Hannu




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Tom Lane
Alvaro Herrera [EMAIL PROTECTED] writes:
 Peter Eisentraut wrote:
 I can see that argument, but I think we can quite simply solve it if we 
 provide a plain-text version of the configuration chapter of the 
 documentation.

 Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
 you run man postgresql.conf and it gives you this manpage documenting
 every option.

Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
Is there any near equivalent on Windows?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Joshua Drake
On Tue, 19 Aug 2008 17:03:48 -0400
Tom Lane [EMAIL PROTECTED] wrote:

 Alvaro Herrera [EMAIL PROTECTED] writes:
  Peter Eisentraut wrote:
  I can see that argument, but I think we can quite simply solve it
  if we provide a plain-text version of the configuration chapter of
  the documentation.
 
  Hmm, let me suggest providing it as a manpage for postgresql.conf,
  i.e., you run man postgresql.conf and it gives you this manpage
  documenting every option.
 
 Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
 Is there any near equivalent on Windows?

Yes there are Windows Help files. I would imagine the installer would
deal with that. Magnus would obviously know better than I.

Sincerely,

Joshua D. Drake

 
   regards, tom lane
 


-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Tue, 2008-08-19 at 16:56 -0400, Tom Lane wrote:
 Alvaro Herrera [EMAIL PROTECTED] writes:
  The actual criterion is not really new user-visible feature versus
  bug fix.  It's more an attempt at measuring how large a potential
  impact the change has.  The patch I saw was introducing a whole new
  message type to go through the shared invalidation queue, which is not
  something to be taken lightly (consider that there are three message
  types of messages currently.)
 
 I hadn't read it yet, but that makes it wrong already.  There's no need
 for any new inval traffic --- the existing syscache inval messages on
 pg_proc entries should serve fine.
 
 More generally, if we are to try to invalidate on the strength of
 pg_proc changes, what of other DDL changes?  Operators, operator
 classes, maybe?  How about renaming a schema? I would like to see a
 line drawn between things we find worth trying to track and things we
 don't.  If there is no such line, we're going to need a patch a lot
 larger than this one.

Or maybe a simpler and smaller patch - just invalidate everything on
every schema change :)

It will have a momentary impact on performance at DDL time, but
otherways might be more robust and easier to check for errors.

-
Hannu



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Hannu Krosing
On Tue, 2008-08-19 at 16:56 -0400, Tom Lane wrote:
 Alvaro Herrera [EMAIL PROTECTED] writes:
  The actual criterion is not really new user-visible feature versus
  bug fix.  It's more an attempt at measuring how large a potential
  impact the change has.  The patch I saw was introducing a whole new
  message type to go through the shared invalidation queue, which is not
  something to be taken lightly (consider that there are three message
  types of messages currently.)
 
 I hadn't read it yet, but that makes it wrong already.  There's no need
 for any new inval traffic --- the existing syscache inval messages on
 pg_proc entries should serve fine.

I have'nt looke at the patch either, but I suspect that what goes
through shared mem is the registration for invalidation, as dependent
function OIDs are only learned while compiling functions

so when f_caller() learns that it caches plan f_called() then it
registers through shared mem message its wish to invalidate this plan if
f_called() is dropped or redefined.

--
Hannu



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Dave Page
On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane [EMAIL PROTECTED] wrote:
 Alvaro Herrera [EMAIL PROTECTED] writes:
 Peter Eisentraut wrote:
 I can see that argument, but I think we can quite simply solve it if we
 provide a plain-text version of the configuration chapter of the
 documentation.

 Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
 you run man postgresql.conf and it gives you this manpage documenting
 every option.

 Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
 Is there any near equivalent on Windows?

No. There are helpfiles (which consist of a navigation tree and a
bunch of pages), but they're what we use for the main docs. There's
nothing akin to a man page.
-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Greg Smith

On Tue, 19 Aug 2008, Josh Berkus wrote:

Well, that doesn't help unless we either provide a .conf generation tool 
(something I favor) or docs somewhere which explain which are the variables 
to be the most concerned with instead of making users read through all 218 of 
them.


The design for a pg_generate_conf tool you suggested that's now dumped 
into http://wiki.postgresql.org/wiki/GUCS_Overhaul#pg_generate_conf seemed 
the only reasonable solution I've ever heard here.  The difference of 
opinion between those those want a tiny file and those who want a full one 
cannot be reconciled.  It's not a logical debate, it's a religious one. 
The best you can do is provide something that's switchable to work for the 
most popular positions:


* The file should be minimal
* Every parameter should be there with lots of documentation
* Just the important parameters should be listed

Because no one who is firmly in one of those camps will ever move to 
another just by arguing here.


I'm going to rewrite that Wiki page to make it more obvious how the 
proposed changes actually map to resolving problems in this area.  Much of 
what's come up in this thread is already addressed there but that's 
clearly not obvious to most people.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread Alvaro Herrera
Dave Page wrote:
 On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane [EMAIL PROTECTED] wrote:
  Alvaro Herrera [EMAIL PROTECTED] writes:

  Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e.,
  you run man postgresql.conf and it gives you this manpage documenting
  every option.
 
  Seems a bit Unix-centric, but +1 for it on Unix machines anyway.
  Is there any near equivalent on Windows?
 
 No. There are helpfiles (which consist of a navigation tree and a
 bunch of pages), but they're what we use for the main docs. There's
 nothing akin to a man page.

Well, so we provide a reference to the help file and that's it.  If
there's a way to provide a link in the config file that would
automatically open the appropriate help file on click, that would be
perfect.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Patch: plan invalidation vs stored procedures

2008-08-19 Thread Robert Haas
  you have functions

 A) caller1() to callerN() which includes call to called1()

 B) one of these functions, say callerM() needs one more field returned
 from called1(), so you either write a completely new function
 called1_v2() with one more field and then update  callerM() to call
 called1_v2()

 C) now, to get rid of called1() you have to replace called1 with
 called1_v2 also in all other functions caller1() to callerN()

 D) then you can drop called1()

True.  I complained about this same problem in the context of views -
you can add a column to a table in place but not to a view, or even a
type created via CREATE TYPE.  I even went so far as to develop a
patch[1] to improve the situation, which to my sadness was not met
with wild enthusiasm.

[1] http://archives.postgresql.org/pgsql-hackers/2008-08/msg00272.php

Does it help to do CREATE OR REPLACE FUNCTION on callerX() after
dropping and recreating called1()?  If so, you might want to just
recreate all of your functions every time you do a release.  I wrote a
perl script that does this and it's worked pretty well for me.
Besides possibly avoiding this problem, it means that I don't really
need to worry about which functions I've modified in this release
quite as much, since I'm just going to push out the most-current
definition for all of them.

 Nope, but the amount of release engineering (and deployment-time work)
 you need to do depends a lot on fragility of the system.

Also true, but I think comparing plan invalidation to transactional
semantics is quite unfair.  There's basically no amount of user code
which will compensate for the lack of an ACID-compliant database.  On
the other hand, working around the lack of plan invalidation (or the
inability to add columns to views without recreating them) just
requires being careful to catch all of the stray references in your
DDL and testing thoroughly before you roll out to production, which
are good things to do anyway.  That's not to say that we shouldn't
have plan invalidation, just that I don't think it's anywhere close to
the same level of broken.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] A smaller default postgresql.conf

2008-08-19 Thread David Fetter
On Tue, Aug 19, 2008 at 07:12:47PM -, Greg Sabino Mullane wrote:
  I'm really not in favor of having comments in the conf file that
  try to tell you about stuff you might want to set, much less why.
  That task properly belongs to some kind of introductory chapter in
  the SGML docs.  Novice DBAs are unlikely even to *find* the config
  file, let alone look inside it, if there's not an introductory
  chapter telling them about Things They Ought To Do.
 
 Ugh, you are heading in the wrong direction.  The configuration file
 should be well documented: moving the documentation further away
 from it is the wrong idea, especially if it means firing up a web
 browser to do so.  As link is fine, and recommended, but a bare
 configuration file would be far, far worse than the mess we have
 today.  I like Josh B's version a lot.  It's not perfect (I'd add a
 URL for each config for example), but it's a great start.  Text
 space is cheap, and having a consistent, well-documented,
 easy-to-read conf file is something worth shooting for.

How about a man page for postgresql.conf?  We already ship very nice
man pages for SQL commands.  While we're at it, we could ship one for
pg_hba.conf, too :)

What do we do about man pages on Windows?

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] compilig libpq with borland 5.5

2008-08-19 Thread Hiroshi Saito
Hi.

I made tests compiling both sources (from CVS repository and from HTTP), and
i got the next results:

1-) MSVS 2005
*Source from CVS repository: *fatal error U1073: Don't know how to make
'libpq-dist.rc'
*Source from http (ver. 8.3.3): **Successfully generated*

It can be made from 'make distprep'. and def file is also made.
However, I make it except windows environment.

Regards,
Hiroshi Saito

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


  1   2   >