Re: [GENERAL] Port?
How come? It is working here since years. Can you try this command and send me the screenshot of the error you are getting if any. pg_ctl start -o -i & Regards, Kallol. -Original Message- From: b b [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 12:06 To: Kallol Nandi Subject: Re: [GENERAL] Port? But -i is not a valid option. I tried it said so. Also pg_ctl --help doesn't include a -i anyway. Cheers. --- Kallol Nandi <[EMAIL PROTECTED]> wrote: > Hi, > > Include -i in the options for starting the server. > > e.g. pg_ctl start -o -i & > > -i enables TCP/IP connections > > Regards, > Kallol > > -Original Message- > From: b b [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 31, 2003 11:56 > To: Kallol Nandi > Subject: Re: [GENERAL] Port? > > > > I looked up the pg_ctl command and it doesn't have > a > -i option? what would it do anyway? > > Cheers. > > > --- Kallol Nandi <[EMAIL PROTECTED]> wrote: > > Have you started postmaster with the -i option? > > If not then doing so will solve your problem. > > > > Regards, > > Kallol. > > > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] > Behalf > > Of b b > > Sent: Thursday, July 31, 2003 11:27 > > To: postgresql > > Subject: [GENERAL] Port? > > > > > > > > What port does the postgresql server listen on. I > > am > > trying to connect using pg_connect from php using > > port > > 5432 with the following command: > > $dbconn3 = pg_connect ("host=localhost port=5432 > > dbname=dbname user=myname password=mypass"); > > > > I get the following: > > Warning: pg_connect(): Unable to connect to > > PostgreSQL server: could not connect to server: > > Connection refused Is the server running on host > > localhost and accepting TCP/IP connections on port > > 5432? ... > > > > How do we know the port postgresql is listening > on? > > > > Cheers. > > > > __ > > Do you Yahoo!? > > Yahoo! SiteBuilder - Free, easy-to-use web site > > design software > > http://sitebuilder.yahoo.com > > > > ---(end of > > broadcast)--- > > TIP 9: the planner will ignore your desire to > choose > > an index scan if your > > joining column's datatypes do not match > > > > ---(end of > > broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site > design software > http://sitebuilder.yahoo.com > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Port?
Hi, Include -i in the options for starting the server. e.g. pg_ctl start -o -i & -i enables TCP/IP connections Regards, Kallol -Original Message- From: b b [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2003 11:56 To: Kallol Nandi Subject: Re: [GENERAL] Port? I looked up the pg_ctl command and it doesn't have a -i option? what would it do anyway? Cheers. --- Kallol Nandi <[EMAIL PROTECTED]> wrote: > Have you started postmaster with the -i option? > If not then doing so will solve your problem. > > Regards, > Kallol. > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf > Of b b > Sent: Thursday, July 31, 2003 11:27 > To: postgresql > Subject: [GENERAL] Port? > > > > What port does the postgresql server listen on. I > am > trying to connect using pg_connect from php using > port > 5432 with the following command: > $dbconn3 = pg_connect ("host=localhost port=5432 > dbname=dbname user=myname password=mypass"); > > I get the following: > Warning: pg_connect(): Unable to connect to > PostgreSQL server: could not connect to server: > Connection refused Is the server running on host > localhost and accepting TCP/IP connections on port > 5432? ... > > How do we know the port postgresql is listening on? > > Cheers. > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site > design software > http://sitebuilder.yahoo.com > > ---(end of > broadcast)--- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Port?
On Thu, 2003-07-31 at 00:56, b b wrote: > What port does the postgresql server listen on. I am > trying to connect using pg_connect from php using port > 5432 with the following command: > $dbconn3 = pg_connect ("host=localhost port=5432 > dbname=dbname user=myname password=mypass"); > > I get the following: > Warning: pg_connect(): Unable to connect to > PostgreSQL server: could not connect to server: > Connection refused Is the server running on host > localhost and accepting TCP/IP connections on port > 5432? ... > > How do we know the port postgresql is listening on? This should help: $ netstat -a |grep postgresql -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!"| |unknown | +-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Port?
Have you started postmaster with the -i option? If not then doing so will solve your problem. Regards, Kallol. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of b b Sent: Thursday, July 31, 2003 11:27 To: postgresql Subject: [GENERAL] Port? What port does the postgresql server listen on. I am trying to connect using pg_connect from php using port 5432 with the following command: $dbconn3 = pg_connect ("host=localhost port=5432 dbname=dbname user=myname password=mypass"); I get the following: Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 5432? ... How do we know the port postgresql is listening on? Cheers. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Port?
What port does the postgresql server listen on. I am trying to connect using pg_connect from php using port 5432 with the following command: $dbconn3 = pg_connect ("host=localhost port=5432 dbname=dbname user=myname password=mypass"); I get the following: Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 5432? ... How do we know the port postgresql is listening on? Cheers. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] hexadecimal to decimal
Tom Lane wrote: It seems entirely sensible to me for the postmaster to choke on invalid settings in postgresql.conf. Better than failing to mention the problem at all, anyway. 2) do you want a patch that exports plperl_init_all() (and I guess similar init functions in pltcl and plpython)? Yeah, I guess. Might as well make one in plpgsql too --- even if it does nothing today, it might be useful in the future, so the documentation ought to recommend "call 'plxxx_init' when preloading plxxx" as a general thing. OK -- I'll put a patch together. Thanks, Joe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] hexadecimal to decimal
Joe Conway <[EMAIL PROTECTED]> writes: > My reasoning at the time was that library preloading shouldn't prevent > the postmaster from starting, even if it is unsuccessful, but now I > wonder if that was a good idea. It seems entirely sensible to me for the postmaster to choke on invalid settings in postgresql.conf. Better than failing to mention the problem at all, anyway. > 2) do you want a patch that exports plperl_init_all() (and I guess > similar init functions in pltcl and plpython)? Yeah, I guess. Might as well make one in plpgsql too --- even if it does nothing today, it might be useful in the future, so the documentation ought to recommend "call 'plxxx_init' when preloading plxxx" as a general thing. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] hexadecimal to decimal
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: I tried that after I posted, but only saw roughly 30% improvement (which is consistent with my earlier tests IIRC). Not bad, but this still left plperl initial call at ~40 msec versus plpgsql at ~4 msec. Hm. And the first call to a plpgsql function does require opening a shared library. Curious that libperl seems so much more heavyweight than plpgsql. I found the problem (or arguably two). Hows this look from a fresh psql session: regression=# explain analyze select hex_to_int(f1) from foo; QUERY PLAN --- Seq Scan on foo (cost=0.00..22.50 rows=1000 width=6) (actual time=3.31..3.53 rows=3 loops=1) Total runtime: 3.69 msec (2 rows) regression=# explain analyze select hex_to_int_perl('ff'); QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) (actual time=2.38..2.39 rows=1 loops=1) Total runtime: 2.43 msec (2 rows) regression=# explain analyze select hex_to_int(f1) from foo; QUERY PLAN --- Seq Scan on foo (cost=0.00..22.50 rows=1000 width=6) (actual time=0.29..0.49 rows=3 loops=1) Total runtime: 0.54 msec (2 rows) regression=# explain analyze select hex_to_int_perl('ff'); QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.15..0.15 rows=1 loops=1) Total runtime: 0.18 msec (2 rows) Now the first call to the perl function is quicker than plpgsql and 90+% faster than without preloading :-) The first problem is that the initialization function for plperl, plperl_init_all() is declared static, hence it couldn't be loaded externally at all. The second problem is that when I wrote process_preload_libraries() I used this line to call the init function: initfunc = (func_ptr) load_external_function(filename, funcname, false, NULL); That false means that load_external_function() doesn't report errors if the funcname cannot be found ;( My reasoning at the time was that library preloading shouldn't prevent the postmaster from starting, even if it is unsuccessful, but now I wonder if that was a good idea. What do you think: 1) should that call to load_external_function() use true for signalNotFound? 2) do you want a patch that exports plperl_init_all() (and I guess similar init functions in pltcl and plpython)? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] psql -e
I assume this is the fflush you mentioned. Patch attached and applied. --- Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Rajesh Kumar Mallah writes: > >> The echo feature of psql echos the query after its executed. > >> does it makes more sense to have the reverse. > > > The query is printed *before* it is executed, but you might not see it > > because your terminal is not flushing the stdout at the right times. > > It might be a good idea to do an explicit fflush(stdout) right after > printing the query. I observe that PSQLexec() does this, and SendQuery > does too in one path --- but not in the other. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/bin/psql/common.c === RCS file: /cvsroot/pgsql-server/src/bin/psql/common.c,v retrieving revision 1.66 diff -c -c -r1.66 common.c *** src/bin/psql/common.c 23 Jul 2003 08:47:39 - 1.66 --- src/bin/psql/common.c 31 Jul 2003 04:21:53 - *** *** 595,602 return false; } else if (VariableEquals(pset.vars, "ECHO", "queries")) puts(query); ! SetCancelConn(); if (PQtransactionStatus(pset.db) == PQTRANS_IDLE && --- 595,605 return false; } else if (VariableEquals(pset.vars, "ECHO", "queries")) + { puts(query); ! fflush(stdout); ! } ! SetCancelConn(); if (PQtransactionStatus(pset.db) == PQTRANS_IDLE && ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] hexadecimal to decimal
Joe Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> It might help if libperl were to be preloaded into the postmaster in the >> way you created ... > I tried that after I posted, but only saw roughly 30% improvement (which > is consistent with my earlier tests IIRC). Not bad, but this still left > plperl initial call at ~40 msec versus plpgsql at ~4 msec. Hm. And the first call to a plpgsql function does require opening a shared library. Curious that libperl seems so much more heavyweight than plpgsql. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] hexadecimal to decimal
Tom Lane wrote: Presumably, the 50 msec difference represents the time to load up the perl library and initialize a perl interpreter. It might help if libperl were to be preloaded into the postmaster in the way you created ... I tried that after I posted, but only saw roughly 30% improvement (which is consistent with my earlier tests IIRC). Not bad, but this still left plperl initial call at ~40 msec versus plpgsql at ~4 msec. It is possible that the initialization function that I used, plperl_init_all(), doesn't include everything it could. I might play around with it when I get a few moments. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] hexadecimal to decimal
Joe Conway <[EMAIL PROTECTED]> writes: > So based on this simple test, I'd say that if you only run the function > once per connect, use plpgsql, but if you run the function many times > during one backend session, use perl. Presumably, the 50 msec difference represents the time to load up the perl library and initialize a perl interpreter. It might help if libperl were to be preloaded into the postmaster in the way you created ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Don't removes/recycles WAL files at all
Michael Govorun <[EMAIL PROTECTED]> writes: > I've found answer! Postgresql have been compiled with aggressive > optimizations: > gcc version egcs-2.91.66 > CFLAGS='-O9 -funroll-loops -ffast-math -malign-double -mcpu=pentiumpro > -march=pentiumpro -fomit-frame-pointer -fno-exceptions' > After recompiling server with -O2 WAL recycling properly. But I got > many errors after installing recompiled server and had to dump > database with "optimized" postgres and restore with -O2 version. Yeah, adding or removing -malign-double would change the layout of our on-disk data structures, so it's no surprise you'd need to dump and reload for that. Also, -ffast-math is known to be Evil. I thought we had a check to prevent that (looks ...) yeah we do; how'd you get by this check in timestamp.c? /* * gcc's -ffast-math switch breaks routines that expect exact results from * expressions like timeval / 3600, where timeval is double. */ #ifdef __FAST_MATH__ #error -ffast-math is known to break this code #endif As for the other stuff, it theoretically should work, but isn't egcs-2.91.66 rather old and buggy? Perhaps a newer gcc would work better. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Don't removes/recycles WAL files at all
Michael Govorun <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> There's something broken there. Check to see why checkpoint operations >> are not completing (look in the postmaster log for relevant error >> messages). Open transactions should not affect this. > No other errors messages in my log. Maybe you have a checkpoint process that's just stuck, and is not getting anything done yet is preventing the postmaster from launching new checkpoints? Use ps to look for postmaster child processes that have been around a long time. I seem to recall a similar report of a stuck checkpoint a few weeks ago ... check the archives. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres Hanging on Inserts
On Wed, Jul 30, 2003 at 01:03:55PM -0500, Adam Kavan wrote: > I have found the problem (I think) below is the list of all the locks > pending on the relation. The relation is a hash index on the table that is > being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have > an ExclusiveLock on the index, and they both are waiting to get an > ExclusiveLock on the relation. Oh, so this is the problem. Truth is hash indexes in Postgres are known to have poor concurrency, though I didn't expect them to be subject to deadlocks... you should change the hash index to a btree index and the problem will "go away"; you will also probably see a performance improvement if there's concurrent insertion and access. BTrees are way more developed than hashes. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "The ability to monopolize a planet is insignificant next to the power of the source" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Minimal system (was Re: [GENERAL] Basic questions before start)
Ron Johnson wrote: On Wed, 2003-07-30 at 09:25, scott.marlowe wrote: On Wed, 30 Jul 2003, Gogulus wrote: As the clients should be able to work without network connection, they have to have a local database, and if net connection is on, do the synchronization with master db. The main idea is, sale cannot stop because of net connection breakage. That's why I am asking if 100 Mhz of CPU, 32 Mbytes of RAM can take care of a database with around 100 tables, 3-4 of these tables having 50-6 of records, others have at most 1000. I would say yes, but I would also say that you should design this around a character based interface. The overhead of a GUI is gonna make it much slower. I don't know if you're familiar with the ncurses library, but that's what I'd use, along with C or a lightweight scripting language like Perl or PHP. Or Python, which has an excellent curses library. How could he do local and remote access in PHP? Wouldn't a local Apache server (which takes more RAM) be necessary? Also regarding PHP, "links" is a great text-mode web browser that handles style sheets and frames. PHP has a command line version, and it's own GTK. I write all my processing scripts in PHP to leverage all the function and classes I've writting for the web.
Re: [GENERAL] Diff between contrib/dbmirror and rserv
On Wed, 30 Jul 2003, Andrew Sullivan wrote: > A version of replication that descended from rserv is going to appear on > gborg someday soon. I'm not sure when. Got hit by a death in the family this past weekend ... will dive into moving this forward more over the next few days :( Sorry for the delay ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres Hanging on Inserts
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, July 30, 2003 9:25 AM Subject: Re: [GENERAL] Postgres Hanging on Inserts > Adam Kavan <[EMAIL PROTECTED]> writes: > > I looked into pg_locks and they are all waiting to get an exclusive > > lock on the same relation. Is there anyway for me to tell what this > > relation is? > > To decipher the OIDs in pg_locks, join against pg_class.oid, or just do > select relname from pg_class where oid = ; > > > Does anyone know what it could be and how I can fix this > > problem? > > Look for the process that already has a lock on the same relation, and > find out what it's waiting for. > > regards, tom lane I have found the problem (I think) below is the list of all the locks pending on the relation. The relation is a hash index on the table that is being INSERT'd rapidly. From what I can see pid 10024 and 10025 both have an ExclusiveLock on the index, and they both are waiting to get an ExclusiveLock on the relation. Those are the only locks either pid doesn't have so I suspect that is what is causing the deadlock. Is there something I've done wrong? Both pids are just doing simple inserts. data=# select * from pg_locks where relation = 3731653 order by granted; relation | database | transaction | pid | mode | granted --+--+-+---+-+- 3731653 |16976 | | 10091 | ShareLock | f 3731653 |16976 | | 10077 | ShareLock | f 3731653 |16976 | | 10178 | ShareLock | f 3731653 |16976 | | 10116 | ShareLock | f 3731653 |16976 | | 10108 | ShareLock | f 3731653 |16976 | | 10076 | ShareLock | f 3731653 |16976 | | 10079 | ShareLock | f 3731653 |16976 | | 10110 | ShareLock | f 3731653 |16976 | | 10023 | ExclusiveLock | f 3731653 |16976 | | 10177 | ShareLock | f 3731653 |16976 | | 10208 | ShareLock | f 3731653 |16976 | | 10166 | ShareLock | f 3731653 |16976 | | 10142 | ShareLock | f 3731653 |16976 | | 10160 | ShareLock | f 3731653 |16976 | | 10214 | ShareLock | f 3731653 |16976 | | 10226 | ShareLock | f 3731653 |16976 | | 10031 | ShareLock | f 3731653 |16976 | | 10237 | ShareLock | f 3731653 |16976 | | 10075 | ShareLock | f 3731653 |16976 | | 10109 | ShareLock | f 3731653 |16976 | | 10207 | ShareLock | f 3731653 |16976 | | 10190 | ShareLock | f 3731653 |16976 | | 10041 | ShareLock | f 3731653 |16976 | | 10130 | ShareLock | f 3731653 |16976 | | 10043 | ShareLock | f 3731653 |16976 | | 10026 | ShareLock | f 3731653 |16976 | | 10074 | ShareLock | f 3731653 |16976 | | 10092 | ShareLock | f 3731653 |16976 | | 10158 | ShareLock | f 3731653 |16976 | | 10024 | ExclusiveLock | f 3731653 |16976 | | 10141 | ShareLock | f 3731653 |16976 | | 10189 | ShareLock | f 3731653 |16976 | | 10238 | ShareLock | f 3731653 |16976 | | 10027 | ShareLock | f 3731653 |16976 | | 10078 | ShareLock | f 3731653 |16976 | | 10025 | ExclusiveLock | f 3731653 |16976 | | 10159 | ShareLock | f 3731653 |16976 | | 10225 | ShareLock | f 3731653 |16976 | | 9951 | ShareLock | f 3731653 |16976 | | 10029 | ShareLock | f 3731653 |16976 | | 10196 | ShareLock | f 3731653 |16976 | | 10028 | ShareLock | f 3731653 |16976 | | 10128 | ShareLock | f 3731653 |16976 | | 9951 | AccessShareLock | t 3731653 |16976 | | 10024 | ExclusiveLock | t 3731653 |16976 | | 10025 | ExclusiveLock | t 3731653 |16976 | | 9951 | ShareLock | t 3731653 |16976 | | 10023 | ShareLock | t (48 rows) > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Don't removes/recycles WAL files at all
Please, help. versions: 7.3.3, 7.3.4 Server don't recycles or removes WAL files at all, generating 12-20 files every day. -rw---1 postgres postgres 16777216 Jul 29 01:54 -rw---1 postgres postgres 16777216 Jul 29 01:55 0001 -rw---1 postgres postgres 16777216 Jul 29 02:15 0002 -rw---1 postgres postgres 16777216 Jul 29 06:28 0003 -rw---1 postgres postgres 16777216 Jul 29 09:26 0004 -rw---1 postgres postgres 16777216 Jul 29 11:35 0005 -rw---1 postgres postgres 16777216 Jul 29 13:25 0006 -rw---1 postgres postgres 16777216 Jul 29 15:05 0007 -rw---1 postgres postgres 16777216 Jul 29 16:17 0008 -rw---1 postgres postgres 16777216 Jul 29 17:42 0009 -rw---1 postgres postgres 16777216 Jul 29 19:18 000A -rw---1 postgres postgres 16777216 Jul 29 21:19 000B -rw---1 postgres postgres 16777216 Jul 29 23:11 000C -rw---1 postgres postgres 16777216 Jul 30 01:10 000D -rw---1 postgres postgres 16777216 Jul 30 03:51 000E -rw---1 postgres postgres 16777216 Jul 30 04:00 000F -rw---1 postgres postgres 16777216 Jul 30 04:00 0010 -rw---1 postgres postgres 16777216 Jul 30 06:10 0011 -rw---1 postgres postgres 16777216 Jul 30 08:24 0012 -rw---1 postgres postgres 16777216 Jul 30 09:45 0013 -rw---1 postgres postgres 16777216 Jul 30 11:08 0014 -rw---1 postgres postgres 16777216 Jul 30 12:10 0015 -rw---1 postgres postgres 16777216 Jul 30 13:21 0016 -rw---1 postgres postgres 16777216 Jul 30 14:17 0017 -rw---1 postgres postgres 16777216 Jul 30 15:24 0018 -rw---1 postgres postgres 16777216 Jul 30 16:28 0019 -rw---1 postgres postgres 16777216 Jul 30 17:34 001A -rw---1 postgres postgres 16777216 Jul 30 18:45 001B -rw---1 postgres postgres 16777216 Jul 30 20:00 001C -rw---1 postgres postgres 16777216 Jul 30 21:10 001D -rw---1 postgres postgres 16777216 Jul 30 21:48 001E I have all settings as defaults in postgresql.conf: #checkpoint_segments = 3# in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds # #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 # #fsync = true #wal_sync_method = fsync# the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 -- Michael Govorun ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Minimal system (was Re: [GENERAL] Basic questions before start)
On Wed, 2003-07-30 at 09:25, scott.marlowe wrote: > On Wed, 30 Jul 2003, Gogulus wrote: > > > As the clients should be able to work without network connection, they > > have to have a local database, and if net connection is on, do the > > synchronization with master db. The main idea is, sale cannot stop > > because of net connection breakage. > > > > That's why I am asking if 100 Mhz of CPU, 32 Mbytes of RAM can take care > > of a database with around 100 tables, 3-4 of these tables having > > 50-6 of records, others have at most 1000. > > I would say yes, but I would also say that you should design this around a > character based interface. The overhead of a GUI is gonna make it much > slower. > > I don't know if you're familiar with the ncurses library, but that's what > I'd use, along with C or a lightweight scripting language like Perl or > PHP. Or Python, which has an excellent curses library. How could he do local and remote access in PHP? Wouldn't a local Apache server (which takes more RAM) be necessary? Also regarding PHP, "links" is a great text-mode web browser that handles style sheets and frames. -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!"| |unknown | +-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] plpython and current_timestamp / current_user
Hi. 1 - current_timestamp and current_user are not enable in plpython? 2 - About timestamp using plpython: a. upd= strftime(''%Y-%m-%d %H:%M:%S'', localtime()) TD["new"]["updated"] = upd psql:/tmp/insereusuario.sql:2: NOTICE: ('2003-07-30 13:55:32',) psql:/tmp/insereusuario.sql:2: ERROR: TIMESTAMP(154) precision must be between 0 and 6 b. qsql = plpy.prepare("SELECT fqdn_id, user, now() AS upd FROM fqdns WHERE domain = $1", ["text"]) qry = plpy.execute(qsql, [userdomain],1) plpy.notice(qry[0]["upd"]) TD["new"]["updated"] = qry[0]["upd"] psql:/tmp/insereusuario.sql:2: NOTICE: ('2003-07-30 13:58:23.19289-03',) psql:/tmp/insereusuario.sql:2: ERROR: TIMESTAMP(154) precision must be between 0 and 6 3. links to plpython manuals are welcome. Thank you. Vitor ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Alter table
On Wednesday 30 July 2003 17:21, Guillaume Houssay wrote: > I am looking for the SQL query to add a column in a table and having the > column at specific position in the table (not at the end of the table). Sorry - you'll need to dump and restore the table. PG only adds columns at the end. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Alter table
On Wed, 30 Jul 2003, Guillaume Houssay wrote: > I am looking for the SQL query to add a column in a table and having > the column at specific position in the table (not at the end of the > table). > > Do you have any idea ? I think that's a TODO item (i.e. it's not implemented yet.) Currently you'd have to either pg_dump and rearrange your data, or recreate the table with a select into / insert from select type thing. select field1, field2, field4, ''::text as newfield, field3 into newtable from oldtable; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Diff between contrib/dbmirror and rserv
On Wed, Jul 30, 2003 at 11:16:53AM +0200, Thierry Missimilly wrote: > Hi, > > I don't understand by reading the README files, what are the differences > between thes contrib dbmirror and rserv. They are different approaches to the implementation of the same thing. They're not related. > And is rserv limited to a demo or is it a full solution ? contrib/rserv, just like all the other code in the tarball, is free software. A version of replication that descended from rserv is going to appear on gborg someday soon. I'm not sure when. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] ALTER TABLE...DROP CONSTRAINT
In post 7.2.x releases ALTER TABLE tablename DROP CONSTRAINT is supported
[GENERAL] Alter table
I am looking for the SQL query to add a column in a table and having the column at specific position in the table (not at the end of the table). Do you have any idea ? GH
Re: [GENERAL] pg_dump
On Mon, Jul 28, 2003 at 07:18:13PM +0200, Frank Finner wrote: > Hi, > > surely you are right. I?m still using 7.2.3 as production database, so I > have no experience with .pgpass. Where to put .pgpass if you use pg_dump > from a PHP script and the database user is not a system user? I think you need the .pgpass in the home directory of the user running the server processing the PHP (e.g. ~apache or ~nobody or whatever you use). I'm not sure how well it will work, though, given that those users typically have /bin/false as a shell. I haven't tested it. A Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Clone a database to other machine
On Mon, Jul 28, 2003 at 01:38:34PM -0700, Terence Chang wrote: > customized database and initial data load into server 1. Can I just > copy all of the database files physically from server 1 to server 2 > and server 3? I assume this is one of the method to restore a > database. Not unless all the databases are shut down. You could use pg_dump. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] concurrent writes
On Tue, Jul 29, 2003 at 01:07:57PM +0200, Andreas Jung wrote: > > This problem appeard in 7.3.2 but it seems to have been fixed in 7.3.3. > Our administrator complained that there has not been a notice in the > CHANGELOG...so I am hestitating about choosing Postgres vs. Oracle :-) I think your administrator is talking out of his hat. We switch back and forth between Linux and Solaris all the time here, and PostgreSQL works the same all the time. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] concurrent writes
On Tue, Jul 29, 2003 at 12:33:06PM +0200, Andreas Jung wrote: > hi, > > we are running Postgres 7.3.3 successfully on our portal sites There's a nasty bug in 7.3.3 which is fixed in 7.3.4. > N processes need to write update/insert within the same time and within > the same transaction data in one table. More detailed: every process > opens its own connection, starts a transaction, updates *different* rows > and then commits. These two descriptions are not of the same thing. Different processes cannot share the same transaction, unless they're using the same connection. > According to our postgres adminstrator, Postgres seems > to behave differently on Linux and Solaris. Any ideas on that? Differently how? Start up time for a connection is sure worse: fork() on Solaris is slow as my dog with his foot in a bandage. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Basic questions before start
On Wed, 30 Jul 2003, Gogulus wrote: > As the clients should be able to work without network connection, they > have to have a local database, and if net connection is on, do the > synchronization with master db. The main idea is, sale cannot stop > because of net connection breakage. > > That's why I am asking if 100 Mhz of CPU, 32 Mbytes of RAM can take care > of a database with around 100 tables, 3-4 of these tables having > 50-6 of records, others have at most 1000. I would say yes, but I would also say that you should design this around a character based interface. The overhead of a GUI is gonna make it much slower. I don't know if you're familiar with the ncurses library, but that's what I'd use, along with C or a lightweight scripting language like Perl or PHP. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] substring implementation (long string)
Scott Cain wrote: Thanks--that looks like exactly what I need. Here's a related question: when I do the alter table, is there a way I can make it "retroactive". That is, I ran the alter table, and nothing happened. I expected the storage space for the database to jump, and for it to take a while to do it, but it did not. So I am guessing that the alter table only applies to new data put in. Is there a way to make it apply to all the data already in the column, short of dropping the table and reloading it. Dropping and reloading is unattractive because of several foreign key constraints on this table. You probably could do something like: UPDATE mytable SET somefield = somefield; Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] substring implementation (long string)
Joe, Thanks--that looks like exactly what I need. Here's a related question: when I do the alter table, is there a way I can make it "retroactive". That is, I ran the alter table, and nothing happened. I expected the storage space for the database to jump, and for it to take a while to do it, but it did not. So I am guessing that the alter table only applies to new data put in. Is there a way to make it apply to all the data already in the column, short of dropping the table and reloading it. Dropping and reloading is unattractive because of several foreign key constraints on this table. Thanks, Scott On Wed, 2003-07-30 at 00:46, Joe Conway wrote: > Scott Cain wrote: > > I am wondering about the implementation of substring for very large > > strings. I've got strings that are several million characters long and > > frequently need to extract relatively small substrings (5000-4 > > characters) (that's right, it's DNA). Before I cared much about > > performance, I retrieved the whole string and and substr'ed it in perl. > > I realized recently it is better to do the substring in postgres > > (performance increase by an order of magnitude). So here is what I am > > wondering: does postgres read the whole string into memory before it > > does the substring, or does it have some sort of smart way of reading > > just the substring from disk? > > I believe that if you store the text uncompressed (which is not the > default) then the substring function can just grab the section of > interest. Normally text columns this large are compressed though, which > requires them to be read completely from disk before they are sliced. > > See: > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-altertable.html > > In particular: > ALTER TABLE [ ONLY ] table [ * ] ALTER [ COLUMN ] column >SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } > > SET STORAGE > > This form sets the storage mode for a column. This controls whether > this column is held inline or in a supplementary table, and whether the > data should be compressed or not. PLAIN must be used for fixed-length > values such as INTEGER and is inline, uncompressed. MAIN is for inline, > compressible data. EXTERNAL is for external, uncompressed data and > EXTENDED is for external, compressed data. EXTENDED is the default for > all data types that support it. The use of EXTERNAL will make substring > operations on a TEXT column faster, at the penalty of increased storage > space. > > > I think you'll want > ALTER TABLE mytable ALTER COLUMN bigtextcol SET STORAGE EXTERNAL; > > As far as I know, there is no way to create a table with STORAGE > EXTERNAL; you have to create the table and then alter it with the above > statement. > > > HTH, > > Joe > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- Scott Cain, Ph. D. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres Hanging on Inserts
Adam Kavan <[EMAIL PROTECTED]> writes: > I looked into pg_locks and they are all waiting to get an exclusive > lock on the same relation. Is there anyway for me to tell what this > relation is? To decipher the OIDs in pg_locks, join against pg_class.oid, or just do select relname from pg_class where oid = ; > Does anyone know what it could be and how I can fix this > problem? Look for the process that already has a lock on the same relation, and find out what it's waiting for. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Unused Indexes
Mike Mascari wrote: Tim McAuley wrote: Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. You must cast the 1 to a bigint: SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint This should probably be listed under FAQ 4.8, but it isn't. That's it! I had actually just come across that before reading this email. It gets the explain back into shape anyway! Thanks! Tim ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unused Indexes
You need to convert the int's to bigints. select id where col1 = 1::bigint and col2 = 1::bigint Regards, Ed -Original Message- From: Tim McAuley <[EMAIL PROTECTED]> Date: Wed, 30 Jul 2003 13:46:46 To:[EMAIL PROTECTED] Subject: [GENERAL] Unused Indexes Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unused Indexes
Assuming you have done a 'VACUUM ANALYZE' on the table in question you are most likely running into a type coercion issue. So explicitly cast your constants to bigint and the index should start being considered. select id from where col2 = 1::bigint and col2 = 1::bigint Tim McAuley wrote: Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Changing DB ownership
Dumb question maybe, but how does one change database ownership? I've tried several permutations of: ALTER DATABASE SET ("|'| )owner("|'| ) TO ("|'| )newowner("|'| ); and I tried looking at pg_database and I *was* able to hack this (got a clue half way through writing this email... sorry): UPDATE pg_database SET datdba = 504 WHERE datname='chris'; And that worked as expected, but I'm worried that I might have missed something somewhere else. Will the above UPDATE cause problems down the road? Any info/clue would be much appreciated. Thanks in advance. Cheers, Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL SUM query limited by dates
use a sub-select SELECT SUM(volume) FROM (SELECT volumn FROM WHERE element = 'name1' ORDER BY date DESC LIMIT 30) t Castle, Lindsay wrote: Hi All, A quick select query I'm having some dramas with; I'm trying to SUM a number of values based on them being the latest entries based on their date. Eg I have a table with element (text), date (date) and volume (numeric), it has 100 entries, I want to grab 30 entries with the most recent dates and total up the volume information. I thought something along the lines of: SELECT SUM(volume) from WHERE element = 'name1' GROUP BY date ORDER BY date DESC LIMIT 30 Of course (I believe) this will only sum up anything that has matching dates. Can I do this within a SELECT SUM() statement or do I need to look at aggregate functions? Or perhaps the HAVING clause could be of use for this one? My dataset has one row per date and could possibly be out of date order, hence the order by being a requirement (unless there is another way to ensure only the most recent dates are accounted for). Thanks in advance, Linz ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unused Indexes
Mike Mascari wrote: > Tim McAuley wrote: > >>Hi, >> >>I have a table which I have populated with over 5000 entries. There is a >>combined index placed on two of the columns (both bigint). I am trying >>a simple select (i.e. select id where col1 = 1 and col2 = 1) covering >>these two columns and it keeps using a seq scan. Is this correct? I >>would have thought that with this number of entries that an index scan >>should be used. > > > You must cast the 1 to a bigint: > > SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint That should read: SELECT id WHERE col1 = 1::bigint AND col2 = 1::bigint. My dyslexia is kicking in... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Unused Indexes
Tim McAuley wrote: > Hi, > > I have a table which I have populated with over 5000 entries. There is a > combined index placed on two of the columns (both bigint). I am trying > a simple select (i.e. select id where col1 = 1 and col2 = 1) covering > these two columns and it keeps using a seq scan. Is this correct? I > would have thought that with this number of entries that an index scan > should be used. You must cast the 1 to a bigint: SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint This should probably be listed under FAQ 4.8, but it isn't. Hope that helps, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Unused Indexes
Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR STRINGS IN PGSQL
Deepa K writes: > Is proper error numbers are defined for all > error strings returned by pgsql c library. No, you will have to wait for version 7.4 for that. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] ERROR STRINGS IN PGSQL
Hi All, Is proper error numbers are defined for all error strings returned by pgsql c library. Is so, where can I get those error numbers and is it possible to return error strings using error numbers. regards, Deepa K. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL consultant
Hi, On Wed, 30 Jul 2003, Kaare Rasmussen wrote: > I tried to find the page with PostgreSQL consultants. After 10 minutes I gave > up. > > I believe that I've heard that there is such a page, but maybe I'm wrong? Or > maybe I just suck at searching the PostgreSQL site ? http://techdocs.PostgreSQL.org http://www.pgsql.com/partnerlinks Regards, -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.tdmsoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PostgreSQL consultant
Hi I tried to find the page with PostgreSQL consultants. After 10 minutes I gave up. I believe that I've heard that there is such a page, but maybe I'm wrong? Or maybe I just suck at searching the PostgreSQL site ? -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 12.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 12.00-16.00 Web: www.suse.dk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster