date:20100528

[GENERAL] Anderson dos Santos Donda wants to stay in touch on LinkedIn

2010-05-28 Thread Anderson dos Santos Donda

LinkedIn
Anderson dos Santos Donda requested to add you as a connection on 
LinkedIn:
--

Andrew,

I'd like to add you to my professional network on LinkedIn.

- Anderson dos Santos Donda

Accept invitation from Anderson dos Santos Donda
http://www.linkedin.com/e/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2082952686_2/1BpC5vrmRLoRZcjkkZt5YCpnlOt3RApnhMpmdzgmhxrSNBszYOnPoUdz8Rej8Uc399bRFEtSxDhnkPbP8Sd3AMejcQcP4LrCBxbOYWrSlI/EML_comm_afe/

View invitation from Anderson dos Santos Donda
http://www.linkedin.com/e/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2082952686_2/39vdzwSczkVczwMcAALqnpPbOYWrSlI/svi/
 
--

DID YOU KNOW you can conduct a more credible and powerful reference check using 
LinkedIn? Enter the company name and years of employment or the prospective 
employee to find their colleagues that are also in your network. This provides 
you with a more balanced set of feedback to evaluate that new hire.
http://www.linkedin.com/e/rsr/inv-27/

 
--
(c) 2010, LinkedIn Corporation

[GENERAL] Error of text2ltree

2010-05-28 Thread Vu Hong Thuan

I can't use text2ltree. Any suggestion? Thanks.

SELECT text2ltree('abc'). It's okay. But, got error if:

SELECT text2ltree(to_char(243, '0999'))

 Error **

ERROR: syntax error at position 0 SQL state: 42601

Re: [GENERAL] sintax error in script

2010-05-28 Thread Luis Daniel Lucio Quiroz

Le vendredi 28 mai 2010 20:34:13, Shoaib Mir a écrit :
> On Sat, May 29, 2010 at 11:09 AM, Luis Daniel Lucio Quiroz <
> 
> luis.daniel.lu...@gmail.com> wrote:
> > pgsql is telling me /-- END has a sintax error,
> > just to confirm, is it okay if i get rid of that line?
> 
> I think you should be fine removing this, as if I re-call I haven't seen
> something like this in PLPSQL functions myself
thanx

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

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

2010-05-28 Thread Tatsuo Ishii

> Gary Fu  writes:
> > Nilesh Govindarajan wrote:
> >> Could you please explain a little more what you want to do actually ?
> 
> > I have the following problem to run 'configure' when building pgpool 
> > 2.3.3 with libpq from normal built of postgreSQL 8.4.4.
> 
> > configure:24789: result: no
> > configure:24799: error: libpq is not installed or libpq is old
> 
> This is a bug in pgpool's configure script, which you should report to
> them not us.  It's not our responsibility to supply libdl.

Starting from pgpool-II 2.3 it provides SSL support and it needs
libssl then libssl needs libdl. Because pgpool's configure does not
link libdl(which is a bug), pgpool relies on libpq's reference to
libdl. Unfortunately libpq does not refer to libdl unless libpq
is configured with SSL support. Probably his PostgreSQL 8.4.4 build
was made without SSL support. Sorry for making a noise.

I'm studying now libpq's build to know how to refer to libdl if SSL is
enabled...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

Re: [GENERAL] sintax error in script

2010-05-28 Thread Shoaib Mir

On Sat, May 29, 2010 at 11:09 AM, Luis Daniel Lucio Quiroz <
luis.daniel.lu...@gmail.com> wrote:

> pgsql is telling me /-- END has a sintax error,
> just to confirm, is it okay if i get rid of that line?
>
>
I think you should be fine removing this, as if I re-call I haven't seen
something like this in PLPSQL functions myself

-- 
Shoaib Mir
http://shoaibmir.wordpress.com/

[GENERAL] getaddrinfo problems in pl/python

2010-05-28 Thread Gregory Golberg

Hi all,

I am having problems with getaddrinfo in plpython. Pl/Python is the
same as my system
python (2.6), this is MacOS X. Here's what happens in the python prompt:

>>> from _socket import *
>>> import _socket
>>> import sys
>>> print _socket.__file__
/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/lib-dynload/_socket.so
>>> print sys.version
2.6.1 (r261:67515, Feb 11 2010, 15:47:53)
[GCC 4.2.1 (Apple Inc. build 5646)]
>>> ga = getaddrinfo('localhost',8088,0,SOCK_STREAM)
>>> print ga
[(30, 1, 6, '', ('::1', 8088, 0, 0)), (30, 1, 6, '', ('fe80::1%lo0',
8088, 0, 1)), (2, 1, 6, '', ('127.0.0.1', 8088))]

In other words, everything is OK. (I am printing the version and the
file just so it can be compared to the
output from plpython).

Now, let's do it in pl/python:


SET client_min_messages='LOG';

CREATE OR REPLACE FUNCTION trycon(host VARCHAR) RETURNS text
AS $$
from _socket import *
import _socket
import sys
plpy.log(_socket.__file__)
plpy.log(sys.version)
ga = getaddrinfo(host,8088,0,SOCK_STREAM)
plpy.log(str(ga))
return str(ga)
$$ LANGUAGE plpythonu;

SELECT trycon('localhost');

outputs:

LOG:  
('/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/lib-dynload/_socket.so',)
LOG:  ('2.6.1 (r261:67515, Feb 11 2010, 15:47:53) \n[GCC 4.2.1 (Apple
Inc. build 5646)]',)
ERROR:  PL/Python: PL/Python function "trycon" failed
DETAIL:  : [Errno 8] nodename nor servname
provided, or not known

What am I missing? Thanks for the help in advance.

-gg

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

[GENERAL] sintax error in script

2010-05-28 Thread Luis Daniel Lucio Quiroz

CREATE OR REPLACE FUNCTION to_number(integer)
  RETURNS "numeric" AS
$BODY$
BEGIN
RETURN to_number($1, 'S99D99');
EXCEPTION 
  WHEN OTHERS THEN 
RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
/-- END


pgsql is telling me /-- END has a sintax error,
just to confirm, is it okay if i get rid of that line?

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

Re: [GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Tom Wilcox

I ran this query:

EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org;

And I got this result:

"Seq Scan on match_data  (cost=0.00..9762191.68 rows=32205168 width=206)
(actual time=76873.592..357450.519 rows=2961 loops=1)"
"Total runtime: 8028212.367 ms"


On 28 May 2010 19:39, Tom Wilcox  wrote:

> Oops. Sorry about that.
>
> I am having this problem with multiple queries however I am confident that
> a fair number may involve the custom plpython "normalise" function which I
> have made myself. I didn't think it would be complicated enough to produce a
> memory problem.. here it is:
>
> -- Normalises common address words (i.e. 'Ground' maps to 'grd')
> CREATE OR REPLACE FUNCTION normalise(s text) RETURNS text AS $$
> ADDR_FIELD_DELIM = ' '
>
> # Returns distinct list without null or empty elements
> def distinct_str(list):
> seen = set()
> return [x for x in list if x not in seen and not seen.add(x) and
> x!=None and len(x)>0]
>
> # normalise common words in given address string
> def normalise(match_data):
> if match_data==None: return ''
> import re
> # Tokenise
> toks = distinct_str(re.split(r'\s', match_data.lower()))
> out = ''
> for tok in toks:
> ## full word replace
> if tok == 'house' : out += 'hse'+ADDR_FIELD_DELIM
> elif tok == 'ground' : out += 'grd'+ADDR_FIELD_DELIM
> elif tok == 'gnd' : out += 'grd'+ADDR_FIELD_DELIM
> elif tok == 'front' : out += 'fnt'+ADDR_FIELD_DELIM
> elif tok == 'floor' : out += 'flr'+ADDR_FIELD_DELIM
> elif tok == 'floors' : out += 'flr'+ADDR_FIELD_DELIM
> elif tok == 'flrs' : out += 'flr'+ADDR_FIELD_DELIM
> elif tok == 'fl' : out += 'flr'+ADDR_FIELD_DELIM
> elif tok == 'basement' : out += 'bst'+ADDR_FIELD_DELIM
> elif tok == 'subbasement' : out += 'sbst'+ADDR_FIELD_DELIM
> elif tok == 'bsmt' : out += 'bst'+ADDR_FIELD_DELIM
> elif tok == 'lbst' : out += 'lower bst'+ADDR_FIELD_DELIM
> elif tok == 'street' : out += 'st'+ADDR_FIELD_DELIM
> elif tok == 'road' : out += 'rd'+ADDR_FIELD_DELIM
> elif tok == 'lane' : out += 'ln'+ADDR_FIELD_DELIM
> elif tok == 'rooms' : out += 'rm'+ADDR_FIELD_DELIM
> elif tok == 'room' : out += 'rm'+ADDR_FIELD_DELIM
> elif tok == 'no' : pass
> elif tok == 'number' : pass
> elif tok == 'and' : out += '&'+ADDR_FIELD_DELIM
> elif tok == 'rear' : out += 'rr'+ADDR_FIELD_DELIM
> elif tok == 'part' : out += 'pt'+ADDR_FIELD_DELIM
> elif tok == 'south' : out += 's'+ADDR_FIELD_DELIM
> elif tok == 'sth' : out += 's'+ADDR_FIELD_DELIM
> elif tok == 'north' : out += 'n'+ADDR_FIELD_DELIM
> elif tok == 'nth' : out += 'n'+ADDR_FIELD_DELIM
> elif tok == 'west' : out += 'w'+ADDR_FIELD_DELIM
> elif tok == 'wst' : out += 'w'+ADDR_FIELD_DELIM
> elif tok == 'east' : out += 'e'+ADDR_FIELD_DELIM
> elif tok == 'est' : out += 'e'+ADDR_FIELD_DELIM
> elif tok == 'first' : out += '1st'+ADDR_FIELD_DELIM
> elif tok == 'second' : out += '2nd'+ADDR_FIELD_DELIM
> elif tok == 'third' : out += '3rd'+ADDR_FIELD_DELIM
> elif tok == 'fourth' : out += '4th'+ADDR_FIELD_DELIM
> elif tok == 'fifth' : out += '5th'+ADDR_FIELD_DELIM
> elif tok == 'sixth' : out += '6th'+ADDR_FIELD_DELIM
> elif tok == 'seventh' : out += '7th'+ADDR_FIELD_DELIM
> elif tok == 'eighth' : out += '8th'+ADDR_FIELD_DELIM
> elif tok == 'ninth' : out += '9th'+ADDR_FIELD_DELIM
> elif tok == 'tenth' : out += '10th'+ADDR_FIELD_DELIM
> elif tok == 'eleventh' : out += '11th'+ADDR_FIELD_DELIM
> elif tok == 'twelfth' : out += '12th'+ADDR_FIELD_DELIM
> elif tok == 'thirteenth' : out += '13th'+ADDR_FIELD_DELIM
> elif tok == 'fourteenth' : out += '14th'+ADDR_FIELD_DELIM
> elif tok == 'fifteenth' : out += '15th'+ADDR_FIELD_DELIM
> elif tok == 'sixteenth' : out += '16th'+ADDR_FIELD_DELIM
> elif tok == 'seventeenth' : out += '17th'+ADDR_FIELD_DELIM
> elif tok == 'eighteenth' : out += '18th'+ADDR_FIELD_DELIM
> elif tok == 'nineteenth' : out += '19th'+ADDR_FIELD_DELIM
> elif tok == 'twentieth' : out += '20th'+ADDR_FIELD_DELIM
> # numbers 0 - 20
> elif tok == 'one' : out += '1'+ADDR_FIELD_DELIM
> elif tok == 'two' : out += '2'+ADDR_FIELD_DELIM
> elif tok == 'three' : out += '3'+ADDR_FIELD_DELIM
> elif tok == 'four' : out += '4'+ADDR_FIELD_DELIM
> elif tok == 'five' : out += '5'+ADDR_FIELD_DELIM
> elif tok == 'six' : out += '6'+ADDR_FIELD_DELIM
> elif tok == 'seven' : out += '7'+ADDR_FIELD_DELIM
> elif tok == 'eight' : out += '8'+ADDR_FIELD_DELIM
> elif tok == 'nine' : out += '9'+ADDR_FIELD_DELIM
> elif tok == 'ten' : out += '10'+ADDR_FIELD_DELIM
> elif tok == 'eleven' : out += '11'+ADDR_FIELD_DELIM
>

Re: [GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Alban Hertroys

On 28 May 2010, at 20:39, Tom Wilcox wrote:

> out = ''
> for tok in toks:
> ## full word replace
> if tok == 'house' : out += 'hse'+ADDR_FIELD_DELIM
> elif tok == 'ground' : out += 'grd'+ADDR_FIELD_DELIM
> elif tok == 'gnd' : out += 'grd'+ADDR_FIELD_DELIM
> elif tok == 'front' : out += 'fnt'+ADDR_FIELD_DELIM
> elif tok == 'floor' : out += 'flr'+ADDR_FIELD_DELIM
> elif tok == 'floors' : out += 'flr'+ADDR_FIELD_DELIM

Not that it would solve your problems, but you can write the above much more 
elegantly using a dictionary:

# normalize the token
try:
out += {
'house' : 'hse',
'ground': 'grd',
'gnd'   : 'grd',
'front' : 'fnt',
'floor' : 'flr',
...
}[tok]
except KeyError:
out += tok

# add a field delimiter if the token isn't among the exceptions for those
if tok not in ('borough', 'city', 'of', 'the', 'at', 'incl', 'inc'):
out += ADDR_FIELD_DELIM

You should probably define those lists outside the for-loop though, I'm not 
sure the Python interpreter is smart enough to declare those lists only once 
otherwise. The concept remains though.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c00531510211149731783!



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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread Adrian Klaver

On Friday 28 May 2010 1:34:24 pm erobles wrote:
> On 05/28/2010 03:18 PM, alvherre wrote:
> > Excerpts from erobles's message of vie may 28 16:08:23 -0400 2010:
> >> it did'nt work  :-(  , because the script is  for  pg 8.2 and i have pg
> >> 7.2 and the table  pg_cast doesn't exist.  whatever is a good idea but i
> >> need  to know the   table or tables  on postgres 7.2.
> >
> > Of course it didn't work if you tried to run it in 7.2; you're supposed
> > to run it in the 8.3 server, not the old one.  The point is to make 8.3
> > sort-of compatible with the old behavior.
>
> did you see the script?
>
> didn't  it work because the  first command is a psql  pointing a pg
> server 8.2
> the second command is a psql pointing a pg server 8.3
> then  the command  are executed   and  get the differences
> after that  made the  properly casting fucntions
>
>
> this result ok , because the postgres 8.x.x have the same database
> catalog in this case have the table pg_cast
> so the query works  OK.
>
>
>
> the problem in the postgres 7.2   doesn't exists  the table pg_cast so i
> can't get the information need it to  get the differences
>
> so, i need  modify the first command  run it and get the  info in the
> same format to get it.

I believe you are looking at the wrong file; the shell script. Try instead the 
other link http://wiki.postgresql.org/wiki/Image:Pg83-implicit-casts.sql It 
contains the functions you need.

-- 
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread Scott Marlowe

On Fri, May 28, 2010 at 2:34 PM, erobles  wrote:
>
>
> On 05/28/2010 03:18 PM, alvherre wrote:
>>
>> Excerpts from erobles's message of vie may 28 16:08:23 -0400 2010:
>>
>>
>>>
>>> it did'nt work  :-(  , because the script is  for  pg 8.2 and i have pg
>>> 7.2 and the table  pg_cast doesn't exist.  whatever is a good idea but i
>>> need  to know the   table or tables  on postgres 7.2.
>>>
>>
>> Of course it didn't work if you tried to run it in 7.2; you're supposed
>> to run it in the 8.3 server, not the old one.  The point is to make 8.3
>> sort-of compatible with the old behavior.
>>
>>
>
> did you see the script?
>
> didn't  it work because the  first command is a psql  pointing a pg server
> 8.2
> the second command is a psql pointing a pg server 8.3
> then  the command  are executed   and  get the differences
> after that  made the  properly casting fucntions

Couldn't you just build an 8.2 server for this one time use?

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread Tom Lane

erobles  writes:
> how can i get the same info from postgres 7.2 , and  be  able  to  get  
> the differences between  8.3 and 7.2

Before the pg_cast catalog there simply wasn't any explicit
representation of available casts.  I think what you need to look for is
single-argument functions that have the same name as their result type,
but it was a long time ago.

regards, tom lane

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread erobles




On 05/28/2010 03:34 PM, erobles wrote:



On 05/28/2010 03:18 PM, alvherre wrote:

Excerpts from erobles's message of vie may 28 16:08:23 -0400 2010:


it did'nt work  :-(  , because the script is  for  pg 8.2 and i have pg
7.2 and the table  pg_cast doesn't exist.  whatever is a good idea 
but i

need  to know the   table or tables  on postgres 7.2.

Of course it didn't work if you tried to run it in 7.2; you're supposed
to run it in the 8.3 server, not the old one.  The point is to make 8.3
sort-of compatible with the old behavior.


did you see the script?

didn't  it work because the  first command is a psql  pointing a pg 
server 8.2

the second command is a psql pointing a pg server 8.3
then  the command  are executed   and  get the differences
after that  made the  properly casting fucntions


this result ok , because the postgres 8.x.x have the same database 
catalog in this case have the table pg_cast

so the query works  OK.



the problem in the postgres 7.2   doesn't exists  the table pg_cast so 
i can't get the information need it to  get the differences


so, i need  modify the first command  run it and get the  info in the 
same format to get it.












i mean:

 if postgres  8.3 have a table pg_cast  and their fields  used on the 
script are:

castfunc,
castsource,
casttarget,
castfunc::regprocedure,
castcontext


how can i get the same info from postgres 7.2 , and  be  able  to  get  
the differences between  8.3 and 7.2



Regards.



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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread erobles




On 05/28/2010 03:18 PM, alvherre wrote:

Excerpts from erobles's message of vie may 28 16:08:23 -0400 2010:

   

it did'nt work  :-(  , because the script is  for  pg 8.2 and i have pg
7.2 and the table  pg_cast doesn't exist.  whatever is a good idea but i
need  to know the   table or tables  on postgres 7.2.
 

Of course it didn't work if you tried to run it in 7.2; you're supposed
to run it in the 8.3 server, not the old one.  The point is to make 8.3
sort-of compatible with the old behavior.

   

did you see the script?

didn't  it work because the  first command is a psql  pointing a pg 
server 8.2

the second command is a psql pointing a pg server 8.3
then  the command  are executed   and  get the differences
after that  made the  properly casting fucntions


this result ok , because the postgres 8.x.x have the same database 
catalog in this case have the table pg_cast

so the query works  OK.



the problem in the postgres 7.2   doesn't exists  the table pg_cast so i 
can't get the information need it to  get the differences


so, i need  modify the first command  run it and get the  info in the 
same format to get it.










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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread alvherre

Excerpts from erobles's message of vie may 28 16:08:23 -0400 2010:

> it did'nt work  :-(  , because the script is  for  pg 8.2 and i have pg 
> 7.2 and the table  pg_cast doesn't exist.  whatever is a good idea but i 
> need  to know the   table or tables  on postgres 7.2.

Of course it didn't work if you tried to run it in 7.2; you're supposed
to run it in the 8.3 server, not the old one.  The point is to make 8.3
sort-of compatible with the old behavior.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] Statement Pooling

2010-05-28 Thread Janning

On Thursday 27 May 2010 16:56:28 you wrote:
> > Additionally c3p0 can cache java instances of
> > "java.sql.PreparedStatement" which means it is caching the java object.
> > So when using
> > c3p0.maxStatementsPerConnection  =   100 it caches at most 100 different
> > objects. It saves time on creating objects, but this has nothing to do
> > with the postgresql database and its prepared statements.
> >
> > Right?
>
> That's the idea.

Really? Today I think c3p0 statement pooling it is not only about saving time 
in object creation, but to let postgresql reuse an already prwpeared 
statement.

> > As we use about 100 different statements I would set
> >   c3p0.maxStatementsPerConnection  =   100
> >
> > Is this reasonable? Is there a real benefit activating it?
>
> Answering that question for your situation really requires benchmarking
> with and without statement caching turned on. Your best bet is probably to
> set it to a value that seems decent, and revisit it if you find a
> performance bottleneck you need to resolve which looks like it's related to
> statement caching.

Ok thanks for your detailed answer. I did some benchmarking and it seems to be 
faster with c3p0 statement cache. 

> > I remember postgresql 8.4 is replanning prepared statements when
> > statistics change occur, but I didn't find it in the release notes. It is
> > just saying "Invalidate cached plans when referenced schemas, functions,
> > operators, or operator classes are modified". Does PG replans prepared
> > statements from time to time if underlying data statistics change?
>
> I don't think so, though I may be wrong. The change you refer to replans
> such things when the actual objects change, such as when you remove a
> column or something that would make the plan fail to execute.

Ok, so i should be aware of this. if the execution plan postgresql is caching 
gets old, i can run into trouble. 

So i should close my connections in the pool from time to time to force 
replanning of my prepared statements.

i have asked this question on stackoverflow too, because i thought it was to 
jdbc/hibernate specific for this list after sending and not getting an answer. 
So for further reference i post a link to this question, where you can find an 
excellent answer, too:

http://stackoverflow.com/questions/2920740/should-i-activate-c3p0-statement-
pooling/2921340#2921340

kind regards
Janning

http://www.kicktipp.de/

>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com



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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread erobles




On 05/28/2010 02:04 PM, erobles wrote:



On 05/28/2010 01:51 PM, Adrian Klaver wrote:

On 05/28/2010 11:45 AM, erobles wrote:



On 05/28/2010 01:26 PM, Adrian Klaver wrote:

On 05/28/2010 11:20 AM, erobles wrote:

hi!
only for ask, there is a way to deactivate type validation, so i 
can do


select rtrim(number_field) from table ; with no error and the 
message:

"You might need to explicit type casts"

this is postgres 8.3.1.



What are you trying to do? I am trying to think what rtrim does on a
number.




you are right, i'm trying to make a rtrim to a number.
the reason of my question is we have a lot of apps with similar 
querys ,

when we have postgres7.2 this kind of querys just simply execute very
well, so.. when change to postgres 8.3 this querys fail beacuse they
needit an explicit cast.

one solution is change the querys of apps and recompile, but time is
short and the deadline is near.

other solution is create a function to each explicit cast, but we dont
have the time to research if there are more mismatch querys.

so the easy way is deactivate the validation type ...





so another


For short term solution see here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html 






thanks, downloading and testing.



it did'nt work  :-(  , because the script is  for  pg 8.2 and i have pg 
7.2 and the table  pg_cast doesn't exist.  whatever is a good idea but i 
need  to know the   table or tables  on postgres 7.2.  such as  pg_cast 
on postgres 8.3, to modify the script.



How can i see the 'private' tables of postgres  7.2, i tried with \d  on 
template1 , if a made a select on pg_types i have a result, but i don't 
know  which  is the name of the others tables  such a


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

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

2010-05-28 Thread erobles




On 05/28/2010 02:16 PM, Tom Lane wrote:

Gary Fu  writes:
   

Nilesh Govindarajan wrote:
 

Could you please explain a little more what you want to do actually ?
   
   

I have the following problem to run 'configure' when building pgpool
2.3.3 with libpq from normal built of postgreSQL 8.4.4.
 
   

configure:24789: result: no
configure:24799: error: libpq is not installed or libpq is old
 

This is a bug in pgpool's configure script, which you should report to
them not us.  It's not our responsibility to supply libdl.

regards, tom lane

   



run pgpool's configure with the options:

--with-psql=/dir/where/you/have/pg844
--with-psql-includedir=/dir/dir/where/you/have/pg844/include
--with-psql-libdir=/dir/dir/where/you/have/pg844/lib

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

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

2010-05-28 Thread Tom Lane

Gary Fu  writes:
> Nilesh Govindarajan wrote:
>> Could you please explain a little more what you want to do actually ?

> I have the following problem to run 'configure' when building pgpool 
> 2.3.3 with libpq from normal built of postgreSQL 8.4.4.

> configure:24789: result: no
> configure:24799: error: libpq is not installed or libpq is old

This is a bug in pgpool's configure script, which you should report to
them not us.  It's not our responsibility to supply libdl.

regards, tom lane

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread erobles




On 05/28/2010 01:51 PM, Adrian Klaver wrote:

On 05/28/2010 11:45 AM, erobles wrote:



On 05/28/2010 01:26 PM, Adrian Klaver wrote:

On 05/28/2010 11:20 AM, erobles wrote:

hi!
only for ask, there is a way to deactivate type validation, so i 
can do


select rtrim(number_field) from table ; with no error and the message:
"You might need to explicit type casts"

this is postgres 8.3.1.



What are you trying to do? I am trying to think what rtrim does on a
number.




you are right, i'm trying to make a rtrim to a number.
the reason of my question is we have a lot of apps with similar querys ,
when we have postgres7.2 this kind of querys just simply execute very
well, so.. when change to postgres 8.3 this querys fail beacuse they
needit an explicit cast.

one solution is change the querys of apps and recompile, but time is
short and the deadline is near.

other solution is create a function to each explicit cast, but we dont
have the time to research if there are more mismatch querys.

so the easy way is deactivate the validation type ...





so another


For short term solution see here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html 






thanks, downloading and testing.

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread Adrian Klaver


On 05/28/2010 11:45 AM, erobles wrote:



On 05/28/2010 01:26 PM, Adrian Klaver wrote:

On 05/28/2010 11:20 AM, erobles wrote:

hi!
only for ask, there is a way to deactivate type validation, so i can do

select rtrim(number_field) from table ; with no error and the message:
"You might need to explicit type casts"

this is postgres 8.3.1.



What are you trying to do? I am trying to think what rtrim does on a
number.




you are right, i'm trying to make a rtrim to a number.
the reason of my question is we have a lot of apps with similar querys ,
when we have postgres7.2 this kind of querys just simply execute very
well, so.. when change to postgres 8.3 this querys fail beacuse they
needit an explicit cast.

one solution is change the querys of apps and recompile, but time is
short and the deadline is near.

other solution is create a function to each explicit cast, but we dont
have the time to research if there are more mismatch querys.

so the easy way is deactivate the validation type ...





so another


For short term solution see here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html


--
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread erobles




On 05/28/2010 01:26 PM, Adrian Klaver wrote:

On 05/28/2010 11:20 AM, erobles wrote:

hi!
only for ask, there is a way to deactivate type validation, so i can do

select rtrim(number_field) from table ; with no error and the message:
"You might need to explicit type casts"

this is postgres 8.3.1.



What are you trying to do? I am trying to think what rtrim does on a 
number.





you are right, i'm trying to make a rtrim to a number.
 the reason of my question is   we have a lot of apps   with similar 
querys , when  we have postgres7.2 this  kind of querys just simply  
execute very well, so.. when change to  postgres 8.3   this querys fail 
beacuse they needit  an explicit cast.


one solution is change the querys of apps and recompile, but time  is  
short and the deadline is near.


other solution is create a function to each  explicit cast, but   we 
dont have  the time to   research if there  are more   mismatch querys.


so the easy way  is deactivate the validation type ...





so  another

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

Re: [GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Tom Wilcox

Oops. Sorry about that.

I am having this problem with multiple queries however I am confident that a
fair number may involve the custom plpython "normalise" function which I
have made myself. I didn't think it would be complicated enough to produce a
memory problem.. here it is:

-- Normalises common address words (i.e. 'Ground' maps to 'grd')
CREATE OR REPLACE FUNCTION normalise(s text) RETURNS text AS $$
ADDR_FIELD_DELIM = ' '

# Returns distinct list without null or empty elements
def distinct_str(list):
seen = set()
return [x for x in list if x not in seen and not seen.add(x) and x!=None
and len(x)>0]

# normalise common words in given address string
def normalise(match_data):
if match_data==None: return ''
import re
# Tokenise
toks = distinct_str(re.split(r'\s', match_data.lower()))
out = ''
for tok in toks:
## full word replace
if tok == 'house' : out += 'hse'+ADDR_FIELD_DELIM
elif tok == 'ground' : out += 'grd'+ADDR_FIELD_DELIM
elif tok == 'gnd' : out += 'grd'+ADDR_FIELD_DELIM
elif tok == 'front' : out += 'fnt'+ADDR_FIELD_DELIM
elif tok == 'floor' : out += 'flr'+ADDR_FIELD_DELIM
elif tok == 'floors' : out += 'flr'+ADDR_FIELD_DELIM
elif tok == 'flrs' : out += 'flr'+ADDR_FIELD_DELIM
elif tok == 'fl' : out += 'flr'+ADDR_FIELD_DELIM
elif tok == 'basement' : out += 'bst'+ADDR_FIELD_DELIM
elif tok == 'subbasement' : out += 'sbst'+ADDR_FIELD_DELIM
elif tok == 'bsmt' : out += 'bst'+ADDR_FIELD_DELIM
elif tok == 'lbst' : out += 'lower bst'+ADDR_FIELD_DELIM
elif tok == 'street' : out += 'st'+ADDR_FIELD_DELIM
elif tok == 'road' : out += 'rd'+ADDR_FIELD_DELIM
elif tok == 'lane' : out += 'ln'+ADDR_FIELD_DELIM
elif tok == 'rooms' : out += 'rm'+ADDR_FIELD_DELIM
elif tok == 'room' : out += 'rm'+ADDR_FIELD_DELIM
elif tok == 'no' : pass
elif tok == 'number' : pass
elif tok == 'and' : out += '&'+ADDR_FIELD_DELIM
elif tok == 'rear' : out += 'rr'+ADDR_FIELD_DELIM
elif tok == 'part' : out += 'pt'+ADDR_FIELD_DELIM
elif tok == 'south' : out += 's'+ADDR_FIELD_DELIM
elif tok == 'sth' : out += 's'+ADDR_FIELD_DELIM
elif tok == 'north' : out += 'n'+ADDR_FIELD_DELIM
elif tok == 'nth' : out += 'n'+ADDR_FIELD_DELIM
elif tok == 'west' : out += 'w'+ADDR_FIELD_DELIM
elif tok == 'wst' : out += 'w'+ADDR_FIELD_DELIM
elif tok == 'east' : out += 'e'+ADDR_FIELD_DELIM
elif tok == 'est' : out += 'e'+ADDR_FIELD_DELIM
elif tok == 'first' : out += '1st'+ADDR_FIELD_DELIM
elif tok == 'second' : out += '2nd'+ADDR_FIELD_DELIM
elif tok == 'third' : out += '3rd'+ADDR_FIELD_DELIM
elif tok == 'fourth' : out += '4th'+ADDR_FIELD_DELIM
elif tok == 'fifth' : out += '5th'+ADDR_FIELD_DELIM
elif tok == 'sixth' : out += '6th'+ADDR_FIELD_DELIM
elif tok == 'seventh' : out += '7th'+ADDR_FIELD_DELIM
elif tok == 'eighth' : out += '8th'+ADDR_FIELD_DELIM
elif tok == 'ninth' : out += '9th'+ADDR_FIELD_DELIM
elif tok == 'tenth' : out += '10th'+ADDR_FIELD_DELIM
elif tok == 'eleventh' : out += '11th'+ADDR_FIELD_DELIM
elif tok == 'twelfth' : out += '12th'+ADDR_FIELD_DELIM
elif tok == 'thirteenth' : out += '13th'+ADDR_FIELD_DELIM
elif tok == 'fourteenth' : out += '14th'+ADDR_FIELD_DELIM
elif tok == 'fifteenth' : out += '15th'+ADDR_FIELD_DELIM
elif tok == 'sixteenth' : out += '16th'+ADDR_FIELD_DELIM
elif tok == 'seventeenth' : out += '17th'+ADDR_FIELD_DELIM
elif tok == 'eighteenth' : out += '18th'+ADDR_FIELD_DELIM
elif tok == 'nineteenth' : out += '19th'+ADDR_FIELD_DELIM
elif tok == 'twentieth' : out += '20th'+ADDR_FIELD_DELIM
# numbers 0 - 20
elif tok == 'one' : out += '1'+ADDR_FIELD_DELIM
elif tok == 'two' : out += '2'+ADDR_FIELD_DELIM
elif tok == 'three' : out += '3'+ADDR_FIELD_DELIM
elif tok == 'four' : out += '4'+ADDR_FIELD_DELIM
elif tok == 'five' : out += '5'+ADDR_FIELD_DELIM
elif tok == 'six' : out += '6'+ADDR_FIELD_DELIM
elif tok == 'seven' : out += '7'+ADDR_FIELD_DELIM
elif tok == 'eight' : out += '8'+ADDR_FIELD_DELIM
elif tok == 'nine' : out += '9'+ADDR_FIELD_DELIM
elif tok == 'ten' : out += '10'+ADDR_FIELD_DELIM
elif tok == 'eleven' : out += '11'+ADDR_FIELD_DELIM
elif tok == 'twelve' : out += '12'+ADDR_FIELD_DELIM
elif tok == 'thirteen' : out += '13'+ADDR_FIELD_DELIM
elif tok == 'fourteen' : out += '14'+ADDR_FIELD_DELIM
elif tok == 'fifteen' : out += '15'+ADDR_FIELD_DELIM
elif tok == 'sixteen' : out += '16'+ADDR_FIELD_DELIM
elif tok == 'seventeen' : out += '17'+ADDR_FIELD_DELIM
elif tok == 'eighteen' : out += '18'+ADDR_FIELD_DELIM
elif tok == 'nineteen' : out += '19'+ADDR_FI

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

2010-05-28 Thread Gary Fu


Nilesh Govindarajan wrote:

On Tue, May 25, 2010 at 7:48 PM, Gary Fu  wrote:
  

Hi,

On my mandriva linux, I don't have problem to build pgpool 2.3.3 with
postgresql 8.4.2.  But when I upgraded to 8.4.4, I cannot build pgpool again
due to the libdl.so is not required in libpq.so (from the ldd command).  Do
you know how to build the 8.4.4 so that libdl.so is required in libpq.so ?

10:16am 32 g...@nppdist:~/postgres/postgresql-8.4.4> locate libdl
/lib/libdl-2.6.1.so
/lib/libdl.so.2

10:16am 33 g...@nppdist:~/postgres/postgresql-8.4.4> ldd
./src/interfaces/libpq/libpq.so
   linux-gate.so.1 =>  (0xe000)
   libcrypt.so.1 => /lib/libcrypt.so.1 (0x40026000)
   libc.so.6 => /lib/i686/libc.so.6 (0x40054000)
   /lib/ld-linux.so.2 (0x8000)


Thanks,
Gary

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





Could you please explain a little more what you want to do actually ?

  
I have the following problem to run 'configure' when building pgpool 
2.3.3 with libpq from normal built of postgreSQL 8.4.4.


configure:24789: result: no
configure:24799: error: libpq is not installed or libpq is old


However, when I rebuilt the 8.4.4 with 'LDFLAGS='-ldl' during configure, 
I don't have problem to build pgpool 2.3.3.


Thanks,
Gary




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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread Bill Moran

In response to Adrian Klaver :

> On 05/28/2010 11:20 AM, erobles wrote:
> > hi!
> > only for ask, there is a way to deactivate type validation, so i can do
> >
> > select rtrim(number_field) from table ; with no error and the message:
> > "You might need to explicit type casts"
> >
> > this is postgres 8.3.1.
> >
> 
> What are you trying to do? I am trying to think what rtrim does on a number.

It doesn't work on numbers, which is why he gets the "need type casts" error.

Direct answer to the question: no, there is no way to disable type validation,
it's a core feature and it wouldn't make sense.

Solution to your problem:
select rtrim(CAST(number_field AS TEXT)) from table;

Although I can't imagine why you'd be trying to trim whitespace from a
numeric field ... it will never have whitespace.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread Adrian Klaver


On 05/28/2010 11:20 AM, erobles wrote:

hi!
only for ask, there is a way to deactivate type validation, so i can do

select rtrim(number_field) from table ; with no error and the message:
"You might need to explicit type casts"

this is postgres 8.3.1.



What are you trying to do? I am trying to think what rtrim does on a number.

--
Adrian Klaver
adrian.kla...@gmail.com

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

[GENERAL] there is a way to deactivate type validation on 8.3.1????

2010-05-28 Thread erobles


hi!
only for ask,  there is a way to deactivate type validation, so  i can do

select rtrim(number_field)  from table ;  with no error  and the  
message: "You might need to explicit type casts"


this is postgres 8.3.1.

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

Re: [GENERAL] cannot use column references in default expression?

2010-05-28 Thread Nilesh Govindarajan

On Fri, May 28, 2010 at 10:50 PM, Jignesh Shah
 wrote:
> Could anyone please help me to get rid of following error? I want to set the
> 'ishuman' column based on the value of 'ID' column but it is not allowing me
> to do so. Any alternatives?
>
> techdb=> CREATE TABLE Users (
>    ID INTEGER,
>    isHuman    BOOLEAN NOT NULL
>   DEFAULT (ID IS NULL)
>   CHECK (isHuman = ID IS NULL),
>    Name  VARCHAR NOT NULL);
> ERROR:  cannot use column references in default expression
> techdb=>
>
> Thanks,
> Jignesh
>


I don't know much about the error, but an alternative solution I see
is using Triggers. WIth triggers you can manipulate the value during
INSERT or UPDATE.

-- 
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com

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

Re: [GENERAL] cannot use column references in default expression?

2010-05-28 Thread Adrian Klaver


On 05/28/2010 10:41 AM, Jignesh Shah wrote:

Trigger should be the last solution. This used to be working but I think
with latest postgresql upgrade, this stopped working. Might be someone
around here knows whats going on here.

Thanks,
Jignesh

On Fri, May 28, 2010 at 11:00 PM, Craig Ringer
wrote:


On 29/05/2010 1:20 AM, Jignesh Shah wrote:


Could anyone please help me to get rid of following error? I want to set
the 'ishuman' column based on the value of 'ID' column but it is not
allowing me to do so. Any alternatives?



Use a BEFORE trigger to set it.

--
Craig Ringer





What previous version? I just tried 8.3.5 and it did not work there either.

--
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [GENERAL] cannot use column references in default expression?

2010-05-28 Thread Christophe Pettus



On May 28, 2010, at 10:41 AM, Jignesh Shah wrote:

Trigger should be the last solution. This used to be working but I  
think with latest postgresql upgrade, this stopped working. Might be  
someone around here knows whats going on here.


I'm afraid not.  It's never been allowed to have a variable in the  
expression for a DEFAULT value.  To quote the manual:



DEFAULT default_expr
The DEFAULT clause assigns a default data value for the column whose  
column definition it appears within. The value is any variable-free  
expression (subqueries and cross-references to other columns in the  
current table are not allowed). The data type of the default  
expression must match the data type of the column.



A trigger is the appropriate solution in this case.

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [GENERAL] cannot use column references in default expression?

2010-05-28 Thread Jignesh Shah

Trigger should be the last solution. This used to be working but I think
with latest postgresql upgrade, this stopped working. Might be someone
around here knows whats going on here.

Thanks,
Jignesh

On Fri, May 28, 2010 at 11:00 PM, Craig Ringer
wrote:

> On 29/05/2010 1:20 AM, Jignesh Shah wrote:
>
>> Could anyone please help me to get rid of following error? I want to set
>> the 'ishuman' column based on the value of 'ID' column but it is not
>> allowing me to do so. Any alternatives?
>>
>
> Use a BEFORE trigger to set it.
>
> --
> Craig Ringer
>

Re: [GENERAL] cannot use column references in default expression?

2010-05-28 Thread Craig Ringer


On 29/05/2010 1:20 AM, Jignesh Shah wrote:

Could anyone please help me to get rid of following error? I want to set
the 'ishuman' column based on the value of 'ID' column but it is not
allowing me to do so. Any alternatives?


Use a BEFORE trigger to set it.

--
Craig Ringer

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

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Craig Ringer


On 28/05/2010 9:17 PM, Joachim Worringen wrote:


In the "import data" case, however, I really need concurrent processing
on the CPU in the first place, so you may be right on this one. I'll
check it.


If you run into GIL problems, you can always work around it by spawning 
multiple processes and communicating between them via pipes, signals, 
shm, etc. That way you get one GIL per Python instance.


BTW, if you need decent CPU performance with Python, make sure you're 
using the Pysco specializing compiler module for Python. Activating it 
requires literally four lines at the start of your main module and the 
speed improvement is mind blowing.


--
Craig Ringer

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

[GENERAL] cannot use column references in default expression?

2010-05-28 Thread Jignesh Shah

Could anyone please help me to get rid of following error? I want to set the
'ishuman' column based on the value of 'ID' column but it is not allowing me
to do so. Any alternatives?

techdb=> CREATE TABLE Users (
   ID INTEGER,
   isHumanBOOLEAN NOT NULL
  DEFAULT (ID IS NULL)
  CHECK (isHuman = ID IS NULL),
   Name  VARCHAR NOT NULL);
ERROR:  cannot use column references in default expression
techdb=>

Thanks,
Jignesh

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

2010-05-28 Thread Tatsuo Ishii

> On Fri, May 28, 2010 at 10:13 PM, Gary Fu  wrote:
> > Nilesh Govindarajan wrote:
> >>
> >> On Tue, May 25, 2010 at 7:48 PM, Gary Fu  wrote:
> >>
> >>>
> >>> Hi,
> >>>
> >>> On my mandriva linux, I don't have problem to build pgpool 2.3.3 with
> >>> postgresql 8.4.2.  But when I upgraded to 8.4.4, I cannot build pgpool
> >>> again
> >>> due to the libdl.so is not required in libpq.so (from the ldd command).
> >>>  Do
> >>> you know how to build the 8.4.4 so that libdl.so is required in libpq.so
> >>> ?
> >>>
> >>> 10:16am 32 g...@nppdist:~/postgres/postgresql-8.4.4> locate libdl
> >>> /lib/libdl-2.6.1.so
> >>> /lib/libdl.so.2
> >>>
> >>> 10:16am 33 g...@nppdist:~/postgres/postgresql-8.4.4> ldd
> >>> ./src/interfaces/libpq/libpq.so
> >>>       linux-gate.so.1 =>  (0xe000)
> >>>       libcrypt.so.1 => /lib/libcrypt.so.1 (0x40026000)
> >>>       libc.so.6 => /lib/i686/libc.so.6 (0x40054000)
> >>>       /lib/ld-linux.so.2 (0x8000)
> >>>
> >>>
> >>> Thanks,
> >>> Gary
> >>>
> >>> --
> >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >>> To make changes to your subscription:
> >>> http://www.postgresql.org/mailpref/pgsql-general
> >>>
> >>>
> >>
> >>
> >> Could you please explain a little more what you want to do actually ?
> >>
> >>
> >
> > I have the following problem to run 'configure' when building pgpool 2.3.3
> > with libpq from normal built of postgreSQL 8.4.4.
> >
> > configure:24789: result: no
> > configure:24799: error: libpq is not installed or libpq is old
> >
> >
> > However, when I rebuilt the 8.4.4 with 'LDFLAGS='-ldl' during configure, I
> > don't have problem to build pgpool 2.3.3.

He didn't have problem with PostgreSQL 8.4.2+pgpool-II 2.3.3 and he's
wondering why he now starts to have a problem with 8.4.4, I think.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

2010-05-28 Thread Nilesh Govindarajan

On Fri, May 28, 2010 at 10:13 PM, Gary Fu  wrote:
> Nilesh Govindarajan wrote:
>>
>> On Tue, May 25, 2010 at 7:48 PM, Gary Fu  wrote:
>>
>>>
>>> Hi,
>>>
>>> On my mandriva linux, I don't have problem to build pgpool 2.3.3 with
>>> postgresql 8.4.2.  But when I upgraded to 8.4.4, I cannot build pgpool
>>> again
>>> due to the libdl.so is not required in libpq.so (from the ldd command).
>>>  Do
>>> you know how to build the 8.4.4 so that libdl.so is required in libpq.so
>>> ?
>>>
>>> 10:16am 32 g...@nppdist:~/postgres/postgresql-8.4.4> locate libdl
>>> /lib/libdl-2.6.1.so
>>> /lib/libdl.so.2
>>>
>>> 10:16am 33 g...@nppdist:~/postgres/postgresql-8.4.4> ldd
>>> ./src/interfaces/libpq/libpq.so
>>>       linux-gate.so.1 =>  (0xe000)
>>>       libcrypt.so.1 => /lib/libcrypt.so.1 (0x40026000)
>>>       libc.so.6 => /lib/i686/libc.so.6 (0x40054000)
>>>       /lib/ld-linux.so.2 (0x8000)
>>>
>>>
>>> Thanks,
>>> Gary
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>>
>> Could you please explain a little more what you want to do actually ?
>>
>>
>
> I have the following problem to run 'configure' when building pgpool 2.3.3
> with libpq from normal built of postgreSQL 8.4.4.
>
> configure:24789: result: no
> configure:24799: error: libpq is not installed or libpq is old
>
>
> However, when I rebuilt the 8.4.4 with 'LDFLAGS='-ldl' during configure, I
> don't have problem to build pgpool 2.3.3.
>
> Thanks,
> Gary
>
>
>
>


So your problem is sovled right ? Why worry then ?

-- 
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com

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

Re: [GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Bill Moran

In response to Tom Wilcox :

> In addition, I have discovered that the update query that runs on each row
> of a 27million row table and fails with Out of memory error will work when
> limited to 1million rows in an extremely shorter period of time:
> 
> EXPLAIN ANALYZE
> UPDATE nlpg.match_data SET org = normalise(org) WHERE match_data_id
> < 100;
> 
> "Index Scan using match_data_pkey1 on match_data  (cost=0.00..3285969.97
> rows=1147685 width=206) (actual time=0.280..18018.601 rows=99 loops=1)"
> "  Index Cond: (match_data_id < 100)"
> "Total runtime: 182732.207 ms"
> 
> 
> Whereas this fails with Out of Memory:
> 
> UPDATE nlpg.match_data SET org = normalise(org) WHERE match_data_id;

You're not liable to get shit for answers if you omit the mailing list from
the conversation, especially since I know almost nothing about tuning
PostgreSQL installed on Windows.

Are there multiple queries having this problem?  The original query didn't
have normalise() in it, and I would be highly suspicious that a custom
function may have a memory leak or other memory-intensive side-effects.
What is the code for that function?

For example, does:
UPDATE nlpg.match_data SET org = org WHERE match_data_id;
finish in a reasonable amount of time or exhibit the same out of memory
problem?

It'd be nice to see a \d on that table ... does it have any triggers or
cascading foreign keys?

And stop 

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [GENERAL] Config Changes Broke Postgres Service (Windows)

2010-05-28 Thread Alban Hertroys

On 28 May 2010, at 15:40, Tom Wilcox wrote:

> Hi,
> 
> I am fighting with Postgres on a 64-bit Windows (Server 2008) machine with 
> 96GB trying to get it to use as much memory as possible (I am the only user 
> and I am running complex queries on large tables). [See my previous thread 
> for details "Out of Memory and Configuration Problems (Big Computer)"].

There is no 64-bit Windows version of the postgresql binaries, so you probably 
won't be able to use more than 2 or 4 GB of all that memory...

There will be one once Postgres 9 is released: 
http://wiki.postgresql.org/wiki/64bit_Windows_port

> I have changed my postgres.conf file by changing the values of shared_buffers 
> to 1024, work_mem = 1024 and effective_cache_size=2703601.

I'm pretty sure those values are in bytes, so these are awfully small values 
for a database server.

> Now my postgres service wont start from the windows Services dialog, however 
> using pgAdmin I can start the service and connect to my database, run queries 
> etc.

Sorry, can't help you there - I rarely use Windows.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bffe51f10216640413027!



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

Re: [GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Bill Moran

In response to Tom Wilcox :



> Also, can anyone give me any pointers for configuring postgres to use 
> ALL 96GB of RAM in my new machine? I would like to know it was using 
> everything available.. especially when it is possible to load an entire 
> 30m row table into memory! I am currently using the default 
> configuration from standard installation.
> 
> Any help/suggestions are very much appreciated.

There is a LOT of information missing from this email, such as PG
version and the OS it's running on.

As a starter, though, the default config is not appropriate for most
workloads, and is certainly not optimal for a machine with 96G of RAM.

Start out by following any of the dozens of PG tuning howtos to get
your basic memory setting set up.  For example:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [GENERAL] Config Changes Broke Postgres Service (Windows)

2010-05-28 Thread Tom Wilcox


Hi Stephen,

Thanks for the response. Unfortunately, we are somewhat tied to a 
Windows platform and I would expect us to sooner switch to SQL Server 
rather than move to Linux/Unix/BSD.. Although, (in complete contrast to 
what I just said), I am toying with the idea of the dual boot or 
virtualisation options..


Cheers,
Tom

On 28/05/2010 15:44, Stephen Frost wrote:

* Tom Wilcox (hungry...@googlemail.com) wrote:
   

Can anyone tell me what might be going on and how I can fix it so that
postgres uses as much memory and processing power as poss... in a stable
manner?
 

I realize this probably isn't the answer you're looking for, and
hopefully someone can come up with a better answer for you, but my first
reaction to this would be "move to a non-Windows platform", eg: Linux,
BSD, Solaris, etc..

Thanks,

Stephen
   



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

[GENERAL] Re: [GENERAL] hi，for help!

2010-05-28 Thread Ashesh Vashi

What is the output of the following commands?
psql -l
psql --version

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company


On Fri, May 28, 2010 at 7:11 PM, zhenyang guo  wrote:

> hi,  i have a  problem not knowing how to fix . the follwing are error
> information:
>
> "
> g...@guo-desktop:~$ su
> Password:
> r...@guo-desktop:/home/guo# su - postgres
> postg...@guo-desktop:~$ psql
> psql: FATAL:  database "postgres" does not exist
> postg...@guo-desktop:~$ createdb test
> createdb: could not connect to database postgres: FATAL:  database
> "postgres" does not exist
>
>
> "
> i thought it was due to  postgresql ,so i reinstall it ,still not work.
> i search the problem from the internet, but don't get it .
> need your help. thank  you!
>

Re: [GENERAL] Config Changes Broke Postgres Service (Windows)

2010-05-28 Thread Stephen Frost

* Tom Wilcox (hungry...@googlemail.com) wrote:
> Can anyone tell me what might be going on and how I can fix it so that
> postgres uses as much memory and processing power as poss... in a stable
> manner?

I realize this probably isn't the answer you're looking for, and
hopefully someone can come up with a better answer for you, but my first
reaction to this would be "move to a non-Windows platform", eg: Linux,
BSD, Solaris, etc..

Thanks,

Stephen

signature.asc
Description: Digital signature

Re: [GENERAL] hi，for help!

2010-05-28 Thread Daniel Bausch

You need to "initdb" first.

Am Freitag, 28. Mai 2010 15:41:38 schrieb zhenyang guo:
> hi,  i have a  problem not knowing how to fix . the follwing are error
> information:
> 
> "
> g...@guo-desktop:~$ su
> Password:
> r...@guo-desktop:/home/guo# su - postgres
> postg...@guo-desktop:~$ psql
> psql: FATAL:  database "postgres" does not exist
> postg...@guo-desktop:~$ createdb test
> createdb: could not connect to database postgres: FATAL:  database
> "postgres" does not exist
> 
> 
> "
> i thought it was due to  postgresql ,so i reinstall it ,still not work.
> i search the problem from the internet, but don't get it .
> need your help. thank  you!
> 

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

Re: [GENERAL] hi，for help!

2010-05-28 Thread Andreas Kretschmer

zhenyang guo  wrote:

> hi,  i have a  problem not knowing how to fix . the follwing are error
> information:
> 
> "
> g...@guo-desktop:~$ su
> Password:
> r...@guo-desktop:/home/guo# su - postgres
> postg...@guo-desktop:~$ psql
> psql: FATAL:  database "postgres" does not exist
> postg...@guo-desktop:~$ createdb test
> createdb: could not connect to database postgres: FATAL:  database "postgres"
> does not exist

Which version?

Well, try:

createdb -T template0 test


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

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

[GENERAL] Config Changes Broke Postgres Service (Windows)

2010-05-28 Thread Tom Wilcox

Hi,

I am fighting with Postgres on a 64-bit Windows (Server 2008) machine with
96GB trying to get it to use as much memory as possible (I am the only user
and I am running complex queries on large tables). [See my previous thread
for details "Out of Memory and Configuration Problems (Big Computer)"].

I have changed my postgres.conf file by changing the values of
shared_buffers to 1024, work_mem = 1024 and effective_cache_size=2703601.

Now my postgres service wont start from the windows Services dialog, however
using pgAdmin I can start the service and connect to my database, run
queries etc.

So my problem is that Windows no longer seems to think my postgres service
is running even though it appears to be working fine through a client..

Can anyone tell me what might be going on and how I can fix it so that
postgres uses as much memory and processing power as poss... in a stable
manner?

Cheers,
Tom

P.S. Sorry if this is a repeat but I have been searching for an answer to
this for a LONG time (>3months).

[GENERAL] subscribe

2010-05-28 Thread Jacqui Caren-home


subscribe

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

[GENERAL] hi，for help!

2010-05-28 Thread zhenyang guo

hi,  i have a  problem not knowing how to fix . the follwing are error
information:

"
g...@guo-desktop:~$ su
Password:
r...@guo-desktop:/home/guo# su - postgres
postg...@guo-desktop:~$ psql
psql: FATAL:  database "postgres" does not exist
postg...@guo-desktop:~$ createdb test
createdb: could not connect to database postgres: FATAL:  database
"postgres" does not exist


"
i thought it was due to  postgresql ,so i reinstall it ,still not work.
i search the problem from the internet, but don't get it .
need your help. thank  you!

Re: [GENERAL] UPDATE...RETURNING col INTO var - what is returned?

2010-05-28 Thread Rainer Pruy

Thank you for your reply.
Yes, testing proved "after" values being returned.

Nevertheless I'm wondering whether this behaviour is (formally) defined 
somewhere
or just happens to be so by implementation detail.
(Of course, this is a minor concern as the PG team for sure will guard against 
a change of the existing semantics)

With that behaviour a second question rising is:

Is there an easy way to update columns of a table and depend on the previous 
values in an efficient way?
(Clearly, one could use SELECT RETURNING INTO before the update,
but that is for sure not my notion of "efficient")

This caused my initial doubts:
In most cases if you update a column value, you will "know" the new value (as 
you control the assignment).
(Exceptions might be using timestamp values (one that change in real time not 
such that stay with transaction start)
or using values written by other transactions (if transaction separation would 
allow;  might not be save anyway))

So returning "old" values would have provided access to additional information, 
right?

Rainer

Am 27.05.2010 19:12, schrieb Alban Hertroys:
> On 27 May 2010, at 18:12, Rainer Pruy wrote:
> 
>> Hi all,
>> I just got quite confused on the exact semantics
>> of "RETURNING expressions INTO target" with an update statement.
>>
>> And while trying to resolve failed to find an answer in the  docs.
>>
>> Question was whether - in case "expression" is involving a column that is 
>> assigned by the update itself -
>> the value returned is based on the "old" value or the "new" value".
>>
>> While I'm no back to assuming is has to be the "old" one,
>> I did not find a reference to that fact with the docs.
> 
> I'm pretty sure it's the new value, since that's what INSERT...RETURNING 
> returns - very convenient if the value you inserted was generated somehow (by 
> a sequence for example).
> Since you can also UPDATE some column using a generated value, it'd make 
> sense if it would behave the same way.
> 
> Alban Hertroys
> 
> --
> Screwing up is an excellent way to attach something to the ceiling.
> 
> 
> !DSPAM:737,4bfea81210411699814628!
> 
> 
> 

-- 
Rainer Pruy
Geschäftsführer

Acrys Consult GmbH & Co. KG
Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel
Tel: +49-6101-98760-0 Fax: +49-6101-98760-50
Web: http://www.acrys.com -  Email: off...@acrys.com
Handelsregister: Frankfurt am Main, HRA 31151
Komplementärin: Acrys Verwaltungs GmbH
Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel
Handelsregister: Frankfurt am Main, HRB 57625

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

Re: [GENERAL] Searching for paper about pg's optimizer cost model

2010-05-28 Thread Tom Lane

Daniel Bausch  writes:
> does anybody know a paper (or any document but the code itself) containing a 
> description of the current cost model used by the query optimizer/planner.

If you look on the project website you can find slides from various
conference presentations that give overviews of the planner.  But if you
want details you'll have to read the code.  It's at least moderately
well commented, though.  Most of the skeletons in this particular
closet are in costsize.c and selfuncs.c.

regards, tom lane

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

Re: [GENERAL] PostgreSQL DBA Opening

2010-05-28 Thread Peter Geoghegan

This is the wrong list for this. You should post to pgsql-jobs only,

Regards,
Peter Geoghegan

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

[GENERAL] PostgreSQL DBA Opening

2010-05-28 Thread Dbarrett

Company:   Aggregate Knowledge
Location:  San Mateo, CA
Position:  PostgreSQL Database Administrator

Job Description:

Aggregate Knowledge, a fast growing company backed by top-tier venture
capital, is pioneering the new category discovery as an on-line
service. As a result of our rapid growth, we are looking for senior
engineers to join and help grow our world-operations team.

Your responsibilities will focus on service delivery: designing,
implementing, and maintaining a best-in-class infrastructure with
strict availability and performance requirements. You'll be expected
to work across functional boundaries, contribute to process
development, and participate in an on-call rotation.

The most important job requirement is the ability, experience, and
desire to provide a computationally intensive service, non-stop. Your
troubleshooting skills should be top-notch, and your communication
skills should be strong. We'd like to see experience in at least a few
key areas: performance analysis, advanced storage systems, network
engineering, and infrastructure/support systems.

Responsibilities:

•Rotating 24x7 On-call support
•Develop high performance relational databases to store
content, user personalization information, and billing/tracking data,
etc. Develop and implement maintenance and administration procedures
for the database systems.
•Ensures that backups are performed regularly on all
databases.
•Work closely with software engineers to develop and maintain
stored procedures, compiled SQL, etc. Ensure that data access methods,
database kernel patches, APIs, etc. are kept current.
•Develop a system for gathering database performance metrics.
Using these metrics, develop a plan to ensure that database systems
are correctly configured for optimal performance. Manage and/or
participate in implementation of database system tuning, to ensure
optimal performance.
•Ensure that database systems are adequately monitored.
Develop and implement a plan to ensure maximum reliability and
availability of database systems. Proactively monitor database system
status to determine early warning signs of problems.
•Author and maintain scripts to perform various aspects of
administration. The DBA will document these scripts and maintain them
in the source repository.


Requirements:

•5 years experience as a DBA
•3 years experience of Postgres administration and tuning in a
Linux environment
•Must have strong knowledge of data relationships, data
normalization
•SQL, Perl, python, ruby, bash, C or other scripting
experience
•Understanding of LVM, SAN and RAID
•Cacti, Nagios, or other monitoring and metrics tools
•Ability to learn quickly and adapt to a fast paced growing
and evolving service delivery system
•Ability to manage projects through their entire life cycle
•Excellent communication skills

Candidate must be able to troubleshoot and resolve network,
application, and systems issues expediently. Candidates must be
flexible to work rotating shifts in a 24 x 7 environment. Candidate
must possess excellent verbal and written communication skills.
Occasional visits to our local data center will be needed, so
transportation is a necessity.
Local candidates only, please, as no relocation can be offered.
Unsolicited resumes from agencies are not welcome; Aggregate Knowledge
is not responsible for any fees associated with unsolicited resumes.

For immediate consideration please send your resumes and salary goals
to dbarr...@maximumhr.com

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

Re: [GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Stephen Frost

* Tom Wilcox (hungry...@googlemail.com) wrote:
> UPDATE tbl SET f1 = COALESCE(f2,'') || ' ' || COALESCE(f3);
>
> Can anyone suggest reasons why I might be running out of memory on such  
> a simple query?

Do you have any triggers on that table?  Or FK's?

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Joachim Worringen


On 05/28/2010 02:55 PM, Craig Ringer wrote:

On 28/05/10 17:41, Joachim Worringen wrote:

Greetings,

my Python application (http://perfbase.tigris.org) repeatedly needs to
insert lots of data into an exsting, non-empty, potentially large table.
Currently, the bottleneck is with the Python application, so I intend to
multi-thread it.


That may not be a great idea. For why, search for "Global Interpreter
Lock" (GIL).

It might help if Python's mostly blocked on network I/O, as the GIL is
released when Python blocks on the network, but still, your results may
not be great.


I verified that the thread actually execute queries concurrently. That 
does imply that they are blocked on I/O while the query is running, and 
that the query performance does in fact scale for this reason.


In the "import data" case, however, I really need concurrent processing 
on the CPU in the first place, so you may be right on this one. I'll 
check it.



will I get a speedup? Or will table-locking serialize things on the
server side?


Concurrent inserts work *great* with PostgreSQL, it's Python I'd be
worried about.


That's the part of answer I wanted to hear.,,

 thanks, Joachim



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

[GENERAL] Out of Memory and Configuration Problems (Big Computer)

2010-05-28 Thread Tom Wilcox



Hi,

I am fairly new to postgres and I have been using it with Python to 
develop a set of functions that operate on some moderately large tables 
(30million rows with 13-15 columns).


I have spent some time messing with the configuration file to get PG to 
use indexes when planning queries, etc.


At the moment, I have a fairly fundamental, recurring problem which is 
that a query has ran out of memory. This particular query is a 
sequential scan UPDATE query that is operating on a table with 
approximately 27,778,000 rows:


UPDATE tbl SET f1 = COALESCE(f2,'') || ' ' || COALESCE(f3);

ERROR: out of memory
DETAIL: Failed on request of size 36
Run time (est): 6,000,000ms

EXPLAIN shows the query plan as:

Seq Scan on tbl (cost=0.00..2088542.83 rows=59868855 width=128)

I have noticed that the "rows=59xx" suggests that the plan features 
2 x the number of rows in the table. Perhaps I am writing poor SQL?


Can anyone suggest reasons why I might be running out of memory on such 
a simple query? Is it possible that, because it is executed as a 
transaction, the changes are not being committed until the query is 
complete and therefore the whole table is being stored in memory?


Also, can anyone give me any pointers for configuring postgres to use 
ALL 96GB of RAM in my new machine? I would like to know it was using 
everything available.. especially when it is possible to load an entire 
30m row table into memory! I am currently using the default 
configuration from standard installation.


Any help/suggestions are very much appreciated.

Cheers,
Tom

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Craig Ringer


On 28/05/10 17:41, Joachim Worringen wrote:

Greetings,

my Python application (http://perfbase.tigris.org) repeatedly needs to
insert lots of data into an exsting, non-empty, potentially large table.
Currently, the bottleneck is with the Python application, so I intend to
multi-thread it.


That may not be a great idea. For why, search for "Global Interpreter 
Lock" (GIL).


It might help if Python's mostly blocked on network I/O, as the GIL is 
released when Python blocks on the network, but still, your results may 
not be great.



will I get a speedup? Or will table-locking serialize things on the
server side?


Concurrent inserts work *great* with PostgreSQL, it's Python I'd be 
worried about.



--
Craig Ringer

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

Re: [GENERAL] hi, trying to compile postgres 8.3.11

2010-05-28 Thread Giles Lean

erobles  wrote:

>> Do you need the Pg server to run on SCO OpenServer?
> 
> Yes,  i need  it  :-P

Of course it's none of my business, but whenever I had a
supplier insisting on some idosyncratic or obsolete OS I
started thinking hard about replacing the supplier and
their product.

Even worse if the supplier is gone and you're using a
totally unsupported product.

All IMHO of course, and I've supported some peculiar
setups when business requirements made alternatives
impossible.

Good luck,

Giles

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

Re: [GENERAL] Merge data from two databases

2010-05-28 Thread Goran Hasse

Actually you have to write a program to insert one database into another.
This is so the program
can check for concistancy and maybe recect records or datasets that are
inconsistent.

GH

2010/5/28 Jamie Lawrence-Jenner 

>  Hi There
>
>
>
> We have a stats databases which has been dumped out a few times over the
> last year.
>
>
>
> What i would now like to do is to merge these dumps into one database. The
> problem is is that when the dumps were done, a drop and create was carried
> out on the database so primary key columns will collide
>
>
>
> I have three dumpes, how do i merge the data into one database please
>
>
>
> Many thanks
>
>
>
> Jamie
>



-- 
gor...@gmail.com
Mob: 070-5530148

[GENERAL] Searching for paper about pg's optimizer cost model

2010-05-28 Thread Daniel Bausch

Hello,

does anybody know a paper (or any document but the code itself) containing a 
description of the current cost model used by the query optimizer/planner.
I'm about to write a master thesis in this area that aims for more exact 
estimates. Best would be some mathematical formulas with a rationale, but 
anything else would help, too.

Regards,
Daniel Bausch
TU-Darmstadt (Germany)

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

[GENERAL] Merge data from two databases

2010-05-28 Thread Jamie Lawrence-Jenner

Hi There

 

We have a stats databases which has been dumped out a few times over the
last year. 

 

What i would now like to do is to merge these dumps into one database. The
problem is is that when the dumps were done, a drop and create was carried
out on the database so primary key columns will collide

 

I have three dumpes, how do i merge the data into one database please

 

Many thanks

 

Jamie

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Alban Hertroys

On 28 May 2010, at 12:14, Martin Gainty wrote:

> Good Afternoon Szymon!
>  
> Could you explain what a Python GIL is? and if there is any workaround to 
> Python GIL we can implement to achieve better performance..possibly at the 
> database level?

See here: http://en.wikipedia.org/wiki/Global_Interpreter_Lock

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bff996b10419162611771!



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

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Martin Gainty


Good Afternoon Szymon!

 

Could you explain what a Python GIL is? and if there is any workaround to 
Python GIL we can implement to achieve better performance..possibly at the 
database level?


Mit freundlichen Grüßen/Les plus sincères amitiés 
Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.



 



Date: Fri, 28 May 2010 11:48:16 +0200
Subject: Re: [GENERAL] INSERTing lots of data
From: mabew...@gmail.com
To: joachim.worrin...@iathh.de
CC: pgsql-general@postgresql.org


2010/5/28 Joachim Worringen 

Greetings,

my Python application (http://perfbase.tigris.org) repeatedly needs to insert 
lots of data into an exsting, non-empty, potentially large table. Currently, 
the bottleneck is with the Python application, so I intend to multi-thread it. 
Each thread should work on a part of the input file.

I already multi-threaded the query part of the application, which requires to 
use one connection per thread - cursors a serialized via a single connection.

Provided that
- the threads use their own connection
- the threads perform all INSERTs within a single transaction
- the machine has enough resources

 will I get a speedup? Or will table-locking serialize things on the server 
side?

Suggestions for alternatives are welcome, but the data must go through the 
Python application via INSERTs (no bulk insert, COPY etc. possible)




Remember about Python's GIL in some Python implementations so those threads 
could be serialized at the Python level.


This is possible that those inserts will be faster. The speed depends on the 
table structure, some constraints and triggers and even database configuration. 
The best answer is: just check it on some test code, make a simple 
multithreaded aplication and try to do the inserts and check that out.




regards
Szymon Guz

  
_
Hotmail is redefining busy with tools for the New Busy. Get more from your 
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Joachim Worringen


On 05/28/2010 11:48 AM, Szymon Guz wrote:

Remember about Python's GIL in some Python implementations so those
threads could be serialized at the Python level.


My multi-threaded queries scale nicely with Python 2.6 on Linux, so this 
is not an issue here. But the queries do not perform concurrent write 
accesses on the same table.



This is possible that those inserts will be faster. The speed depends on
the table structure, some constraints and triggers and even database
configuration. The best answer is: just check it on some test code, make
a simple multithreaded aplication and try to do the inserts and check
that out.


Sure, testing always shows something, but I wonder if something general 
can be said about the execution of concurrent write transaction on the 
same table (no triggers, some non-NULL constraints, one index).


http://www.postgresql.org/docs/8.4/interactive/mvcc-intro.html says 
about MVCC:

"
The main advantage of using the MVCC model of concurrency control rather 
than locking is that in MVCC locks acquired for querying (reading) data 
do not conflict with locks acquired for writing data, and so reading 
never blocks writing and writing never blocks reading.

"

It does not mention whether writing may block writing, or if it always 
does. 
http://bytes.com/topic/python/answers/728130-parallel-insert-postgresql-thread 
indicates it should not block - can this be confirmed by some Postgresql 
guru?


thanks, Joachim



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

Re: [GENERAL] INSERTing lots of data

2010-05-28 Thread Szymon Guz

2010/5/28 Joachim Worringen 

> Greetings,
>
> my Python application (http://perfbase.tigris.org) repeatedly needs to
> insert lots of data into an exsting, non-empty, potentially large table.
> Currently, the bottleneck is with the Python application, so I intend to
> multi-thread it. Each thread should work on a part of the input file.
>
> I already multi-threaded the query part of the application, which requires
> to use one connection per thread - cursors a serialized via a single
> connection.
>
> Provided that
> - the threads use their own connection
> - the threads perform all INSERTs within a single transaction
> - the machine has enough resources
>
>  will I get a speedup? Or will table-locking serialize things on the server
> side?
>
> Suggestions for alternatives are welcome, but the data must go through the
> Python application via INSERTs (no bulk insert, COPY etc. possible)
>
>
Remember about Python's GIL in some Python implementations so those threads
could be serialized at the Python level.

This is possible that those inserts will be faster. The speed depends on the
table structure, some constraints and triggers and even database
configuration. The best answer is: just check it on some test code, make a
simple multithreaded aplication and try to do the inserts and check that
out.


regards
Szymon Guz

[GENERAL] INSERTing lots of data

2010-05-28 Thread Joachim Worringen


Greetings,

my Python application (http://perfbase.tigris.org) repeatedly needs to 
insert lots of data into an exsting, non-empty, potentially large table. 
Currently, the bottleneck is with the Python application, so I intend to 
multi-thread it. Each thread should work on a part of the input file.


I already multi-threaded the query part of the application, which 
requires to use one connection per thread - cursors a serialized via a 
single connection.


Provided that
- the threads use their own connection
- the threads perform all INSERTs within a single transaction
- the machine has enough resources

 will I get a speedup? Or will table-locking serialize things on the 
server side?


Suggestions for alternatives are welcome, but the data must go through 
the Python application via INSERTs (no bulk insert, COPY etc. possible)


 thanks, Joachim


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

Re: [GENERAL] Download

2010-05-28 Thread Piotr Kublicki

From:   Devrim GÜNDÜZ 
To: Piotr Kublicki 
Cc: pgsql-general@postgresql.org
Date:   28/05/2010 09:36
Subject:Re: [GENERAL] Download

On Fri, 2010-05-28 at 08:37 +0100, Piotr Kublicki wrote:

> And here:
>
http://pgfoundry.org/docman/view.php/148/1338/PostgreSQL-RPM-Installation-PGDG.pdf

That is a bit outdated. :(

What's wrong with it? I used (some) information from this document and
found it useful (just replacing older versions of PGDG with newer ones).
For the beginners is fine. But as the author you obviously know better.

Cheers

This email (and attachments) are confidential and intended for the addressee(s) 
only. If you are not the intended recipient please notify the sender, 
delete any copies and do not take action in reliance on it. Any views expressed 
are the author's and do not represent those of IOP, except where specifically 
stated. IOP takes reasonable precautions to protect against viruses but accepts 
no responsibility for loss or damage arising from virus infection. 
For the protection of IOP's systems and staff emails are scanned 
automatically.” 

Institute of Physics Registered in England under Registration No 293851 
Registered Office:  76/78 Portland Place, London W1B 1NT

Re: [GENERAL] Download

2010-05-28 Thread Devrim GÜNDÜZ

On Fri, 2010-05-28 at 08:37 +0100, Piotr Kublicki wrote:

> And here:
> http://pgfoundry.org/docman/view.php/148/1338/PostgreSQL-RPM-Installation-PGDG.pdf

That is a bit outdated. :(

-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


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

Re: [GENERAL] Download

2010-05-28 Thread Piotr Kublicki

Faiyaz,

You can start from here:
http://www.postgresonline.com/journal/index.php?/archives/144-An-almost-idiots-guide-to-Install-and-Upgrade-to-PostgreSQL-8.4-with-Yum.html

Section titled "Installing PostgreSQL 8.4.1 from Yum repository"

And here:
http://pgfoundry.org/docman/view.php/148/1338/PostgreSQL-RPM-Installation-PGDG.pdf

Good luck!




From:   Faiyaz Allie 
To: 
Date:   27/05/2010 17:52
Subject:[GENERAL] Download
Sent by:pgsql-general-ow...@postgresql.org



Hi There

I would like to find out where I can download Postgresql 8.1.7 rpm for
Redhat 5.5. I’m trying to load an application that requires it. I’ve tried
to download it from your site but the link times out.


Any help will be appreciated.

Regards



   
 Faiyaz Allie  
 Operations Manager
 Managed Services  
   
 (Embedded image moved to file: pic32524.gif)  
 cid:image001.gif@01CADA17.16863E80
   
 (Embedded image moved to file: pic43054.gif)  
 cid:image002.gif@01CADA17.16863E80  +27 11 790 2500   
 (Embedded image moved to file: pic43879.gif)  
 cid:image002.gif@01CADA17.16863E80  +27 11 790 2706 (Direct)  
 (Embedded image moved to file: pic28189.gif)  
 cid:image004.gif@01CADA17.16863E80  +27 82 450 9495 (Cell)
 (Embedded image moved to file: pic30657.gif)  
 cid:image005.gif@01CADA17.16863E80  +27 11 790 2599 (Fax) 
 (Embedded image moved to file: pic34863.gif)  
 cid:image006.gif@01CADA17.16863E80  faiy...@securedata.co.za  
   





   
   The contents and any attachments are subject to the following disclaimer:   
  http://www.securedataholdings.co.za/disclaimer.php   
   






 (Embedded image moved to file: pic35283.gif)cid:image007.gif@01CADA17.16863E80 


This email (and attachments) are confidential and intended for the addressee(s) 
only. If you are not the intended recipient please notify the sender, 
delete any copies and do not take action in reliance on it. Any views expressed 
are the author's and do not represent those of IOP, except where specifically 
stated. IOP takes reasonable precautions to protect against viruses but accepts 
no responsibility for loss or damage arising from virus infection. 
For the protection of IOP's systems and staff emails are scanned 
automatically.” 

Institute of Physics Registered in England under Registration No 293851 
Registered Office:  76/78 Portland Place, London W1B 1NT  
<><><><><><><>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

67 matches

Mail list logo