Re: [GENERAL] Server Performance
Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote on 01/04/2009 06:53:07: chris.el...@shropshire.gov.uk wrote: Scott Marlowe scott.marl...@gmail.com wrote on 31/03/2009 15:53:34: On Tue, Mar 31, 2009 at 8:21 AM, chris.el...@shropshire.gov.uk wrote: Scott Marlowe scott.marl...@gmail.com wrote on 31/03/2009 15:16:01: I'd call IBM and ask them to come pick up their boat anchors. My sentiments exactly, unfortunately, I seem stuck with them :( Can you at least source your own RAID controllers? Yes I will be, I never really did trust IBM and I certainly don't now! I just need to choose the correct RAID card now, good performance at the right price. you are jumping to conclusions too quickly - while the 8k is not the worlds fastest raid card available it is really not (that) bad at all. we have plenty of x3650 in production and last time I tested I was easily able to get 2000tps even on an untuned postgresql install and with fwer disks. Could you provide any more information upon your configurations if possible, please? So I really think you are looking at another problem here (be it defective hardware or a driver/OS level issue). Hardware is always a possiblity, finally managed to get hold of IBM too. I have tried two different Linux distro's, with different kernels, My current Mandriva test using a fairly upto date kernel. I may try a custom kernel. is your SLES10 install updated to the latest patch levels available and are you running the recommended driver version for that version of SLES? Yes Stefan ** If you are not the intended recipient of this email please do not send it on to others, open any attachments or file the email locally. Please inform the sender of the error and then delete the original email. For more information, please refer to http://www.shropshire.gov.uk/privacy.nsf **
[GENERAL] Performance with Boolean datatype
Hi, I would like to know is there any perf differences between case 1: when i use ( ...where validate=FALSE) [validate as Boolean] case 2: when i use ( ... where validate=1) [validate as int(1)] Thanks DEVI.G
[GENERAL] Need help with : org.postgresql.util.PSQLException : ERROR: deadlock detected
hey all, I have a stored procedure that updates a couple of tables within my database. org.postgresql.util.PSQLException : ERROR: deadlock detected Detail: Process 31580 waits for AccessExclusiveLock on relation 289553 of database 285107; blocked by process 16024. Process 16024 waits for AccessShareLock on relation 289471 of database 285107; blocked by process 31580. All tables in that database are heavy readed, and only my stored procedure copies some data within a table. The process within my stored procedure is like this but I have a couple of these within my stored procedure: LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE; ALTER TABLE mytable DISABLE TRIGGER trg_mytable_log; CREATE TEMPORARY TABLE mytemptable AS SELECT * FROM mytable WHERE country_code=_country_code_to; CREATE TEMPORARY TABLE mytemptable_log AS SELECT * FROM mytable_log WHERE country_code=_country_code_to; CREATE INDEX tmytemptable_idx ON mytemptable(part_num, vehicle_names_item_id,country_code); DELETE FROM mytable where country_code=_country_code_to; DELETE FROM mytable_log where country_code=_country_code_to; INSERT INTO mytable (p..) SELECT . FROM mytable WHERE INSERT INTO mytable_log (...) SELECT FROM mytable_log WHERE . INSERT INTO mytable SELECT * FROM mytemptable WHERE .. INSERT INTO mytable_log SELECT * FROM mytemptable_log WHERE . UPDATE mytable a SET . ALTER TABLE mytable ENABLE TRIGGER trg_mytable_log; For me it's perfectly fine to wait until the tables can get locked, but I am actually in a loss why it happens in the first place. I don't think that the table should have been locked at all?? Other users do only complex SELECTS on the tables... Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexing unknown words with Tsearch2
Hi, First of all, excuse my poor english :) I'm working on a fulltext database with tsearch2, which contains french historical writings. I'm using the fr_ispell dictionnary that can be found here : http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ (ispell-french.tar.gz http://www.sai.msu.su/%7Emegera/postgres/gist/tsearch/V2/dicts/ispell/ispell-french.tar.gz - submitted by Max Jacob) The database encoding is LATIN1 The problem is the writings contains many names of personnalities. For example : Churchill (the database covers WWII). But when I try to search for these names, nothing is found. I tried many things, like this introduction : http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html And I think the problem's root is that no lexem is found (I could even say an empty lexem is found). With the default en_stem dictionnary, I get this : SELECT lexize('en_stem', 'churchill'); {churchil} Then, I try to add the french dictionnary : INSERT INTO pg_ts_dict (SELECT 'fr_ispell', dict_init, 'DictFile=/home/.../french.dict,' 'AffFile=/home/.../french.aff,' 'StopFile=/home/.../french.stop', dict_lexize FROM pg_ts_dict WHERE dict_name = 'ispell_template'); And the result is : SELECT lexize('fr_ispell', 'churchill'); My questions are : - Is it OK to give empty string as a result for a word that is not in the dictionnary, neither in the stop words ? - Is there a way to get the word itself as a result, when the word is not in the dictionnary, neither in the stop words ? - If yes, how ? I'm also interested in any information you could give me... Many thanks ! Greg Maitrallain. -- 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] Indexing unknown words with Tsearch2
Greg Maitrallain greg.maitrall...@evodia.fr writes: The problem is the writings contains many names of personnalities. For example : Churchill (the database covers WWII). But when I try to search for these names, nothing is found. I tried many things, like this introduction : http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html And I think the problem's root is that no lexem is found (I could even say an empty lexem is found). I think you've misconfigured your dictionary list. You normally want to use an ispell dictionary together with some other one, like a snowball dictionary. Using it by itself means exactly that only words known to the dictionary will be indexed. 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] string_to_array with empty input
On Tue, Mar 31, 2009 at 05:08:45PM +0100, Greg Stark wrote: Both interpretations are clearly consistent but it depends on whether you think it's a bunch of text strings concatenated together or if it's a list of objects. The example of string_to_array('',',')::int[] is relevant to this point. The whole there's one empty element only makes sense if you're thinking in terms of string processing. If it's a list of any other kind of object it probably doesn't make sense; you can't say there's one empty integer or one empty composite object or one empty anything else. I think this is about the only sensible option, but my reasoning is somewhat different. My original intuition was that array_to_string and string_to_array should be (perfect) inverses of each other. Unfortunately I can't see any way to get this to happen; zero length arrays or NULL elements in the array mean than array_to_string isn't injective. This means that the composition of the two functions won't result in an injective function and my original premise is false. Note that as far as I can tell string_to_array is injective. I'm assuming that the delimiter won't appear as part of an element of the array; e.g. an array of integers and space as a delimiter is OK, but using the same delimiter with unconstrained text is not OK, a blank delimiter is never OK as it's always part of a string. Injective means there exists more than one array that encodes to the same string. The examples being how do you sanely encode '{}' and '{NULL}' in a unique way; '{}' is a bad example because it's just an artifact of how strings are represented. The complications needed to allow this to happen would make it a very similar function as the array's normal output_function function and hence wouldn't serve a useful purpose. All of this implies that we have to make a compromise somewhere. The semantics that most closely match the existing behaviour would be; for array_to_string: 1) remove NULL values from input array 2) call output_function on remaining elements 3) intersperse[1] the delimiter between the remaining elements 4) concatenate the resulting array for string_to_array: 1) check if input is zero length; return empty array 2) split array based on delimiter and return Note that both functions are STRICT; i.e. a NULL for either parameter should cause the function to return NULL. Arguably in string_to_array it could be non-strict if the input string is empty, but it's probably worth keeping it strict to simplify the semantics. Here are some examples: array_to_string('{}'::TEXT[],',') = '' array_to_string('{NULL}'::TEXT[],',') = '' array_to_string('{NULL,NULL}'::TEXT[],',') = '' array_to_string('{a,NULL}'::TEXT[],',')= 'a' array_to_string('{NULL,a}'::TEXT[],',')= 'a' array_to_string('{a,b}'::TEXT[],',') = 'a,b' array_to_string('{a,NULL,b}'::TEXT[],',') = 'a,b' string_to_array('',',') = '{}' string_to_array(' ',',')= '{ }' string_to_array(',',',')= '{,}' string_to_array('a',',')= '{a}' string_to_array('a,',',') = '{a,}' string_to_array(',a',',') = '{,a}' string_to_array('a,b',',') = '{a,b}' My thinking before was that it should be doing: string_to_array('',',') = '{}' instead, but I now think that Greg has a point and these are nicer/more generally useful semantics. Hum, that all got a bit more verbose than I was expecting. Ah well, I hope it's somewhat useful. -- Sam http://samason.me.uk/ [1] as in the intersperse function in Haskell http://www.haskell.org/onlinereport/list.html#sect17.3 intersperse # [a, bar] == [a, #, bar] note that here we're working with arrays of string, rather than arrays of characters as in the report. -- 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] Postgres: Packaging Server Startup
Hi, I did not run the initdb.exe and thats why i could not start the server.Here is what i did: 1. Executed initdb.exe as follows: runas /user:postgres D:\pgsql\bin\initdb.exe -D:\pgsql\data 2.Started the server by executing the following cmd: runas /user:postgres D:\pgsql\bin\pg_ctl.exe -w start -D D:\pgsql\data and it started fine.Thanks to Richard and Ray ! On Tue, Mar 31, 2009 at 6:13 PM, Raymond O'Donnell r...@iol.ie wrote: On 31/03/2009 12:14, CM J wrote: D:\pgsql\bin\pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data I wonder if that -P is causing trouble? According to the output of --help, there's no -P option with a START operation; however, -p is used to specify the path to the postgres binaries. Just a stab in the dark. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
[GENERAL] Statement trigger on delete object OID
Hello, Is there any way to get some attributes values of the first object involved in a delete on cascade group inside a trigger definition? I wish I could fire a trigger after a delete on cascade has been done but I need some informations which are linked to the first element deleted. Using a trigger on each row could give access to the OLD object but firing a trigger on a cascade relation may be a performance killer. Any clue ? Regards, Laurent -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark st...@enterprisedb.com wrote: On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason s...@samason.me.uk wrote: string_to_array('',',')::INT[] = invalid input syntax for integer: Oof. That's a good point. +1. I find this argument much more compelling than anything else that's been offered up so far. ...Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Retain PREPARE or connect trigger
I am using rsyslog (a syslog to database application) to connect to my postgresql database. It then executes Insert after Insert 100s a minute I have been reading about the PREPARE statement and think that could dramatically increase my insert speed. The problem is, as you all know, that PREPARE only works for the session that it was executed. I cannot make rsyslog prepare the statement when it start's it's session, so how can I use the ability of PREPARE in this situation? I was thinking maybe that there would be away to trigger the prepare statement when the rsyslog user connects. Or is there another option like PREPARE that persists? Thanks for your help. Justin Funk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Retain PREPARE or CONNECT TRIGGER
I am using rsyslog (a syslog to database application) to connect to my postgresql database. It then executes Insert after Insert 100s a minute I have been reading about the PREPARE statement and think that could dramatically increase my insert speed. The problem is, as you all know, that PREPARE only works for the session that it was executed. I cannot make rsyslog prepare the statement when it start's it's session, so how can I use the ability of PREPARE in this situation? I was thinking maybe that there would be away to trigger the prepare statement when the rsyslog user connects. Or is there another option like PREPARE that persists? Thanks for your help. Justin Funk -- 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] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
Believe it or not, this morning I found that the IT departement has installed Trend Micro Office Scan on the server. I will contact them to remove it. Do I still need to dump everything thing and load back or this will solve the problem? If I need to dump, what type of dump do you recommend? On Tue, Mar 31, 2009 at 8:52 PM, Craig Ringer cr...@postnewspapers.com.auwrote: Patrick Desjardins wrote: I am on Windows Server 2003 and humm I will have to check tommorow morning but I do not think any Anti-Virus is scanning. Sometimes even an antivirus package that has its realtime protection features disabled will still cause problems. This comes back to what Scott Marlowe said: A lot of anti-virus packages are dumb as a brick. They often fail to unload hook DLLs when resident protection is disabled, and sometimes even keep on scanning, just ignoring the results! (I've seen this multiple times). I remain of the opinion that antivirus software has no place on a database server. There should be no way a virus can get near it, because you're NEVER granting users access to it except via the database engine, and the only hole in the Windows Firewall should be for the database. Since this issue keeps on cropping up, I wonder how the other DB vendors that support Windows handle it? Do antivirus products have standard APIs for exceptions - don't scan me ? If so, isn't that a gaping security hole? And if not, how do other DBs manage to get anything done when some half-wit dodgy AV software is installed? Or do the other folks (Oracle etc) just have these sorts of issues too? Proposed FAQ entry: - Q: I'm getting weird, intermittent errors when starting PostgreSQL or executing SQL statements. My PostgreSQL server runs on Windows. A(1): If you are running a version of PostgreSQL less than 8.3, upgrade. Remember to dump your database (you can use PgAdmin for this) BEFORE uninstalling the old version of PostgreSQL. A(2): If you have any antivirus software installed, COMPLETELY UNINSTALL it (at least as a test to see if it is the problem). Many anti-virus packages are written without considering the needs of databases, and do things that will interfere with the way a database accesses its files. Some have implementation problems that mean that even disabling their real-time protection is insufficient, since they STILL interfere with the database even when supposedly disabled. Lots of AV packages also cause severe performance problems with a database even when they appear to work fine. To see if your antivirus software is causing your problems, completely uninstall it and reboot your computer before re-testing. Q: I'm getting inexplicable network connection errors or network performance problems with PostgreSQL. My PostgreSQL server runs on Windows. A(1): If you are running a version of PostgreSQL less than 8.3, upgrade. Remember to dump your database (you can use PgAdmin for this) BEFORE uninstalling the old version of PostgreSQL. A(2): If you have any 3rd party firewall software installed, COMPLETELY UNINSTALL it. Disabling it is not good enough, as many firewall packages continue to interfere with Windows' networking even when disabled. 3rd party firewall packages should not be necessary on any version of Windows with a built-in firewall, and tend to cause more problems than they solve. They are unsuitable for use on a machine intended for server use. bIf, after uninstalling your firewall, you lose your network connection or have other networking problems/b, run the following command: netsh ip interface reset %HOMEPATH%\Desktop\resetlog.txt which should clean up any mess left by the poorly written firewall package's failure to cleanly uninstall its self. [Needs link to section in server admin docs PostgreSQL Server Administration for Windows that discusses AV scanning, isolated server, firewall, datadir location, permissions, etc - I'm happy to write at least a basic version of this if folks here agree it'd be useful.] - -- Craig Ringer
[GENERAL] Re: [GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX0 01: could not read block 2354 of relation…
On Wed, Apr 1, 2009 at 6:37 AM, Patrick Desjardins mrdesjard...@gmail.com wrote: Believe it or not, this morning I found that the IT departement has installed Trend Micro Office Scan on the server. I will contact them to remove it. Do I still need to dump everything thing and load back or this will solve the problem? If I need to dump, what type of dump do you recommend? I personally wouldn't trust the data on that server anymore, since it's possible some other files have managed to get corrupted. I'd restore from a known good backup. Then I'd go box the IT guys who put anti-virus on your db server about the ears. Sounds like someone's IT needs to learn the basics of Change Management. -- 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] ERROR: XX001: could not read block 235 4 of relation…
2009/3/31 Patrick Desjardins mrdesjard...@gmail.com: Hi, I have the error ERROR: XX001: could not read block 2354 of relation 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying to Insert data into a table. I would say that 99% of Insert works and 100% of read works. This is only happenning since few weeks. I have done Vaccum Analyze without any success (the vaccum take 65 minutes but nothing is fixed, still have the ERROR XXX001). I have this error in the backend application and when I do some Insert Query in the PgAdmin tool. This kind of error occurs generally on 7.x versions or if you are using fsync =off and you have a surprised system down. Try to do a pg_dump of the database or clean (no delete) del wal files (this is not recommendable for new users). What can I do to fix that problem? Thank you, -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- 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] ERROR: XX001: could not read block 235 4 of relation…
2009/4/1 Patrick Desjardins mrdesjard...@gmail.com: We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup because weeks have pass since the first error occurs. I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump like you suggest. In 7.x versions, i declare a var in postgresql.conf (zero_damaged_pages = on ) and make the dump with this option. I don't know if this var is in newly versions. Did you try to make a reindex? -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- 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] ERROR: XX001: could not read block 235 4 of relation…
2009/4/1 Emanuel Calvo Franco postgres@gmail.com: 2009/4/1 Patrick Desjardins mrdesjard...@gmail.com: Did you try to make a reindex? If you get an error with that, try to drop indexes and create again. -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Robert Haas robertmh...@gmail.com writes: On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark st...@enterprisedb.com wrote: On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason s...@samason.me.uk wrote: string_to_array('',',')::INT[] = invalid input syntax for integer: Oof. That's a good point. +1. I find this argument much more compelling than anything else that's been offered up so far. Yeah. It seems to me that if you consider only the case where the array elements are text, there's a weak preference for considering '' to be a single empty string; but as soon as you think about any other datatype, there's a strong preference to consider it a zero-element list. So I too have come around to favor the latter interpretation. Do we have any remaining holdouts? 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
[GENERAL] consulta demasiado grande
hola Tengo una interfase web, para ejecutar consultas a una base de datos, pero tiene millones de campos, es el contenido de las trazas de los cisco de la red , la consulta la acoto por fecha, este es mi trabajo de grado , y no me queda mucho tiempo, si alguien pudiera decirme como puedo ejecutar estas consultas para listar el contenido en la web Muchas gracias -- Participe en Universidad 2010, del 8 al 12 de febrero del 2010 La Habana, Cuba http://www.universidad2010.cu http://www.universidad2010.com
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 9:02 AM, Tom Lane wrote: +1. I find this argument much more compelling than anything else that's been offered up so far. Yeah. It seems to me that if you consider only the case where the array elements are text, there's a weak preference for considering '' to be a single empty string; but as soon as you think about any other datatype, there's a strong preference to consider it a zero-element list. So I too have come around to favor the latter interpretation. Do we have any remaining holdouts? Well, I'd just point out that the return value of string_to_array() is text[]. Thus, this is not a problem with string_to_array(), but a casting problem from text[] to int[]. Making string_to_array() return a NULL for this case to make casting simpler is addressing the problem in the wrong place, IMHO. If I want to do this in Perl, for example, I'd do something like this: my @ints = grep { defined $_ $_ ne '' } split ',', $string; So I split the string into an array, and then remove unreasonable values. This also allows me to set defaults: my @ints = map { $_ || 0 } split ',', $string; This ensures that I get the proper number of records in the example of something like '1,2,,4'. So I still think that string_to_array('', ',') should return '{}', and how casting is handled should be left to the user to flexibly handle. That said, I'm not seeing a simple function for modifying an array. I'd have to write one for each specific case. :-( Best, David -- 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] ERROR: XX001: could not read block 235 4 of relation…
I have not reindexes. I will try to use the Reindex command ( http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html). If I get error I will try to drop them. If it doesn't solve I will pg_dump. I still need to wait the IT to remove the Anti-virus. Will give you more news later. On Wed, Apr 1, 2009 at 11:57 AM, Emanuel Calvo Franco postgres@gmail.com wrote: 2009/4/1 Emanuel Calvo Franco postgres@gmail.com: 2009/4/1 Patrick Desjardins mrdesjard...@gmail.com: Did you try to make a reindex? If you get an error with that, try to drop indexes and create again. -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin
[GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation…
We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup because weeks have pass since the first error occurs. I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump like you suggest. On Wed, Apr 1, 2009 at 11:49 AM, Emanuel Calvo Franco postgres@gmail.com wrote: 2009/3/31 Patrick Desjardins mrdesjard...@gmail.com: Hi, I have the error ERROR: XX001: could not read block 2354 of relation 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying to Insert data into a table. I would say that 99% of Insert works and 100% of read works. This is only happenning since few weeks. I have done Vaccum Analyze without any success (the vaccum take 65 minutes but nothing is fixed, still have the ERROR XXX001). I have this error in the backend application and when I do some Insert Query in the PgAdmin tool. This kind of error occurs generally on 7.x versions or if you are using fsync =off and you have a surprised system down. Try to do a pg_dump of the database or clean (no delete) del wal files (this is not recommendable for new users). What can I do to fix that problem? Thank you, -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin
Re: [HACKERS] [GENERAL] string_to_array with empty input
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark st...@enterprisedb.com wrote: On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason s...@samason.me.uk wrote: string_to_array('',',')::INT[] = invalid input syntax for integer: "" Oof. That's a good point. +1. I find this argument much more compelling than anything else that's been offered up so far. Yeah. It seems to me that if you consider only the case where the array elements are text, there's a weak preference for considering '' to be a single empty string; but as soon as you think about any other datatype, there's a strong preference to consider it a zero-element list. So I too have come around to favor the latter interpretation. Do we have any remaining holdouts? regards, tom lane I'm still a hold out, We are taking a string putting it into a array based on a delimiter. That is very simple and straight forward. Yet many argue if we want to cast this into another data type the function should deal with in limited cases. string_to_array('',',')::INT[] works as proposed But string_to_array(',,,', ',' )::INT[] Fails or string_to_array('1,2,,4', ',' )::INT[] Fails . I'm trying to understand the difference between a empty string to a string with many blank entries between the delimiter. Consider ',,' = '' once the delimiter is removed . Yet Seven zero length entries were passed. How is that going to be handled In one case it works and yet other cases it fails. This is inconsistent behavior. Unless all zero length strings are removed or are treated as NULLs I can't see how casting to another type is going to work. If zero length strings are treated as NULLs this creates idea that zero length strings are = to NULLs. The input is a string and the output is text[], casting to another data type is error prone and should be handled by the programmer.
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
On Wed, Apr 1, 2009 at 10:32 AM, Patrick Desjardins mrdesjard...@gmail.com wrote: I have not reindexes. I will try to use the Reindex command (http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html). If I get error I will try to drop them. If it doesn't solve I will pg_dump. I still need to wait the IT to remove the Anti-virus. Will give you more news later. You may be tilting at windmills until they do. I'd go stand behind somebody in IT until they came and fixed it. Seriously. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
David E. Wheeler da...@kineticode.com writes: Well, I'd just point out that the return value of string_to_array() is text[]. True... Thus, this is not a problem with string_to_array(), but a casting problem from text[] to int[]. Nonsense. The question is whether string_to_array is meant to be useful for lists of anything except text. I agree you could argue that it isn't. But even in the domain of text it's not all that cut-and-dried whether string_to_array should return array[] or array[''] for empty input. So ISTM we're giving up less than we gain by choosing the former. 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
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
As far as I know we do not have the paying support. Once I will try everything suggested here, I will try to go in that direction. For the moment, I think all your suggestions might solve the problem. I just need the ok from the IT to start applying some of your suggestions guys. On Wed, Apr 1, 2009 at 12:59 PM, Scott Mead scott.m...@enterprisedb.comwrote: If you are using the EnterpriseDB release, and paying for support, you can always take this to them. Sent from my mobile device -- *From*: Patrick Desjardins *Date*: Wed, 1 Apr 2009 11:53:59 -0400 *To*: Emanuel Calvo Francopostgres@gmail.com *Subject*: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation… We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup because weeks have pass since the first error occurs. I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump like you suggest. On Wed, Apr 1, 2009 at 11:49 AM, Emanuel Calvo Franco postgres@gmail.com wrote: 2009/3/31 Patrick Desjardins mrdesjard...@gmail.com: Hi, I have the error ERROR: XX001: could not read block 2354 of relation 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying to Insert data into a table. I would say that 99% of Insert works and 100% of read works. This is only happenning since few weeks. I have done Vaccum Analyze without any success (the vaccum take 65 minutes but nothing is fixed, still have the ERROR XXX001). I have this error in the backend application and when I do some Insert Query in the PgAdmin tool. This kind of error occurs generally on 7.x versions or if you are using fsync =off and you have a surprised system down. Try to do a pg_dump of the database or clean (no delete) del wal files (this is not recommendable for new users). What can I do to fix that problem? Thank you, -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
If you are using the EnterpriseDB release, and paying for support, you can always take this to them. Sent from my mobile device -Original Message- From: Patrick Desjardins mrdesjard...@gmail.com Date: Wed, 1 Apr 2009 11:53:59 To: Emanuel Calvo Francopostgres@gmail.com Cc: pgsql-general@postgresql.org Subject: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation… We are using EnterpriseDB PostGresql 8.3. I can't simply take a good backup because weeks have pass since the first error occurs. I will try to Vacumm Full first, if nothing is fixed, I will try to pg_dump like you suggest. On Wed, Apr 1, 2009 at 11:49 AM, Emanuel Calvo Franco postgres@gmail.com wrote: 2009/3/31 Patrick Desjardins mrdesjard...@gmail.com: Hi, I have the error ERROR: XX001: could not read block 2354 of relation 1663/17633/17925: read only 0 of 8192 bytes, but only sometime, when trying to Insert data into a table. I would say that 99% of Insert works and 100% of read works. This is only happenning since few weeks. I have done Vaccum Analyze without any success (the vaccum take 65 minutes but nothing is fixed, still have the ERROR XXX001). I have this error in the backend application and when I do some Insert Query in the PgAdmin tool. This kind of error occurs generally on 7.x versions or if you are using fsync =off and you have a surprised system down. Try to do a pg_dump of the database or clean (no delete) del wal files (this is not recommendable for new users). What can I do to fix that problem? Thank you, -- Emanuel Calvo Franco Sumate al ARPUG ! (www.postgres-arg.org - www.arpug.com.ar) ArPUG / AOSUG Member Postgresql Support Admin
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 10:09 AM, Tom Lane wrote: Thus, this is not a problem with string_to_array(), but a casting problem from text[] to int[]. Nonsense. The question is whether string_to_array is meant to be useful for lists of anything except text. I agree you could argue that it isn't. But even in the domain of text it's not all that cut-and-dried whether string_to_array should return array[] or array[''] for empty input. So ISTM we're giving up less than we gain by choosing the former. Yeah. I'm okay with either, as long as it's consistent. I have a mild preference for '{}', but I can live with ARRAY[] instead. As long as it's not NULL that gets returned. Best, David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 10:05 AM, justin wrote: string_to_array('',',')::INT[] works as proposed But string_to_array(',,,', ',' )::INT[] Fails or string_to_array('1,2,,4', ',' )::INT[] Fails . I'm trying to understand the difference between a empty string to a string with many blank entries between the delimiter. Consider ',,' = '' once the delimiter is removed . Yet Seven zero length entries were passed. How is that going to be handled Right, it's making a special case of '', which does seem rather inconsistent to me. Best, David -- 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] SELinux problem rsync'ing WAL logs
Dear Tom, Thanks for your reply and insight! I much appreciate it. I certainly look forward to getting off FC6! In the meantime, I did get it to work - I remembered SELinux protects /home directories especially. So I moved postgres user's home directory from /home/postgres to /data/postgres, and the WAL rsync works now under SELinux. Thanks again! Very helpful! Best, -at -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Wed, Apr 01, 2009 at 10:23:18AM -0700, David E. Wheeler wrote: On Apr 1, 2009, at 10:05 AM, justin wrote: string_to_array('',',')::INT[] works as proposed But string_to_array(',,,', ',' )::INT[] Fails or string_to_array('1,2,,4', ',' )::INT[] Fails . I'm trying to understand the difference between a empty string to a string with many blank entries between the delimiter. Consider ',,' = '' once the delimiter is removed . Yet Seven zero length entries were passed. How is that going to be handled Right, it's making a special case of '', which does seem rather inconsistent to me. Yes it is; but it's a useful special case because it allows: string_to_array(array_to_string(col,','),',') to do the right thing whether it's got zero or more elements in. With the current implementation you get a NULL back in the case of zero elements and the expected array back the rest of the time. To me, it doesn't really matter whether: string_to_array(',', ',' )::INT[] fails or not; because array_to_string will never generate a string that looks like this. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Wed, Apr 1, 2009 at 6:23 PM, David E. Wheeler da...@kineticode.com wrote: Right, it's making a special case of '', which does seem rather inconsistent to me. David E. Wheeler da...@kineticode.com writes: On Apr 1, 2009, at 10:05 AM, justin wrote: string_to_array('',',')::INT[] works as proposed But string_to_array(',,,', ',' )::INT[] Fails or string_to_array('1,2,,4', ',' )::INT[] Fails . I'm trying to understand the difference between a empty string to a string with many blank entries between the delimiter. Well, uh, in one case it's empty and in the other case it's not? Consider ',,' = '' once the delimiter is removed . Yet Seven zero length entries were passed. How is that going to be handled Well it's pretty clear empty delimiters cannot be handled consistently. Some languages handle them as a special case (splitting every character into a separate string, for example -- which I'll point out will result in an empty array as a result for an empty string input) or make it an error. Right, it's making a special case of '', which does seem rather inconsistent to me. It's not a special case -- or it's a special case whichever we choose, depending on which way you look at it. What we're talking about here is replacing the blank values in the following tables. We can get either the first one right in both cases with {} as the result, or we can get the second one right in the second table with {}. Either way there is an inconsistency in at least one case. The existing behaviour of returning NULL is the only consistent choice since the correct value is unknown. And one could argue that it's easier to replace NULL with the correct value if the programmer knows using coalesce than it is to replace either or {}. But I'm still leaning to thinking that using an arbitrary choice that at least gets most users intentions is better. postgres=# select input, string_to_array(array_to_string(input,','),',') as output from (values (array[]::text[]),(array['foo']),(array['foo','bar']),(array['foo','bar','baz'])) as input(input); input |output ---+--- {}| {foo} | {foo} {foo,bar} | {foo,bar} {foo,bar,baz} | {foo,bar,baz} (4 rows) postgres=# select input, string_to_array(array_to_string(input,','),',') as output from (values (array[]::text[]),(array['']),(array['','']),(array['','',''])) as input(input); input| output + {} | {} | {,}| {,} {,,} | {,,} (4 rows) -- greg -- 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] consulta demasiado grande
Hola, En primer lugar esta es un lista en ingles, más gente podrá ayudarte si escribes en ese idioma. También deberías darnos mas información para poder ayudarte. Un esquema de la BD, que datos quieres obtener, etc. This is an english list, more people can help if you write in english. You should also give us some info about your DB and define which data you want to obtain. Saludos, Agustín On Apr 1, 2009, at 12:56 PM, inf200...@ucf.edu.cu wrote: hola Tengo una interfase web, para ejecutar consultas a una base de datos, pero tiene millones de campos, es el contenido de las trazas de los cisco de la red , la consulta la acoto por fecha, este es mi trabajo de grado , y no me queda mucho tiempo, si alguien pudiera decirme como puedo ejecutar estas consultas para listar el contenido en la web Muchas gracias Participe en Universidad 2010, del 8 al 12 de febrero del 2010 La Habana, Cuba http://www.universidad2010.cu http://www.universidad2010.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] High consumns memory
Scott the problem is that the memory gets higher and higher each PL/SQL procedure call. Some “I don’t know what” is not been freed(released) from the memory after execution. There’s any way that I can see what is allocated and released when the PL/SQL procedure is called or finished ? 2009/3/31 Scott Marlowe scott.marl...@gmail.com On Tue, Mar 31, 2009 at 5:44 PM, Anderson Valadares anderva...@gmail.com wrote: I have a software developed in Delphi as a Windows Service, but, i don't know why, it consumns an unexpected large system memory (1.3g). The service access PostgresSQL by ODBC driver (psqlodbc_08_03_0400) and it consist simply of a loop calling a procedure PL/PGSQL. How to discover what is causing or why this high memory usage ? What objects are being used on this session ? Software developed in Delphi 7 as a windows service. PostgresSQL 8.3.6 Database with PostGis extension Server p52a S.O.: Red Hat Enterprise Linux AS release 4 (Nahant Update 1) linux 2.6.9-11.EL #1 SMP ppc64 ppc64 ppc64 GNU/Linux S.O. information top - 11:39:15 up 6 days, 19:15, 1 user, load average: 2.15, 2.02, 1.86 Tasks: 127 total, 1 running, 126 sleeping, 0 stopped, 0 zombie Cpu(s): 9.5% us, 2.6% sy, 0.0% ni, 71.2% id, 16.3% wa, 0.1% hi, 0.2% si Mem: 4107392k total, 4101520k used, 5872k free,17708k buffers Swap: 2031608k total, 244k used, 2031364k free, 3091708k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 32662 postgres 16 0 1317m 1.3g 516m D 52.5 32.1 349:57.48 postgres 8953 postgres 17 0 548m 482m 479m S 33.5 12.0 2:50.09 postgres 1944 postgres 16 0 550m 520m 516m S 7.3 13.0 165:30.47 postgres 32659 postgres 15 0 544m 516m 514m S 1.3 12.9 16:42.60 postgres 1935 postgres 15 0 543m 514m 513m S 1.0 12.8 15:15.56 postgres This doesn't look bad at all. The pgsql instances are using a pretty reasonable amount of memory for caching (somewhere in the 512Meg range) and one long running query is using a lot more memory (in the 600M range) Your machine has 3G of cache out of 4G of ram, and it's using almost not swap. Now, when this is running next time, using psql, try something like: select * from pg_stat_activity where procpid=32662; or whatever pid is using up a fair chunk of memory to see the query that's doing it.
Re: [GENERAL] High consumns memory
On Wed, Apr 1, 2009 at 12:59 PM, Anderson Valadares anderva...@gmail.com wrote: Scott the problem is that the memory gets higher and higher each PL/SQL procedure call. Some “I don’t know what” is not been freed(released) from the memory after execution. There’s any way that I can see what is allocated and released when the PL/SQL procedure is called or finished ? I think you just aren't familiar with how memory is accounted for in top. Honestly, nothing looks out of place there. Do you know VIRT RES and SHR mean in top? There's a good post here that explains it for the most part: http://www.kdedevelopers.org/node/1445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
I even wrote down the password when I installed the DB and now it doesn't work! I have logged in once to the DB through pgAdmin, and choose to store the password and it said that it was stored in plain text.. where can I find it? in what file?? I even created a DB that I haven't used yet so I am certain I have been in there.. what has happend? Most importantly, where can I find the password if it was stored? / Jennifer
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Wed, Apr 01, 2009 at 07:40:16PM +0100, Greg Stark wrote: The existing behaviour of returning NULL is the only consistent choice since the correct value is unknown. And one could argue that it's easier to replace NULL with the correct value if the programmer knows using coalesce than it is to replace either or {}. Couldn't a similar argument be applied for division by zero? Since it's not known whether the user wants to get a divide by zero exception or infinity PG should return NULL and punt the choice to the user. I think everybody would agree that this would be a bad thing to do! But I'm still leaning to thinking that using an arbitrary choice that at least gets most users intentions is better. I'd agree; returning NULL and not forcing the user to make a choice is a bad design decision---the user doesn't need to put a coalesce in and hence their code will probably break in strange ways when they're not expecting it. Nobody suggest adding a third parameter to string_to_array, please! The general mantra that seems to apply here is one good option is better than two bad ones. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Wed, Apr 1, 2009 at 12:52 PM, David E. Wheeler da...@kineticode.com wrote: Well, I'd just point out that the return value of string_to_array() is text[]. Thus, this is not a problem with string_to_array(), but a casting problem from text[] to int[]. Making string_to_array() return a NULL for this case to make casting simpler is addressing the problem in the wrong place, IMHO. If I want to do this in Perl, for example, I'd do something like this: my @ints = grep { defined $_ $_ ne '' } split ',', $string; I've written code that looks a whole lot like this myself, but there's no easy way to do that in SQL. SQL, in particular, lacks closures, so grep {} and map {} don't exist. I really, really wish they did, but I believe that our type system is too woefully pathetic to be up to the job. So it seems to me that arguing that SQL (which lacks those primitives) should match Perl (which has them) isn't really getting us anywhere. my @ints = map { $_ || 0 } split ',', $string; This ensures that I get the proper number of records in the example of something like '1,2,,4'. I can't see that there's any way to do this in SQL regardless of how we define this operation. ...Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Wed, Apr 1, 2009 at 1:05 PM, justin jus...@emproshunts.com wrote: I'm still a hold out, We are taking a string putting it into a array based on a delimiter. That is very simple and straight forward. Yet many argue if we want to cast this into another data type the function should deal with in limited cases. string_to_array('',',')::INT[] works as proposed But string_to_array(',,,', ',' )::INT[] Fails or string_to_array('1,2,,4', ',' )::INT[] Fails . But... but... those aren't comma-separated lists of integers. If they were, it would work. string_to_array('cow,dog,horse')::INT[] will also fail. If you take 0 items of any type whatsoever and join them together with commas, you will get the empty string. It is also true that if you join 1 item together with commas, you will get that item back, and if that item is the empty string, you will now have the empty string. I think it's better to worry more about the first case because it applies to any type at all, whereas the latter case ONLY applies in situations where the empty string is a potentially legal value. ...Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] %r in restore_command?
Hi, Please allow me to rephrase a question I asked on this list some time ago. Could somebody shine some light on what exactly influences the value of the %r parameter in the restore_command (as used in recovery.conf)? I'm using this in a hot-standby-configuration in combination with pg_standby and _sometimes_ my archive on shipped transaction logs grow really huge. The value of %r then never changes any more in subsequent calls of the restore_command, causing pg_standby to not delete any WAL segment anymore. Thanks, Duco -- 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: Installed PG with pgAdmin, some days later, now my password don't work!
It is still stored..and I got hold of it.. I used it and it is not workin!? What could be the problem? How should I log in? See the picture I attached... On Wed, Apr 1, 2009 at 10:16 PM, Jennifer Trey jennifer.t...@gmail.comwrote: I even wrote down the password when I installed the DB and now it doesn't work! I have logged in once to the DB through pgAdmin, and choose to store the password and it said that it was stored in plain text.. where can I find it? in what file?? I even created a DB that I haven't used yet so I am certain I have been in there.. what has happend? Most importantly, where can I find the password if it was stored? / Jennifer pgadmin.log Description: Binary data -- 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] Installed PG with pgAdmin, some days later, now my password don't work!
On Wed, Apr 1, 2009 at 3:16 PM, Jennifer Trey jennifer.t...@gmail.com wrote: I even wrote down the password when I installed the DB and now it doesn't work! I have logged in once to the DB through pgAdmin, and choose to store the password and it said that it was stored in plain text.. where can I find it? in what file?? I even created a DB that I haven't used yet so I am certain I have been in there.. what has happend? Most importantly, where can I find the password if it was stored? / Jennifer It's in the docs http://www.postgresql.org/docs/8.3/static/libpq-pgpass.html Cheers, diego -- 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] Installed PG with pgAdmin, some days later, now my password don't work!
On 01/04/2009 20:16, Jennifer Trey wrote: Most importantly, where can I find the password if it was stored? It's in a file called pgpass.conf - on Windows, this is stored in the Application Data\postgresql directory under your profile. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Re: Installed PG with pgAdmin, some days later, now my password don't work!
On 01/04/2009 20:38, Jennifer Trey wrote: It is still stored..and I got hold of it.. I used it and it is not workin!? What could be the problem? How should I log in? See the picture I attached... Well, the error message says that the role under which you;re trying to log in doesn't exist, which seems plain enough - have you checked that it does? Would you or someone else have zapped it? How are you logging in? - through pgAdmin, from psql, or from some other client? If from psql, are you using the -U parameter? If you don't, psql tried to use the currently logged-in OS user. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
Yes, I found it.. but I cannot log in? Is there any simple way just to scratch the server and add a new one? thru pgAdmin please? I tried to Add Server but it requires a password too!? and thats not working either.. why does a new server require a new password? To Raymonds last, I am using pgAdmin, and that file I accidentally attached instead of an image I was planning to attach.. On Wed, Apr 1, 2009 at 10:40 PM, Raymond O'Donnell r...@iol.ie wrote: On 01/04/2009 20:16, Jennifer Trey wrote: Most importantly, where can I find the password if it was stored? It's in a file called pgpass.conf - on Windows, this is stored in the Application Data\postgresql directory under your profile. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
Re: [HACKERS] [GENERAL] string_to_array with empty input
If someone can show me a real world example this logic simplifies the code and has more uses I'll bite I just presently can't see how this works better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
Sorry, for the confusion.. just want to make something I wrote more clear: I tried to add a server and it required the use of a password and not to add a password.. upon the creation and connection, it says that it failed. The Server gets created but I cannot log in to it, even though its new... and also, I found it (the password) but I cannot log in with it anyway... / Jennifer -- Forwarded message -- From: Jennifer Trey jennifer.t...@gmail.com Date: Wed, Apr 1, 2009 at 10:47 PM Subject: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work! To: pgsql-general@postgresql.org Yes, I found it.. but I cannot log in? Is there any simple way just to scratch the server and add a new one? thru pgAdmin please? I tried to Add Server but it requires a password too!? and thats not working either.. why does a new server require a new password? To Raymonds last, I am using pgAdmin, and that file I accidentally attached instead of an image I was planning to attach.. On Wed, Apr 1, 2009 at 10:40 PM, Raymond O'Donnell r...@iol.ie wrote: On 01/04/2009 20:16, Jennifer Trey wrote: Most importantly, where can I find the password if it was stored? It's in a file called pgpass.conf - on Windows, this is stored in the Application Data\postgresql directory under your profile. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
Re: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
By the way, yesterday I used the TuningWizard too, could it have changed some of these things? It does create a new config file.. is it possible? On Wed, Apr 1, 2009 at 10:50 PM, Jennifer Trey jennifer.t...@gmail.comwrote: Sorry, for the confusion.. just want to make something I wrote more clear: I tried to add a server and it required the use of a password and not to add a password.. upon the creation and connection, it says that it failed. The Server gets created but I cannot log in to it, even though its new... and also, I found it (the password) but I cannot log in with it anyway... / Jennifer -- Forwarded message -- From: Jennifer Trey jennifer.t...@gmail.com Date: Wed, Apr 1, 2009 at 10:47 PM Subject: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work! To: pgsql-general@postgresql.org Yes, I found it.. but I cannot log in? Is there any simple way just to scratch the server and add a new one? thru pgAdmin please? I tried to Add Server but it requires a password too!? and thats not working either.. why does a new server require a new password? To Raymonds last, I am using pgAdmin, and that file I accidentally attached instead of an image I was planning to attach.. On Wed, Apr 1, 2009 at 10:40 PM, Raymond O'Donnell r...@iol.ie wrote: On 01/04/2009 20:16, Jennifer Trey wrote: Most importantly, where can I find the password if it was stored? It's in a file called pgpass.conf - on Windows, this is stored in the Application Data\postgresql directory under your profile. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals --
Re: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work!
On 01/04/2009 20:52, Jennifer Trey wrote: By the way, yesterday I used the TuningWizard too, could it have changed some of these things? It does create a new config file.. is it possible? On Wed, Apr 1, 2009 at 10:50 PM, Jennifer Trey jennifer.t...@gmail.comwrote: Sorry, for the confusion.. just want to make something I wrote more clear: I tried to add a server and it required the use of a password and not to add a password.. upon the creation and connection, it says that it failed. The Server gets created but I cannot log in to it, even though its new... Without meaning to add to your woes, could you be persuaded to avoid top-posting, on this list at least? - it makes the flow of discussion really hard to follow. Thanks! :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Server Performance
chris.el...@shropshire.gov.uk wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote on 01/04/2009 06:53:07: chris.el...@shropshire.gov.uk wrote: Scott Marlowe scott.marl...@gmail.com wrote on 31/03/2009 15:53:34: On Tue, Mar 31, 2009 at 8:21 AM, chris.el...@shropshire.gov.uk wrote: Scott Marlowe scott.marl...@gmail.com wrote on 31/03/2009 15:16:01: I'd call IBM and ask them to come pick up their boat anchors. My sentiments exactly, unfortunately, I seem stuck with them :( Can you at least source your own RAID controllers? Yes I will be, I never really did trust IBM and I certainly don't now! I just need to choose the correct RAID card now, good performance at the right price. you are jumping to conclusions too quickly - while the 8k is not the worlds fastest raid card available it is really not (that) bad at all. we have plenty of x3650 in production and last time I tested I was easily able to get 2000tps even on an untuned postgresql install and with fwer disks. Could you provide any more information upon your configurations if possible, please? x3650, dual quadcore Xeon 5430. Servraid 8k with 256MB-BBWC and likely RAID6 during that testing. OS was/is debian etch/amd64. Don't have the exact (pgbench) test parameters handy anymore though... So I really think you are looking at another problem here (be it defective hardware or a driver/OS level issue). Hardware is always a possiblity, finally managed to get hold of IBM too. I have tried two different Linux distro's, with different kernels, My current Mandriva test using a fairly upto date kernel. I may try a custom kernel. also test with different IO schedulers(especially deadline and noop). Stefan -- 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] Installed PG with pgAdmin, some days later, now my password don't work!
On Wed, Apr 1, 2009 at 3:50 PM, Jennifer Trey jennifer.t...@gmail.com wrote: Sorry, for the confusion.. just want to make something I wrote more clear: I tried to add a server and it required the use of a password and not to add a password.. upon the creation and connection, it says that it failed. The Server gets created but I cannot log in to it, even though its new... and also, I found it (the password) but I cannot log in with it anyway... / Jennifer -- Forwarded message -- From: Jennifer Trey jennifer.t...@gmail.com Date: Wed, Apr 1, 2009 at 10:47 PM Subject: [GENERAL] Installed PG with pgAdmin, some days later, now my password don't work! To: pgsql-general@postgresql.org Yes, I found it.. but I cannot log in? Is there any simple way just to scratch the server and add a new one? thru pgAdmin please? I tried to Add Server but it requires a password too!? and thats not working either.. why does a new server require a new password? To Raymonds last, I am using pgAdmin, and that file I accidentally attached instead of an image I was planning to attach.. To be able to log in with pgAdmin, you have to configure the postgres server to listen in a TCP socket. To do this, you have to edit its configuration file (postgresql.conf), located probably in the postgresql installation directory (not sure as I don't use windows, might be slightly different). You just have to add a single line listen_addresses = '*' then restart the service and pgAdmin should be able to log in. -- 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] Server Performance
On Wed, Apr 1, 2009 at 2:01 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: also test with different IO schedulers(especially deadline and noop). But wasn't the OP getting something like 6 tps? I mean, something is so horrifically wrong a simple change like the io scheduler can't hope to fix things. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Split strings into array elements using provided delimiter string_to_array('xx~^~yy~^~zz', '~^~') output: {xx,yy,zz} http://www.postgresql.org/docs/8.3/interactive/functions-array.html ? Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. Date: Wed, 1 Apr 2009 15:49:42 -0400 From: jus...@emproshunts.com To: robertmh...@gmail.com CC: t...@sss.pgh.pa.us; st...@enterprisedb.com; s...@samason.me.uk; pgsql-general@postgresql.org; pgsql-hack...@postgresql.org Subject: Re: [HACKERS] [GENERAL] string_to_array with empty input If someone can show me a real world example this logic simplifies the code and has more uses I'll bite I just presently can't see how this works better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Rediscover Hotmail®: Get e-mail storage that grows with you. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009
Re: [HACKERS] [GENERAL] string_to_array with empty input
Martin Gainty wrote: Split strings into array elements using provided delimiter string_to_array('xx~^~yy~^~zz', '~^~') output: {xx,yy,zz} http://www.postgresql.org/docs/8.3/interactive/functions-array.html Sorry thats not the question i'm asking. We are debating if it makes sense to change the output in certain cases. I'm for not returning nulls or returning zero element array. I'm asking how is the other better by giving a real world example??? I don't see the plus side at the moment.
Re: [HACKERS] [GENERAL] string_to_array with empty input
Robert Haas robertmh...@gmail.com writes: If you take 0 items of any type whatsoever and join them together with commas, you will get the empty string. It is also true that if you join 1 item together with commas, you will get that item back, and if that item is the empty string, you will now have the empty string. I think it's better to worry more about the first case because it applies to any type at all, whereas the latter case ONLY applies in situations where the empty string is a potentially legal value. I'm starting to vacillate again. It's clear that for the purposes of string_to_array, an empty input string is fundamentally ambiguous: it could mean a list of no things, or a list of one empty thing. So the two cases in which an application can safely make use of this function are (1) if lists of no things never happen. (2) if lists never contain empty things. Either rule allows us to resolve the ambiguity. We've been discussing the fact that (2) is an okay assumption for many non-text data types, but none-the-less string_to_array is in itself a text function and (2) is not very good for text. Making this worse, the format *appears* to work fine for empty strings, so long as you don't have exactly one of them. So it seems like applications might be much more likely to violate (2) than (1). Another way to state the point is that we can offer people a choice of two limitations: string_to_array doesn't work for zero-length lists, or string_to_array doesn't work for empty strings (except most of the time, it does). The former is sounding less likely to bite people unexpectedly. Or we could stick to the current behavior and say use COALESCE() to resolve the ambiguity, if you need to. 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: [HACKERS] [GENERAL] string_to_array with empty input
On Wed, Apr 1, 2009 at 5:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Or we could stick to the current behavior and say use COALESCE() to resolve the ambiguity, if you need to. If there's no consensus on changing the behavior, it's probably better to be backward compatible than not. ...Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Tom Lane wrote: I'm starting to vacillate again. It's clear that for the purposes of string_to_array, an empty input string is fundamentally ambiguous: it could mean a list of no things, or a list of one empty thing. Agreed. Of the two, a list of one empty thing makes string_to_array closer to an inverse of array_to_string. Or we could stick to the current behavior and say use COALESCE() to resolve the ambiguity, if you need to. Currently string_to_array(null, ',') yields a null result - indistinguishable from string_to_array('',','). Wrapping in coalesce does not help distinguish true null input from empty-string input. I'm not sure at the moment what other cases exist where non-null input generates null output. If the decision is to leave the behavior unchanged, it at least cries out for a documentation patch. 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: [HACKERS] [GENERAL] string_to_array with empty input
On Thursday 2. April 2009, Steve Crawford wrote: Currently string_to_array(null, ',') yields a null result - indistinguishable from string_to_array('',','). Wrapping in coalesce does not help distinguish true null input from empty-string input. I'm not sure at the moment what other cases exist where non-null input generates null output. Somehow this reminds me of the old division by zero problem. IMO, the proper way to handle this kind of anomaly would be to test if the length of the string is non-zero before submitting it to the string_to_array() function. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] possible small contribution to the PostgreSQL manual? Example for two-phase commit section.
Hi. We're trying to implement two-phase commit and did not find a complete working example in the manual. We found examples of the separate pieces, but not the sequence in which to put them together. Then we found this text, PREPARE TRANSACTION is used in place of regular COMMIT to perform the 1st phase, COMMIT PREPARED and ROLLBACK PREPARED perform the final 2nd phase commit or abort. I'd like to offer two examples to illustrate that, for possible inclusion in the manual: 1. Here's the sequence that two-phase commits: BEGIN; update mytable set a_col='something' where red_id=1000; PREPARE TRANSACTION 'foobar'; COMMIT PREPARED 'foobar'; 2. Here's the sequence that rolls back, leaving the table unchanged: BEGIN; update mytable set a_col='something' where red_id=1000; PREPARE TRANSACTION 'foobar'; ROLLBACK PREPARED 'foobar'; If there is somebody on this list involved with editing the manual, this message is for you. :)Examples make new things clearer, and easier to learn. Just a suggestion. :) Best, Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general