Re: [GENERAL] Dell Poweredge server and Postgres
On Sat, Jun 5, 2010 at 5:27 PM, u235sentinel wrote: > I'm curious if anyone has had any experiences (good and bad) using Postgres > on Dell PowerEdge servers. I've had lots of experience with Dell, most of it poor. Wrong upgrade CPUs, use of non-buffered memory meaning I can't max out my machine's RAM, mediocre performance from most PERC RAID controllers. I just got a quote for a 4 CPU / 48 core AMD Magny Cours with 32 15k6 seagates and 128Gig ram from my favorite white box supplier, with a 5 year warranty and great support for $25k including shipping. If Dell can deliver that much horsepower for that price let me know. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Difference between these two queries ?
Hi, I have a doubt about JOINS. What is the difference between: 1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and bu.uid = 5; 2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id = bu.bid AND bu.uid = 5; What is the first type of join called ? and is it possible that they have different execution times ? -- Nilesh Govindarajan Facebook: nilesh.gr Twitter: nileshgr Website: www.itech7.com Cheap and Reliable VPS Hosting: http://j.mp/arHk5e -- 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] Dell Poweredge server and Postgres
u235sentinel wrote: I'm curious if anyone has had any experiences (good and bad) using Postgres on Dell PowerEdge servers. Poweredge is a brandname that describes every single server Dell has sold since about 15 years ago, maybe more. I had a Poweredge that was a dual 600Mhz pentium-III w/ 1GB ram and 4 x 72gb scsi disks. My manager and I are looking at replacing a Sun x4540 server with a Dell server connected to a disk subsystem (or two). We're looking at the R710 servers connected to an MD1220 I believe (I'd have to look again at the quote). why are you looking at replacing it? you go onto say its working great. The concern we have is our 4540 has a 2TB database which is working great. The server has 48 hard drives (250 gig drives) in RAID 10 across 6 disk controllers. A couple HBA controllers connected to a couple dozen disks may be slower (though dell assures us it will be faster than our Sun box). of course they do, they want to sell you their iorn...do they have benchmarks of their proposed configuration vs a x4540 doing the sorts of tasks you require to back up their claim? The MD1220 is connected to the host with a single X4 SAS cable. btw, the x4540 has each disk on its own SATA channel, 8 channels to the SATA chip, and each SATA chip on a PCI-E x4 bus, so it has sufficient IO backplane bandwidth to keep all 48 disks busy at once. ZFS is extremely good at this.The Sun Thumpers have been benchmarked with rather high IOPS numbers, that few other sorts of systems can sustain in real world tests. you can make ZFS on a thumper even faster by using a couple SSDs for the ZIL logs. I thought I'd toss this out and see if anyone has any thoughts on this. I'm inclined to try it. The drives quoted are 15k drives and the PERC controller has cache vs. the Sun controllers have no cache AFAIK. Solaris and ZFS use main memory as the cache. main memory is faster than any memory out on an IO controller. BTW, in the next few months I believe we're be hitting the 2.5-3 TB size for our database. The tables are partitioned which helps a lot. Performance would be a problem otherwise with that much data I think :-) upgrade your thumper to 1TB drives and go to town.They -should- be Sun approved drives, however, as there is all sorts of room for sketchiness using the wrong SATA disks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dell Poweredge server and Postgres
I'm curious if anyone has had any experiences (good and bad) using Postgres on Dell PowerEdge servers. My manager and I are looking at replacing a Sun x4540 server with a Dell server connected to a disk subsystem (or two). We're looking at the R710 servers connected to an MD1220 I believe (I'd have to look again at the quote). The concern we have is our 4540 has a 2TB database which is working great. The server has 48 hard drives (250 gig drives) in RAID 10 across 6 disk controllers. A couple HBA controllers connected to a couple dozen disks may be slower (though dell assures us it will be faster than our Sun box). I thought I'd toss this out and see if anyone has any thoughts on this. I'm inclined to try it. The drives quoted are 15k drives and the PERC controller has cache vs. the Sun controllers have no cache AFAIK. BTW, in the next few months I believe we're be hitting the 2.5-3 TB size for our database. The tables are partitioned which helps a lot. Performance would be a problem otherwise with that much data I think :-) Thanks! -- 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] Null comparisons and the transform_null_equals run-time parameter
"Ken Winter" writes: > In case anybody else needs this functionality, let me offer a family of > functions that do comparisons that treat NULL as a real value (rather than > as "unknown"). For example: Er ... this'd be a lot shorter using IS [NOT] DISTINCT FROM. 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] pgbouncer
Mario Ignacio Rodríguez Cortés writes: > Well, i have a question if you know about this, i have a webserver in a > server and i have a database server, the question is: where should I > install the pgbouncer? in webserver, in database server or is the same? > whats your experience. I always install an instance of pgbouncer per webserver, and sometimes I also have another pgbouncer on the PostgreSQL box too. I don't think I ever share a single pgbouncer for several web servers directly though. Regards, -- dim -- 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] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8"
2010/6/4 Wang, Mary Y : > Hi, > > I'm getting this error from postgres " ERROR: character 0x90 of encoding > "WIN1252" has no equivalent in "UTF8" " and from a dump file when I tried to > use psql command to restore the dump. > > I have SET client_encoding = 'win1252' in the dump file. > > Any ideas? > 0x90 isn't a valid WIN-1252 character. http://msdn.microsoft.com/en-us/goglobal/cc305145.aspx Osvaldo -- 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] Null comparisons and the transform_null_equals run-time parameter
Steve ~ Thanks for the great tips. See comments below. > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Steve Atkins > Sent: Saturday, June 05, 2010 1:04 PM > To: PostgreSQL pg-general List > Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run- > time parameter > > > On Jun 5, 2010, at 9:46 AM, Ken Winter wrote: > > In case anybody else needs this functionality, let me offer a family of > > functions that do comparisons that treat NULL as a real value (rather > than > > as "unknown"). For example: > > You should take a look at "is not distinct from". Wow, that's the same wheel I just reinvented! Works fine in my test functions. Guess I can scrap my home-brewed functions that do the same thing. > > The reason I need this is that I'm writing functions to test my database > > programming (triggers, rules, etc), and these tests sometimes need to > treat > > Null as just another value in doing test comparisons. > > You also might find http://pgtap.org/ useful. Yes indeed, looks very promising. I see there are also test frameworks available at http://en.dklab.ru/lib/dklab_pgunit/ and http://www.epictest.org/ . This will take me a while to digest, but hopefully I'll be able to scrap, or greatly simplify, my elaborate homemade test functions too. It's always a joy to be able to replace my own code with somebody else's. ~ Ken -- 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] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8"
On Saturday 05 June 2010 7:07:35 am Wang, Mary Y wrote: > Thanks Alban, but I'm still confused. > I just read the PostgreSQL manual, and got the impression that UTF8 would > take care of ALL character sets, so I put SET client_encoding = 'UTF8' in > the dump file, and reload it again, then I got even more errors like: > psql:/tmp/060410finalsr15dump.txt:31430: ERROR: invalid byte sequence for > encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:31795: ERROR: > invalid byte sequence for encoding "UTF8": 0x93 > psql:/tmp/060410finalsr15dump.txt:31826: ERROR: invalid byte sequence for > encoding "UTF8": 0x85 psql:/tmp/060410finalsr15dump.txt:31827: ERROR: > invalid byte sequence for encoding "UTF8": 0x92 > psql:/tmp/060410finalsr15dump.txt:31874: ERROR: invalid byte sequence for > encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:32111: ERROR: > invalid byte sequence for encoding "UTF8": 0x93 > psql:/tmp/060410finalsr15dump.txt:32112: ERROR: invalid byte sequence for > encoding "UTF8": 0x92 > > What is the client encoding that I should set to so that it would take care > all character sets? > http://www.postgresql.org/docs/8.3/interactive/multibyte.html > > Any suggestions? > Thanks > Mary > I am afraid it does not work that way. You have to think translation tables. In order for the process to work Postgres needs to know the correct encoding it is receiving in order to make the translation to UTF8. Much the same problem as giving someone who knows how to translate French to English, a document that contains a mixture of words in different languages. If they don't know what the words are they cannot be translated. -- 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] Null comparisons and the transform_null_equals run-time parameter
On Jun 5, 2010, at 9:46 AM, Ken Winter wrote: > I get it. Thanks, Tom. > > In case anybody else needs this functionality, let me offer a family of > functions that do comparisons that treat NULL as a real value (rather than > as "unknown"). For example: You should take a look at "is not distinct from". > > The reason I need this is that I'm writing functions to test my database > programming (triggers, rules, etc), and these tests sometimes need to treat > Null as just another value in doing test comparisons. You also might find http://pgtap.org/ useful. Cheers, Steve -- 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] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8"
On 5 Jun 2010, at 16:07, Wang, Mary Y wrote: > Thanks Alban, but I'm still confused. > I just read the PostgreSQL manual, and got the impression that UTF8 would > take care of ALL character sets, so I put SET client_encoding = 'UTF8' in the > dump file, and reload it again, then I got even more errors like: Ah no, that won't work if your data isn't encoded according to UTF-8. > What is the client encoding that I should set to so that it would take care > all character sets? > http://www.postgresql.org/docs/8.3/interactive/multibyte.html We can't know that. It's your client application(s) that put the data into your database, it depends on their encoding(s). That's the encoding you need. If no encoding was specified you probably ended up with a mixed bag of encodings. AFAIK the only way out of that is to check everything by hand - a computer won't know what the data means, so isn't likely to be able to figure out what encoding was used. If you don't care, set your database to use SQL_ASCII and it will just store the literal bytes as they are, without trying to interpret their encodings. If you already have a mixed bag of encodings, that's the easiest solution to get back to a working system - be aware though that you probably were already having issues with displaying some data correctly. > -Original Message- > From: Alban Hertroys [mailto:dal...@solfertje.student.utwente.nl] > Sent: Saturday, June 05, 2010 1:35 AM > To: Wang, Mary Y > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] ERROR: character 0x90 of encoding "WIN1252" has no > equivalent in "UTF8" > > On 5 Jun 2010, at 4:44, Wang, Mary Y wrote: > >> Hi, >> >> I'm getting this error from postgres " ERROR: character 0x90 of encoding >> "WIN1252" has no equivalent in "UTF8" " and from a dump file when I tried >> to use psql command to restore the dump. >> >> I have SET client_encoding = 'win1252' in the dump file. >> >> Any ideas? > > > Apparently Win1252 is the not the right encoding to use for your data, as > 0x90 is undefined in that encoding. See > http://dbaspot.com/forums/ingres-database/414795-re-info-ingres-fw-how-insert-control-charactersintovarcharcolumn.html > for someone with the same problem (on Ingres) and links to two different > code pages listing the characters therein. > > Alban Hertroys > > -- > If you can't see the forest for the trees, cut the trees and you'll see there > is no forest. > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > > Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c0a80a710151031451958! -- 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] Null comparisons and the transform_null_equals run-time parameter
I get it. Thanks, Tom. In case anybody else needs this functionality, let me offer a family of functions that do comparisons that treat NULL as a real value (rather than as "unknown"). For example: CREATE OR REPLACE FUNCTION eqnull(varchar, varchar) RETURNS boolean AS $BODY$ /* Return True if both args have the same non-NULL values or both args are NULL; otherwise False. */ DECLARE v1 ALIAS FOR $1; v2 ALIAS FOR $2; BEGIN -- NULL = NULL IF v1 IS NULL AND v2 IS NULL THEN RETURN True; -- NULL != Any non-NULL value ELSIF v1 IS NULL AND v2 IS NOT NULL THEN RETURN False; -- Any non-NULL value != NULL ELSIF v1 IS NOT NULL AND v2 IS NULL THEN RETURN False; -- Non-NULL value = non-NULL value ELSIF v1 = v2 THEN RETURN True; -- Non-NULL value != non-NULL value ELSE RETURN False; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE ; You need a separate function for each data type you want to compare; the only difference among these functions is their argument types. I've implemented variants for VARCHAR, NUMERIC, TIMESTAMP, and BOOLEAN. The reason I need this is that I'm writing functions to test my database programming (triggers, rules, etc), and these tests sometimes need to treat Null as just another value in doing test comparisons. ~ Ken > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Saturday, June 05, 2010 9:41 AM > To: Ken Winter > Cc: 'PostgreSQL pg-general List' > Subject: Re: [GENERAL] Null comparisons and the transform_null_equals run- > time parameter > > "Ken Winter" writes: > > When the run-time parameter transform_null_equals is on, shouldn't two > > variables with NULL values evaluate as equal? > > No. That setting does not change the runtime behavior of comparison. > The only thing it does is change the literal syntax "something = NULL" > to "something IS NULL". > > 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] so, does this overlap or not...? - fencepost question on overlaps()
Frank van Vugt writes: > One might consider adding a single line to the end of 9.9 of the docs that > warns for this behaviour and/or add the specific example? I put some more explanation and examples into the 9.0 docs: http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html 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] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8"
Thanks Alban, but I'm still confused. I just read the PostgreSQL manual, and got the impression that UTF8 would take care of ALL character sets, so I put SET client_encoding = 'UTF8' in the dump file, and reload it again, then I got even more errors like: psql:/tmp/060410finalsr15dump.txt:31430: ERROR: invalid byte sequence for encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:31795: ERROR: invalid byte sequence for encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:31826: ERROR: invalid byte sequence for encoding "UTF8": 0x85 psql:/tmp/060410finalsr15dump.txt:31827: ERROR: invalid byte sequence for encoding "UTF8": 0x92 psql:/tmp/060410finalsr15dump.txt:31874: ERROR: invalid byte sequence for encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:32111: ERROR: invalid byte sequence for encoding "UTF8": 0x93 psql:/tmp/060410finalsr15dump.txt:32112: ERROR: invalid byte sequence for encoding "UTF8": 0x92 What is the client encoding that I should set to so that it would take care all character sets? http://www.postgresql.org/docs/8.3/interactive/multibyte.html Any suggestions? Thanks Mary -Original Message- From: Alban Hertroys [mailto:dal...@solfertje.student.utwente.nl] Sent: Saturday, June 05, 2010 1:35 AM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8" On 5 Jun 2010, at 4:44, Wang, Mary Y wrote: > Hi, > > I'm getting this error from postgres " ERROR: character 0x90 of encoding > "WIN1252" has no equivalent in "UTF8" " and from a dump file when I tried to > use psql command to restore the dump. > > I have SET client_encoding = 'win1252' in the dump file. > > Any ideas? Apparently Win1252 is the not the right encoding to use for your data, as 0x90 is undefined in that encoding. See http://dbaspot.com/forums/ingres-database/414795-re-info-ingres-fw-how-insert-control-charactersintovarcharcolumn.html for someone with the same problem (on Ingres) and links to two different code pages listing the characters therein. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:1099,4c0a0c3410156830940052! -- 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] Null comparisons and the transform_null_equals run-time parameter
"Ken Winter" writes: > When the run-time parameter transform_null_equals is on, shouldn't two > variables with NULL values evaluate as equal? No. That setting does not change the runtime behavior of comparison. The only thing it does is change the literal syntax "something = NULL" to "something IS NULL". 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] [ADMIN] Exception while accessing database
On Thu, Jun 3, 2010 at 2:14 AM, Kevin Grittner wrote: > Manohar Bhattarai wrote: > > > FATAL: password authentication failed for user "postgres" > > > But the password is the same that i use to login postgres user in > > the terminal. > > What could be the problem? > > When you say "postgres user in the terminal", do you mean logging in > to the operating system? If so, that has nothing to do with the > database login unless you choose to make it so by having an "ident" > entry in the pg_hba.conf file. > > http://www.postgresql.org/docs/8.3/interactive/client-authentication.html > > > I am not able to understand how to start creating a new user > > and a new database after a fresh installation of postgres. > > Once you get logged in with the postgres user, that would be the > CREATE DATABASE and CREATE USER statements: > > http://www.postgresql.org/docs/8.3/interactive/sql-createdatabase.html > > http://www.postgresql.org/docs/8.3/interactive/sql-createuser.html > > -Kevin > Thank you. The problem is solved. :) Thanks for your help. -- Regards, Manohar Bhattarai (मनोहर भट्टराई) Blogs: http://manoharbhattarai.wordpress.com/ http://manoharbhattarai.posterous.com/ http://manoharbhattarai.blogspot.com/ Microblogs: Twitter :- http://twitter.com/manoharmailme Identi.ca :- http://identi.ca/manoharbhattarai
Re: [GENERAL] ERROR: character 0x90 of encoding "WIN1252" has no equivalent in "UTF8"
On 5 Jun 2010, at 4:44, Wang, Mary Y wrote: > Hi, > > I'm getting this error from postgres " ERROR: character 0x90 of encoding > "WIN1252" has no equivalent in "UTF8" " and from a dump file when I tried to > use psql command to restore the dump. > > I have SET client_encoding = 'win1252' in the dump file. > > Any ideas? Apparently Win1252 is the not the right encoding to use for your data, as 0x90 is undefined in that encoding. See http://dbaspot.com/forums/ingres-database/414795-re-info-ingres-fw-how-insert-control-charactersintovarcharcolumn.html for someone with the same problem (on Ingres) and links to two different code pages listing the characters therein. 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,4c0a0c3a10151431365867! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
