[GENERAL] Polygon data type
Hello all,
I know that this is the postgres forum and not the libpqxx forum (I've been
there but no answer yet).
I want to retrieve the points of a polygon that are stored in the postgres
db. The contents of the db are:
polygonid |vertices
---+-
2 |((1,0),(1.5,-1),(2,-1),(2,1),(1,1),(0,0),(0,2),(3,2),(3,-2),(1,-2))
4 | ((3,3),(4,4),(5,5))
The vertices column is of type Polygon.
I'm using libpqxx library for C++.
Suppose I want to retrieve and access the points in the vertices column, I
would execute these statements in C++:
result R = W.exec (select * from polygon_tbl);
for (result::const_iterator r = R.begin();
r != R.end();
++r)
{
int x = 0;
cout Polygon ID: r[0].to(x) endl;
//Suppose i would like to print the first point of every polygon,
//how would i access it?
cout First vertex: r[1][0] endl;???
//Or suppose i would like to print the first x coordinate of
//every polygon, how would i access it?
cout First x coordinate: r[1][0][0] endl; //
(am just guessing here..)
}
Sorry I'm very new to libpqxx. I've pretty much understood how the libpqxx
works but I'm stuck with the Polygon types. We actually just need a simple
storage for our polygons in Postgres but I'm not sure how to access them
using libpqxx. I know I can parse it as a string using r[1].c_str(), but is
this the proper way to do it (parsing the text)? Or is there a container
that I should use to store the vertices and access them using the container
?
best regards,
mark
Re: [GENERAL] Bug in ecpg lib ?
I wrote: What I notice about your program is that you connect to the database in the main thread, then start a new thread and use the connection in that new thread. I don't know, but I'd expect that since ecpg keeps a thread-specific sqlca, this could cause problems. Indeed I find with the debugger that in your sample sqlca is allocated and initialized twice, once when the catabase connection is attempted, and once when the SQL statement is run. I think that the good way to do it would be: - start a thread - connect to the database - do work - disconnect from the database - terminate the thread I thought some more about that, and it is obvioisly nonsense. Why shouldn't you use a connection object in a different thread? I'll try to come up with some more findings to help figure out what's going on. Yours, Laurenz Albe -- 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] Bug in ecpg lib ?
Hi Laurenz, Thank you for your effort. I appreciate it very much. I have been trying to figure this thing out myself too, breakpointing and single stepping my way through some of the ecpg code, but without much clarification. (More that I learned new things about pthread). I have been trying to figure out whether this is a real thing or more a mudflapth mis-judgement. Also on most (the faster ones) machines mudflap complains either about invalid pointer in free() or double free() or corruption. I haven't been able to verify this yet. Specifically on one (slower) machine, I have only seen this mudflapth complaint once, though I have been both running and debugging it on that many times. Are you sure what you suggest is nonsense ? In the light of the sqlca struct being local to each thread ? I tried to put the open and close connection within the thread, but I was still able to get the mudflap complaint. Theoretically, I guess one could use just 1 connection for all db access in all threads just having them enclosed within pthread_mutex_[un]lock()s !? (Not what I do, though.) And for your previous mail: Yes, I know that my example does not make the connection, but are still doing the select ... It doesn't matter, however, if it does make a connection, it still bumps out. And yes, I am aware that I open the connection in the main thread and use it another. This is the way real daemon program was designed. Once again, thank you, Leif - Albe Laurenz laurenz.a...@wien.gv.at wrote: I wrote: What I notice about your program is that you connect to the database in the main thread, then start a new thread and use the connection in that new thread. I don't know, but I'd expect that since ecpg keeps a thread-specific sqlca, this could cause problems. Indeed I find with the debugger that in your sample sqlca is allocated and initialized twice, once when the catabase connection is attempted, and once when the SQL statement is run. I think that the good way to do it would be: - start a thread - connect to the database - do work - disconnect from the database - terminate the thread I thought some more about that, and it is obvioisly nonsense. Why shouldn't you use a connection object in a different thread? I'll try to come up with some more findings to help figure out what's going on. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pqSocketCheck() didn't return, hung in poll()? Is it a bug?
Hi all, I have this problem today: one thread of our application hung, and we found the thread waiting for the result of poll() function. poll() was called by pgSocketCheck(). So is it a bug of pg? I found the bug report from website(http://archives.postgresql.org/pgsql-bugs/2008-02/msg00260.php), and want to know: 1. Has this problem been solved in the newest version? 2. If not, what shall I do to avoid this problem? Thanks a lot! PS: version info: PostgreSQL version: PostgreSQL 8.3.3 on i686-pc-linux-gnu Operating system: CentOS release 5.2 (Final) Kernel version: Linux version 2.6.18-92.el5xen (mockbu...@builder16.centos.org) gdb info: (gdb) bt #0 0x00d31402 in __kernel_vsyscall () #1 0x003d3dc3 in poll () from /lib/i686/nosegneg/libc.so.6 #2 0x0013de5d in pqSocketCheck () from /usr/local/pgsql/lib/libpq.so.5 #3 0x0013df8d in pqWaitTimed () from /usr/local/pgsql/lib/libpq.so.5 #4 0x0013e003 in pqWait () from /usr/local/pgsql/lib/libpq.so.5 #5 0x0013d792 in PQgetResult () from /usr/local/pgsql/lib/libpq.so.5 -- Yours Sincerely, Liz -- 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] Passing a table to function
personally they way I do it, is by creating temporary table, in transaction - and use it in function. Obviously that's very indirect, and not obvious if you see function's declaration. But works fine. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Password?
Hi When I write the following commands at the prompt, createuser -S -d -R user1 createdb sample I am asked to enter a password. I have not set any password anywhere. Which password is it asking for? Please help. I have built from source on Windows XP. Thanks is advance, Regards Swati
Re: [GENERAL] Password?
Ms swati chande schrieb: Hi When I write the following commands at the prompt, createuser -S -d -R user1 createdb sample I am asked to enter a password. I have not set any password anywhere. Which password is it asking for? Please help. I have built from source on Windows XP. Thanks is advance, Regards Swati Hi Swati, what are the setting of your pg_hba.conf? I assume that there is a entry like this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all password That means that the password you are asked is the password of the standard user for your cluster - commonly postgres. Cheers Andy -- 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] Password?
Andreas Wenk schrieb: Ms swati chande schrieb: Hi When I write the following commands at the prompt, createuser -S -d -R user1 createdb sample I am asked to enter a password. I have not set any password anywhere. Which password is it asking for? Please help. I have built from source on Windows XP. Thanks is advance, Regards Swati Hi Swati, what are the setting of your pg_hba.conf? I assume that there is a entry like this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all password That means that the password you are asked is the password of the standard user for your cluster - commonly postgres. Cheers Andy *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... see also createdb --help for options ... Cheers Andy -- 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] Password?]
Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. -- 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] Bug in ecpg lib ?
l...@crysberg.dk wrote:
I have been trying to figure this thing out myself too,
breakpointing and single stepping my way through some of the
ecpg code, but without much clarification. (More that I
learned new things about pthread). I have been trying to
figure out whether this is a real thing or more a mudflapth
mis-judgement. Also on most (the faster ones) machines
mudflap complains either about invalid pointer in free() or
double free() or corruption. I haven't been able to verify
this yet. Specifically on one (slower) machine, I have only
seen this mudflapth complaint once, though I have been both
running and debugging it on that many times.
Are you sure what you suggest is nonsense ? In the light
of the sqlca struct being local to each thread ? I tried to
put the open and close connection within the thread, but I
was still able to get the mudflap complaint. Theoretically, I
guess one could use just 1 connection for all db access in
all threads just having them enclosed within
pthread_mutex_[un]lock()s !? (Not what I do, though.)
The sqlca is local to each thread, but that should not be a problem.
On closer scrutiny of the source, it works like this:
Whenever a thread performs an SQL operation, it will allocate
an sqlca in its thread-specific data area (TSD) in the ECPG function
ECPGget_sqlca(). When the thread exits or is cancelled, the
sqlca is freed by pthread by calling the ECPG function
ecpg_sqlca_key_destructor(). pthread makes sure that each
destructor function is only called once per thread.
So when several threads use a connection, there will be
several sqlca's around, but that should not matter as they get
freed when the thread exits.
After some experiments, I would say that mudflap's complaint
is a mistake.
I've compiled your program against a debug-enabled PostgreSQL 8.4.0 with
$ ecpg crashex
$ gcc -Wall -O0 -g -o crashex crashex.c -I /magwien/postgres-8.4.0/include \
-L/magwien/postgres-8.4.0/lib -lecpg -Wl,-rpath,/magwien/postgres-8.4.0/lib
and run a gdb session:
$ gdb
GNU gdb Red Hat Linux (6.3.0.0-1.138.el3rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type show copying to see the conditions.
There is absolutely no warranty for GDB. Type show warranty for details.
This GDB was configured as i386-redhat-linux-gnu.
Set the program to be debugged:
(gdb) file crashex
Reading symbols from /home/laurenz/ecpg/crashex...done.
Using host libthread_db library /lib/tls/libthread_db.so.1.
This is where the source of libecpg is:
(gdb) dir
/home/laurenz/rpmbuild/BUILD/postgresql-8.4.0/src/interfaces/ecpg/ecpglib
Source directories searched:
/home/laurenz/rpmbuild/BUILD/postgresql-8.4.0/src/interfaces/ecpg/ecpglib:$cdir:$cwd
Start the program (main thread):
(gdb) break main
Breakpoint 1 at 0x804892c: file crashex.pgc, line 54.
(gdb) run
Starting program: /home/laurenz/ecpg/crashex
[Thread debugging using libthread_db enabled]
[New Thread -1218572160 (LWP 29290)]
[Switching to Thread -1218572160 (LWP 29290)]
Breakpoint 1, main (argc=1, argv=0xbfffce44) at crashex.pgc:54
54PerformTask( 25 );
(gdb) delete
Delete all breakpoints? (y or n) y
Set breakpoint #2 in the function where sqlca is freed:
(gdb) break ecpg_sqlca_key_destructor
Breakpoint 2 at 0x457a27: file misc.c, line 124.
(gdb) list misc.c:124
119
120 #ifdef ENABLE_THREAD_SAFETY
121 static void
122 ecpg_sqlca_key_destructor(void *arg)
123 {
124 free(arg); /* sqlca
structure allocated in ECPGget_sqlca */
125 }
126
127 static void
128 ecpg_sqlca_key_init(void)
Set breakpoint #3 where a new sqlca is allocated in ECPGget_sqlca():
(gdb) break misc.c:147
Breakpoint 3 at 0x457ad2: file misc.c, line 147.
(gdb) list misc.c:134,misc.c:149
134 struct sqlca_t *
135 ECPGget_sqlca(void)
136 {
137 #ifdef ENABLE_THREAD_SAFETY
138 struct sqlca_t *sqlca;
139
140 pthread_once(sqlca_key_once, ecpg_sqlca_key_init);
141
142 sqlca = pthread_getspecific(sqlca_key);
143 if (sqlca == NULL)
144 {
145 sqlca = malloc(sizeof(struct sqlca_t));
146 ecpg_init_sqlca(sqlca);
147 pthread_setspecific(sqlca_key, sqlca);
148 }
149 return (sqlca);
(gdb) cont
Continuing.
Breakpoint #3 is hit when the main thread allocates an sqlca during connect:
Breakpoint 3, ECPGget_sqlca () at misc.c:147
147 pthread_setspecific(sqlca_key, sqlca);
(gdb) where
#0 ECPGget_sqlca () at misc.c:147
#1 0x00456d57 in ECPGconnect (lineno=41, c=0, name=0x9bf2008
t...@localhost:1238,
user=0x8048a31 laureny, passwd=0x0, connection_name=0x8048a14 dbConn,
autocommit=0)
at
Re: [GENERAL] ZFS prefetch considered evil?
On Jul 8, 2009, at 2:50 AM, Yaroslav Tykhiy wrote: Hi All, I have a mid-size database (~300G) used as an email store and running on a FreeBSD + ZFS combo. Its PG_DATA is on ZFS whilst xlog goes to a different FFS disk. ZFS prefetch was enabled by default and disk time on PG_DATA was near 100% all the time with transfer rates heavily biased to read: ~50-100M/s read vs ~2-5M/s write. A former researcher, I was going to set up disk performance monitoring to collect some history and see if disabling prefetch would have any effect, but today I had to find out the difference the hard way. Sorry, but that's why the numbers I can provide are quite approximate. Due to a peak in user activity the server just melted down, with mail data queries taking minutes to execute. As the last resort, I rebooted the server with ZFS prefetch disabled -- it couldn't be disabled at run time in FreeBSD. Now IMAP feels much more responsive; transfer rates on PG_DATA are mostly 10M/s read and 1-2M/s write; and disk time stays way below 100% unless a bunch of email is being inserted. My conclusion is that although ZFS prefetch is supposed to be adaptive and handle random access more or less OK, in reality there is plenty of room for improvement, so to speak, and for now Postgresql performance can benefit from its staying just disabled. The same may apply to other database systems as well. Are you sure you weren't hitting swap? IIRC prefetch tries to keep data (disk blocks?) in memory that it fetched recently. ZFS uses quite a bit of memory, so if you distributed all your memory to be used by just postgres and disk cache then you didn't leave enough space for the prefetch data and _something_ will be moved to swap. If you're running FreeBSD i386 then ZFS requires some careful tuning due to the limits a 32-bit OS puts on memory. I recall ZFS not being very stable on i386 a while ago for those reasons, which has by now been fixed as far as possible, but it's not ideal (and it likely never will be). You'll probably want to ask about this on the FreeBSD mailing lists as well, they'll know much better than I do ;) 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,4a54776e10131807247821! -- 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] Normalize INTERVAL ouput format in a db driver
I could manage to identify/describe interval fields by testing the different values returned by PQfmod(). Could someone confirm that PQfmod() returns will not change in future versions? I have seen in the docs that there is a deprecated compile-time option that defines how time, timestamp and intervals are stored (using double or int64), I guess this should not affect the value returned by PQfmod()... right? Thanks! Seb Sebastien FLAESCH wrote: Further, little libpq question: When using INTERVAL types, can I rely on PQfmod(), PQfsize() to determine the exact definition of the INTERVAL precision? = what YEAR/MONTH/DAY/HOUR/MINUTE/SECOND fields where used to create the column. I get different values for the type modifier, but how to interpret this? Can someone point me to the documentation or source code where I can find more details about this? I found this: http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO But there are not much details in PQfmod() description... Thanks! Seb Sebastien FLAESCH wrote: Hi all, Just testing 8.4rc2 INTERVALs... According to the doc, INTERVAL output format is controlled by SET intervalstyle. I am writing an interface/driver and need a solution to fetch/convert interval values independently from the current format settings... I could force my driver to implicitly set the intervalstyle to iso_8601, but I would prefer to leave this in the hands of the programmer... Imagine you have to write and ODBC interface/driver with libpq that must support the SQLINTERVAL C structure, how would you deal with PostgreSQL intervals? Is it possible to query the current intervalstyle? Thanks! Seb -- 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] Password?
On Wed, Jul 8, 2009 at 3:22 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Andreas Wenk schrieb: Ms swati chande schrieb: Hi When I write the following commands at the prompt, createuser -S -d -R user1 createdb sample I am asked to enter a password. I have not set any password anywhere. Which password is it asking for? Please help. I have built from source on Windows XP. Thanks is advance, Regards Swati Hi Swati, what are the setting of your pg_hba.conf? I assume that there is a entry like this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all password That means that the password you are asked is the password of the standard user for your cluster - commonly postgres. Cheers Andy *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... see also createdb --help for options ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general If you don't need the password authentication you have to edit the pg_conf file and replace password with trust, after this reload the cluster. It won't prompt you for password. Thanks, Abbas.
Re: [GENERAL] Password?
Ms swati chande schrieb: --- On *Wed, 7/8/09, Andreas Wenk /a.w...@netzmeister-st-pauli.de/* wrote: From: Andreas Wenk a.w...@netzmeister-st-pauli.de Subject: Re: [GENERAL] Password? To: Ms swati chande swat...@yahoo.com, PG-General Mailing List pgsql-general@postgresql.org Date: Wednesday, July 8, 2009, 3:47 PM Ms swati chande schrieb: Thanks Andy, I am working on Windows XP. Have built from source using Visual Studio 2005. I have made a change in pg_hba.conf to include the ipconfig of my system. # TYPE DATABASEUSERCIDR-ADDRESS METHOD *hostall all my ipconfig trust* # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: #hostall all ::1/128 trust This was to take care of the following problem: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets For this I changed the listen_addresses to my current ip. and made the same change in pg_hba.conf. Thanks Regards Swati So does it work now ? Why is there a * sign before host? This seems to be incorrect ... P.S.: dont' forget to reply also to the mailinglist (reply to all) No its still not working. The * doesn't exist in pg_hba. It was probably in the mail as I had formatted that line to be 'bold'. ah ok .. Actually it should work if you set listen_addresses to '*' in postgresql.conf. Did you change anything else in postgresql.conf or pg_hba.conf? I am not too experienced with Windows so maybe someone with more knowledge is able to find the trick (I installed 8.4 once with the one click installer ...no problems at all). But as far as I understand something is wrong with: WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets I understand correct, that you fixed this? Then it should work as I mentioned earlier ... Cheers Andy -- 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] Password?
Ms swati chande schrieb: Thanks Andy, I am working on Windows XP. Have built from source using Visual Studio 2005. I have made a change in pg_hba.conf to include the ipconfig of my system. # TYPE DATABASEUSERCIDR-ADDRESS METHOD *hostall all my ipconfig trust* # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: #hostall all ::1/128 trust This was to take care of the following problem: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets For this I changed the listen_addresses to my current ip. and made the same change in pg_hba.conf. Thanks Regards Swati So does it work now ? Why is there a * sign before host? This seems to be incorrect ... P.S.: dont' forget to reply also to the mailinglist (reply to all) -- 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] Password?
ah ok .. Actually it should work if you set listen_addresses to '*' in postgresql.conf. Did you change anything else in postgresql.conf or pg_hba.conf? I am not too experienced with Windows so maybe someone with more knowledge is able to find the trick (I installed 8.4 once with the one click installer ...no problems at all). But as far as I understand something is wrong with: WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets I understand correct, that you fixed this? Then it should work as I mentioned earlier ... Cheers Andy Ok. Will check the '*' part of it and then get back. Infact it was to move ahead with it that I changes the listen addresses in postgresql.conf. But will take another look into it. Thanks, Regards Swati
Re: [GENERAL] Password?
On Wed, Jul 8, 2009 at 4:12 PM, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Ms swati chande schrieb: --- On *Wed, 7/8/09, Andreas Wenk /a.w...@netzmeister-st-pauli.de/* wrote: From: Andreas Wenk a.w...@netzmeister-st-pauli.de Subject: Re: [GENERAL] Password? To: Ms swati chande swat...@yahoo.com, PG-General Mailing List pgsql-general@postgresql.org Date: Wednesday, July 8, 2009, 3:47 PM Ms swati chande schrieb: Thanks Andy, I am working on Windows XP. Have built from source using Visual Studio 2005. I have made a change in pg_hba.conf to include the ipconfig of my system. # TYPE DATABASEUSERCIDR-ADDRESS METHOD *hostall all my ipconfig trust* # IPv4 local connections: hostall all 127.0.0.1/32 trust # IPv6 local connections: #hostall all ::1/128 trust Yes, the * sign should removed and have to mention listen_addresses = ' * ' in Postgresql.conf file. This was to take care of the following problem: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets For this I changed the listen_addresses to my current ip. and made the same change in pg_hba.conf. Thanks Regards Swati So does it work now ? Why is there a * sign before host? This seems to be incorrect ... P.S.: dont' forget to reply also to the mailinglist (reply to all) No its still not working. The * doesn't exist in pg_hba. It was probably in the mail as I had formatted that line to be 'bold'. ah ok .. Actually it should work if you set listen_addresses to '*' in postgresql.conf. Did you change anything else in postgresql.conf or pg_hba.conf? I am not too experienced with Windows so maybe someone with more knowledge is able to find the trick (I installed 8.4 once with the one click installer ...no problems at all). But as far as I understand something is wrong with: WARNING: could not create listen socket for * FATAL: could not create any TCP/IP sockets I understand correct, that you fixed this? Then it should work as I mentioned earlier ... Cheers Andy -- 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] Problem search on text arrays, using the overlaps () operator
Hello,
Le 8/07/09 0:52, John Cheng a écrit :
I don't mean to be pesky. I was just wondering if there is anything
else I should try?
Should I simply rewrite all queries, change the form
WHERE textarr '{foo, bar}'::text[]
To
WHERE (textarr '{foo}'::text[]
OR textarr '{bar}'::text[])
?
While still puzzled by the big runtime difference you report between the
2 condition forms, I went on assessing these runtimes on my side from
the new case statements that are assumed to reflect more the real world.
Here are some measure results I got: (sorry for this long table)
seq style runtime
--- - ---
(db=slf)
N01 OR-EA 6 237
N02 CC-EA 5 250
N03 OR+EA 12 628
N04 CC+EA 12 700
N05 OR+EA 15 679
N06 CC+EA 11 510
N07 CC-EA 7 712
N08 OR-EA 8 741
N09 CC-EA 4 963
N10 OR-EA 6 499
(db=stg)
N11 CC+EA 15 978
N12 OR+EA 15 350
N13 CC-EA 8 102
N14 OR-EA 9 428
N15 OR-EA 5 267
N16 CC-EA 5 017
N17 OR-EA 6 119
N18 CC-EA 4 955
N19 OR+EA 11 722
N20 CC+EA 11 532
N21 OR-EA 7 303
N22 CC-EA 5 438
N23 CC-EA 5 519
N24 OR-EA 5 373
N25 OR-EA 5 422
N26 CC-EA 5 064
(db=stg)
N27 CC-EA 8 314
(db=slf)
N28 OR-EA 6 656
(db=stg)
N29 OR-EA 6 760
(db=slf)
N30 CC-EA 6 753
(db=stg)
N31 CC-EA 5 500
(db=slf)
N32 OR-EA 5 907
(db=stg)
N33 OR-EA 5 391
(db=slf)
N34 CC-EA 5 517
--- - --
Legend
--
seq: sequence order.
style: condition style of query.
CC: style arr{f,b} (one clause with multi-value text table).
OR: style arr{f} or arr{b} (many clauses with 1-value text table).
OR2: same style as style OR, with explicit JOIN in query expression.
+EA: performed with EXPLAIN ANALYZE on query. Slower.
-EA: performed without EXPLAIN ANALYZE on query. Faster.
runtime: run time in milliseconds.
(db=?): indicates that the following sequences have been performed after
a drop-and-create process for all the tables and indexes.
--
Results from 2 selected EXPLAIN ANALYZE sequences:
-- seq 03 (OR+EA)
Aggregate (cost=37630.52..37630.53 rows=1 width=0) (actual
time=12628.182..12628.184 rows=1 loops=1)
- Hash Join (cost=25989.12..37601.04 rows=11792 width=0) (actual
time=8796.002..12231.422 rows=30 loops=1)
Hash Cond: ((bar.id)::numeric = foo.bar_id)
- Seq Scan on bar (cost=0.00..4328.00 rows=30 width=4)
(actual time=0.025..402.458 rows=30 loops=1)
- Hash (cost=24636.81..24636.81 rows=82425 width=8) (actual
time=8795.027..8795.027 rows=2097152 loops=1)
- Bitmap Heap Scan on foo (cost=1565.44..24636.81
rows=82425 width=8) (actual time=670.248..5098.109 rows=2097152 loops=1)
Recheck Cond: ((keywords '{ford}'::text[]) OR
(keywords '{toyota}'::text[]) OR (keywords '{volkswagen}'::text[])
OR (keywords '{saturn}'::text[]) OR (keywords '{honda}'::text[])
OR (keywords '{porsche}'::text[]) OR (keywords '{hummer}'::text[])
OR (keywords '{ferrari}'::text[]))
- BitmapOr (cost=1565.44..1565.44 rows=83879
width=0) (actual time=665.516..665.516 rows=0 loops=1)
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=114.013..114.013
rows=262144 loops=1)
Index Cond: (keywords '{ford}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=72.398..72.398
rows=262144 loops=1)
Index Cond: (keywords '{toyota}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=74.118..74.118
rows=262144 loops=1)
Index Cond: (keywords
'{volkswagen}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=58.486..58.486
rows=262144 loops=1)
Index Cond: (keywords '{saturn}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=114.671..114.671
rows=524288 loops=1)
Index Cond: (keywords '{honda}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=115.290..115.290
rows=524288 loops=1)
Index Cond: (keywords
'{porsche}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=58.184..58.184
rows=262144 loops=1)
Index Cond: (keywords '{hummer}'::text[])
- Bitmap Index Scan on foo_idx
(cost=0.00..175.07 rows=10485 width=0) (actual time=58.336..58.336
[GENERAL] sslv3 alert illegal parameter
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, My server started spitting this out every second! LOG: could not accept SSL connection: sslv3 alert illegal parameter PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33) Server key in place.. What can I do about it?! TIA, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKVHAz2FH5GXCfxAsRAixyAJ9m3TxToFE1sNdeDz9SsB9IoU/Q9wCePy3T Yicl2v5RsJ+D424U9L7bKvw= =1j89 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] = Null is Null?
Hi! select * from any where is_deleted = Null select * from any where is_deleted in (Null) They are show 0 record. select * from any where is_deleted is Null It is show all records. Some of other DBs are uses Null as Null in = comparisons. Is PG not? What are the rules? Thanks: dd
Re: [Re: [GENERAL] Password?]
Yes, Its the currently logged on user. --- On Wed, 7/8/09, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: From: Andreas Wenk a.w...@netzmeister-st-pauli.de Subject: [Re: [GENERAL] Password?] To: PG-General Mailing List pgsql-general@postgresql.org Date: Wednesday, July 8, 2009, 3:54 PM Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] = Null is Null?
On Jul 8, 2009, at 1:30 PM, Durumdara wrote: Hi! select * from any where is_deleted = Null select * from any where is_deleted in (Null) They are show 0 record. Correct, that's normal in SQL. NULL means 'unknown', so you can't say whether is_deleted is true or false when it's NULL. The result of that comparison is NULL as well, 'unknown' and that makes the where-clause evaluate to false, so you don't get any rows. If it were otherwise you wouldn't be able to do some queries. select * from any where is_deleted is Null This is exactly the reason the 'is null' operator exists. It's exclusively for checking for null values. You can't say 'is_deleted is true' for example. Some of other DBs are uses Null as Null in = comparisons. Is PG not? What are the rules? Some DB's say that an empty string is the same as null, it doesn't mean they're right. In fact, it can be rather inconvenient if an empty string in your data also has a meaning (namely 'known to be an empty string' instead of 'unknown')! What if I'm comparing two columns, say in an outer join, and one of the columns is null? Does that mean I don't get my row back while I should? To me it seems like those other DB's use their comparisons with null inconsistently, or they wouldn't be able to do outer joins... 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,4a548b0a10137687714970! -- 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] howto determine rows count to be returned by DECLARE ... SELECT ...
Konstantin Izmailov pgf...@gmail.com writes: However, it is not clear how to determine max rows count that the cursor can return. The count is necessary for two purposes: render scrollbar and support jumping to the last rows in the grid. You can MOVE LAST, it'll tell you how many rows are in there, then MOVE FIRST or wherever then FETCH 10 or your page size in rows. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] = Null is Null?
Durumdara durumd...@gmail.com writes: Some of other DBs are uses Null as Null in = comparisons. Is PG not? What are the rules? PostgreSQL implements SQL, which has a 3-valued logic. There's True, there's False, and there's NULL. NULL means that we know nothing about what's in there. Would you really want to say that something you know nothing about is the same thing as this other thing you know nothing about? In PostgreSQL, NULL = NULL answers NULL (we know nothing about the result). Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] singletons per row in table AND locking response
Dennis Gearon Sent: Tuesday, July 07, 2009 9:46 PM When locking is involved, does a transaction wait for access to a row or table, or does it just fail back to the calling code? Would it be up to my PHP code to keep hammeing for access to a row/table, or could a user defined function do that? I do not know the answer to your question off hand, but be wary of pausing or hammering the database to establish a lock. Consider the implications of what happens when the application or thread with the lock crashes. Say for example that User A establishes a lock on a table and crashes. The lock persists. User B tries for a lock, is denied, and enters a loop of constantly trying. He'll be stuck in limbo until the first lock is cleared. I think the model of denying the lock and perhaps retrying with a set limit on attempts would be a better approach. For what it's worth, in my own PHP/PostgreSQL application I handle locking through the application and database. The database has a locks table. The application requests a lock by looking for a non-deleted lock in that table for whatever object (table or a row within a table) it wants. If no such lock exists, one is created and returned to the application. The lock is released at the end of the current task. The advantage is that if anything crashes, there is a page in the application that an administrator can delete any lock from, or see who holds a lock on what from when. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital I'd like to have a certain object in my PHP application have essentially individual SERIAL rows per object created site wide. So only one script instance at a time in apache can have access to a row to read and increment a value in a row. Example, (totally random idea, example only), any user on site can create a group. Each group assigns group_user_ids per site member in his group, starting at zero for each new user joining a group, no matter their site_id. My choices so far seem to be: IN PHP, Use a system file for locking only one instance of the class gets access to the table. IN PHP, Use the transaction failure to hammer the database for one instance of the class. IN PHP, Use the transaction failure to hammer the database for each ROW's instance of a class. IN POSTGRESQL, use the transaction failure to hammer the database for each ROW's instance of a class. But maybe there's more to the locking than failed transactions for UPDATE, some kind of sequential queueing of access to tables or rows for transactions? I'm trying to minimize the interfaces, cpu time, etc involved in getting access to the table. extremely basic SQL for this idea. CREATE TABLE group ( group_id SERIAL NOT NULL, CONSTRAINT PK_group PRIMARY KEY (group_id) ); CREATE TABLE singletons_for_last_grp_mbr_id_issued ( group_id INTEGER NOT NULL, last_grp_mbr_id_issued INTEGER DEFAULT 0 NOT NULL, CONSTRAINT PK_singletons PRIMARY KEY (counts_per_main, main_id) ); CREATE UNIQUE INDEX IDX_One_Group_Row_Only ON singletons_for_last_grp_mbr_id_issued (group_id); ALTER TABLE singletons_for_last_grp_mbr_id_issued ADD CONSTRAINT group_singletons_for_last_grp_mbr_id_issued FOREIGN KEY (group_id) REFERENCES group (group_id) Dennis Gearon Signature Warning EARTH has a Right To Life I agree with Bolivian President Evo Morales # The right to life: The right for no ecosystem to be eliminated by the irresponsible acts of human beings. # The right of biosystems to regenerate themselves: Development cannot be infinite. There's a limit on everything. # The right to a clean life: The right for Mother Earth to live without contamination, pollution. Fish and animals and trees have rights. # The right to harmony and balance between everyone and everything: We are all interdependent. See the movie - 'Inconvenient Truth' See the movie - 'Syriana' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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: Password?]
On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test -- 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: Password?]
Jasen Betts schrieb: On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above. Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] = Null is Null?
On Wed, Jul 8, 2009 at 6:03 AM, Alban Hertroysdal...@solfertje.student.utwente.nl wrote: This is exactly the reason the 'is null' operator exists. It's exclusively for checking for null values. You can't say 'is_deleted is true' for example. Uh, yes you can. is false also works. and is not true and is not false. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INTERVAL documentation bug?
I believe the documentation (Data Types section) is missing one INTERVAL field: HOUR TO SECOND http://www.postgresql.org/docs/8.4/static/datatype-datetime.html The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases: YEAR MONTH DAY HOUR MINUTE SECOND YEAR TO MONTH DAY TO HOUR DAY TO MINUTE DAY TO SECOND HOUR TO MINUTE MINUTE TO SECOND It's actually supported: test1= create table tab1 ( iv interval hour to second ); CREATE TABLE test1= insert into tab1 values ( 'PT444H59M59S' ); INSERT 0 1 test1= select * from tab1; iv --- 444:59:59 (1 row) Seb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Client only install
We are running our PostGres db on Windows 2008 Server but have several clients who are running various other versions of Windows (XP, Vista, etc). Is there are binary install for just the client side install so that we do not have to install everything? If there isn't one available how do we run just the client side on the workstations? Do we just not start the postmaster on the client? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Client only install
hello 2009/7/8 Michael Gould mgo...@intermodalsoftwaresolutions.net: We are running our PostGres db on Windows 2008 Server but have several clients who are running various other versions of Windows (XP, Vista, etc). Is there are binary install for just the client side install so that we do not have to install everything? you need only pgAdmin - it's pg client regards Pavel Stehule If there isn't one available how do we run just the client side on the workstations? Do we just not start the postmaster on the client? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] INTERVAL documentation bug?
Sebastien FLAESCH s...@4js.com writes: I believe the documentation (Data Types section) is missing one INTERVAL field: HOUR TO SECOND Hmm, you're right. IIRC I copied and pasted that list straight from the grammar, but I must have lost one line somehow. Thanks for noticing! 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] [Re: Password?]
On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote: Jasen Betts schrieb: On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... That's not true. /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above. Nor is that. Most of the postgresql client tools, including createdb, can be used by any operating system user to connect to the database as any database user. If they are called with -U foo then they will attempt to connect to the database as database user foo. If they are not called with -U then they will usually attempt to connect to the database as the current operating system user (though that can be overridden with the PGUSER or PGSERVICE environment variables). So if I'm logged in as steve and I do createdb test then I will try and connect to the database as database user steve and create the test database. If I do createdb -U postgres test I will try to connect to the database as database user postgres and create the test database. Whether I'm prompted for a password or not depends on the settings in pg_hba.conf. Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Other OS user / database user combinations may or may not need a password depending on whether pg_hba.conf is set up to ask for one or not - how that is set up as default varies, but it's fairly common to require a password. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Re: Password?]
On 08/07/2009 18:14, Steve Atkins wrote: Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Is this really so? I don't think so - I think it depends on pg_hba.conf settings, just like any other 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] Happy Birthday...
Since no one else seems to have sent it yet, I'll do the obligatory annual Happy Birthday email. Happy Birthday, PostgreSQL. You're a teenager now. The pimples will go away, in time. - Josh / eggyknap signature.asc Description: Digital signature
Re: [GENERAL] [Re: Password?]
On Jul 8, 2009, at 10:25 AM, Raymond O'Donnell wrote: On 08/07/2009 18:14, Steve Atkins wrote: Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Is this really so? I don't think so - I think it depends on pg_hba.conf settings, just like any other user. It does, yes. But most of the distributions I've seen tend to set it up that way (as otherwise there's not really a good way to do automated maintenance and backups, nor any easy way to bootstrap the database). So it's not hardwired that way, just typically set up that way (on non-Windows OSes anyway - Windows has issues that likely mean it's setup differently there). 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
[GENERAL] Checkpoint Tuning Question
I'm running a steady state test where I am pushing about 600 queries per second through a Posgres 8.3 system on an 8 CPU Linux system. It's a mix of inserts, updates, and deletes on a few tables - the two biggest ones probably have about 200,000 rows. Harddrive is just a simple, run-of-the-mill desktop drive. Here are parameters that I have changed from defaults: shared_buffers =100MB synchronous_commit=off And, after noting complaints in the log file about checkpoint intervals, I set: checkpoint_segments=10 Then I turned on slow query logging for queries that take more than 1 second, and checkpoint logging. Typically, I see no slow queries. The system handles the load just fine. Once in a while, I'll see a query that takes 3 - 5 seconds. However, once the checkpoint process begins, I get a whole flood of queries that take between 1 and 10 seconds to complete. My throughput crashes to near nothing. The checkpoint takes between 45 seconds and a minute to complete. After the checkpoint completes - the system returns to having very few slow queries, and the keeps up with the load fine. Is there anything I can do to prevent the occasional slow query? Is there anything I can do to prevent (or minimize) the performance impact of the checkpoint? Thanks, Dan -- 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: Password?]
On 08/07/2009 18:30, Steve Atkins wrote: On Jul 8, 2009, at 10:25 AM, Raymond O'Donnell wrote: Is this really so? I don't think so - I think it depends on pg_hba.conf settings, just like any other user. It does, yes. But most of the distributions I've seen tend to set it up that way (as otherwise there's not really a good way to do automated maintenance and backups, nor any easy way to bootstrap the database). So it's not hardwired that way, just typically set up that way (on non-Windows OSes anyway - Windows has issues that likely mean it's setup differently there). Ah, OK - I see what you mean. 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] PostgreSQL and Poker
a quite interesting read. http://www.codingthewheel.com/archives/stranger-than-fiction-story-online-poker-tracker-postgresql especially as an explanation of the growing number of questions from Windows-Users of PostgreSQL And ... for a tag line: PostgreSQL. Thousends bet on it. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? -- 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: Password?]
Steve Atkins schrieb: On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote: Jasen Betts schrieb: On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... That's not true. you are right!Sorry for that mistake. /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above. Nor is that. Why not? I think it is but maybe I did not write it understandable enough. See my example. Most of the postgresql client tools, including createdb, can be used by any operating system user to connect to the database as any database user. If they are called with -U foo then they will attempt to connect to the database as database user foo. If they are not called with -U then they will usually attempt to connect to the database as the current operating system user (though that can be overridden with the PGUSER or PGSERVICE environment variables). So if I'm logged in as steve and I do createdb test then I will try and connect to the database as database user steve and create the test database. If I do createdb -U postgres test I will try to connect to the database as database user postgres and create the test database. this is exactly my example. I am the system user duke but there is no role duke in the db. The result is the error message. Using -U postgres is successful. But in the case shown by Swati she is allready the system user postgres. So there's no need to put the option -U postgres. Correct? See my example. Whether I'm prompted for a password or not depends on the settings in pg_hba.conf. Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Other OS user / database user combinations may or may not need a password depending on whether pg_hba.conf is set up to ask for one or not - how that is set up as default varies, but it's fairly common to require a password. exactly. Thanks a lot for explaining that way better. Cheers, Steve Actually I think we both know how it works ;-) Cheers Andy -- 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] Checkpoint Tuning Question
Dan Armbrust daniel.armbrust.l...@gmail.com writes: However, once the checkpoint process begins, I get a whole flood of queries that take between 1 and 10 seconds to complete. My throughput crashes to near nothing. The checkpoint takes between 45 seconds and a minute to complete. You sure this is 8.3? It should spread out checkpoints over a couple of minutes by default. [thinks...] Maybe you need to increase checkpoint_segments some more. If it's forcing the checkpoint in order to hold down the number of WAL segments used up, that would explain a fast checkpoint. 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] Checkpoint Tuning Question
On Wed, Jul 8, 2009 at 12:50 PM, Tom Lanet...@sss.pgh.pa.us wrote: Dan Armbrust daniel.armbrust.l...@gmail.com writes: However, once the checkpoint process begins, I get a whole flood of queries that take between 1 and 10 seconds to complete. My throughput crashes to near nothing. The checkpoint takes between 45 seconds and a minute to complete. You sure this is 8.3? It should spread out checkpoints over a couple of minutes by default. [thinks...] Maybe you need to increase checkpoint_segments some more. If it's forcing the checkpoint in order to hold down the number of WAL segments used up, that would explain a fast checkpoint. regards, tom lane Just checked - currently running 8.3.4 on the system I'm testing on. With checkpoint_segments set to 10, the checkpoints appear to be happening due to checkpoint_timeout - which I've left at the default of 5 minutes. If I double my test load, I end up with checkpoints happening about every 4 minutes, with the log message that I read to indicate that it used up all 10 segments. So not much I can do to keep the checkpoint process from causing a burst of slow queries? Thanks, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and Poker
On Wed, 8 Jul 2009 19:39:16 +0200 Massa, Harald Armin c...@ghum.de wrote: a quite interesting read. http://www.codingthewheel.com/archives/stranger-than-fiction-story-online-poker-tracker-postgresql There are a couple of comments comment that maybe someone could correct: The popularity of PostgreSQL as DBMS for handhistories is by no means just a matter of some alleged technological superiority over MySQL. Let's not forget that Pokertracker, Holdem Manager etc is proprietary software, so they really don't have any other choice but to bundle with postgreSQL. If they were to ship their products with MySQL, they would either have to open-source their products according to the GPL, or pay hefty commercial license fees. or Bogdan's comment is right on the money. There are licensing issues with MySQL. MySQL commercial licenses are contracts with Sun. Not cheap. It had to be PostgreSQL. I understand the license differences (and for my taste I prefer GPL over BSD) but the above affirmations seems to imply pg couldn't stand up just on its technical merits. I don't think this is the case. Someone more knowledgeable about licenses and with a better English than mine should correct the comments. -- Ivan Sergio Borgonovo http://www.webthatworks.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] Checkpoint Tuning Question
On Wed, Jul 8, 2009 at 1:23 PM, Tom Lanet...@sss.pgh.pa.us wrote: Dan Armbrust daniel.armbrust.l...@gmail.com writes: With checkpoint_segments set to 10, the checkpoints appear to be happening due to checkpoint_timeout - which I've left at the default of 5 minutes. Well, you could increase both those settings so as to put the checkpoints further apart, and/or increase checkpoint_completion_target to spread the checkpoint I/O over a larger fraction of the cycle. regards, tom lane Sorry, didn't mean to get off list. Wouldn't increasing the length between checkpoints result in the checkpoint process taking even longer to complete? The way my system processes and buffers incoming data, having infrequent (but long and disruptive) checkpoints is bad, since it causes the throughput to suffer so bad - my buffers can't hold the flood, and I have to drop data. If I can reduce the impact of the checkpoints, and have them occur more frequently, they my buffers should be able to hold the incoming data during the short durations that I have slow queries. I'll go experiment with checkpoint_completion_target. Thanks, Dan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] now() + '4d' AT TIME ZONE issue
Hi all, I'm trying to select an offset timestamp at a given time zone, but I can't seem to get the syntax right. What I am *trying* to do, which doesn't work: SELECT now() AT TIME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZONE 'America/Toronto'; Which generates the error: ERROR: syntax error at or near AT LINE 1: ...ME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZO... I've tried using an embedded SELECT and CASTing it as a TIMESTAMP with no luck. SELECT now() AT TIME ZONE 'America/Toronto', CAST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZONE 'America/Toronto'; ERROR: syntax error at or near AT LINE 1: ...ST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZO... When I remove the 'AT TIME ZONE' from the offset now in either case the SELECT works. Someone mind beating me with a clue stick? Thanks! Madi -- 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: Password?]
I think I can understand what you both have discussed. But I am unable to unearth my mistake. Regards Swati --- On Wed, 7/8/09, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: From: Andreas Wenk a.w...@netzmeister-st-pauli.de Subject: Re: [GENERAL] [Re: Password?] To: Steve Atkins st...@blighty.com Cc: pgsql-general List pgsql-general@postgresql.org Date: Wednesday, July 8, 2009, 11:20 PM Steve Atkins schrieb: On Jul 8, 2009, at 6:19 AM, Andreas Wenk wrote: Jasen Betts schrieb: On 2009-07-08, Andreas Wenk a.w...@netzmeister-st-pauli.de wrote: Serge Fonville schrieb: *argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf means, that you will be asked for a password for the user you try to create a db with. If no user is given (with createdb -U [username]), this user is postgres ... Wasn't it that it uses the currently logged on user is used if no user is specified? correct - so this will be postgres because other users are not allowed to use these programs ... That's not true. you are right!Sorry for that mistake. /var/lib/postgresql/8.4/bin$ ./createdb test -p 5433 createdb: could not connect to database postgres: FATAL: role duke does not exist $ sudo su postgres postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433 postg...@duke-linux:~/8.4/bin$ auth method in pg_hba.conf is trust in this case. if it's trust any user can do ~postgres/8.4/bin/createdb -U postgres -p 5433 test nope! what you did is calling createdb as system user postgres (I believe because of the ~ sign at the beginning) *and* giving the option -U postgres. That works for sure and you even don't need -U postgres since you are allready postgres. But leave -U postgres away as a system user not equal to postgres ... see my example above. Nor is that. Why not? I think it is but maybe I did not write it understandable enough. See my example. Most of the postgresql client tools, including createdb, can be used by any operating system user to connect to the database as any database user. If they are called with -U foo then they will attempt to connect to the database as database user foo. If they are not called with -U then they will usually attempt to connect to the database as the current operating system user (though that can be overridden with the PGUSER or PGSERVICE environment variables). So if I'm logged in as steve and I do createdb test then I will try and connect to the database as database user steve and create the test database. If I do createdb -U postgres test I will try to connect to the database as database user postgres and create the test database. this is exactly my example. I am the system user duke but there is no role duke in the db. The result is the error message. Using -U postgres is successful. But in the case shown by Swati she is allready the system user postgres. So there's no need to put the option -U postgres. Correct? See my example. Whether I'm prompted for a password or not depends on the settings in pg_hba.conf. Typically the postgres operating system user is allowed to connect to the database as the postgres database user without a password. Other OS user / database user combinations may or may not need a password depending on whether pg_hba.conf is set up to ask for one or not - how that is set up as default varies, but it's fairly common to require a password. exactly. Thanks a lot for explaining that way better. Cheers, Steve Actually I think we both know how it works ;-) Cheers Andy -- 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] Checkpoint Tuning Question
Dan Armbrust daniel.armbrust.l...@gmail.com writes: On Wed, Jul 8, 2009 at 1:23 PM, Tom Lanet...@sss.pgh.pa.us wrote: Well, you could increase both those settings so as to put the checkpoints further apart, and/or increase checkpoint_completion_target to spread the checkpoint I/O over a larger fraction of the cycle. Wouldn't increasing the length between checkpoints result in the checkpoint process taking even longer to complete? You don't really care how long it takes. What you want is for it not to be chewing a bigger fraction of your I/O bandwidth than you can spare. Hence, you want it to take longer. Trying to shorten it is just going to make the spike worse. 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] now() + '4d' AT TIME ZONE issue
Madison Kelly li...@alteeve.com writes: SELECT now() AT TIME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZONE 'America/Toronto'; You've got AS future in the wrong place. 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] Password?
Hi, I started everything again from scratch. 1. Created a new user(Swati), with limited/ restricted rights. Ensured that no password is set anywhere. 2. Ran initdb from the new user. c:\postgresql\bininitdb -D c:\postgresql\data2 It displayed the DEBUG: start transaction and commit transaction states etc. and ended with DEBUG: exit(0) A warning with the following statement was also displayed: WARNING: enabling trust authentication foe local connections you can change this by editing pg_hba.conf or by initdb -A. 3. After this I executed pg_ctl: c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start got the the message:server starting and the logfile contained the following: LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets 4. To take care of the above issues, Made the following change in the postgresql.conf file: listen_addresses = 'xxx.xxx.x.x' (my current ip) and in pg_hba: host all all 'xxx.xxx.x.x' trust 5.Then issued c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start again. Now got the following in logfile: LOG: database system was shut down at 2009-07-08 22:34:50 LOG: database system is ready to accept connections LOG: autovacuum launcher started 6.Opened another command window. Now when I write in the new window (or even in the same), c:\postgresql\bincreatedb demo OR c:\postgresql\bincreateuser -S -d -R svc I am prompted for password, I don't know what to enter here. I think I am making some mistake in pg_hba.conf. Can't make out. Must be some brainless blunder some where. Thanks a ton for sparing your time and bearing with me. Please guide. Regards Swati
Re: [GENERAL] now() + '4d' AT TIME ZONE issue
Try moving your as future SELECT now() AT TIME ZONE 'America/Toronto', CAST ((SELECT now() + '4d') AS TIMESTAMP) AT TIME ZONE 'America/Toronto' as future; -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Madison Kelly Sent: Wednesday, July 08, 2009 1:33 PM To: postgres list Subject: [GENERAL] now() + '4d' AT TIME ZONE issue Hi all, I'm trying to select an offset timestamp at a given time zone, but I can't seem to get the syntax right. What I am *trying* to do, which doesn't work: SELECT now() AT TIME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZONE 'America/Toronto'; Which generates the error: ERROR: syntax error at or near AT LINE 1: ...ME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZO... I've tried using an embedded SELECT and CASTing it as a TIMESTAMP with no luck. SELECT now() AT TIME ZONE 'America/Toronto', CAST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZONE 'America/Toronto'; ERROR: syntax error at or near AT LINE 1: ...ST ((SELECT now() + '4d') AS TIMESTAMP) as future AT TIME ZO... When I remove the 'AT TIME ZONE' from the offset now in either case the SELECT works. Someone mind beating me with a clue stick? Thanks! Madi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Oracle Help in PG?
Hi, Do we have anything like Oracle Help for Java in Postgresql? Thanks
Re: [GENERAL] Password?
Ms swati chande schrieb: Hi, I started everything again from scratch. 1. Created a new user(Swati), with limited/ restricted rights. Ensured that no password is set anywhere. 2. Ran initdb from the new user. c:\postgresql\bininitdb -D c:\postgresql\data2 It displayed the DEBUG: start transaction and commit transaction states etc. and ended with DEBUG: exit(0) A warning with the following statement was also displayed: WARNING: enabling trust authentication foe local connections you can change this by editing pg_hba.conf or by initdb -A. 3. After this I executed pg_ctl: c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start got the the message:server starting and the logfile contained the following: LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets 4. To take care of the above issues, Made the following change in the postgresql.conf file: listen_addresses = 'xxx.xxx.x.x' (my current ip) and in pg_hba: host all all 'xxx.xxx.x.x' trust 5.Then issued c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start again. Now got the following in logfile: LOG: database system was shut down at 2009-07-08 22:34:50 LOG: database system is ready to accept connections LOG: autovacuum launcher started 6.Opened another command window. Now when I write in the new window (or even in the same), c:\postgresql\bincreatedb demo OR c:\postgresql\bincreateuser -S -d -R svc I am prompted for password, I don't know what to enter here. I think I am making some mistake in pg_hba.conf. Can't make out. Must be some brainless blunder some where. Thanks a ton for sparing your time and bearing with me. Please guide. Regards Swati Swati, sorry to say - but I got no solution as I cannot try to simulate this. I do not have a Windows machine ... hopefully someone else can help. One thing anyway ... Step 4. seems to be correct. Actually, is there a user postgres on your system? Why not give postgres then a password (in the windows user administration) and use c:\postgresql\bincreateuser -U postgres -S -d -R svc But this is really vague ... Cheers Andy -- 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] now() + '4d' AT TIME ZONE issue
Tom Lane wrote: Madison Kelly li...@alteeve.com writes: SELECT now() AT TIME ZONE 'America/Toronto', now() + '4d' AS future AT TIME ZONE 'America/Toronto'; You've got AS future in the wrong place. regards, tom lane Thank you both, Chris and Tom. That was indeed my oops. Madi -- 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] Checkpoint Tuning Question
Wouldn't increasing the length between checkpoints result in the checkpoint process taking even longer to complete? You don't really care how long it takes. What you want is for it not to be chewing a bigger fraction of your I/O bandwidth than you can spare. Hence, you want it to take longer. Trying to shorten it is just going to make the spike worse. regards, tom lane I bumped the segments up to 15, and the timeout up to 10 minutes, and changed the completion target to .7. What I observe now is that I get a short (1-2 second) period where I get slow queries - I'm running about 30 queries in parallel at any given time - it appears that all 30 queries get paused for a couple of seconds at the moment that a checkpoint begins. However, after the initial slowdown, I don't get any additional slow queries logged while the checkpoint process runs. My takeaway is that starting the checkpoint process is really expensive - so I don't want to start it very frequently. And the only downside to longer intervals between checkpoints is a longer recovery time if the system crashes? Thanks, Dan -- 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] Password?
Ms swati chande wrote: Made the following change in the postgresql.conf file: listen_addresses = 'xxx.xxx.x.x' (my current ip) and in pg_hba: host all all 'xxx.xxx.x.x' trust ... 6.Opened another command window. Now when I write in the new window (or even in the same), c:\postgresql\bincreatedb demo OR c:\postgresql\bincreateuser -S -d -R svc I am prompted for password, I don't know what to enter here. try ... createuser -h xxx.xxx.x.x -S -d -R svc by default, its connecting to localhost (127.0.0.1) rather than your IP. (note this behavior is different than on Unix/Linux type systems, where by default it connects to a 'unix domain socket', which doesn't exist on MS Windows). If you only want to connect to this database from the same computer, I'd suggest using 127.0.0.1/localhost rather than xxx.xxx.x.x in both the listen_address and pg_hba... -- 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] about pg_stat_get_db_xact_commit
abdelhak benmohamed wrote: Hellow I like to track the number of committed transaction for my database So I use the following command Select pg_stat_get_db_xact_commit(16384) (16384 is the oid of my database) It gives me 35 But if I execute the same command another time, it gives me more than 35. Between the first select and the second select I don’t execute transaction on my database Please, can any one help me to understanding the cause of the change? Maybe autovacuum running underneath committed some transactions. (Also, each time you call the function it starts and commit a new transaction). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Checkpoint Tuning Question
On Wed, 8 Jul 2009, Dan Armbrust wrote: With checkpoint_segments set to 10, the checkpoints appear to be happening due to checkpoint_timeout - which I've left at the default of 5 minutes. OK, then that's as far upwards as you probably need to tweak that for your workload, even though most systems tuned for performance need 30+ instead. If what you're getting nailed with is the pause during the initial checkpoint processing, the only real option you have on the database side is to lower shared_buffers. You might get some improvement playing with operating system tunables too, to make it buffer less data and write more aggressively instead. The idea is that when the checkpoint starts, there should be little else buffered already. On Linux this is best accomplished with tuning dirty_background_ratio downward for example. There's more on this subject than you probably ever wanted to know at http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm if you haven't seen that already. -- * 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
Re: [GENERAL] Checkpoint Tuning Question
On Wed, 8 Jul 2009, Dan Armbrust wrote: My takeaway is that starting the checkpoint process is really expensive - so I don't want to start it very frequently. And the only downside to longer intervals between checkpoints is a longer recovery time if the system crashes? And additional disk space wasted in hold the write-ahead logs. You're moving in the right direction here, the less checkpoints the better as long as you can stand the recovery time. What you'll discover if you bump checkpoint_segments up high enough is that you have to lengthen the test run you're trying, because eventually you'll reach a point where there are none of them happening during some test runs. -- * 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
Re: [GENERAL] PostgreSQL and Poker
On Wed, Jul 8, 2009 at 12:27 PM, Ivan Sergio Borgonovom...@webthatworks.it wrote: On Wed, 8 Jul 2009 19:39:16 +0200 Massa, Harald Armin c...@ghum.de wrote: a quite interesting read. http://www.codingthewheel.com/archives/stranger-than-fiction-story-online-poker-tracker-postgresql There are a couple of comments comment that maybe someone could correct: The popularity of PostgreSQL as DBMS for handhistories is by no means just a matter of some alleged technological superiority over MySQL. Let's not forget that Pokertracker, Holdem Manager etc is proprietary software, so they really don't have any other choice but to bundle with postgreSQL. If they were to ship their products with MySQL, they would either have to open-source their products according to the GPL, or pay hefty commercial license fees. or Bogdan's comment is right on the money. There are licensing issues with MySQL. MySQL commercial licenses are contracts with Sun. Not cheap. It had to be PostgreSQL. I understand the license differences (and for my taste I prefer GPL over BSD) but the above affirmations seems to imply pg couldn't stand up just on its technical merits. I don't think this is the case. Exactly, it could have been interbase / firebird, sqllite, berkelydb, and a couple other choices that are free. MySQL's licensing just took them out of the running right at the start. I'm not sure the comments need correction really, although the alleged bit kind of rubs me the wrong way, but you're not gonna convince a MySQL fanboi about anything anyway. -- 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] Oracle Help in PG?
On Wed, Jul 8, 2009 at 12:53 PM, SHARMILA JOTHIRAJAHsharmi...@yahoo.com wrote: Hi, Do we have anything like Oracle Help for Java in Postgresql? Not being familiar with what Oracle Help for Java it might help if you tell us what that 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] = Null is Null?
Some DB's say that an empty string is the same as null, it doesn't mean they're right. In fact, it can be rather inconvenient if an empty string in your data also has a meaning (namely 'known to be an empty string' instead of 'unknown')! This is the behavior in Oracle. And I found that out the hard way... what a PITA. Anyhow, this isn't specific to Postgres, but here's a Wikipedia entry for how ternary or 3 valued logic works. http://en.wikipedia.org/wiki/Ternary_logic -- 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] Checkpoint Tuning Question
Greg Smith gsm...@gregsmith.com writes: On Wed, 8 Jul 2009, Dan Armbrust wrote: What I observe now is that I get a short (1-2 second) period where I get slow queries - I'm running about 30 queries in parallel at any given time - it appears that all 30 queries get paused for a couple of seconds at the moment that a checkpoint begins. However, after the initial slowdown, I don't get any additional slow queries logged while the checkpoint process runs. If what you're getting nailed with is the pause during the initial checkpoint processing, the only real option you have on the database side is to lower shared_buffers. He's only got 100MB of shared buffers, which doesn't seem like much considering it's apparently a fairly beefy system. I definitely don't see how one CPU spinning over the buffer headers in BufferSync is going to create the sort of hiccup he's describing. Dan, are you sure that this hiccup is happening at the *start* of a checkpoint? Do you have log_checkpoints turned on, and if so what does it show? 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] Checkpoint Tuning Question
Tom Lane wrote: He's only got 100MB of shared buffers, which doesn't seem like much considering it's apparently a fairly beefy system. a beefy system with... Harddrive is just a simple, run-of-the-mill desktop drive. which is going to severely limit random write throughput -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and Poker
On Wed, 8 Jul 2009, Ivan Sergio Borgonovo wrote: Someone more knowledgeable about licenses and with a better English than mine should correct the comments. Someone named Bogdan already commented adequately about the license stuff. I just debunked the idea that SQLite would be usable here. All this poker talk is bad, I've been staying away from the tables for a while now but fear this topic is going to pull me back again--just to see how the database apps have matured, of course. -- * 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
Re: [GENERAL] PostgreSQL and Poker
On Wed, 8 Jul 2009 13:22:14 -0600 Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Jul 8, 2009 at 12:27 PM, Ivan Sergio Borgonovom...@webthatworks.it wrote: On Wed, 8 Jul 2009 19:39:16 +0200 Massa, Harald Armin c...@ghum.de wrote: a quite interesting read. http://www.codingthewheel.com/archives/stranger-than-fiction-story-online-poker-tracker-postgresql There are a couple of comments comment that maybe someone could correct: The popularity of PostgreSQL as DBMS for handhistories is by no means just a matter of some alleged technological superiority over MySQL. Let's not forget that Pokertracker, Holdem Manager etc is proprietary software, so they really don't have any other choice but to bundle with postgreSQL. If they were to ship their products with MySQL, they would either have to open-source their products according to the GPL, or pay hefty commercial license fees. or Bogdan's comment is right on the money. There are licensing issues with MySQL. MySQL commercial licenses are contracts with Sun. Not cheap. It had to be PostgreSQL. I understand the license differences (and for my taste I prefer GPL over BSD) but the above affirmations seems to imply pg couldn't stand up just on its technical merits. I don't think this is the case. Exactly, it could have been interbase / firebird, sqllite, berkelydb, and a couple other choices that are free. MySQL's licensing just took them out of the running right at the start. You can actually build up closed source software with MySQL as a server, it depends on how you do it. Aren't there any DB with LGPL library license? Still the above statement sounds too much as: pg wasn't chosen for it's technical merits but for the license. I don't think their only option was pg for licensing reasons. Or am I misunderstanding what you wrote? or... am I plainly wrong? -- Ivan Sergio Borgonovo http://www.webthatworks.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] Checkpoint Tuning Question
John R Pierce pie...@hogranch.com writes: a beefy system with... Harddrive is just a simple, run-of-the-mill desktop drive. which is going to severely limit random write throughput True, which is why he's having to flail so hard to keep the checkpoint from saturating his I/O. However, the latest report says that he managed that, and yet there's still a one-or-two-second transient of some sort. I'm wondering what's causing that. If it were at the *end* of the checkpoint, it might be the disk again (failing to handle a bunch of fsyncs, perhaps). But if it really is at the *start* then there's something else odd happening. 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] Checkpoint Tuning Question
On Wed, 8 Jul 2009, Tom Lane wrote: He's only got 100MB of shared buffers, which doesn't seem like much considering it's apparently a fairly beefy system. I definitely don't see how one CPU spinning over the buffer headers in BufferSync is going to create the sort of hiccup he's describing. Agreed, it doesn't seem like a likely cause. If the problem reduces in magnitude in proportion with the size of the buffer cache, we might have to accept that's it's true regardless; that's why I was curious to see what impact that had on the test results. -- * 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
Re: [GENERAL] Checkpoint Tuning Question
However, the latest report says that he managed that, and yet there's still a one-or-two-second transient of some sort. I'm wondering what's causing that. If it were at the *end* of the checkpoint, it might be the disk again (failing to handle a bunch of fsyncs, perhaps). But if it really is at the *start* then there's something else odd happening. regards, tom lane Log output during heavy load - checkpoint logging on, and slow query logging on for queries that take longer than 1 second. Blank space inserted to show interesting bits Almost all of the slow query log messages are logged within about 3 seconds of the checkpoint starting message. LOG: checkpoint starting: xlog LOG: duration: 1101.419 ms execute unnamed: select dynamichos0_.ethernetmacaddr as ethernet1_0_, dynamichos0_.ipaddr as ipaddr0_, dynamichos0_.cpemac as cpemac0_, dynamichos0_.last_updated as last3_0_, dynamichos0_.leasetime as leasetime0_, dynamichos0_.regtime as regtime0_ from iphost dynamichos0_, cpe cpe1_ where dynamichos0_.cpemac=cpe1_.cpemac and 1=1 and dynamichos0_.ethernetmacaddr=$1 and dynamichos0_.cpemac=$2 and cpe1_.regBaseId=$3 and dynamichos0_.ipaddr$4 DETAIL: parameters: $1 = '01:01:01:56:01:7F', $2 = '00:55:00:82', $3 = '01:01:01:56', $4 = '000.000.000.000' LOG: duration: 1101.422 ms execute unnamed: insert into iphost (cpemac, last_updated, leasetime, regtime, ethernetmacaddr, ipaddr) values ($1, $2, $3, $4, $5, $6) DETAIL: parameters: $1 = '01:AA:00:A0', $2 = '2009-07-08 15:33:20.673', $3 = '2009-07-08 21:06:40.67', $4 = '2009-07-08 15:33:20.67', $5 = '01:01:02:AB:01:9D', $6 = '2.171.156.0' snip about 200 lines of similar messages LOG: duration: 1501.905 ms execute unnamed: select dynamichos0_.ethernetmacaddr as ethernet1_0_, dynamichos0_.ipaddr as ipaddr0_, dynamichos0_.cpemac as cpemac0_, dynamichos0_.last_updated as last3_0_, dynamichos0_.leasetime as leasetime0_, dynamichos0_.regtime as regtime0_ from iphost dynamichos0_, cpe cpe1_ where dynamichos0_.cpemac=cpe1_.cpemac and 1=1 and dynamichos0_.ethernetmacaddr=$1 and dynamichos0_.cpemac=$2 and cpe1_.regBaseId=$3 and dynamichos0_.ipaddr$4 DETAIL: parameters: $1 = '01:01:01:C3:01:8B', $2 = '00:C2:00:8E', $3 = '01:01:01:C3', $4 = '000.000.000.000' LOG: checkpoint complete: wrote 9975 buffers (77.9%); 0 transaction log file(s) added, 0 removed, 15 recycled; write=156.576 s, sync=0.065 s, total=156.662 s LOG: checkpoint starting: xlog LOG: duration: 1104.780 ms execute unnamed: delete from iphost where ethernetmacaddr=$1 and ipaddr=$2 and last_updated=$3 DETAIL: parameters: $1 = '01:01:01:33:01:AA', $2 = '1.50.169.0', $3 = '2009-07-08 15:32:57.131' LOG: duration: 1106.499 ms execute unnamed: select cpe0_.cpemac as cpemac2_0_, cpe0_.changeTime as changeTime2_0_, cpe0_.comment as comment2_0_, cpe0_.configuration as configur4_2_0_, cpe0_.cpeconfigid as cpeconf17_2_0_, cpe0_.cpefilterid as cpefilt18_2_0_, cpe0_.endTime as endTime2_0_, cpe0_.ispId as ispId2_0_, cpe0_.last_updated as last7_2_0_, cpe0_.reglocationid as regloca19_2_0_, cpe0_.modelId as modelId2_0_, cpe0_.numberOfHosts as numberOf9_2_0_, cpe0_.regBaseId as regBaseId2_0_, cpe0_.regTime as regTime2_0_, cpe0_.roamAllowed as roamAll12_2_0_, cpe0_.serialNumber as serialN13_2_0_, cpe0_.slaid as slaid2_0_, cpe0_.enable as enable2_0_, cpe0_.staticip as staticip2_0_, cpe0_.subscriberid as subscri21_2_0_, cpe0_.swVersion as swVersion2_0_, cpe0_.vlanid as vlanid2_0_, cpe0_.voipid as voipid2_0_ from cpe cpe0_ where cpe0_.cpemac=$1 DETAIL: parameters: $1 = '00:84:00:37' snip about 300 lines of similar messages LOG: duration: 1205.828 ms execute unnamed: insert into iphost (cpemac, last_updated, leasetime, regtime, ethernetmacaddr, ipaddr) values ($1, $2, $3, $4, $5, $6) DETAIL: parameters: $1 = '02:31:00:25', $2 = '2009-07-08 15:39:53.718', $3 = '2009-07-08 21:13:13.715', $4 = '2009-07-08 15:39:53.715', $5 = '01:01:03:32:01:22', $6 = '3.51.33.0' LOG: duration: 1203.287 ms execute unnamed: insert into iphost (cpemac, last_updated, leasetime, regtime, ethernetmacaddr, ipaddr) values ($1, $2, $3, $4, $5, $6) DETAIL: parameters: $1 = '03:47:00:81', $2 = '2009-07-08 15:39:53.72', $3 = '2009-07-08 21:13:13.717', $4 = '2009-07-08 15:39:53.717', $5 = '01:01:04:48:01:7E', $6 = '4.74.125.0' LOG: duration: 1201.480 ms execute unnamed: insert into iphost (cpemac, last_updated, leasetime, regtime, ethernetmacaddr, ipaddr) values ($1, $2, $3, $4, $5, $6) DETAIL: parameters: $1 = '01:12:00:7F', $2 = '2009-07-08 15:39:53.725', $3 = '2009-07-08 21:13:13.721', $4 = '2009-07-08 15:39:53.721', $5 = '01:01:02:13:01:7C', $6 = '2.19.123.0' LOG: checkpoint complete: wrote 9794 buffers (76.5%); 0 transaction log file(s) added, 0 removed, 15 recycled; write=148.084 s, sync=0.062 s, total=148.172 s -- 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] Oracle Help in PG?
alot of Oracle experts answer t...@yahoogroups.com what is your oracle question? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Wed, 8 Jul 2009 11:53:51 -0700 From: sharmi...@yahoo.com Subject: [GENERAL] Oracle Help in PG? To: pgsql-general@postgresql.org Hi, Do we have anything like Oracle Help for Java in Postgresql? Thanks _ Windows Live™ SkyDrive™: Get 25 GB of free online storage. http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_SD_25GB_062009
Re: [GENERAL] Oracle Help in PG?
Oracle help for java is nothing at all to do with oracle the database, I don't think. It's just html-based online docs, with a web-based and standalone java based browser. So I'm not sure I understand the original posters question. Postgresql is documented in docbook, and there are downloads as PDF and windows help files as well as live html at http://www.postgresql.org/docs/manuals/ . Cheers, Steve On Jul 8, 2009, at 2:14 PM, Martin Gainty wrote: alot of Oracle experts answer t...@yahoogroups.com what is your oracle question? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Wed, 8 Jul 2009 11:53:51 -0700 From: sharmi...@yahoo.com Subject: [GENERAL] Oracle Help in PG? To: pgsql-general@postgresql.org Hi, Do we have anything like Oracle Help for Java in Postgresql? Thanks Windows Live™ SkyDrive™: Get 25 GB of free online storage. Get it on your BlackBerry or iPhone. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and Poker
On Wednesday 8. July 2009, Scott Marlowe wrote: I'm not sure the comments need correction really, although the alleged bit kind of rubs me the wrong way, but you're not gonna convince a MySQL fanboi about anything anyway. A MySQL fanboi will take offense of the mere fact that anybody will actually prefer any other db engine over MySQL. And of course he will consider anybody preferring any other db engine as (insert name of any product here) fanbois. And that is of course one of the reasons why Postgres is not accepted in the enterprise. I'm a little surprised that this particular MySQL fanboi actually managed to spell PostgreSQL correctly. -- 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
Re: [GENERAL] Problem search on text arrays, using the overlaps () operator
Accidentally sent to nha only --- On Wed, 7/8/09, John Cheng jlch...@ymail.com wrote: From: John Cheng jlch...@ymail.com Subject: Re: [GENERAL] Problem search on text arrays, using the overlaps () operator To: nha lyondi...@free.fr Date: Wednesday, July 8, 2009, 4:24 PM Hi nha, I will try out your suggestion about a materialized view. I had never even thought about trying it. As luck would have it, I had to try out these tests on a different database today, which resulted in a different query plan that executed both forms in the same time. This different plan used a merge join instead of a hash join. I will research this are more to see if I learn anything new. You also pointed out that my queries (reports) are simply reporting off of a lot of data. Perhaps I need to see if the windowing functions in 8.4 can help improve things, or perhaps try to partition the data. Unfortunately, these kind of changes will be bigger than I had expected. -- 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] Checkpoint Tuning Question
Dan Armbrust daniel.armbrust.l...@gmail.com writes: Almost all of the slow query log messages are logged within about 3 seconds of the checkpoint starting message. LOG: checkpoint complete: wrote 9975 buffers (77.9%); 0 transaction log file(s) added, 0 removed, 15 recycled; write=156.576 s, sync=0.065 s, total=156.662 s Huh. And there's just about no daylight between the total checkpoint time and the write+sync time (barely more than 20msec in both examples). So that seems to wipe out the thought I had that maybe we'd underestimated the work involved in one of the other steps of checkpoint. As Greg commented upthread, we seem to be getting forced to the conclusion that the initial buffer scan in BufferSync() is somehow causing this. There are a couple of things it'd be useful to try here: * see how the size of the hiccup varies with shared_buffers; * try inserting a delay into that scan loop, as per attached quick-and-dirty patch. (Numbers pulled from the air, but we can worry about tuning after we see if this is really where the problem is.) regards, tom lane *** src/backend/storage/buffer/bufmgr.c~Tue Jan 1 14:45:51 2008 --- src/backend/storage/buffer/bufmgr.c Wed Jul 8 18:12:49 2009 *** *** 1037,1042 --- 1037,1045 } UnlockBufHdr(bufHdr); + + if ((buf_id % 256) == 0) + pg_usleep(1000L); } if (num_to_write == 0) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] temp tables and replication/connection sharing/pooling
I was wondering if I was going to use any of the replication available (I'm thinking especially to pgpool but I'm also interested in any other tool as slony, pgcluster...), should I take into account any side effect on temp tables? I'm currently using php and pg_connect for a web application. I'm not planning to use pg_pconnect. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.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] PostgreSQL and Poker
On Jul 8, 2009, at 1:20 PM, Greg Smith wrote: On Wed, 8 Jul 2009, Ivan Sergio Borgonovo wrote: Someone more knowledgeable about licenses and with a better English than mine should correct the comments. Someone named Bogdan already commented adequately about the license stuff. I just debunked the idea that SQLite would be usable here. All this poker talk is bad, I've been staying away from the tables for a while now but fear this topic is going to pull me back again-- just to see how the database apps have matured, of course. I just LOL'd at a db guy trying to stay away from the tables :) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] Password?
On Wednesday 08 July 2009 11:48:08 am Ms swati chande wrote: Hi, I started everything again from scratch. 1. Created a new user(Swati), with limited/ restricted rights. Ensured that no password is set anywhere. How limited? Can this user do administrative tasks, i.e create database,create user? 2. Ran initdb from the new user. c:\postgresql\bininitdb -D c:\postgresql\data2 It displayed the DEBUG: start transaction and commit transaction states etc. and ended with DEBUG: exit(0) A warning with the following statement was also displayed: WARNING: enabling trust authentication foe local connections you can change this by editing pg_hba.conf or by initdb -A. 3. After this I executed pg_ctl: c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start got the the message:server starting and the logfile contained the following: LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets LOG: could not bind IPv4 socket: No error HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost FATAL: could not create any TCP/IP sockets 4. To take care of the above issues, Made the following change in the postgresql.conf file: listen_addresses = 'xxx.xxx.x.x' (my current ip) and in pg_hba: host all all 'xxx.xxx.x.x' trust Can you show the complete pg_hba.conf file? Or to put it another way is the above line the only uncommented line in the file? 5.Then issued c:\postgresql\binpg_ctl -D c:\postgresql\data2 -l logfile start again. Now got the following in logfile: LOG: database system was shut down at 2009-07-08 22:34:50 LOG: database system is ready to accept connections LOG: autovacuum launcher started 6.Opened another command window. Now when I write in the new window (or even in the same), c:\postgresql\bincreatedb demo OR c:\postgresql\bincreateuser -S -d -R svc I am prompted for password, I don't know what to enter here. Are you running this as 'Swati' user? I think I am making some mistake in pg_hba.conf. Can't make out. Must be some brainless blunder some where. Thanks a ton for sparing your time and bearing with me. Please guide. Regards Swati -- 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] ZFS prefetch considered evil?
On 08/07/2009, at 8:39 PM, Alban Hertroys wrote: On Jul 8, 2009, at 2:50 AM, Yaroslav Tykhiy wrote: Hi All, I have a mid-size database (~300G) used as an email store and running on a FreeBSD + ZFS combo. Its PG_DATA is on ZFS whilst xlog goes to a different FFS disk. ZFS prefetch was enabled by default and disk time on PG_DATA was near 100% all the time with transfer rates heavily biased to read: ~50-100M/s read vs ~2-5M/s write. A former researcher, I was going to set up disk performance monitoring to collect some history and see if disabling prefetch would have any effect, but today I had to find out the difference the hard way. Sorry, but that's why the numbers I can provide are quite approximate. Due to a peak in user activity the server just melted down, with mail data queries taking minutes to execute. As the last resort, I rebooted the server with ZFS prefetch disabled -- it couldn't be disabled at run time in FreeBSD. Now IMAP feels much more responsive; transfer rates on PG_DATA are mostly 10M/s read and 1-2M/s write; and disk time stays way below 100% unless a bunch of email is being inserted. My conclusion is that although ZFS prefetch is supposed to be adaptive and handle random access more or less OK, in reality there is plenty of room for improvement, so to speak, and for now Postgresql performance can benefit from its staying just disabled. The same may apply to other database systems as well. Are you sure you weren't hitting swap? A sceptic myself, I genuinely understand your doubt. But this time I was sure because I paid attention to the name of the device involved. Moreover, a thrashing system wouldn't have had such a disparity between disk read and write rates. IIRC prefetch tries to keep data (disk blocks?) in memory that it fetched recently. What you described is just a disk cache. And a trivial implementation of prefetch would work as follows: An application or other file/disk consumer asks the provider (driver, kernel, whatever) to read, say, 2 disk blocks worth of data. The provider thinks, I know you are short- sighted; I bet you are going to ask for more contiguous blocks very soon, so it schedules a disk read for many more contiguous blocks than requested and caches them in RAM. For bulk data applications such as file serving this trick works as a charm. But other applications do truly random access and they never come back after the prefetched blocks; in this case both disk bandwidth and cache space are wasted. An advanced implementation can try to distinguish sequential and random access patterns, but in reality it appears to be a challenging task. ZFS uses quite a bit of memory, so if you distributed all your memory to be used by just postgres and disk cache then you didn't leave enough space for the prefetch data and _something_ will be moved to swap. I hope you know that FreeBSD is exceptionally good at distributing available memory between its consumers. That said, useless prefetch indeed puts extra pressure on disk cache and results in unnecessary cache evictions, thus making things even worse. It is true that ZFS is memory hungry and so rather sensitive to non-optimal memory use patterns. Useless prefetch wastes memory that could be used to speed up other ZFS operations. If you're running FreeBSD i386 then ZFS requires some careful tuning due to the limits a 32-bit OS puts on memory. I recall ZFS not being very stable on i386 a while ago for those reasons, which has by now been fixed as far as possible, but it's not ideal (and it likely never will be). I use FreeBSD/amd64 and I'm generally happy with ZFS on that platform. You'll probably want to ask about this on the FreeBSD mailing lists as well, they'll know much better than I do ;) Are you a local FreeBSD expert? ;-) Jokes apart, I don't think this topic has to do with FreeBSD as such; it is mostly about making the advanced technologies of Postgresql and ZFS go well together. Even ZFS developers admit that in database related applications exceptions from general ZFS practices and rules may be called for. When I set up my next ZFS based Postgresql server, I think I'll play with the recordsize property of ZFS and see if setting it to PAGESIZE makes any difference. Thanks, Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and Poker
On Wed, 8 Jul 2009, Ivan Sergio Borgonovo wrote: You can actually build up closed source software with MySQL as a server, it depends on how you do it. I seriously doubt that: http://www.mysql.com/about/legal/licensing/oem/ The terms under which you can treat MySQL as a more open piece of software are pretty tightly constrained: http://www.mysql.com/about/legal/licensing/foss-exception/ -- * 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] c++ program to connect to postgre database
Hello, I'm thinking of making a program in C++ (API) that will connect, read, and write to a PostgreSQL database. I honestly have no idea where to start and how to implement it in C++ using Dev C++ IDE. Any help would be greatly appreciated. Thanks. Sincerely, Rhey New Email addresses available on Yahoo! Get the Email name you#39;ve always wanted on the new @ymail and @rocketmail. Hurry before someone else does! http://mail.promotions.yahoo.com/newdomains/aa/
Re: [GENERAL] c++ program to connect to postgre database
ramon rhey serrano wrote: Hello, I'm thinking of making a program in C++ (API) that will connect, read, and write to a PostgreSQL database. I honestly have no idea where to start and how to implement it in C++ using Dev C++ IDE. Any help would be greatly appreciated. C++ easily calls C libaries... I'd probably use libpq calls, see http://www.postgresql.org/docs/current/static/libpq.html there is an available C++ wrapper on this also, see http://pqxx.org/development/libpqxx/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and Poker
On Wed, Jul 8, 2009 at 10:05 PM, Greg Smithgsm...@gregsmith.com wrote: On Wed, 8 Jul 2009, Ivan Sergio Borgonovo wrote: You can actually build up closed source software with MySQL as a server, it depends on how you do it. I seriously doubt that: http://www.mysql.com/about/legal/licensing/oem/ The terms under which you can treat MySQL as a more open piece of software are pretty tightly constrained: http://www.mysql.com/about/legal/licensing/foss-exception/ Back in the day (v6.5.2) I picked pgsql because it had the features I needed, good enough performance, fair stability (as good or better than mysql's of the day) and that it didn't have mysql's restrictive licensing. Over time I think pgsql has come much further than mysql, and the focus there has never been as clear as it is here, in terms of don't surprise the user in dangerous ways. I think the mysql fanbois who say these things in logs are usually just not at all familiar with using pgsql on a daily basis. Under any modern load, pgsql and mysql are usually no more than +/- 30% or so in performance, unless you're using a broken GA release like 5.0.x or so (whichever one tweakers tested them on that it imploded on the Sun 32 thread CPU). To be fair, pgsql has had performance regressions show up, and some of them take a few weeks to figure out. But they're usually not as catastrophic as the one that mysql had in that test. So when I have to use MySQL it's a small project / application that doesn't tax the machine or the db. Like internal ticketing maybe. Whenever I think man, this might get really big or have thousands of hits per minute. I want pgsql. When I need to process and replicate 200 transactions per second or more, I really want to use pgsql more than mysql. I know that between log shipping and slony I can guarantee downtimes in the minutes during which things switch over, or are switched by hand, than the possible hours involved should I have to restore from backup. Mysql has come a long way, but the still somewhat loose adherence to data integrity princples makes it a bad choice for important data. Until there's a version that just runs on innodb and only innodb or something like it, which has ALL the cool features (network db, transactional db, full text indexing db) in one handler I don't want to mess with it's sort of fits here, sort of fits there feature set. I am happy about the companies that may be forking it. It'd be nice to have a pure GPL no commercial license version that runs on one solid reliable table handler. Or even allows me, the db to easily pick which ones go where, so I don't have banking systems getting built on myisam. I think that focusing on one good table handler in postgresql has been a winning proposition so far. That and release discipline which has been pretty meh grade in the last few years for mysql. -- 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] ZFS prefetch considered evil?
On Wed, Jul 8, 2009 at 7:53 PM, Yaroslav Tykhiyy...@barnet.com.au wrote: Are you a local FreeBSD expert? ;-) Jokes apart, I don't think this topic has to do with FreeBSD as such; it is mostly about making the advanced technologies of Postgresql and ZFS go well together. Even ZFS developers admit that in database related applications exceptions from general ZFS practices and rules may be called for. That may or may not be true. What other OSes have ZFS someone could try to duplicate your results and then test fixes for them? I run various linux flavors, but would be more than willing to repurpose a test server for pgsql testing freebsd. Got an 8 disk HW RAID machine due in in a month I could test on for a few days. It appears the only way to run it under linux (my primary OS) is with fuse. But I'm willing to try it there too. -- 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] ZFS prefetch considered evil?
On Wed, 8 Jul 2009, Yaroslav Tykhiy wrote: My conclusion is that although ZFS prefetch is supposed to be adaptive and handle random access more or less OK, in reality there is plenty of room for improvement, so to speak, and for now Postgresql performance can benefit from its staying just disabled. The same may apply to other database systems as well. Yup; this is even spelled out at http://www.cuddletech.com/blog/pivot/entry.php?id=1040 ...the most common complain tends to be by databases which strictly work in fixed 8K blocks and manage their own caches very effectively. If you think you have such a case, file-level prefetch can be tuned on the fly using mdb, I encourage you to play with it and see what is best for your workload... Anecdotal reports (which never seem to have repeatable test cases sadly) abound about prefetch issues: http://southbrain.com/south/2008/04/the-nightmare-comes-slowly-zfs.html http://unix.derkeiler.com/Mailing-Lists/FreeBSD/current/2007-06/msg00671.html Also, there was a pretty serious ZFS problem in this area that got fixed in the middle of last year on Solaris. Your FreeBSD install might be based on a build that is using the older, known bad logic here. See http://www.solarisinternals.com/wiki/index.php/ZFS_Evil_Tuning_Guide#Device-Level_Prefetching and http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6437054 for details. -- * 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
