Re: [GENERAL] running two servers on one machine
... as a reminder, this is running on mac os 10.5 Eric On Mar 30, 2009, at 7:53 PM, Eric Smith wrote: Ahhh! Can't seem to make this go away! Here is the log file entry: FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5432001, size=4030464, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 4030464 bytes), reduce PostgreSQL's shared_buffers parameter (currently 300) and/or its max_connections parameter (currently 23). The PostgreSQL documentation contains more information about shared memory configuration. Following the queue on the postgres web page, I created /etc/ sysctl.conf and added the following five entries: kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 But when I try to start two servers, I get the same error. Should I be bumping these numbers up? Eric On Mar 30, 2009, at 7:18 PM, Tom Lane wrote: Eric Smith eric_h_sm...@mac.com writes: Log file says could not create shared memory segment. It also says that I should change max_connections or shared_buffers. Error message claims that max_connections is 23, but postgresql.conf has this listed as 20. The autovacuum workers get added on. Error message claims that shared_buffers is 300, but postgresql.conf has this listed as 2400kB. Same thing, different units. Anyway, you left out the interesting part of the error message, ie the kernel error code. If it's Cannot allocate memory then you probably need to increase SHMALL. 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] running two servers on one machine
Bingo! I doubled each of the entries below, and the two servers are now running together quite happily! Regards, Eric On Mar 30, 2009, at 7:56 PM, Eric Smith wrote: ... as a reminder, this is running on mac os 10.5 Eric On Mar 30, 2009, at 7:53 PM, Eric Smith wrote: Ahhh! Can't seem to make this go away! Here is the log file entry: FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=5432001, size=4030464, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 4030464 bytes), reduce PostgreSQL's shared_buffers parameter (currently 300) and/or its max_connections parameter (currently 23). The PostgreSQL documentation contains more information about shared memory configuration. Following the queue on the postgres web page, I created /etc/ sysctl.conf and added the following five entries: kern.sysv.shmmax=4194304 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=1024 But when I try to start two servers, I get the same error. Should I be bumping these numbers up? Eric On Mar 30, 2009, at 7:18 PM, Tom Lane wrote: Eric Smith eric_h_sm...@mac.com writes: Log file says could not create shared memory segment. It also says that I should change max_connections or shared_buffers. Error message claims that max_connections is 23, but postgresql.conf has this listed as 20. The autovacuum workers get added on. Error message claims that shared_buffers is 300, but postgresql.conf has this listed as 2400kB. Same thing, different units. Anyway, you left out the interesting part of the error message, ie the kernel error code. If it's Cannot allocate memory then you probably need to increase SHMALL. 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
Sorry for top-posting--blame apple.
Hm my first instinct was indeed to make it a zero-length array. I was
thinking of the input as a list and surely there are no elements in
a list which empty. I had to think a while until a length-1 array made
sense.
I suppose the thinking was string_to_array is the inverse of an
array_to_string operation then there are multiple possible answers.
You might have joined a zero length or a singleton array of an empty
string
and since it's unknown which was the original value null is the right
answer...
I agree that picking an arbitrary choice is going to be more useful in
practice though.
--
Greg
On 30 Mar 2009, at 23:26, Tom Lane t...@sss.pgh.pa.us wrote:
Steve Crawford scrawf...@pinpointresearch.com writes:
I have a query that converts a string to an array with the
string_to_array function. Sometimes the input is an empty string
(not a
null, but a string of zero-length). I had expected the result to be a
one-element array with an empty string as the first and only
element but
instead it returned null. I looked at the docs and didn't find the
observed behavior documented.
The behavior is pretty intentional according to the source code:
/* return NULL for empty input string */
if (inputstring_len 1)
{
text_position_cleanup(state);
PG_RETURN_NULL();
}
I agree this seems less than consistent though, especially seeing
that you *don't* get a null for a zero-length separator, which if
anything is a more poorly defined case.
I doubt it'd be a good idea to back-patch a change for this,
but I could see altering the definition for 8.4.
Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
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 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)
My first thought was that it should be a zero-element array, because
then the string_to_array() behaviour would conform to the notion that
it returns an array with 1 element per string fragment bounded by the
delimiter.
However, I note that if you provide an empty delimiter, or one which
doesn't occur anywhere in the source string, you get an array with one
element, being the entire source string.
# select string_to_array('1-2-3', '-');
{1,2,3}
# select string_to_array('1-2-3', 'x');
{1-2-3}
Given this behaviour, I would argue for consistent treatment for a
zero-length source string: it should return an array with one element,
being the entire source string, whenever there is no string splitting
to take place. And if the source string happens to be zero-length,
then the return value would be as expected by the OP.
Cheers,
BJ
--
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] partial TEXT search on an index
Hi, Ow Mun Heng schrieb: I don't think I understand how PG implements fulltext searching or if my search actually needs to use fulltext search. No, that's something different. See the fine manual. basically, I have a btree index on a SERIAL_NUMBER which is of sort like ABC12345 or AAA123434 or AAB131441 I would like to have search on the specific text of the SERIAL_NUMBER eg: select * from table where serial_number LIKE 'AAA%' Depending on the PostgreSQL version and locale you are using chances are, that you need to create another index on that column to make the query use an index. C.f. http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html CREATE INDEX another_index ON your_table (serial_number varchar_pattern_ops); does not use the index. What Do i need to do to have it recognise the partial index? (is it called?) No, that's not a partial index (look at e.g. wiki for an explanation). Ciao, Thomas -- 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] Schema for J2EE project
margaretgil...@chromalloy.com wrote: Using Postgresql 8.2.11 on Ubuntu 7.10. I am writing a J2EE project but do not want user specific schemas because this application sits on a production floor and any production employee can walk to a workstation and access the database to punch work time. I know that the public schema is not supposed to be used. Is the best practice to make one schema for the database and then delete public? One or more. I use schemas to group tables and functions by usage (lookups, util, system) as well as to provide permission control. Will using a schema other than public cause problems for users accessing the database with ODBC from Windows? Shouldn't, but iirc you'll need to explicitly specify schema.table rather than relying on search_path. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres: Packaging Server Startup
Hi, I am working on packaging postgres database in our java based application.I downloaded postgresql-8.2.12-1-binaries-no-installer.zip from the postgres site and installed it on a windows 2003 machine using administrator account.I later created a postgres service login account and changed the permissions on the postgres directory to include this user too. When logged in as administrator , i tried starting the server using the following cmd: pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data However, i get the following error message: waiting for server to start...FATAL: postgres: could not locate matching postgres executable I also tried the starting the server using runas : runas /user:postgres pg_ctl.exe -wait start -D D:\pgsql\data The above too failed.Please let me know on what i am missing here. Thanks.
Re: [GENERAL] Postgres: Packaging Server Startup
CM J wrote: pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data However, i get the following error message: waiting for server to start...FATAL: postgres: could not locate matching postgres executable That does seem to suggest it found the executable but it didn't match versions with the data directory. Do you have two copies installed? If so, you'll probably want to specify the complete path to pg_ctl -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server Performance
Hi Been having interesting times with an IBM x3650 with 8 15k RPM 73GB drives in RAID 10 and a ServRAID 8K controller with Write-Back cache enabled (battery installed and working). Currently getting a pgbench score of 4.7 transactions per second! After playing with the postgresql configuration file, I'm certain that this is not a postgresql problem. I have tried two different Linux distro's upon the server both with the same problems. I'm fairly certain that this is a problem with the hardware configuration / setup, however I'm still waiting for IBM to contact me! Initially I started with the OS on a RAID 1 array and a 6 drive RAID 10 array for postgresql. With this setup I got 3tps, altering the RAID configuration to a single 8 drive array, running both the OS and postgresql. I was able to reach 700tps, however after upgrading to the latest RAID controller firmware this has now fallen back to 4tps. Benchmarking another server I have access to, 4 15k 73GB RPM disks with a Dell Perc 5/i controller. I consistently get a pgbench score of 1400tps. Therefore taking a linear extrapolation I expect the IBM x3650 to manage ~3000tps. Additionally my Laptop with a 5400 RPM sata disk was able to score ~200tps. I have two of these IBM x3650's running the following configurations: 1) IBM x3650 IBM ServRAID controller (Rebranded Adaptec card, using the aacraid driver) 2 15k RPM 73GB RAID 1 (OS array) 6 15k RPM 73GB RAID 10 (Postgresql data array) 2 quad core 3.0GHz Intel Xeons 8 GB ram SuSE Linux Enterprise Server 10 (2.6.16 kernel) Postgresql 8.3.4 (compiled from source) 2) IBM x3650 IBM ServRaid controller (Rebranded Adaptec card, using the aacraid driver) 8 15k RPM 73GB RAID 10 (OS and Postgres data array) 2 quad core 3.0GHz Intel Xeons 8 GB ram Mandriva 2009 Free (2.6.27.19 kernel) Postgresql 8.3.7 As I said, I have the same problem on both machines, I'm expecting that this is caused by the low quality RAID controllers IBM has floged us. I'm interested to find out whether any one out there has had similar problems with IBM ServRAID controllers, or IBM hardware in general? What SAS RAID controllers are people using? What RAID configurations are people using? What SAS RAID controllers would anyone recommend purchasing? Any information is gratefully received Chris Ellis Shropshire Council chris.el...@shropshire.gov.uk ** 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 **
Re: [GENERAL] partial TEXT search on an index
On Tue, 31 Mar 2009, Thomas Pundt wrote:
Hi,
Ow Mun Heng schrieb:
I don't think I understand how PG implements fulltext searching or if my
search actually needs to use fulltext search.
No, that's something different. See the fine manual.
in 8.4 it will be possible to use prefix search in full-text search,
eg, to_tsquery('AAA:*')
basically, I have a btree index on a SERIAL_NUMBER which is of sort like
ABC12345 or AAA123434 or AAB131441
I would like to have search on the specific text of the SERIAL_NUMBER
eg:
select * from table where serial_number LIKE 'AAA%'
Depending on the PostgreSQL version and locale you are using chances
are, that you need to create another index on that column to make the
query use an index. C.f.
http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html
CREATE INDEX another_index ON your_table (serial_number varchar_pattern_ops);
does not use the index. What Do i need to do to have it recognise the
partial index? (is it called?)
No, that's not a partial index (look at e.g. wiki for an explanation).
Ciao,
Thomas
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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 don't have multiple postgres installed.Anyway, tried your suggestion to use the full path name and here is what i get: D:\pgsql\binD:\pgsql\bin\pg_ctl.exe -U postgres -P mypasswd -w start -D:\pgsql\data pg_ctl: could not open PID file :/pgsql/data/postmaster.pid: Invalid argument There is no data directory created by default (i used no-installer-zip file to install postgres), so i went ahead and created a dummy data directory which gave the same result as above. Any suggestions ?? Thanks. On Tue, Mar 31, 2009 at 4:09 PM, Richard Huxton d...@archonet.com wrote: CM J wrote: pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data However, i get the following error message: waiting for server to start...FATAL: postgres: could not locate matching postgres executable That does seem to suggest it found the executable but it didn't match versions with the data directory. Do you have two copies installed? If so, you'll probably want to specify the complete path to pg_ctl -- Richard Huxton Archonet Ltd
Re: [GENERAL] Postgres: Packaging Server Startup
CM J wrote: Hi, I don't have multiple postgres installed. Hmm. OK. Anyway, tried your suggestion to use the full path name and here is what i get: D:\pgsql\binD:\pgsql\bin\pg_ctl.exe -U postgres -P mypasswd -w start -D:\pgsql\data You've got a typo here - the -D has run into D:\... pg_ctl: could not open PID file :/pgsql/data/postmaster.pid: Invalid argument There is no data directory created by default (i used no-installer-zip file to install postgres), so i went ahead and created a dummy data directory which gave the same result as above. Any suggestions ?? It's probably worth checking the permissions on that directory, but I don't think it's the problem here. You have run initdb on that directory though, haven't you? If not, do that before worrying about the rest of this email. pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data However, i get the following error message: waiting for server to start...FATAL: postgres: could not locate matching postgres executable Reading the error message and searching the source-code for it (always worth having a copy of the source, even if you don't read C) I found that the error message isn't being produced by pg_ctl. It's being produced by postgres - the backend itself. It's checking that it's version number matches the version number reported by running /path/to/my/dir/postgres.exe -V (grab the source and have a look in port/exec.c at about line 395). Now, that means one of three things: 1. It can't reach the current directory (permissions) 2. It can't run postgres (permissions) 3. The version-string is incorrect. I don't see how #2 is possible (pg_ctl has started postgres.exe fine) so that leaves #1 or #3. The third is easy to check run postgres.exe -V yourself and see what it says. -- Richard Huxton Archonet Ltd -- 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, Please ignore my previous mail. I had given data directory location incorrectly.Anyways,even after giving the absolute path for exe location, i get the same error message as before: D:\pgsql\bin\pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data *waiting for server to start...FATAL: postgres: could not locate matching postgres executable* Thanks ! On Tue, Mar 31, 2009 at 4:31 PM, CM J postgres.new...@gmail.com wrote: Hi, I don't have multiple postgres installed.Anyway, tried your suggestion to use the full path name and here is what i get: D:\pgsql\binD:\pgsql\bin\pg_ctl.exe -U postgres -P mypasswd -w start -D:\pgsql\data pg_ctl: could not open PID file :/pgsql/data/postmaster.pid: Invalid argument There is no data directory created by default (i used no-installer-zip file to install postgres), so i went ahead and created a dummy data directory which gave the same result as above. Any suggestions ?? Thanks. On Tue, Mar 31, 2009 at 4:09 PM, Richard Huxton d...@archonet.com wrote: CM J wrote: pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data However, i get the following error message: waiting for server to start...FATAL: postgres: could not locate matching postgres executable That does seem to suggest it found the executable but it didn't match versions with the data directory. Do you have two copies installed? If so, you'll probably want to specify the complete path to pg_ctl -- Richard Huxton Archonet Ltd
[GENERAL] Postgres: Packaging Server Startup
Hi, I am working on packaging postgres database in our java based application.I downloaded postgresql-8.2.12-1-binaries-no-installer.zip from the postgres site and installed it on a windows 2003 machine using administrator account.I later created a postgres service login account and changed the permissions on the postgres directory to include this user too. When logged in as administrator , i tried starting the server using the following cmd: pg_ctl.exe -U postgres -P mypasswd -w start -D D:\pgsql\data However, i get the following error message: waiting for server to start...FATAL: postgres: could not locate matching postgres executable I also tried the starting the server using runas : runas /user:postgres pg_ctl.exe -wait start -D D:\pgsql\data The above too failed.Please let me know on what i am missing here. 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] [GENEAL] dynamically changing table
On Mar 30, 2009, at 5:39 PM, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. Ignoring design implications (you got enough replies about that I think)... You could add the columns you're sure that you need and put the rest in an XML field. That field can then contain any custom fields that you don't need right away while you still have the data available in a useful fashion. If it turns out some of those custom fields should end up in the table as a column it isn't hard to extract the data from the XML field. It's probably a good idea to add a version attribute or field to your XML that you increment on each model change (an xsd would be even better, it allows you to specify types for your data) so that you know which fields to expect in the document. You can query XML fields using xpath expressions in your queries. 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,49d20a0c129741113880388! -- 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
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 -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how can i migrate just the users from one db to a new one
I've a DB and I want to migrate the users to a new empty DB. How do I dump/reload just the users? Thanks, reid -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Space for pg_dump
Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? 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] how can i migrate just the users from one db to a new one
On Tuesday 31 March 2009 6:44:52 am Reid Thompson wrote: I've a DB and I want to migrate the users to a new empty DB. How do I dump/reload just the users? Thanks, reid pg_dumpall -g From: http://www.postgresql.org/docs/8.3/interactive/app-pg-dumpall.html -g --globals-only Dump only global objects (roles and tablespaces), no databases. -- Adrian Klaver akla...@comcast.net -- 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 Tue, Mar 31, 2009 at 3:37 AM, chris.el...@shropshire.gov.uk wrote: Hi Been having interesting times with an IBM x3650 with 8 15k RPM 73GB drives in RAID 10 and a ServRAID 8K controller with Write-Back cache enabled (battery installed and working). Currently getting a pgbench score of 4.7 transactions per second! After playing with the postgresql configuration file, I'm certain that this is not a postgresql problem. I have tried two different Linux distro's upon the server both with the same problems. I'm fairly certain that this is a problem with the hardware configuration / setup, however I'm still waiting for IBM to contact me! Initially I started with the OS on a RAID 1 array and a 6 drive RAID 10 array for postgresql. With this setup I got 3tps, altering the RAID configuration to a single 8 drive array, running both the OS and postgresql. I was able to reach 700tps, however after upgrading to the latest RAID controller firmware this has now fallen back to 4tps. Benchmarking another server I have access to, 4 15k 73GB RPM disks with a Dell Perc 5/i controller. I consistently get a pgbench score of 1400tps. Therefore taking a linear extrapolation I expect the IBM x3650 to manage ~3000tps. Additionally my Laptop with a 5400 RPM sata disk was able to score ~200tps. SNIP What SAS RAID controllers are people using? What RAID configurations are people using? What SAS RAID controllers would anyone recommend purchasing? I am using an Areca 1680 series controller. 16 SAS 15k5 disks. 2 RAID-1, 12 RAID-10, 2 hot spares. 512Meg bbu. RHEL 5.2 I can sustain around 3000 tps with pgbench and 30 minute runs. I'd call IBM and ask them to come pick up their boat anchors. -- 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
Scott Marlowe scott.marl...@gmail.com wrote on 31/03/2009 15:16:01: On Tue, Mar 31, 2009 at 3:37 AM, chris.el...@shropshire.gov.uk wrote: Hi Been having interesting times with an IBM x3650 with 8 15k RPM 73GB drives in RAID 10 and a ServRAID 8K controller with Write-Back cache enabled (battery installed and working). Currently getting a pgbench score of 4.7 transactions per second! After playing with the postgresql configuration file, I'm certain that this is not a postgresql problem. I have tried two different Linux distro's upon the server both with the same problems. I'm fairly certain that this is a problem with the hardware configuration / setup, however I'm still waiting for IBM to contact me! Initially I started with the OS on a RAID 1 array and a 6 drive RAID 10 array for postgresql. With this setup I got 3tps, altering the RAID configuration to a single 8 drive array, running both the OS and postgresql. I was able to reach 700tps, however after upgrading to the latest RAID controller firmware this has now fallen back to 4tps. Benchmarking another server I have access to, 4 15k 73GB RPM disks with a Dell Perc 5/i controller. I consistently get a pgbench score of 1400tps. Therefore taking a linear extrapolation I expect the IBM x3650 to manage ~3000tps. Additionally my Laptop with a 5400 RPM sata disk was able to score ~200tps. SNIP What SAS RAID controllers are people using? What RAID configurations are people using? What SAS RAID controllers would anyone recommend purchasing? I am using an Areca 1680 series controller. 16 SAS 15k5 disks. 2 RAID-1, 12 RAID-10, 2 hot spares. 512Meg bbu. RHEL 5.2 I can sustain around 3000 tps with pgbench and 30 minute runs. Thanks for the Info. I'd call IBM and ask them to come pick up their boat anchors. My sentiments exactly, unfortunately, I seem stuck with them :( Chris Ellis ** 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 **
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Tue, Mar 31, 2009 at 05:45:33PM +1100, Brendan Jurd wrote:
On Tue, Mar 31, 2009 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Does anyone want to argue for keeping it the same? Or perhaps
argue that a zero-element array is a more sensible result than
a one-element array with one empty string? (It doesn't seem
like it to me, but maybe somebody thinks so.)
Given this behaviour, I would argue for consistent treatment for a
zero-length source string: it should return an array with one element,
being the entire source string, whenever there is no string splitting
to take place. And if the source string happens to be zero-length,
then the return value would be as expected by the OP.
I'd agree with this as well, just to be verbose:
string_to_array(NULL,',') = NULL
string_to_array('',',') = {}
string_to_array('a',',')= {a}
string_to_array('a,',',') = {a,}
string_to_array('a,b',',') = {a,b}
However, I can see (nasty and hacky) reasons why the current behaviour
is there. You'd get the following error if this change was accepted:
string_to_array('',',')::INT[] = invalid input syntax for integer:
Which you don't get at the moment; although you do currently get it in
other common cases such as:
string_to_array('1,',',')::INT[]
If you want backwards compatible behaviour you could always bung a
NULLIF in there:
string_to_array(NULLIF('',''),',')::INT[] = NULL
To aid porting of code and general utility, I'd be tempted to add a pair
of functions like:
CREATE FUNCTION array_filter_blanks(TEXT[]) RETURNS TEXT[]
LANGUAGE SQL IMMUTABLE STRICT AS $$
ARRAY(SELECT s FROM unnest($1) AS s WHERE s '') $$;
CREATE FUNCTION array_nullif(ANYARRAY,ANYELEMENT) RETURNS ANYARRAY
LANGUAGE SQL IMMUTABLE AS $$
ARRAY(SELECT NULLIF(s,$2) FROM unnest($1) AS s) $$;
Although, this is obviously going above and beyond what you originally
asked for.
--
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: [GENERAL] string_to_array with empty input
On Tue, Mar 31, 2009 at 7:45 AM, Brendan Jurd dire...@gmail.com wrote:
My first thought was that it should be a zero-element array, because
then the string_to_array() behaviour would conform to the notion that
it returns an array with 1 element per string fragment bounded by the
delimiter.
However, I note that if you provide an empty delimiter, or one which
doesn't occur anywhere in the source string, you get an array with one
element, being the entire source string.
Yeah, actually the more I think about it the more I think it would be
strange for most uses to get a singleton array for this case.
What do you really expect to be returned for things like
select count_elements(string_to_array('butter,tea,milk',','))
select count_elements(string_to_array('butter,tea',','))
select count_elements(string_to_array('butter',','))
select count_elements(string_to_array('',','))
...
I could see lists like this being stored when people gather data using
a web form or something and don't want to bother normalizing some
trivial bit of data collection which they'll never individually, but
have to unnest the list for some display purposes.
The cases where it makes more sense to return a singleton array are
going to be parsing things like /etc/password where there are specific
meanings for each element, but when some are optional. I can't think
of any examples offhand though.
--
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] string_to_array with empty input
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.
--
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] Server Performance
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? -- 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
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. Chris Ellis ** 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 **
Re: [GENERAL] string_to_array with empty input
On Tue, Mar 31, 2009 at 03:43:37PM +0100, Greg Stark wrote:
On Tue, Mar 31, 2009 at 7:45 AM, Brendan Jurd dire...@gmail.com wrote:
My first thought was that it should be a zero-element array, because
then the string_to_array() behaviour would conform to the notion that
it returns an array with 1 element per string fragment bounded by the
delimiter.
However, I note that if you provide an empty delimiter, or one which
doesn't occur anywhere in the source string, you get an array with one
element, being the entire source string.
Yeah, actually the more I think about it the more I think it would be
strange for most uses to get a singleton array for this case.
Really? I think it's strange not to!
What do you really expect to be returned for things like
select count_elements(string_to_array('butter,tea,milk',','))
select count_elements(string_to_array('butter,tea',','))
select count_elements(string_to_array('butter',','))
select count_elements(string_to_array('',','))
I'd expect 3,2,1 and 1.
That's also a disingenuous example; what would you expect back from:
select count_elements(string_to_array('butter,,milk',','))
I think the semantics you want is what you'd get from:
array_filter_blanks(string_to_array($1,$2))
where I defined array_filter_blanks in my previous post.
--
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
Greg Stark st...@enterprisedb.com writes:
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.
Isn't that an argument in favor of the zero-size-array definition?
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] weird problem with PG 8.1
Hello everyone,
I'm having this completely weird problem that ORDER BY doesn't seem to
work correctly in PG 8.1 as bundled in RedHat 5.
When I issue:
SELECT * FROM virtualization;
I get all the fields:
reservations=# SELECT * FROM virtualization;
id | Virtualization | color
+-+-
1 | BOX | #FAFAFA
2 | LPAR| #99
3 | BOX ZONE HOST | #FAFAFA
4 | NPAR| #9966CC
5 | VPAR| #9966CC
But when I try to order by column Virtualization:
reservations=# SELECT * FROM virtualization ORDER BY Virtualization;
ERROR: could not identify an ordering operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
The 'virtualization' table is just a normal table with VARCHAR column of
Virtualization:
reservations=# \d virtualization
Table public.virtualization
Column | Type| Modifiers
+---+-
id | integer | not null default
nextval('virtualization_id_seq'::regclass)
Virtualization | character varying |
color | character varying |
Indexes:
virtualization_pkey PRIMARY KEY, btree (id)
When I try to specify table.column I get this:
reservations=# SELECT * FROM virtualization ORDER BY
virtualization.Virtualization;
ERROR: column virtualization.virtualization does not exist
What's going on?
Regards,
mk
--
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 Tue, Mar 31, 2009 at 8:54 AM, 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. The older Areca and 3ware cards are pretty reasonably priced (Areca 12xx series, 3ware 95xx series) and the newer ones aren't too badly priced for the performance you get. What's your budget for the RAID card? Note that you might also be able to get away with the crappy RAID card they sold you if you put it into jbod mode and use software RAID instead. Just guessing. -- 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] Space for pg_dump
SHARMILA JOTHIRAJAH wrote: Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? That will depend on how many indexes etc. make up that 600GB. Also how compressible your data is if you are using -Fc. Certainly less than a live database, but by how much it's difficult to say. -- Richard Huxton Archonet Ltd -- 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] weird problem with PG 8.1
On Tue, Mar 31, 2009 at 9:35 AM, Marcin Krol mrk...@gmail.com wrote: Hello everyone, I'm having this completely weird problem that ORDER BY doesn't seem to work correctly in PG 8.1 as bundled in RedHat 5. When I issue: SELECT * FROM virtualization; I get all the fields: reservations=# SELECT * FROM virtualization; id | Virtualization | color +-+- 1 | BOX | #FAFAFA 2 | LPAR | #99 3 | BOX ZONE HOST | #FAFAFA 4 | NPAR | #9966CC 5 | VPAR | #9966CC But when I try to order by column Virtualization: reservations=# SELECT * FROM virtualization ORDER BY Virtualization; ERROR: could not identify an ordering operator for type virtualization HINT: Use an explicit ordering operator or modify the query. Since you named it Virtualization you now get to quote it whenever you reference it. Virtualization, without quotes, case folds to virtualization, which doesn't exist. Virtualization will allow you to reference it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Space for pg_dump
On Tue, Mar 31, 2009 at 7:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? Note you can find out by doing: pg_dump dbname | wc -- 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] weird problem with PG 8.1
Marcin Krol mrk...@gmail.com writes: When I issue: SELECT * FROM virtualization; I get all the fields: reservations=# SELECT * FROM virtualization; id | Virtualization | color +-+- But when I try to order by column Virtualization: reservations=# SELECT * FROM virtualization ORDER BY Virtualization; You need SELECT * FROM virtualization ORDER BY Virtualization; What you typed is a request to sort by the composite row value, which isn't supported in any pre-8.4 release. You might care to go re-read the manual about identifier quoting and case folding. 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 Mar 31, 2009, at 8:34 AM, Sam Mason wrote:
What do you really expect to be returned for things like
select count_elements(string_to_array('butter,tea,milk',','))
3 = {butter,tea,milk}
select count_elements(string_to_array('butter,tea',','))
2 = {butter,tea}
select count_elements(string_to_array('butter',','))
1 = {butter}
select count_elements(string_to_array('',','))
1 = ARRAY['']
I'd expect 3,2,1 and 1.
That's also a disingenuous example; what would you expect back from:
select count_elements(string_to_array('butter,,milk',','))
3 = ARRAY['butter', '', 'milk']
I think the semantics you want is what you'd get from:
array_filter_blanks(string_to_array($1,$2))
where I defined array_filter_blanks in my previous post.
Yeah, if I wanted something like that in Perl, I'd do:
my @stuff = grep { $_ } split /,/, $string;
In no case would I ever expect a NULL, however, unless I was trying to
split on NULL.
NULL = string_to_array(NULL, ',');
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] weird problem with PG 8.1
Hi, Le mardi 31 mars 2009 à 17:35:58, Marcin Krol a écrit : [...] I'm having this completely weird problem that ORDER BY doesn't seem to work correctly in PG 8.1 as bundled in RedHat 5. When I issue: SELECT * FROM virtualization; I get all the fields: reservations=# SELECT * FROM virtualization; id | Virtualization | color +-+- 1 | BOX | #FAFAFA 2 | LPAR| #99 3 | BOX ZONE HOST | #FAFAFA 4 | NPAR| #9966CC 5 | VPAR| #9966CC But when I try to order by column Virtualization: reservations=# SELECT * FROM virtualization ORDER BY Virtualization; ERROR: could not identify an ordering operator for type virtualization HINT: Use an explicit ordering operator or modify the query. You should put double quotes for the column name because of the uppercase V in its name. -- Guillaume. http://www.postgresqlfr.org http://dalibo.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] Space for pg_dump
--- On Tue, 3/31/09, Scott Marlowe scott.marl...@gmail.com wrote: From: Scott Marlowe scott.marl...@gmail.com Subject: Re: [GENERAL] Space for pg_dump To: SHARMILA JOTHIRAJAH sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Tuesday, March 31, 2009, 11:49 AM On Tue, Mar 31, 2009 at 7:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? Note you can find out by doing: pg_dump dbname | wc Yes...I could find the space used after creating the dump. But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now Thanks Sharmila -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] non starting server
Long time reader, first time poster :-) Postgresql 8.3.something Windows Server 2003 So, the hard drive filled up this morning but server was still up. I ran a few deletes some of them finished then the server crashed. Now it won't start back up. Just getting could not start server on the command line. According to the google I think I may need to run pg_resetxlog. I'm a little nervous about running that though as this is a 450ish gig database that has been going through some restructuring lately and I'm not confident our backup is up to date. Log is below also with pg_resetxlog -n. I'm wondering if any one has other ideas or a reason why I shouldn't try pg_resetxlog. Log says this... 2009-03-31 09:30:41 CDT LOG: database system was interrupted while in recovery at 2009-03-31 08:47:54 CDT 2009-03-31 09:30:41 CDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2009-03-31 09:30:41 CDT LOG: loaded library $libdir/plugins/plugin_debugger.dll 2009-03-31 09:30:41 CDT LOG: database system was not properly shut down; automatic recovery in progress 2009-03-31 09:30:41 CDT FATAL: the database system is starting up 2009-03-31 09:30:41 CDT LOG: redo starts at 5AB/E3003DC0 2009-03-31 09:30:42 CDT LOG: loaded library $libdir/plugins/plugin_debugger.dll ... 2009-03-31 09:30:53 CDT LOG: could not open file pg_xlog/000105AB00E8 (log file 1451, segment 232): No such file or directory ... 2009-03-31 09:53:00 CDT LOG: received fast shutdown request 2009-03-31 09:55:05 CDT LOG: received immediate shutdown request 2009-03-31 10:07:27 CDT LOG: database system was interrupted while in recovery at 2009-03-31 09:30:41 CDT 2009-03-31 10:07:27 CDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2009-03-31 10:07:27 CDT LOG: database system was not properly shut down; automatic recovery in progress 2009-03-31 10:07:27 CDT LOG: redo starts at 5AB/E3003DC0 2009-03-31 10:07:28 CDT LOG: loaded library $libdir/plugins/plugin_debugger.dll 2009-03-31 10:07:28 CDT FATAL: the database system is starting up 2009-03-31 10:07:28 CDT LOG: could not open file pg_xlog/000105AB00E8 (log file 1451, segment 232): No such file or directory ... 2009-03-31 10:07:30 CDT LOG: loaded library $libdir/plugins/plugin_debugger.dll 2009-03-31 10:07:30 CDT FATAL: the database system is starting up 2009-03-31 10:07:31 CDT LOG: startup process (PID 2336) was terminated by exception 0xC00D 2009-03-31 10:07:31 CDT HINT: See C include file ntstatus.h for a description of the hexadecimal value. 2009-03-31 10:07:31 CDT LOG: aborting startup due to startup process failure 2009-03-31 10:07:31 CDT LOG: loaded library $libdir/plugins/plugin_debugger.dll 2009-03-31 10:07:31 CDT FATAL: the database system is starting up pg_resetxlog -n mapped win32 error code 2 to 2pg_control values: First log file ID after reset:1451 First log file segment after reset: 232 pg_control version number:833 Catalog version number: 200711281 Database system identifier: 5194713519237881468 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/552938947 Latest checkpoint's NextOID: 37828980 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: English_United States.1252 LC_CTYPE: English_United States.1252 Thanks, Bradley Russell Programmer NPC International NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments, and any copies of this message from your computer system.
Re: [GENERAL] Space for pg_dump
On Tue, Mar 31, 2009 at 9:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now Sadly, there is no exact maths for such things. If your database has tons of indexes and such, it might be 20 or 100 times bigger on disk than it will be during backup. If it's all compressible text with few indexes, it might be a 1:1 or so size. You can't really tell without running pg_dump. The advantage of doing pg_dump|wc -l is that the db doesn't have to be stored somewhere. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to see how the data is structured in postgreqsql
Hi, There, I am a new sysadmin and I am new to postgresql and database. I have got a very old gentoo server that has been running postgresql db for years and now the log files alone are about 10 Giga bytes. we run apache server and we have a ruby script that parse the access_log of apache and put the access data in to this db. the script runs daily in a cron job. Now the problem is, since we did not rotate the access_log generated by apache , access_log is getting so big that each day the parsing job of the log file and data entry in to the db takes hours to finish and it eats a lot of cpu resources and puts great pressure on the old server. I would like to know how the my log data is organized in the database and then I will see if I can do something to optimize the data entry into the db. Any pointers is greatly appreciated. Zhengquan -- 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] Space for pg_dump
On Tue, Mar 31, 2009 at 9:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: --- On Tue, 3/31/09, Scott Marlowe scott.marl...@gmail.com wrote: From: Scott Marlowe scott.marl...@gmail.com Subject: Re: [GENERAL] Space for pg_dump To: SHARMILA JOTHIRAJAH sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Tuesday, March 31, 2009, 11:49 AM On Tue, Mar 31, 2009 at 7:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? Note you can find out by doing: pg_dump dbname | wc Yes...I could find the space used after creating the dump. But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now It's hard to say. Why can't you RUN the example command on each db and see for yourself? It doesn't create a backup, per se, it just creates one and hands it to wc to see how big it is. -- 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 4:34 PM, Sam Mason s...@samason.me.uk wrote:
That's also a disingenuous example; what would you expect back from:
select count_elements(string_to_array('butter,,milk',','))
I think the semantics you want is what you'd get from:
array_filter_blanks(string_to_array($1,$2))
where I defined array_filter_blanks in my previous post.
I think you're still thinking from a programmers point of view
thinking about string processing.
From a functional point of view, if I give you a comma separated list
of items which is an empty string and ask you how many items are in it
nobody would normally say there's one empty thing in the list.
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.
--
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] how to see how the data is structured in postgreqsql
On 31/03/2009 16:58, zhang zhengquan wrote: I would like to know how the my log data is organized in the database and then I will see if I can do something to optimize the data entry into the db. pgAdmin is a really good tool that lets you see all the objects in your database in a tree view: www.pgadmin.org Alternatively, you could use \d at the psql command line. 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] weird problem with PG 8.1
2009/3/31 Marcin Krol mrk...@gmail.com:
Hello everyone,
I'm having this completely weird problem that ORDER BY doesn't seem to work
correctly in PG 8.1 as bundled in RedHat 5.
When I issue:
SELECT * FROM virtualization;
I get all the fields:
reservations=# SELECT * FROM virtualization;
id | Virtualization | color
+-+-
1 | BOX | #FAFAFA
2 | LPAR | #99
3 | BOX ZONE HOST | #FAFAFA
4 | NPAR | #9966CC
5 | VPAR | #9966CC
But when I try to order by column Virtualization:
reservations=# SELECT * FROM virtualization ORDER BY Virtualization;
ERROR: could not identify an ordering operator for type virtualization
HINT: Use an explicit ordering operator or modify the query.
The 'virtualization' table is just a normal table with VARCHAR column of
Virtualization:
reservations=# \d virtualization
Table public.virtualization
Column | Type | Modifiers
+---+-
id | integer | not null default
nextval('virtualization_id_seq'::regclass)
Virtualization | character varying |
color | character varying |
Indexes:
virtualization_pkey PRIMARY KEY, btree (id)
When I try to specify table.column I get this:
reservations=# SELECT * FROM virtualization ORDER BY
virtualization.Virtualization;
ERROR: column virtualization.virtualization does not exist
What's going on?
Try:
SELECT * FROM virtualization ORDER BY virtualization.Virtualization;
From the manual:
Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
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] string_to_array with empty input
Sam Mason wrote:
I'd expect 3,2,1 and 1.
That's also a disingenuous example; what would you expect back from:
select count_elements(string_to_array('butter,,milk',','))
I think the semantics you want is what you'd get from:
array_filter_blanks(string_to_array($1,$2))
where I defined array_filter_blanks in my previous post.
I agree the function should not be changing values passed.
Stripping/Dropping empty strings is changing what was passed into the
function instead breaking it into a array.
--
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] how to see how the data is structured in postgreqsql
Thank you so much Ray, I will give it a go. Z 2009/3/31 Raymond O'Donnell r...@iol.ie: On 31/03/2009 16:58, zhang zhengquan wrote: I would like to know how the my log data is organized in the database and then I will see if I can do something to optimize the data entry into the db. pgAdmin is a really good tool that lets you see all the objects in your database in a tree view: www.pgadmin.org Alternatively, you could use \d at the psql command line. 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] Space for pg_dump
--- On Tue, 3/31/09, Scott Marlowe scott.marl...@gmail.com wrote: From: Scott Marlowe scott.marl...@gmail.com Subject: Re: [GENERAL] Space for pg_dump To: SHARMILA JOTHIRAJAH sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Tuesday, March 31, 2009, 12:07 PM On Tue, Mar 31, 2009 at 9:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now Sadly, there is no exact maths for such things. If your database has tons of indexes and such, it might be 20 or 100 times bigger on disk than it will be during backup. If it's all compressible text with few indexes, it might be a 1:1 or so size. You can't really tell without running pg_dump. The advantage of doing pg_dump|wc -l is that the db doesn't have to be stored somewhere. Thanks...I started pg_dump|wc -l and its running now Another question is that wc -l gives you the no of lines...right... What is the size of each line...or how do you get the size from that? -- 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] Space for pg_dump
On Tue, Mar 31, 2009 at 10:31 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: --- On Tue, 3/31/09, Scott Marlowe scott.marl...@gmail.com wrote: From: Scott Marlowe scott.marl...@gmail.com wrote: But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now Sadly, there is no exact maths for such things. If your database has tons of indexes and such, it might be 20 or 100 times bigger on disk than it will be during backup. If it's all compressible text with few indexes, it might be a 1:1 or so size. You can't really tell without running pg_dump. The advantage of doing pg_dump|wc -l is that the db doesn't have to be stored somewhere. Thanks...I started pg_dump|wc -l and its running now Another question is that wc -l gives you the no of lines...right... What is the size of each line...or how do you get the size from that? Whoops, pretty sure my first reply was just wc. the -l is a habit from using to count lines. Do it again without the -l... sorry. -- 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] [GENEAL] dynamically changing table
In article 437faa9f-df2d-429e-9856-eb2026b55...@solfertje.student.utwente.nl, Alban Hertroys dal...@solfertje.student.utwente.nl writes: On Mar 30, 2009, at 5:39 PM, A B wrote: Hi, In the next project I'm going to have a number of colums in my tables, but I don't know how many, they change. They all use integers as datatype though.. One day, I get 2 new columns, a week later I loose one column, and so on in a random pattern. Ignoring design implications (you got enough replies about that I think)... You could add the columns you're sure that you need and put the rest in an XML field. mantra If you have a problem and want to solve it using XML, you have two problems. /mantra Why serializing the rest of the data in an XML field? contrib/hstore seems to accomplish the same, without the XML overhead. -- 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
Sam Mason wrote:
string_to_array('',',')::INT[] = invalid input syntax for integer:
Which you don't get at the moment; although you do currently get it in
other common cases such as:
string_to_array('1,',',')::INT[]
If you want backwards compatible behaviour you could always bung a
NULLIF in there:
string_to_array(NULLIF('',''),',')::INT[] = NULL
But consider this fails also
select string_to_array('1, , 3', ',' )::int[] = ERROR: invalid input
syntax for integer:
yet this works
select string_to_array('1, 2, 3',',')::int[]
--
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 5:48 PM, justin jus...@emproshunts.com wrote:
But consider this fails also
select string_to_array('1, , 3', ',' )::int[] = ERROR: invalid input
syntax for integer:
yet this works
select string_to_array('1, 2, 3',',')::int[]
Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.
The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.
I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.
--
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] [GENEAL] dynamically changing table
On Mar 31, 2009, at 6:41 PM, Harald Fuchs wrote: In article 437faa9f-df2d-429e-9856-eb2026b55...@solfertje.student.utwente.nl , Alban Hertroys dal...@solfertje.student.utwente.nl writes: You could add the columns you're sure that you need and put the rest in an XML field. mantra If you have a problem and want to solve it using XML, you have two problems. /mantra mantra A mantra is never good argumentation, whether for or against. /mantra I don't like mantras, they're like dogmas, they prevent people from thinking. I consider them dangerous ;) Why serializing the rest of the data in an XML field? contrib/hstore seems to accomplish the same, without the XML overhead. Although I'm no fan of XML, it does have it's benefits. In this case, it's standardised (which means there are libraries for about every language to deal with XML data), it can store and constrain type information (although a bit limited IMO) about the data and there are many tools for manipulating data contained in XML and those can usually also be used at the application side of things. contrib/hstore doesn't do these things for you, so it adds problems of its own. The OP may run into problems with database layers in his programming language of choice that can't deal with the specifics of querying a hstore field for example. If type information in the data is of importance, he'll have to explicitly cast the data (based on the key field). If certain fields are constrained to certain value ranges, he'll have to check those in his application. It all depends on the requirements of the OP and how far he wants to go with this, if hstore solves his problem, by all means, go with it. In either case, using an XML field is a valid solution. Nobody said it's a perfect one. 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,49d2554e129747441114695! -- 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
Greg Stark wrote:
On Tue, Mar 31, 2009 at 5:48 PM, justin jus...@emproshunts.com wrote:
But consider this fails also
select string_to_array('1, , 3', ',' )::int[] = ERROR: invalid input
syntax for integer: " "
yet this works
select string_to_array('1, 2, 3',',')::int[]
Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.
The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.
I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.
I disagree. Casting a string to something else can be a very error
prone to begin with.
Having string_to_array() to deal with that possibility is out of its
scope IMHO.
Consider this. I have intelligent part numbers that need to be split
apart to simplify searching and do math with.
string_to_array(' F-2500-50 ', '-' ) ::int[]
Still fails with an error as expected. what is the difference between
' ' and 'F'
So before doing any thing a test needs to be done to verify the
contents, so it can be casted to something else.
[GENERAL] Pg Conference East: Registration closing
Hello, As a reminder for all of those in our community that like to register at the last minuted (that means most of us), registration will be closing on Wednesday April first. On line registration is much easier than registering at the door so please bounce on over to: http://www.postgresql.us/purchase And get your registration in! Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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 6:44 PM, justin jus...@emproshunts.com wrote:
Consider this. I have intelligent part numbers that need to be split apart
to simplify searching and do math with.
string_to_array(' F-2500-50 ', '-' ) ::int[]
Yeah, that's what I've said about three times. If you're building a
parser and think of it in terms of string parsing then yes, a
singletone array makes sense.
Still fails with an error as expected. what is the difference between ' '
and 'F'
I don't know what you're talking about. The question is how many
elements are in ''. No space. And no separators. To repeat for the
last time. If you think in terms of string processing then the answer
1 is reasonable. But if you think it's a list of separate items then
anyone will say that's an empty list and contains no elements.
Nobody has ever suggested filtering out empty elements or dealing
specially with spaces or anything else like that. If you're talking
about that then you've missed the original question.
One last try.
If there's a column called shopping list which is a comma-separated
list of items to buy in the store and I store '' in it. How many items
do you think that means you should go shopping for? Do you think
that's one item that should be rejected because it's an empty string?
Or do you think that's an empty list with zero items listed?
What would it look like if it was a zero-length list? You can ask what
would it look like if it was a shopping list of one item called ''.
But I agree both are theoretically consistent, but one is actually
useful in 99% of use cases. The other is only useful in unusual cases.
--
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] string_to_array with empty input
This thread being cross posted has made it a bit confusing Greg Stark wrote: Nobody has ever suggested filtering out empty elements or dealing specially with spaces or anything else like that. If you're talking about that then you've missed the original question. "Does anyone want to argue for keeping it the same? Or perhaps argue that a zero-element array is a more sensible result than a one-element array with one empty string? (It doesn't seem like it to me, but maybe somebody thinks so.)" That means to me dropping empty strings or removing values that don't make sense. Then the argument begins what values make since to drop. Just zero length strings or include strings with million white spaces??? One last try. If there's a column called "shopping list" which is a comma-separated list of items to buy in the store and I store '' in it. How many items do you think that means you should go shopping for? Do you think that's one item that should be rejected because it's an empty string? Or do you think that's an empty list with zero items listed? It can't be rejected, Its an Empty shopping list although a worthless shopping list . What would it look like if it was a zero-length list? You can ask what would it look like if it was a shopping list of one item called ''. But I agree both are theoretically consistent, but one is actually useful in 99% of use cases. The other is only useful in unusual cases. I'm still confused which one you want here
[GENERAL] Triggers for a MPTT based table
Hi all, I wish to use a MPTT based table to store some hierachical datas. Here is my table definition: CREATE TABLE region ( id SERIAL NOT NULL, full_path VARCHAR(255) NOT NULL, lhs INTEGER NOT NULL, rhs INTEGER NOT NULL, level INTEGER NOT NULL, parent_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(parent_id) REFERENCES region (id) ON DELETE cascade ) I'm trying to manage lhs, rhs and level with triggers. Here are my triggers: CREATE OR REPLACE FUNCTION update_tree_oninsert() RETURNS TRIGGER AS $BODY$ BEGIN IF NEW.parent_id != 0 THEN UPDATE region SET level = (select level from region where id = NEW.parent_id)+1 where id = NEW.id; UPDATE region SET lhs = (select rhs from region where id = NEW.parent_id) where id = NEW.id; UPDATE region SET rhs = lhs + 1 where id = NEW.id; UPDATE region SET rhs = rhs + 2 WHERE rhs = (select rhs from region where id = NEW.parent_id) and id != NEW.id; UPDATE region SET lhs = lhs + 2 WHERE lhs = (select rhs from region where id = NEW.id) and id != NEW.id; END IF; RETURN NEW ; END ; $BODY$ LANGUAGE 'plpgsql' ; CREATE TRIGGER add_upd AFTER INSERT ON region FOR EACH ROW EXECUTE PROCEDURE update_tree_oninsert(); CREATE OR REPLACE FUNCTION update_tree_ondelete() RETURNS TRIGGER AS $BODY$ BEGIN UPDATE region SET lhs = lhs - (OLD.rhs-OLD.lhs+1) WHERE lhs OLD.rhs; UPDATE region SET rhs = rhs - (OLD.rhs-OLD.lhs+1) WHERE rhs OLD.rhs; RETURN OLD ; END ; $BODY$ LANGUAGE 'plpgsql' ; CREATE TRIGGER del_upd AFTER DELETE ON region FOR EACH ROW EXECUTE PROCEDURE update_tree_ondelete(); So here is my problem: When I insert data in the table region, the trigger on insert is OK but as soon as I try to remove an entry from the database For example: id |full_path| lhs | rhs | level | parent_id +-+-+-+---+--- 1 | world | 1 | 70 | 0 | 2 | world/continent0| 2 | 67 | 1 | 1 3 | world/continent0/country0 | 3 | 34 | 2 | 2 4 | world/continent0/country0/region0 | 4 | 13 | 3 | 3 5 | world/continent0/country0/region0/city0 | 5 | 6 | 4 | 4 6 | world/continent0/country0/region0/city1 | 7 | 8 | 4 | 4 7 | world/continent0/country0/region0/city2 | 9 | 10 | 4 | 4 8 | world/continent0/country0/region0/city3 | 11 | 12 | 4 | 4 9 | world/continent0/country0/region1 | 14 | 23 | 3 | 3 10 | world/continent0/country0/region1/city0 | 15 | 16 | 4 | 9 11 | world/continent0/country0/region1/city1 | 17 | 18 | 4 | 9 12 | world/continent0/country0/region1/city2 | 19 | 20 | 4 | 9 13 | world/continent0/country0/region1/city3 | 21 | 22 | 4 | 9 14 | world/continent0/country0/region2 | 24 | 33 | 3 | 3 15 | world/continent0/country0/region2/city0 | 25 | 26 | 4 |14 16 | world/continent0/country0/region2/city1 | 27 | 28 | 4 |14 17 | world/continent0/country0/region2/city2 | 29 | 30 | 4 |14 18 | world/continent0/country0/region2/city3 | 31 | 32 | 4 |14 19 | world/continent0/country1 | 35 | 66 | 2 | 2 20 | world/continent0/country1/region0 | 36 | 45 | 3 |19 21 | world/continent0/country1/region0/city0 | 37 | 38 | 4 |20 22 | world/continent0/country1/region0/city1 | 39 | 40 | 4 |20 23 | world/continent0/country1/region0/city2 | 41 | 42 | 4 |20 24 | world/continent0/country1/region0/city3 | 43 | 44 | 4 |20 25 | world/continent0/country1/region1 | 46 | 55 | 3 |19 26 | world/continent0/country1/region1/city0 | 47 | 48 | 4 |25 27 | world/continent0/country1/region1/city1 | 49 | 50 | 4 |25 28 | world/continent0/country1/region1/city2 | 51 | 52 | 4 |25 29 | world/continent0/country1/region1/city3 | 53 | 54 | 4 |25 30 | world/continent0/country1/region2 | 56 | 65 | 3 |19 31 | world/continent0/country1/region2/city0 | 57 | 58 | 4 |30 32 | world/continent0/country1/region2/city1 | 59 | 60 | 4 |30 33 | world/continent0/country1/region2/city2 | 61 | 62 | 4 |30 34 | world/continent0/country1/region2/city3 | 63 | 64 | 4 |30 35 | world/continent_otot| 68 | 69 | 1 | 1 But as soon I'm trying to delete an entry I get this error about constraint being violated: Does anybody have any clue ? -- Laurent RAHUEL, Chef de Projet __ net-ng 14 rue Patis Tatelin, Bât G
Re: [GENERAL] Server Performance
On Tue, 31 Mar 2009, chris.el...@shropshire.gov.uk wrote: Been having interesting times with an IBM x3650 with 8 15k RPM 73GB drives in RAID 10 and a ServRAID 8K controller with Write-Back cache enabled (battery installed and working). Currently getting a pgbench score of 4.7 transactions per second! pgbench is a very high level test of your system. It can tell you when a system is doing well, but it's almost useless for figuring out what's wrong if there's a problem. You shouldn't run pgbench until you've first done a lower-level benchmark such as bonnie++ on the hardware. That should give you a better idea what's going on here, and if the badness shows up there it will be much easier to get someone at IBM to pay attention too. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ERROR: XX001: could not read block 2354 of relation …
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. What can I do to fix that problem? Thank you,
Re: [GENERAL] Space for pg_dump
On Tue, Mar 31, 2009 at 08:57:28AM -0700, SHARMILA JOTHIRAJAH wrote: Note you can find out by doing: pg_dump dbname | wc Yes...I could find the space used after creating the dump. But I need to pre-allocate some space for storing these dumps I'm not sure if you realize that you don't need any space for the command that Scott showed (pg_dump dbname | wc). it will not write anything to disk. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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…
On Tue, Mar 31, 2009 at 2:10 PM, Patrick Desjardins mrdesjard...@gmail.com wrote: 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. What OS, pg version etc are you running? If windows, are you running any anti-virus software? A lot of anti-virus packages are dumb as a brick and lock files when checking them and cause these problems. If so, either exclude the pg directories from virus checking or turn it off altogether. Just a guess. Not enough info to really 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] Re: [GENERAL] ERROR: XX001: could not read block 235 4 of relation…
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. On Tue, Mar 31, 2009 at 6:53 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Tue, Mar 31, 2009 at 2:10 PM, Patrick Desjardins mrdesjard...@gmail.com wrote: 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. What OS, pg version etc are you running? If windows, are you running any anti-virus software? A lot of anti-virus packages are dumb as a brick and lock files when checking them and cause these problems. If so, either exclude the pg directories from virus checking or turn it off altogether. Just a guess. Not enough info to really know.
[GENERAL] High consumns memory
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 postgresql.conf information: name |setting | unit -+--+-- archive_command | wal_archive_command.sh %p %f | archive_mode| on | autovacuum_analyze_scale_factor | 0.4 | autovacuum_analyze_threshold| 500 | autovacuum_vacuum_threshold | 1000 | checkpoint_segments | 15 | checkpoint_timeout | 1800 | s DateStyle | ISO, DMY | default_statistics_target | 50 | effective_cache_size| 249600 | 8kB fsync | on | lc_monetary | en_US.UTF-8 | lc_numeric | en_US.UTF-8 | lc_time | en_US.UTF-8 | listen_addresses| *| log_autovacuum_min_duration | 0| ms log_checkpoints | on | log_destination | stderr | log_directory | /p01/log | log_filename| postgresql-%Y-%m-%d_%H%M%S.log | log_line_prefix | %t [%p]: [%l-1] | log_lock_waits | on | log_min_duration_statement | 250 | ms log_min_error_statement | error| log_rotation_age| 1440 | min log_rotation_size | 20480| kB log_temp_files | 10240| kB logging_collector | on | maintenance_work_mem| 409600 | kB max_connections | 100 | max_fsm_pages | 3458000 | shared_buffers | 64000| 8kB tcp_keepalives_idle | 0| s wal_buffers | 100 | 8kB work_mem| 5120 | kB
Re: [GENERAL] High consumns memory
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 %MEM TIME+ 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELinux problem rsync'ing WAL logs
Ok, this is not strictly a PostgreSQL issue,
but I am trying to enable WAL log shipping on our PostgreSQL 8.1.10
(upgrade to 8.3.7 is in the works).
My archive_command is 'rsync %p postg...@node2:/file/to/$f /dev/null'
This works fine only if and only if SE Linux is disabled on node 1
(the source node).
I am running Fedora Core 6 on node 1. (Upgrade to CentOS 5.2 is in the works.)
I used audit2allow on the SELinux messages, and generated an SE Linux
module to allow
Postgres to rsync the files out...
allow postgresql_t ssh_exec_t:file { read execute execute_no_trans };
allow postgresql_t ssh_port_t:tcp_socket name_connect;
allow postgresql_t user_home_t:dir { search getattr };
allow postgresql_t user_home_t:file { read getattr };
But this still does now work. (Works fine if I disable SELinux, by the way.)
The error I get is:
LOG: archive command /usr/local/bin/rsync -e /usr/bin/ssh
pg_xlog/0001001D0015
postg...@node2:WAL/0001001D0015 /dev/null failed: return
code 65280
Could not create directory '/home/postgres/.ssh'.
Host key verification failed.
rsync: connection unexpectedly closed (0 bytes received so far) [sender]
rsync error: unexplained error (code 255) at io.c(632) [sender=3.0.4]
If anybody has any clue as to whats going on here, I would sure
appreciate your help.
ssh node2 works fine from node1, I log in using key-based authentication
What stumps me is there are no further complaints from SELinux, but
clearly SELinux is blocking the connection.
I think I'll ask on the SELinux list as well. But if anybody here
has a clue, please give me a shout.
Best,
-at
--
Aleksey Tsalolikhin
UNIX System Administrator
I get stuff done!
http://www.verticalsysadmin.com/
LinkedIn - http://www.linkedin.com/in/atsaloli
--
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
On Tue, Mar 31, 2009 at 9:18 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: Could not create directory '/home/postgres/.ssh'. Host key verification failed. Have you tested ssh node2 as the postgres user with SELinux enabled? This looks like ssh failing to access the .ssh directory where it keeps host keys (the known_keys file) and dying as a result. None of the SELinux module setup lines seem to cover that, so you may want to see if there's an SELinux failure for ssh in the audit log that could give you a clue as to what needs to be allowed. -- - David T. Wilson david.t.wil...@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] SELinux problem rsync'ing WAL logs
On Tue, Mar 31, 2009 at 6:35 PM, David Wilson david.t.wil...@gmail.com wrote: On Tue, Mar 31, 2009 at 9:18 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: Could not create directory '/home/postgres/.ssh'. Host key verification failed. Have you tested ssh node2 as the postgres user with SELinux enabled? Yes, I have, it works fine. With SELinux enabled. That's why I've been tearing my hair out. There must be a different SELinux behavior when the postgres database server tries to do it. Thanks for your reply! -- Aleksey Tsalolikhin UNIX System Administrator I get stuff done! http://www.verticalsysadmin.com/ LinkedIn - http://www.linkedin.com/in/atsaloli -- 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
Aleksey Tsalolikhin atsaloli.t...@gmail.com writes: On Tue, Mar 31, 2009 at 6:35 PM, David Wilson david.t.wil...@gmail.com wrote: Have you tested ssh node2 as the postgres user with SELinux enabled? Yes, I have, it works fine. With SELinux enabled. That's why I've been tearing my hair out. Ah, well, you need to understand one of the first points about SELinux: the standard policy is designed to constrain daemon processes, not interactive processes. So you can run some command when logged in as postgres, and whether that works has nothing whatever to do with whether SELinux will let the postgres daemon do it. I am running Fedora Core 6 on node 1. (Upgrade to CentOS 5.2 is in the works.) Yes, I'd suggest getting off FC6 soon. In my experience the SELinux policy didn't start to just work until around FC8. In particular I recall that FC6 had a bad habit of trying to rate-limit AVC messages to the point where you could not figure out whether (much less why) it was denying any particular thing you tried. My advice is don't even bother trying to debug this on FC6. Get onto a newer platform with a less buggy SELinux implementation, or just turn off SELinux. 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] Server Performance
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. So I really think you are looking at another problem here (be it defective hardware or a driver/OS level issue). is your SLES10 install updated to the latest patch levels available and are you running the recommended driver version for that version of SLES? Stefan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
