Re: [GENERAL] Importance of re-index
Disagree. We only apply reindex on tables that see lots of updates... With our 7.4.x databases we vacuum each day, but we see real performance gains after re-indexing too - we see lower load averages and no decrease in responsiveness over time. Plus we have the benefit of reduced disk space usage. I think that the two things go hand in hand, although vacuum is the most important. John Jim C. Nasby wrote: And if you're vacuuming frequently enough, there shouldn't be that much need to reindex. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Connection string
On Thu, Aug 10, 2006 at 12:02:24AM -0400, Harpreet Dhaliwal wrote: > I already read that documentation. > > My ECPG code for connecting to the DB server is: > > EXEC SQL CONNECT TO 192.168.1.100:/xyz That format isn't shown in the documentation; the ecpg preprocessor should fail with a syntax error if you try using it. > i also tried > > tcp:postgresql://192.168.1.100[:*port*][/*dbname*][?*options*] The above is probably what you need, but without seeing the exact code you tried it's hard to say why it's not working. > unix:postgresql://*192.168.1.100*[:*port*][/*dbname*][?*options*] The ecpg preprocessor shouldn't allow this -- it should fail with an error like "unix domain sockets only work on 'localhost' but not on '192.168.1.100'". > but unfortunately it say DB doesn't exist. Are you sure the database exists? Can you connect to it with psql? > I don't know the right way to use IP addresses while connecting to a > postgres DB using ECPG. If you have a server on 192.168.1.100 listening on the default port (5432, or whatever PGPORT is set to) and you want to connect to a database named "mydb" on that server, then the following should work: EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100/mydb; If the database is listening on another port, say 12345, then this should work: EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100:12345/mydb; If you're getting 'database "mydb" does not exist' errors then try connecting with psql and make sure the database really does exist. If you still have trouble then please post a minimal but complete program so we can see everything you're doing. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Accessing Database Data from C Function
On Aug 8, 2006, at 8:33 PM, Jeffrey Bigham wrote: Do you have a reason to use C instead of a higher-level language? Not really. Basically I know C decently enough and would have to learn PL/pgSQL (although it looks pretty easy). Perl sounds quite attractive for the added benefit of text-processing as you mention. I also have some vague understanding that C might be faster. I hope I'm not opening a can-of-worms, but is C actually faster? It depends on what you're doing. I've found plpgsql to be a better match for a lot of purposes, as it has a much better "impedance match" with the database. I'm a happy C hacker, and use C for PG functions where it seems appropriate, but use plpgsql for >90% of my in database work. Performance is probably marginally slower than C, but development time for day-to-day triggers and functions is a lot faster. Much easier to maintain, too. Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Too many open files from postgres.
"Mavinakuli, Prasanna (STSD)" <[EMAIL PROTECTED]> writes: > We have observed that postgres opens many files in data directory > And won't close till next vacuum (I experience it) > When it hits max limit, we won't able to open any other files. > So what's the solution ..? Reduce PG's max_files_per_process setting, or increase your kernel's max-number-of-open-files limit. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Connection string
Hi I already read that documentation. My ECPG code for connecting to the DB server is: EXEC SQL CONNECT TO 192.168.1.100:/xyz i also tried tcp:postgresql://192.168.1.100[:port][/dbname][ ?options] unix:postgresql://192.168.1.100[:port][ /dbname][?options] but unfortunately it say DB doesn't exist. I don't know the right way to use IP addresses while connecting to a postgres DB using ECPG. On 8/9/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Wed, Aug 09, 2006 at 11:02:00AM -0400, Harpreet Dhaliwal wrote:> I'm trying to connect to postgres database in a distributed environment, > say from machine X to Machine Y (Machine Y has postgres DB)> How should my connection string look like in a program in Machine X.>> EXEC SQL CONNECT TO --??? (Do i need to specify the IP of DB server?) > I tried a few options but nothing works.This appears to be ECPG so see "Connecting to the Database Server"in the ECGP chapter of the documentation: http://www.postgresql.org/docs/8.1/interactive/ecpg-connect.htmlIf the documentation and examples don't help then please post exactlywhat you've tried and what happened (complete error message, etc.). --Michael Fuhr
Re: [GENERAL] Accessing Database Data from C Function
"Jeffrey Bigham" <[EMAIL PROTECTED]> writes: > I also have some vague understanding that C might be faster. I hope I'm > not opening a can-of-worms, but is C actually faster? C should theoretically be faster than any of the alternatives you mention, all else being equal (eg, you are implementing the identical algorithm in each language). Whether the difference is enough to notice is another question --- for example, if effectively all the runtime is spent inside SQL queries, shaving a few microseconds off the time it takes you to issue the queries isn't going to improve your life. You also have to consider the larger effort you'll need to put into coding in C ... it's a pretty low-level language by any modern standard. That effort might more profitably be spent elsewhere, eg improving the SQL queries themselves. My advice is don't code in C until it's clear you have to. If you can prototype in plpgsql or plperl or pl-your-favorite-language, do that first and get it working; and then recode in C if the performance is so bad you can't stand it (and you can prove that the cycles are actually spent in your PL code and not somewhere else like the SQL engine). About the only time I'd not do it that way is if my problem involves data structures too complex to express nicely in my-favorite-pl or if I need access to low-level database details that aren't exposed by my-favorite-pl. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Connection string
On Wed, Aug 09, 2006 at 11:02:00AM -0400, Harpreet Dhaliwal wrote: > I'm trying to connect to postgres database in a distributed environment, > say from machine X to Machine Y (Machine Y has postgres DB) > How should my connection string look like in a program in Machine X. > > EXEC SQL CONNECT TO --??? (Do i need to specify the IP of DB server?) > I tried a few options but nothing works. This appears to be ECPG so see "Connecting to the Database Server" in the ECGP chapter of the documentation: http://www.postgresql.org/docs/8.1/interactive/ecpg-connect.html If the documentation and examples don't help then please post exactly what you've tried and what happened (complete error message, etc.). -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PITR Questions
Jim C. Nasby wrote: Take a look at http://pgfoundry.org/projects/pgpitrha/ I had already seen this however it says that this project has yet to release any files, so I thought it was a dead project. Am I missing something? Also, note that in 8.1, you have to manually archive the last WAL file after pg_stop_backup(), or you backup is useless until that WAL file fills up on its own and is archived. Right, I was hoping to find someone who had well written and tested bash script or something that did this. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Connection string
Hi,I'm trying to connect to postgres database in a distributed environment, say from machine X to Machine Y (Machine Y has postgres DB)How should my connection string look like in a program in Machine X.EXEC SQL CONNECT TO --??? (Do i need to specify the IP of DB server?) I tried a few options but nothing works.Can someone please write this connection string for me?Thanks in advance,~Harpreet
[GENERAL] Too many open files from postgres.
Hello All, We are using postgres with HP-UX. Usually We will have many records,and will do vacuum once in 2 days. We have observed that postgres opens many files in data directory And won't close till next vacuum (I experience it) When it hits max limit, we won't able to open any other files. So what's the solution ..? 1)Is it really postgres issue..doesn't it close opened files immediately after completion of writing..? If so how do we handle the scenario of hitting max fd within next immediate vaccuming..? 2)ideally what's the size of each file..?when it opens second file..?what's criterion..? I can observe files of different sizes in the same directories..? Thx in advance, Prasanna. *lsof* gives following output. postmaste 22481 sfmdb9u REG 64,0x8 16384 6562 /var/opt/sfmdb/pgsql/base/17142/16635 postmaste 22481 sfmdb 10u REG 64,0x8 8192 4373 /var/opt/sfmdb/pgsql/base/17142/16595 postmaste 22481 sfmdb 11u REG 64,0x8 16384 6563 /var/opt/sfmdb/pgsql/base/17142/16636 postmaste 22481 sfmdb 12u REG 64,0x8180224 6510 /var/opt/sfmdb/pgsql/base/17142/16614 postmaste 22481 sfmdb 13u REG 64,0x8204800 4341 /var/opt/sfmdb/pgsql/base/17142/1259 postmaste 22481 sfmdb 14u REG 64,0x8 49152 6506 /var/opt/sfmdb/pgsql/base/17142/16613 postmaste 22481 sfmdb 15u REG 64,0x8 98304 6475 /var/opt/sfmdb/pgsql/base/17142/16610 postmaste 22481 sfmdb 16u REG 64,0x8360448 4340 /var/opt/sfmdb/pgsql/base/17142/1249 postmaste 22481 sfmdb 17u REG 64,0x8106496 6569 /var/opt/sfmdb/pgsql/base/17142/16640 postmaste 22481 sfmdb 18u REG 64,0x8106496 4358 /var/opt/sfmdb/pgsql/base/17142/16392 postmaste 22481 sfmdb 19u REG 64,0x8 16384 3673 /var/opt/sfmdb/pgsql/base/17142/16652 postmaste 22481 sfmdb 20u REG 64,0x8 57344 4337 /var/opt/sfmdb/pgsql/base/17142/1247 postmaste 22481 sfmdb 21u REG 64,0x8 16384 6500 /var/opt/sfmdb/pgsql/base/17142/16612 postmaste 22481 sfmdb 22u REG 64,0x8 16384 4372 /var/opt/sfmdb/pgsql/base/17142/16418 postmaste 22481 sfmdb 23u REG 64,0x8 32768 6568 /var/opt/sfmdb/pgsql/base/17142/16639 postmaste 22481 sfmdb 24u REG 64,0x8122880 6570 /var/opt/sfmdb/pgsql/base/17142/16641 postmaste 22481 sfmdb 25u REG 64,0x8540672 4224 /var/opt/sfmdb/pgsql/base/17142/1255 postmaste 22481 sfmdb 26u REG 64,0x8 16384 6554 /var/opt/sfmdb/pgsql/base/17142/16630 postmaste 22481 sfmdb 27u REG 64,0x8 40960 4356 /var/opt/sfmdb/pgsql/base/17142/16390 postmaste 22481 sfmdb 28u REG 64,0x8 8192 4360 /var/opt/sfmdb/pgsql/base/17142/16396 postmaste 22481 sfmdb 29u REG 64,0x8 16384 6109 /var/opt/sfmdb/pgsql/base/17142/16605 postmaste 22481 sfmdb 30u REG 64,0x8 16384 4361 /var/opt/sfmdb/pgsql/base/17142/16398 postmaste 22481 sfmdb 31u REG 64,0x8 16384 6460 /var/opt/sfmdb/pgsql/base/17142/16606 postmaste 22481 sfmdb 32u REG 64,0x8 8192 4362 /var/opt/sfmdb/pgsql/base/17142/16400 postmaste 22481 sfmdb 33u REG 64,0x8 16384 6553 /var/opt/sfmdb/pgsql/base/17142/16629 postmaste 22481 sfmdb 34u REG 64,0x8 16384 6574 /var/opt/sfmdb/pgsql/base/17142/16647 postmaste 22481 sfmdb 35u REG 64,0x8 16384 6634 /var/opt/sfmdb/pgsql/base/17142/17175 postmaste 22481 sfmdb 36u REG 64,0x8 16384 6461 /var/opt/sfmdb/pgsql/base/17142/16607 postmaste 22481 sfmdb 37u REG 64,0x8 8192 4342 /var/opt/sfmdb/pgsql/base/17142/16384 postmaste 22481 sfmdb 38u REG 64,0x8 16384 6516 /var/opt/sfmdb/pgsql/base/17142/16616 postmaste 22481 sfmdb 39u REG 64,0x8 24576 4344 /var/opt/sfmdb/pgsql/base/17142/16386 postmaste 22481 sfmdb 40u REG 64,0x8 16384 6565 /var/opt/sfmdb/pgsql/base/17142/16637 postmaste 22481 sfmdb 41u REG 64,0x8 16384 4359 /var/opt/sfmdb/pgsql/base/17142/16394 postmaste 22481 sfmdb 42u REG 64,0x8 16384 5566 /var/opt/sfmdb/pgsql/base/17142/16604 postmaste 22481 sfmdb 43u REG 64,0x8 8192 6752 /var/opt/sfmdb/pgsql/base/17142/17244 postmaste 22481 sfmdb 44u REG 64,0x8 8192 6755 /var/opt/sfmdb/pgsql/base/17142/17249 postmaste 22481 sfmdb 45u REG 64,0x8 8192 6746 /var/opt/sfmdb/pgsql/base/17142/17234 postmaste 22481 sfmdb 46u REG 64,0x8 8192 6743 /var/opt/sfmdb/pgsql/base/17142/17229 postmaste 22481 sfmdb 47u REG 64,0x8 1228800 6759 /var/opt/sfmdb/pgsql/base/17142/17256 postmaste 22481 sfmdb 48u REG 64,0x8
Re: [GENERAL] Too many open files from postgres.
Also (obvsly) we aren't able to get a new connection after hitting max fd limit.. Thx, Prasanna. -Original Message- From: Mavinakuli, Prasanna (STSD) Sent: Wednesday, August 09, 2006 10:10 AM To: pgsql-general@postgresql.org Cc: Mavinakuli, Prasanna (STSD) Subject: Too many open files from postgres. Hello All, We are using postgres with HP-UX. Usually We will have many records,and will do vacuum once in 2 days. We have observed that postgres opens many files in data directory And won't close till next vacuum (I experience it) When it hits max limit, we won't able to open any other files. So what's the solution ..? 1)Is it really postgres issue..doesn't it close opened files immediately after completion of writing..? If so how do we handle the scenario of hitting max fd within next immediate vaccuming..? 2)ideally what's the size of each file..?when it opens second file..?what's criterion..? I can observe files of different sizes in the same directories..? Thx in advance, Prasanna. *lsof* gives following output. postmaste 22481 sfmdb9u REG 64,0x8 16384 6562 /var/opt/sfmdb/pgsql/base/17142/16635 postmaste 22481 sfmdb 10u REG 64,0x8 8192 4373 /var/opt/sfmdb/pgsql/base/17142/16595 postmaste 22481 sfmdb 11u REG 64,0x8 16384 6563 /var/opt/sfmdb/pgsql/base/17142/16636 postmaste 22481 sfmdb 12u REG 64,0x8180224 6510 /var/opt/sfmdb/pgsql/base/17142/16614 postmaste 22481 sfmdb 13u REG 64,0x8204800 4341 /var/opt/sfmdb/pgsql/base/17142/1259 postmaste 22481 sfmdb 14u REG 64,0x8 49152 6506 /var/opt/sfmdb/pgsql/base/17142/16613 postmaste 22481 sfmdb 15u REG 64,0x8 98304 6475 /var/opt/sfmdb/pgsql/base/17142/16610 postmaste 22481 sfmdb 16u REG 64,0x8360448 4340 /var/opt/sfmdb/pgsql/base/17142/1249 postmaste 22481 sfmdb 17u REG 64,0x8106496 6569 /var/opt/sfmdb/pgsql/base/17142/16640 postmaste 22481 sfmdb 18u REG 64,0x8106496 4358 /var/opt/sfmdb/pgsql/base/17142/16392 postmaste 22481 sfmdb 19u REG 64,0x8 16384 3673 /var/opt/sfmdb/pgsql/base/17142/16652 postmaste 22481 sfmdb 20u REG 64,0x8 57344 4337 /var/opt/sfmdb/pgsql/base/17142/1247 postmaste 22481 sfmdb 21u REG 64,0x8 16384 6500 /var/opt/sfmdb/pgsql/base/17142/16612 postmaste 22481 sfmdb 22u REG 64,0x8 16384 4372 /var/opt/sfmdb/pgsql/base/17142/16418 postmaste 22481 sfmdb 23u REG 64,0x8 32768 6568 /var/opt/sfmdb/pgsql/base/17142/16639 postmaste 22481 sfmdb 24u REG 64,0x8122880 6570 /var/opt/sfmdb/pgsql/base/17142/16641 postmaste 22481 sfmdb 25u REG 64,0x8540672 4224 /var/opt/sfmdb/pgsql/base/17142/1255 postmaste 22481 sfmdb 26u REG 64,0x8 16384 6554 /var/opt/sfmdb/pgsql/base/17142/16630 postmaste 22481 sfmdb 27u REG 64,0x8 40960 4356 /var/opt/sfmdb/pgsql/base/17142/16390 postmaste 22481 sfmdb 28u REG 64,0x8 8192 4360 /var/opt/sfmdb/pgsql/base/17142/16396 postmaste 22481 sfmdb 29u REG 64,0x8 16384 6109 /var/opt/sfmdb/pgsql/base/17142/16605 postmaste 22481 sfmdb 30u REG 64,0x8 16384 4361 /var/opt/sfmdb/pgsql/base/17142/16398 postmaste 22481 sfmdb 31u REG 64,0x8 16384 6460 /var/opt/sfmdb/pgsql/base/17142/16606 postmaste 22481 sfmdb 32u REG 64,0x8 8192 4362 /var/opt/sfmdb/pgsql/base/17142/16400 postmaste 22481 sfmdb 33u REG 64,0x8 16384 6553 /var/opt/sfmdb/pgsql/base/17142/16629 postmaste 22481 sfmdb 34u REG 64,0x8 16384 6574 /var/opt/sfmdb/pgsql/base/17142/16647 postmaste 22481 sfmdb 35u REG 64,0x8 16384 6634 /var/opt/sfmdb/pgsql/base/17142/17175 postmaste 22481 sfmdb 36u REG 64,0x8 16384 6461 /var/opt/sfmdb/pgsql/base/17142/16607 postmaste 22481 sfmdb 37u REG 64,0x8 8192 4342 /var/opt/sfmdb/pgsql/base/17142/16384 postmaste 22481 sfmdb 38u REG 64,0x8 16384 6516 /var/opt/sfmdb/pgsql/base/17142/16616 postmaste 22481 sfmdb 39u REG 64,0x8 24576 4344 /var/opt/sfmdb/pgsql/base/17142/16386 postmaste 22481 sfmdb 40u REG 64,0x8 16384 6565 /var/opt/sfmdb/pgsql/base/17142/16637 postmaste 22481 sfmdb 41u REG 64,0x8 16384 4359 /var/opt/sfmdb/pgsql/base/17142/16394 postmaste 22481 sfmdb 42u REG 64,0x8 16384 5566 /var/opt/sfmdb/pgsql/base/17142/16604 postmaste 22481 sfmdb 43u REG 64,0x8 8192 6752 /var/opt/sfmdb/pgsql/base/17142/17244 postmaste 22481 sfmdb 44u REG 64,0x8 8192 6755 /var/opt/sfmdb/pgsql/base/17142/17249 postmaste 22481 sfmdb 45u REG 64,0x8 8192 6
[GENERAL] Syslog v/s filename in pg_ctl command
Hello All, When we pass file name in pg_ctl (with option -l) what has to be syslog value.. Should it be enabled..?if so what cud be the value for syslog..? Though I have sent approprite file name with -l option ,and if I use default value for syslog (0) then I am getting the messages in console.. So how can I override getting messages in console...? Thx in advance, Prasanna. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Accessing Database Data from C Function
On 8/8/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Tue, Aug 08, 2006 at 12:16:18PM -0700, [EMAIL PROTECTED] wrote: > I'd like to write a C Function for Postgresql that can access data in > the database, use that to conduct additional queries to find more > information in the database and eventually return a result. I > currently have the functionality I want implemented as php/C programs > that first connect to the database, do the processing and then > disconnect, but I want to make it what I think is called a "stored > procedure" in other databases. It seems odd that I would write a C > program to be included in the database that connects to the localhost > - is that what I should do? Is that somehow more efficient than > running it as a separate process? Server-side functions written in C can use the Server Programming Interface (SPI) to query the database. http://www.postgresql.org/docs/8.1/interactive/spi.html Thanks, that's exactly what I was looking for. Do you have a reason to use C instead of a higher-level language? Not really. Basically I know C decently enough and would have to learn PL/pgSQL (although it looks pretty easy). Perl sounds quite attractive for the added benefit of text-processing as you mention. I also have some vague understanding that C might be faster. I hope I'm not opening a can-of-worms, but is C actually faster? Functions that are mostly queries are probably best done in SQL or PL/pgSQL, while text processing and some OS-level access (e.g., reading and writing files) can be done with PL/Perl, PL/Tcl, PL/Python, PL/Ruby, PL/R, etc. There's even a third-party PL/php if that's your preferred language: http://projects.commandprompt.com/public/plphp Thanks! Jeff -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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] Restoring database from old DATA folder
In article <[EMAIL PROTECTED]>, RPK <[EMAIL PROTECTED]> wrote: % I tried to modify the parameters of the .CONF file when the pgsql-8.1 % service was running. The next time I started, it displayed error: "The % service did not listen to local host". (something like this). % % So I copied the old DATA folder and un-installed PostgreSQL. I again % re-installed it and replace the new DATA folder with the old one. Both % previous and new are same versions and same settings were used during % installation. I think what you need to do is to fix whatever's wrong with your config file. When you made a copy of the old data folder, you copied the config file with it. When you restored the copy, you restored the config file. Do you have a copy of the version that was used the last time the database started? >From what you've said so far, I suggest you search for listen_addresses and set it to '*'. If the database doesn't start, I strongly suggest you look in the log for the error messages and report them verbatim to the list. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Importance of re-index
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jim C. Nasby wrote: > On Thu, Aug 03, 2006 at 05:46:44PM -0500, Scott Marlowe wrote: >> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote: [snip] > And if you're vacuuming frequently enough, there shouldn't be > that much need to reindex. How aggressively does PostgreSQL keep b-trees in balance? Inserting the range [1..1000] should result in a right- unbalanced tree. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE2nZQS9HxQb37XmcRAh4jAJ0bCnk4GOxIt9gUZh9hujDBi/PjwwCfUndS EB9GruGxVJ9Ja0avpurTKwA= =g+1r -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PITR timeline question
Jim Nasby <[EMAIL PROTECTED]> writes: > On Aug 3, 2006, at 3:01 PM, Tom Lane wrote: >> The only way to "roll time backwards" is to replace the data directory >> with a backup, so if I understand your question correctly, the answer >> is no. > Well, specifically I was thinking of... > Roll PITR forward and start database > Do a bunch of stuff > Got more WAL files... start database back in recovery and roll > forward to end of new WAL files (throwing away everything that was > done previously). No, that definitely won't work, since any data pages touched by the "bunch of stuff" and not by the new WAL files will not have been reverted to their prior states --- while any pages touched in common *will* have been overwritten. Net result: inconsistent database. There's been some speculation about allowing a standby server to execute purely read-only operations, but it's just speculation so far. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PITR timeline question
On Aug 3, 2006, at 3:01 PM, Tom Lane wrote: Jim Nasby <[EMAIL PROTECTED]> writes: If you're using multiple timelines with PITR, do you have to re-copy all the data files into the cluster every time you start a new timeline? Or can you copy the data files out of the backup once, and then perform multiple recoveries, each to different timelines? The only way to "roll time backwards" is to replace the data directory with a backup, so if I understand your question correctly, the answer is no. Well, specifically I was thinking of... Roll PITR forward and start database Do a bunch of stuff Got more WAL files... start database back in recovery and roll forward to end of new WAL files (throwing away everything that was done previously). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] WIN32 Build?
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of Bruce Momjian > Sent: Wednesday, August 09, 2006 2:19 PM > To: Shoaib Mir > Cc: DEV; pgsql-general@postgresql.org > Subject: Re: [GENERAL] WIN32 Build? > > Shoaib Mir wrote: > > Dev, > > > > You are doing it a little wrong for Debug version, in order to build the > > Debug version use the following: > > > > nmake /f win32.mak DEBUG=1 > > > > Bruce, > > > > If you remove "inline" the build process goes fine and if you dont, it > first > > gives a few warning and in the end quits the build process with a fatal > > error. > > OK, good to know. If we ever find a symbol that is defined for that > compiler that we can test, we can fix this. #ifdef _MSC_VER #define inline __inline #endif An alternative would be to do this: #ifdef _MSC_VER #define inline #endif And let the compiler inline with /Ob2 (which generally makes better decisions than the programmer anyway). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Not so much load balancing as load limits
On Wed, Aug 09, 2006 at 10:22:20AM -0400, Kenneth Downs wrote: > I'm having some trouble getting a google hit on this topic, and the docs > aren't doing much for me. > > What I'm wondering is, how do I limit any particular postgres operation, > in particular a long-running batch operation with lots of inserts, from > bogging down a server? > > This is not so much a question of how to code the SQL, as it is how to > keep a selfish process from causing a self-inflicted DoS situation. > > Can anybody get me pointed in the right direction with a link or two? > Thanks. Right now, you basically don't. :( Unless you manually break your operation up into multiple steps. There is a lot of discussion on bizgres-general right now about statement queuing, which migth help in your case. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Importance of re-index
On Thu, Aug 03, 2006 at 05:46:44PM -0500, Scott Marlowe wrote: > On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote: > > In addition to making sure databases are vacuumed regularly, it is worth > > running REINDEX on tables that see a lot of updates (or insert/deletes). > > > > Running REINDEX on a regular basis will keep the indexes compacted and > > can noticeably improve the database performance. > > > > The other benefit is that the disk space taken by your database can be > > significantly reduced. > > > > This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs. > > > > FWIW, in my experience it is DEFINITELY worth reindexing regularly. > > But note that reindex is one of those "invasive" commands that may cause > problems for certain types of 24/7 operations, while vacuum is meant to > run concurrently almost any time of day. Reindex was originally > designed to fix broken indexes, and, at least in earlier encarnations, > should something stop it in the middle of reindexing I believe it is > possible to be left with no index. > > It's got its uses, but it's got its issues as well. And if you're vacuuming frequently enough, there shouldn't be that much need to reindex. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PITR Questions
On Fri, Aug 04, 2006 at 03:46:09PM -0400, Matthew T. O'Connor wrote: > Chander Ganesan wrote: > >Matthew T. O'Connor wrote: > >>I have done some googling for real world archive_command examples and > >>haven't really found anything. The example in the PGSQL Docs are > >>qualified by (This is an example, not a recommendation, and may not > >>work on all platforms.) > >> > >>I have it set as follows: > >>archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f' > >It doesn't look to be a *bad* choice. I'd definitely recommend > >keeping a copy off of the current system - which you do here. You > >might also consider keeping a local copy (so you don't have to copy > >them back if you have to do a local recovery). > > I know this can, but what I'm looking for is if someone has written some > scripts that I can crib from that offer some additional features such as > protection from overwriting an existing file, notification of the admin > in case of failure etc.. Take a look at http://pgfoundry.org/projects/pgpitrha/ Also, note that in 8.1, you have to manually archive the last WAL file after pg_stop_backup(), or you backup is useless until that WAL file fills up on its own and is archived. There's a bunch of new functions in 8.2 that will make a lot of this stuff easier, btw. > >>Also, I'm concerned that this clients website has extended periods of > >>time where it's very low traffic, which will result in the same WAL > >>file being used for long periods of time and not getting archived. > >>Does anyone have a tested script available for grabbing the most > >>recent WAL file? I can write one myself, but it seems this is > >>information that should be posted somewhere. > >The checkpoint_timeout value should help with this - its default is > >300 seconds, so you should checkpoint at least once every 5 minutes. > > I don't see how checkpoint_timeout is relevant. Just because we > checkpoint doesn't mean the WAL file will get archived. I have to have > 16M of WAL traffic before a file gets archived regardless of > check-pointing, or am I missing something? You're not. > >You could setup a 'hot standby' system that uses a tool like cron to > >periodically sync your pg_xlog directory to your backup server (or > >just sync it so you have it..) - which might be useful if you go for > >long periods of time between checkpoints. A common scenario is to > >place one server into a "constant recovery" mode by using a > >restore_command that waits for new logs to be available before copying > >them. Periodically sync your pg_xlog directory in this case to ensure > >that when you need to recover you'll have most of what you need...but > >perhaps not all. > > I say the "hot standby" is a common scenario, yet I'm not sure it's even > possible since the docs only mention it in passing, and I wasn't able to > find anyone example script that implements a restore_command that does > this. Am I missing something that is obvious? See above pgfoundry link. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tuning to speed select
On Wed, 2006-08-09 at 16:54 -0400, Tom Laudeman wrote: Great suggestion. I've read about CLUSTER, but never had a chance to use it. The only problem is that this table with 9 million records has 5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER will make at least one of the queries run very fast, especially for an index with a small number of distinct values. Tom- I found multi-column indexes and clustering to be extremely beneficial in 7.4. I still use them in 8.1, but I haven't compared them extensively with equivalent queries that use bitmap index scans. The obvious downside of having more indexes is the additional time and space overhead during insert, update, or delete. The approach I took to design multi-column indexes was to run explain on representative queries and look for seq scans. The seq scans indicate which columns /might/ be well-served by indexes. In 7.4, the order of indexed columns was important. (I saw something in the 8.1 release notes that made me think that this was no longer true, but I haven't verified that.) -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] WIN32 Build?
As a workaround if you dont want to make the change and delete "inline" from wchar.c then you can do the following change in the win32.mak file for libpq:You can see at line 121 in win32.mak file as:
"WIN32" /D "_WINDOWS" /Fp"$(INTDIR)\libpq.pch" /YX\Change it to"WIN32" /D "_WINDOWS" /D "inline=__inline" /Fp"$(INTDIR)\libpq.pch" /YX\
This will help get rid of the libpq build problem without needing to remove "inline" from wchar.c file.Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com
)On 8/10/06, Bruce Momjian <[EMAIL PROTECTED]> wrote:
Shoaib Mir wrote:> Dev,>> You are doing it a little wrong for Debug version, in order to build the> Debug version use the following:>> nmake /f win32.mak DEBUG=1>> Bruce,
>> If you remove "inline" the build process goes fine and if you dont, it first> gives a few warning and in the end quits the build process with a fatal> error.OK, good to know. If we ever find a symbol that is defined for that
compiler that we can test, we can fix this.--->> Log shows:>> ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> ';' be> fore '{'> ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'> undefined; a> ssuming extern returning int> ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
> undefined;> assuming extern returning int> NMAKE : fatal error U1077: 'cl.exe' : return code '0x2'> Stop.>> Thanks,> --> Shoaib Mir> EnterpriseDB (
www.enterprisedb.com)>> On 8/10/06, DEV <[EMAIL PROTECTED]> wrote:> >> > Okay I get a build and I get the release of the libpq built but it does
> > not build the Debug version of that. I have tried going to the directory> > and running nmake /f win32.mak DEBUG but it errors with:> >> > Building the Win32 static library...
> >> >> >> > NMAKE : fatal error U1073: don't know how to make 'DEBUG'> >> > Stop.> >> >> >> > So what am I missing. I see in the mak file for debug but I can not see
> > how to enable that?> >> >> > --> >> > *From:* [EMAIL PROTECTED]
[mailto:> > [EMAIL PROTECTED]] *On Behalf Of *Shoaib Mir> > *Sent:* Wednesday, August 09, 2006 3:30 PM> > *To:* DEV
> > *Cc:* pgsql-general@postgresql.org> > *Subject:* Re: [GENERAL] WIN32 Build?> >> >> >> > I too faced same kind of a problem building libpq on Windows and as a
> > solution to it removed "inline" from the file "\backend\utils\mb\wchar.c"> >> > Did the following ... changed "static inline int" to "static int" instead
> > at a couple of places in the wchar.c file> >> > Run "nmake" after doing this specific change and hopefully it will solve> > the problem.> >> > Thanks,
> > --> > Shoaib Mir> > EnterpriseDB ( www.enterprisedb.com)> >> > On 8/9/06, *DEV* <[EMAIL PROTECTED]
> wrote:> >> > Hello all.> >> > I am trying to build postgres from the source on a WINXP system. I> > am using MSVC++.Net and I use the link with it to get to the command
> > prompt. After I do that I run vcvars32.bat and the nmake /f win32.mak and> > I get:> >> >> >> > C:\Dev\postgresql-8.1.4\src>nmake /f win32.mak> >
> >> >> > Microsoft (R) Program Maintenance Utility Version 7.10.3077> >> > Copyright (C) Microsoft Corporation. All rights reserved.> >> >> >
> > cd include> >> > if not exist pg_config.h copy pg_config.h.win32 pg_config.h> >> > cd ..> >> > cd interfaces\libpq
> >> > nmake /f win32.mak> >> >> >> > Microsoft (R) Program Maintenance Utility Version 7.10.3077> >> > Copyright (C) Microsoft Corporation. All rights reserved.
> >> >> >> > Building the Win32 static library...> >> >> >> > cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp> >> > cl : Command line warning D4029 : optimization is not available in the
> > standard> >> > edition compiler> >> > wchar.c> >> > ..\..\backend\utils\mb\wchar.c(100) : error C2054: expected '(' to follow> > 'inlin
> >> > e'> >> > ..\..\backend\utils\mb\wchar.c(101) : error C2085: 'pg_euc_mblen' : not in> > forma> >> > l parameter list> >> > ..\..\backend\utils\mb\wchar.c(101) : error C2143: syntax error : missing
> > ';' be> >> > fore '{'> >> > ..\..\backend\utils\mb\wchar.c(116) : error C2054: expected '(' to follow> > 'inlin> >> > e'> >
> > ..\..\backend\utils\mb\wchar.c(117) : error C2085: 'pg_euc_dsplen' : not> > in form> >> > al parameter list> >> > ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> > ';' be> >> > fore '{'> >> > ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'> > undefined; a> >> > ssuming extern returning int
> >> > ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'> > undefined;> >> > assuming extern returning int> >> > NMAKE : fatal error U1077: '
cl.exe' : return code '0x2'> >> > Stop.> >> > NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET> > 2003\> >> > VC7\BIN\nmake.exe"' : return code '0x2'
> >> > Stop.> >> >> >> > What do you I need to change to mak
Re: [GENERAL] WIN32 Build?
Shoaib Mir wrote:
> Dev,
>
> You are doing it a little wrong for Debug version, in order to build the
> Debug version use the following:
>
> nmake /f win32.mak DEBUG=1
>
> Bruce,
>
> If you remove "inline" the build process goes fine and if you dont, it first
> gives a few warning and in the end quits the build process with a fatal
> error.
OK, good to know. If we ever find a symbol that is defined for that
compiler that we can test, we can fix this.
---
>
> Log shows:
>
> ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> ';' be
> fore '{'
> ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'
> undefined; a
> ssuming extern returning int
> ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
> undefined;
> assuming extern returning int
> NMAKE : fatal error U1077: 'cl.exe' : return code '0x2'
> Stop.
>
> Thanks,
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
> On 8/10/06, DEV <[EMAIL PROTECTED]> wrote:
> >
> > Okay I get a build and I get the release of the libpq built but it does
> > not build the Debug version of that. I have tried going to the directory
> > and running nmake /f win32.mak DEBUG but it errors with:
> >
> > Building the Win32 static library...
> >
> >
> >
> > NMAKE : fatal error U1073: don't know how to make 'DEBUG'
> >
> > Stop.
> >
> >
> >
> > So what am I missing. I see in the mak file for debug but I can not see
> > how to enable that?
> >
> >
> > --
> >
> > *From:* [EMAIL PROTECTED] [mailto:
> > [EMAIL PROTECTED] *On Behalf Of *Shoaib Mir
> > *Sent:* Wednesday, August 09, 2006 3:30 PM
> > *To:* DEV
> > *Cc:* pgsql-general@postgresql.org
> > *Subject:* Re: [GENERAL] WIN32 Build?
> >
> >
> >
> > I too faced same kind of a problem building libpq on Windows and as a
> > solution to it removed "inline" from the file "\backend\utils\mb\wchar.c"
> >
> > Did the following ... changed "static inline int" to "static int" instead
> > at a couple of places in the wchar.c file
> >
> > Run "nmake" after doing this specific change and hopefully it will solve
> > the problem.
> >
> > Thanks,
> > --
> > Shoaib Mir
> > EnterpriseDB ( www.enterprisedb.com)
> >
> > On 8/9/06, *DEV* <[EMAIL PROTECTED]> wrote:
> >
> > Hello all.
> >
> > I am trying to build postgres from the source on a WINXP system. I
> > am using MSVC++.Net and I use the link with it to get to the command
> > prompt. After I do that I run vcvars32.bat and the nmake /f win32.mak and
> > I get:
> >
> >
> >
> > C:\Dev\postgresql-8.1.4\src>nmake /f win32.mak
> >
> >
> >
> > Microsoft (R) Program Maintenance Utility Version 7.10.3077
> >
> > Copyright (C) Microsoft Corporation. All rights reserved.
> >
> >
> >
> > cd include
> >
> > if not exist pg_config.h copy pg_config.h.win32 pg_config.h
> >
> > cd ..
> >
> > cd interfaces\libpq
> >
> > nmake /f win32.mak
> >
> >
> >
> > Microsoft (R) Program Maintenance Utility Version 7.10.3077
> >
> > Copyright (C) Microsoft Corporation. All rights reserved.
> >
> >
> >
> > Building the Win32 static library...
> >
> >
> >
> > cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp
> >
> > cl : Command line warning D4029 : optimization is not available in the
> > standard
> >
> > edition compiler
> >
> > wchar.c
> >
> > ..\..\backend\utils\mb\wchar.c(100) : error C2054: expected '(' to follow
> > 'inlin
> >
> > e'
> >
> > ..\..\backend\utils\mb\wchar.c(101) : error C2085: 'pg_euc_mblen' : not in
> > forma
> >
> > l parameter list
> >
> > ..\..\backend\utils\mb\wchar.c(101) : error C2143: syntax error : missing
> > ';' be
> >
> > fore '{'
> >
> > ..\..\backend\utils\mb\wchar.c(116) : error C2054: expected '(' to follow
> > 'inlin
> >
> > e'
> >
> > ..\..\backend\utils\mb\wchar.c(117) : error C2085: 'pg_euc_dsplen' : not
> > in form
> >
> > al parameter list
> >
> > ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> > ';' be
> >
> > fore '{'
> >
> > ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'
> > undefined; a
> >
> > ssuming extern returning int
> >
> > ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
> > undefined;
> >
> > assuming extern returning int
> >
> > NMAKE : fatal error U1077: 'cl.exe' : return code '0x2'
> >
> > Stop.
> >
> > NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET
> > 2003\
> >
> > VC7\BIN\nmake.exe"' : return code '0x2'
> >
> > Stop.
> >
> >
> >
> > What do you I need to change to make this work?
> >
> >
> >
> > Brian Doyle
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 9: In versio
Re: [GENERAL] WIN32 Build?
Dev,You are doing it a little wrong for Debug version, in order to build the Debug version use the following:nmake /f win32.mak DEBUG=1Bruce,If you remove "inline" the build process goes fine and if you dont, it first gives a few warning and in the end quits the build process with a fatal error.
Log shows:..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing ';' before '{'..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen' undefined; assuming extern returning int
..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen' undefined;assuming extern returning intNMAKE : fatal error U1077: 'cl.exe' : return code '0x2'Stop.Thanks,-- Shoaib Mir
EnterpriseDB (www.enterprisedb.com)On 8/10/06, DEV <[EMAIL PROTECTED]> wrote:
Okay I get a
build and I get the release of the libpq built but it does not build the Debug
version of that. I have tried going to the directory and running nmake /f
win32.mak DEBUG but it errors with:
Building the
Win32 static library...
NMAKE : fatal
error U1073: don't know how to make 'DEBUG'
Stop.
So what am I
missing. I see in the mak file for debug but I can not see how to enable that?
From:
[EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED]] On Behalf Of Shoaib Mir
Sent: Wednesday, August 09, 2006
3:30 PM
To: DEV
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] WIN32
Build?
I too faced same kind of a
problem building libpq on Windows and as a solution to it removed
"inline" from the file "\backend\utils\mb\wchar.c"
Did the following ... changed "static inline int" to "static
int" instead at a couple of places in the wchar.c file
Run "nmake" after doing this specific change and hopefully it will
solve the problem.
Thanks,
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
On 8/9/06, DEV
<[EMAIL PROTECTED]> wrote:
Hello all.
I am trying
to build postgres from the source on a WINXP system. I am using MSVC++.Net and
I use the link with it to get to the command prompt. After I do that I
run vcvars32.bat and the nmake /f win32.mak and I get:
C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak
Microsoft (R) Program Maintenance
Utility Version 7.10.3077
Copyright (C) Microsoft
Corporation. All rights reserved.
cd include
if not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd interfaces\libpq
nmake /f win32.mak
Microsoft (R) Program Maintenance
Utility Version 7.10.3077
Copyright (C) Microsoft
Corporation. All rights reserved.
Building the Win32 static library...
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp
cl : Command line warning D4029 :
optimization is not available in the standard
edition compiler
wchar.c
..\..\backend\utils\mb\wchar.c(100) :
error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(101) :
error C2085: 'pg_euc_mblen' : not in forma
l parameter list
..\..\backend\utils\mb\wchar.c(101) :
error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(116) :
error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(117) :
error C2085: 'pg_euc_dsplen' : not in form
al parameter list
..\..\backend\utils\mb\wchar.c(117) :
error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(143) :
warning C4013: 'pg_euc_mblen' undefined; a
ssuming extern returning int
..\..\backend\utils\mb\wchar.c(180) :
warning C4013: 'pg_euc_dsplen' undefined;
assuming extern returning int
NMAKE : fatal error U1077: 'cl.exe' :
return code '0x2'
Stop.
NMAKE : fatal error U1077:
'"C:\Program Files\Microsoft Visual Studio .NET 2003\
VC7\BIN\nmake.exe"' : return
code '0x2'
Stop.
What do you I need to change to make
this work?
Brian Doyle
-- Shoaib MirEnterpriseDB (www.enterprisedb.com)
Re: [GENERAL] Tuning to speed select
On Wed, Aug 09, 2006 at 04:54:00PM -0400, Tom Laudeman wrote: > I'll try CLUSTER (I'm looking forward to that test), but if we really > need speed, it will probably be necessary to create copies of the table, > or copy portions of the table elsewhere (essentially creating > materialized views, I suppose). I'm still trying to get my science > compatriot here to tell me which index he most wants to improve, then > I'll CLUSTER the table on that index. If you enable statistics collection then you could use those statistics to see which indexes are used the most. Those indexes might be good candidates for clustering. http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tuning to speed select
Michael, Great suggestion. I've read about CLUSTER, but never had a chance to use it. The only problem is that this table with 9 million records has 5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER will make at least one of the queries run very fast, especially for an index with a small number of distinct values. The speed of the query is (as Michael implies) limited to the rate at which the disk can seek and read. I have done experiments with views and cursors; there was no improvement in speed. I've also tried only pulling back primary keys in the hope that a smaller amount of data would more quickly be read into memory. No speed increase. I have also raised all the usual memory limits, with the expected results (slight speed improvements). I'll try CLUSTER (I'm looking forward to that test), but if we really need speed, it will probably be necessary to create copies of the table, or copy portions of the table elsewhere (essentially creating materialized views, I suppose). I'm still trying to get my science compatriot here to tell me which index he most wants to improve, then I'll CLUSTER the table on that index. Thanks! Tom Michael Fuhr wrote: On Wed, Aug 09, 2006 at 03:46:38PM +0200, Martijn van Oosterhout wrote: On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote: Is there a tuning parameter I can change to increase speed of selects? Clearly, there's already some buffering going on since selecting an indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 seconds on the second try (from pgsql). Your OS is probably buffering, 1GB of RAM holds a lot of data. You can try increasing the shared_buffers parameter, but if the delay is getting data from the disk, that won't really help you. If most of your queries use the same index then clustering on that index might speed up initial (i.e., not-cached) queries by reducing the number of disk pages that need to be read. See the documentation for more information. http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html -- Tom Laudeman [EMAIL PROTECTED] (434) 924-2456 http://www.people.virginia.edu/~twl8n/ http://laudeman.com/
Re: [GENERAL] WIN32 Build?
Okay I get a
build and I get the release of the libpq built but it does not build the Debug
version of that. I have tried going to the directory and running nmake /f
win32.mak DEBUG but it errors with:
Building the
Win32 static library...
NMAKE : fatal
error U1073: don't know how to make 'DEBUG'
Stop.
So what am I
missing. I see in the mak file for debug but I can not see how to enable that?
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shoaib Mir
Sent: Wednesday, August 09, 2006
3:30 PM
To: DEV
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] WIN32
Build?
I too faced same kind of a
problem building libpq on Windows and as a solution to it removed
"inline" from the file "\backend\utils\mb\wchar.c"
Did the following ... changed "static inline int" to "static
int" instead at a couple of places in the wchar.c file
Run "nmake" after doing this specific change and hopefully it will
solve the problem.
Thanks,
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
On 8/9/06, DEV
<[EMAIL PROTECTED]> wrote:
Hello all.
I am trying
to build postgres from the source on a WINXP system. I am using MSVC++.Net and
I use the link with it to get to the command prompt. After I do that I
run vcvars32.bat and the nmake /f win32.mak and I get:
C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak
Microsoft (R) Program Maintenance
Utility Version 7.10.3077
Copyright (C) Microsoft
Corporation. All rights reserved.
cd include
if not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd interfaces\libpq
nmake /f win32.mak
Microsoft (R) Program Maintenance
Utility Version 7.10.3077
Copyright (C) Microsoft
Corporation. All rights reserved.
Building the Win32 static library...
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp
cl : Command line warning D4029 :
optimization is not available in the standard
edition compiler
wchar.c
..\..\backend\utils\mb\wchar.c(100) :
error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(101) :
error C2085: 'pg_euc_mblen' : not in forma
l parameter list
..\..\backend\utils\mb\wchar.c(101) :
error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(116) :
error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(117) :
error C2085: 'pg_euc_dsplen' : not in form
al parameter list
..\..\backend\utils\mb\wchar.c(117) :
error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(143) :
warning C4013: 'pg_euc_mblen' undefined; a
ssuming extern returning int
..\..\backend\utils\mb\wchar.c(180) :
warning C4013: 'pg_euc_dsplen' undefined;
assuming extern returning int
NMAKE : fatal error U1077: 'cl.exe' :
return code '0x2'
Stop.
NMAKE : fatal error U1077:
'"C:\Program Files\Microsoft Visual Studio .NET 2003\
VC7\BIN\nmake.exe"' : return
code '0x2'
Stop.
What do you I need to change to make
this work?
Brian Doyle
Re: [GENERAL] Tuning to speed select
Title: Re: [GENERAL] Tuning to speed select On Wed, 2006-08-09 at 14:58, louis gonzales wrote: > I'm not so sure about that, when you create a view on a table - at least > with Oracle - which is a subset(the trivial or 'proper' subset is the > entire table view) of the information on a table, when a select is > issued against a table, Oracle at least, determines if there is a view > already on a the table which potentially has a smaller amount of > information to process - as long as the view contains the proper > constraints that meet your 'select' criteria, the RDBMS engine will have > fewer records to process - which I'd say, certainly constitutes a time > benefit, in terms of 'performance gain.' > > Hence my reasoning behind determining IF there is a subset of the 'big > table' that is frequented, I'd create a view on this, assuming > postgresql does this too? Maybe somebody else can answer that for the > pgsql-general's general information? > > query-speed itself is going to be as fast/slow as your system is > configured for, however my point was to shave some time off of a 1M+ > record table, but implementing views of 'frequently' visisted/hit > records meeting the same specifications. There are basically two ways to do views. The simple way, is to have a view represent a query that gets run everytime you call it. The more complex way is to "materialize" the view data, and put it into a new table, and then update that table whenever the source table changes. PostgreSQL has native support for the first type. They're cheap and easy, and work for most of the things people need views for (i.e. hiding complexity). PostgreSQL is extensible, and therefore you can institute the second type (i.e. materialized views) on your own. Thanksfully, someone else has already done most of the work for us, by the name of Jonathan Gardner, and you can find his nifty guide by typing "materialized views postgresql" into google. Gardner's materialized views support several update methods depending on what you need from your mat views. It's also a danged fine tutorial on how to write some simple plpgsql functions.
Re: [GENERAL] PL/pgSQL Problem
Michael Fuhr wrote: On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote: ERROR: duplicate key violates unique constraint "systemcounts_pkey" CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date)" PL/pgSQL function "updatesystemcounts" line 8 at SQL statement Is the table's primary key a serial column? If so then the sequence might be out of sync with the values in the table, in which case you'll need to adjust the sequence's value with ALTER SEQUENCE or setval(). Aha, that was exactly what the problem was. Thanks! Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] WIN32 Build?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Interesting. It seem the key line is here: > > > cl : Command line warning D4029 : optimization is not available in the > > standard edition compiler > > > So the "standard" version doesn't support inline functions. > > Doesn't it simply ignore the inline keyword then? Count on Microsoft > for poorly designed tools, I suppose. You would think so. Can someone confirm that the warning generates errors later on? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] WIN32 Build?
Bruce Momjian <[EMAIL PROTECTED]> writes: > Interesting. It seem the key line is here: > cl : Command line warning D4029 : optimization is not available in the > standard edition compiler > So the "standard" version doesn't support inline functions. Doesn't it simply ignore the inline keyword then? Count on Microsoft for poorly designed tools, I suppose. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tuning to speed select
I'm not so sure about that, when you create a view on a table - at least with Oracle - which is a subset(the trivial or 'proper' subset is the entire table view) of the information on a table, when a select is issued against a table, Oracle at least, determines if there is a view already on a the table which potentially has a smaller amount of information to process - as long as the view contains the proper constraints that meet your 'select' criteria, the RDBMS engine will have fewer records to process - which I'd say, certainly constitutes a time benefit, in terms of 'performance gain.' Hence my reasoning behind determining IF there is a subset of the 'big table' that is frequented, I'd create a view on this, assuming postgresql does this too? Maybe somebody else can answer that for the pgsql-general's general information? query-speed itself is going to be as fast/slow as your system is configured for, however my point was to shave some time off of a 1M+ record table, but implementing views of 'frequently' visisted/hit records meeting the same specifications. Harald Armin Massa wrote: Louis, Views certainly help in managing complexity. They do nothing to improve query-speed. Querying a view gets rewritten to queries to the underlying tables on the fly. (as long as there are no materialized views, which are still on a the TODO list) -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Let's set so double the killer delete select all. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] WIN32 Build?
DEV wrote: > Shoaib, > That looks like it worked thank you! > > Bruce, > I am using the following compliler: > C:\Dev\postgresql-8.1.4\src\interfaces\libpq\Release>nmake --help > > Microsoft (R) Program Maintenance Utility Version 7.10.3077 > Copyright (C) Microsoft Corporation. All rights reserved. > > Which came with > Microsoft Visual C++ .NET Version 7.1.3088 > > Don't know if that helps or not! Not really. I need to see all the defines predefined by the compiler. Anyone know how to do that on MSVC? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] WIN32 Build?
Shoaib, That looks like it worked thank you! Bruce, I am using the following compliler: C:\Dev\postgresql-8.1.4\src\interfaces\libpq\Release>nmake --help Microsoft (R) Program Maintenance Utility Version 7.10.3077 Copyright (C) Microsoft Corporation. All rights reserved. Which came with Microsoft Visual C++ .NET Version 7.1.3088 Don't know if that helps or not! -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 09, 2006 3:41 PM To: Shoaib Mir Cc: DEV; pgsql-general@postgresql.org Subject: Re: [GENERAL] WIN32 Build? Shoaib Mir wrote: > I too faced same kind of a problem building libpq on Windows and as a > solution to it removed "inline" from the file > "\backend\utils\mb\wchar.c" > > Did the following ... changed "static inline int" to "static int" > instead at a couple of places in the wchar.c file > > Run "nmake" after doing this specific change and hopefully it will > solve the problem. Interesting. It seem the key line is here: > > cl : Command line warning D4029 : optimization is not available in the > > standard edition compiler So the "standard" version doesn't support inline functions. To fix that, I think we need some compiler define test to tell us if it is a "standard" edition compiler. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tuning to speed select
> Views certainly help in managing complexity. They do nothing to improve > query-speed. > > Querying a view gets rewritten to queries to the underlying tables on the > fly. > (as long as there are no materialized views, which are still on a the TODO > list) Would partial indexs on the most queried regions of the table help in query speed? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] WIN32 Build?
Shoaib Mir wrote: > I too faced same kind of a problem building libpq on Windows and as a > solution to it removed "inline" from the file > "\backend\utils\mb\wchar.c" > > Did the following ... changed "static inline int" to "static int" > instead at a couple of places in the wchar.c file > > Run "nmake" after doing this specific change and hopefully it will > solve the problem. Interesting. It seem the key line is here: > > cl : Command line warning D4029 : optimization is not available in the > > standard edition compiler So the "standard" version doesn't support inline functions. To fix that, I think we need some compiler define test to tell us if it is a "standard" edition compiler. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tuning to speed select
Louis,Views certainly help in managing complexity. They do nothing to improve query-speed. Querying a view gets rewritten to queries to the underlying tables on the fly.(as long as there are no materialized views, which are still on a the TODO list) -- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] WIN32 Build?
I too faced same kind of a problem building libpq on Windows and as a solution to it removed "inline" from the file
"\backend\utils\mb\wchar.c"Did the following ... changed "static inline int" to "static int" instead at a couple of places in the wchar.c file
Run "nmake" after doing this specific change and hopefully it will solve the problem.Thanks,-- Shoaib MirEnterpriseDB (
www.enterprisedb.com)
On 8/9/06, DEV <[EMAIL PROTECTED]> wrote:
Hello all.
I am
trying to build postgres from the source on a WINXP system. I am using MSVC++.Net
and I use the link with it to get to the command prompt. After I do that I run
vcvars32.bat and the nmake /f win32.mak and I get:
C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak
Microsoft (R)
Program Maintenance Utility Version 7.10.3077
Copyright (C)
Microsoft Corporation. All rights reserved.
cd
include
if
not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd
interfaces\libpq
nmake
/f win32.mak
Microsoft (R)
Program Maintenance Utility Version 7.10.3077
Copyright (C)
Microsoft Corporation. All rights reserved.
Building the
Win32 static library...
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp
cl : Command
line warning D4029 : optimization is not available in the standard
edition
compiler
wchar.c
..\..\backend\utils\mb\wchar.c(100)
: error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(101)
: error C2085: 'pg_euc_mblen' : not in forma
l parameter
list
..\..\backend\utils\mb\wchar.c(101)
: error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(116)
: error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(117)
: error C2085: 'pg_euc_dsplen' : not in form
al parameter
list
..\..\backend\utils\mb\wchar.c(117)
: error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(143)
: warning C4013: 'pg_euc_mblen' undefined; a
ssuming
extern returning int
..\..\backend\utils\mb\wchar.c(180)
: warning C4013: 'pg_euc_dsplen' undefined;
assuming
extern returning int
NMAKE : fatal
error U1077: 'cl.exe' : return code '0x2'
Stop.
NMAKE : fatal
error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\
VC7\BIN\nmake.exe"'
: return code '0x2'
Stop.
What do you I
need to change to make this work?
Brian Doyle
Re: [GENERAL] Tuning to speed select
What about creating views on areas of the table that are queried often? I don't know if you have access or the ability to find what type of trends the table has, in terms of queries, but if you create some views on frequently visited information, this could also help. Tom Laudeman wrote: Hi, I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM and an IDE hard drive. My big table has around 9 million records. Is there a tuning parameter I can change to increase speed of selects? Clearly, there's already some buffering going on since selecting an indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 seconds on the second try (from pgsql). cowpea=> explain analyze select bs_fk from blast_result where si_fk=11843254; QUERY PLAN Index Scan using si_fk_index on blast_result (cost=0.00..22874.87 rows=58118 width=4) (actual time=112.249..17472.935 rows=50283 loops=1) Index Cond: (si_fk = 11843254) Total runtime: 17642.522 ms (3 rows) cowpea=> explain analyze select bs_fk from blast_result where si_fk=11843254; QUERY PLAN Index Scan using si_fk_index on blast_result (cost=0.00..22874.87 rows=58118 width=4) (actual time=0.178..341.643 rows=50283 loops=1) Index Cond: (si_fk = 11843254) Total runtime: 505.011 ms (3 rows) cowpea=> Thanks, Tom ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PITR Questions
> I don't see how checkpoint_timeout is relevant. Just because we > checkpoint doesn't mean the WAL file will get archived. I have to have > 16M of WAL traffic before a file gets archived regardless of > check-pointing, or am I missing something? Right, I think ;-) If you want finer-grained backup, you have to do something like rsync the current WAL file frequently. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PL/pgSQL Problem
On 8/9/06, Ron St-Pierre <[EMAIL PROTECTED]> wrote: Hi, I'm having a problem with one of my functions, where I delete all rows containing a particular date and then re-insert a row with that same date. When I try this I get a constraint error. This just started recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL). here's the function: CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS ' DECLARE compDate DATE; currCount INT; BEGIN compDate := current_date::date; LOOP DELETE FROM dm.systemCounts WHERE updateDate::date = compDate::date; INSERT INTO dm.systemCounts (updateDate) VALUES (compDate::date); . and here's the error: ERROR: duplicate key violates unique constraint "systemcounts_pkey" CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date)" PL/pgSQL function "updatesystemcounts" line 8 at SQL statement The only explanation I can think of is that maybe the newer version of postgres needs a COMMIT inside the function. commit inside function is impossible (by definition), however you can do subtransaction inside the function and catch the error. what is the primary key for dm.systemCounts. does it have a default? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] LinuxWorld West
Hello, I will be basically unavailable from this Saturday until the 21st of August. I will be spending a long week in SF at LinuxWorld West. Please use email to contact me if it is important. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PL/pgSQL Problem
On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote: > ERROR: duplicate key violates unique constraint "systemcounts_pkey" > CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( > $1 ::date)" > PL/pgSQL function "updatesystemcounts" line 8 at SQL statement Is the table's primary key a serial column? If so then the sequence might be out of sync with the values in the table, in which case you'll need to adjust the sequence's value with ALTER SEQUENCE or setval(). -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PL/pgSQL Problem
Hi, I'm having a problem with one of my functions, where I delete all rows containing a particular date and then re-insert a row with that same date. When I try this I get a constraint error. This just started recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL). here's the function: CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS ' DECLARE compDate DATE; currCount INT; BEGIN compDate := current_date::date; LOOP DELETE FROM dm.systemCounts WHERE updateDate::date = compDate::date; INSERT INTO dm.systemCounts (updateDate) VALUES (compDate::date); . and here's the error: ERROR: duplicate key violates unique constraint "systemcounts_pkey" CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date)" PL/pgSQL function "updatesystemcounts" line 8 at SQL statement The only explanation I can think of is that maybe the newer version of postgres needs a COMMIT inside the function. Any ideas? Thanks Ron St.Pierre ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] psql/readline clears screen
On 8/9/06, Reece Hart <[EMAIL PROTECTED]> wrote: On Wed, 2006-08-09 at 09:02 -0400, Merlin Moncure wrote: is there a way to configure psql/readline so that it doesn't clear the screen after browsing a query with 'q'? I assume you're using the less text pager on a Unix box. I think what you're seeing is less clearing the screen after displaying a query (i.e., it's not psql or readline). I like the following invocation: less -iMSx4 -FX (see less manpage for explanation). This is particularly useful with ' \pset pager always' (in .psqlrc). The upshot is that less will decide whether the contents fit on one screen both horizontally and vertically; if so it'll display and quit without any interaction, and if not it will permit scrolling in one or both directions. actually, i do my editing about 60% windows, 40% linux until i finish migrating my development platform to os x. on windows, i tried out your less options using the cygwin compiled psql and like them very much. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] WIN32 Build?
Hello all.
I am
trying to build postgres from the source on a WINXP system. I am using MSVC++.Net
and I use the link with it to get to the command prompt. After I do that I run
vcvars32.bat and the nmake /f win32.mak and I get:
C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak
Microsoft (R)
Program Maintenance Utility Version 7.10.3077
Copyright (C)
Microsoft Corporation. All rights reserved.
cd
include
if
not exist pg_config.h copy pg_config.h.win32 pg_config.h
cd ..
cd
interfaces\libpq
nmake
/f win32.mak
Microsoft (R)
Program Maintenance Utility Version 7.10.3077
Copyright (C)
Microsoft Corporation. All rights reserved.
Building the
Win32 static library...
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp
cl : Command
line warning D4029 : optimization is not available in the standard
edition
compiler
wchar.c
..\..\backend\utils\mb\wchar.c(100)
: error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(101)
: error C2085: 'pg_euc_mblen' : not in forma
l parameter
list
..\..\backend\utils\mb\wchar.c(101)
: error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(116)
: error C2054: expected '(' to follow 'inlin
e'
..\..\backend\utils\mb\wchar.c(117)
: error C2085: 'pg_euc_dsplen' : not in form
al parameter
list
..\..\backend\utils\mb\wchar.c(117)
: error C2143: syntax error : missing ';' be
fore '{'
..\..\backend\utils\mb\wchar.c(143)
: warning C4013: 'pg_euc_mblen' undefined; a
ssuming
extern returning int
..\..\backend\utils\mb\wchar.c(180)
: warning C4013: 'pg_euc_dsplen' undefined;
assuming
extern returning int
NMAKE : fatal
error U1077: 'cl.exe' : return code '0x2'
Stop.
NMAKE : fatal
error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\
VC7\BIN\nmake.exe"'
: return code '0x2'
Stop.
What do you I
need to change to make this work?
Brian Doyle
Re: [GENERAL] psql/readline clears screen
On Wed, 2006-08-09 at 09:02 -0400, Merlin Moncure wrote: is there a way to configure psql/readline so that it doesn't clear the screen after browsing a query with 'q'? I assume you're using the less text pager on a Unix box. I think what you're seeing is less clearing the screen after displaying a query (i.e., it's not psql or readline). I like the following invocation: less -iMSx4 -FX (see less manpage for explanation). This is particularly useful with ' \pset pager always' (in .psqlrc). The upshot is that less will decide whether the contents fit on one screen both horizontally and vertically; if so it'll display and quit without any interaction, and if not it will permit scrolling in one or both directions. There are two ways you might enable this: - set and export the LESS environment variable in your shell setup, like this in .bashrc: export LESS='-iMSx4 -FX' - prefix your psql invocation with the LESS environment variable setting, like this: $ LESS='iMSx4 -FX' psql -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Re: [GENERAL] read only transaction, temporary tables
Richard Huxton writes: > Carl R. Brune wrote: >> I should have added that I want to make further use of the temporary >> table after the COMMIT -- the rollback approach you propose makes it >> go away. > In which case the transaction isn't READONLY. It does seem a bit inconsistent that we allow you to write into a temp table during a "READONLY" transaction, but not to create/drop one. I'm not excited about changing it though, as the tests to see if the command is allowed would become vastly more complex. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Not so much load balancing as load limits
I'm having some trouble getting a google hit on this topic, and the docs aren't doing much for me. What I'm wondering is, how do I limit any particular postgres operation, in particular a long-running batch operation with lots of inserts, from bogging down a server? This is not so much a question of how to code the SQL, as it is how to keep a selfish process from causing a self-inflicted DoS situation. Can anybody get me pointed in the right direction with a link or two? Thanks. begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tuning to speed select
On Wed, Aug 09, 2006 at 03:46:38PM +0200, Martijn van Oosterhout wrote: > On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote: > > Is there a tuning parameter I can change to increase speed of selects? > > Clearly, there's already some buffering going on since selecting an > > indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 > > seconds on the second try (from pgsql). > > Your OS is probably buffering, 1GB of RAM holds a lot of data. You can > try increasing the shared_buffers parameter, but if the delay is > getting data from the disk, that won't really help you. If most of your queries use the same index then clustering on that index might speed up initial (i.e., not-cached) queries by reducing the number of disk pages that need to be read. See the documentation for more information. http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tuning to speed select
On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote: > Hi, > > I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM > and an IDE hard drive. My big table has around 9 million records. > > Is there a tuning parameter I can change to increase speed of selects? > Clearly, there's already some buffering going on since selecting an > indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 > seconds on the second try (from pgsql). Your OS is probably buffering, 1GB of RAM holds a lot of data. You can try increasing the shared_buffers parameter, but if the delay is getting data from the disk, that won't really help you. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Data warehouse & OLAP
On 8/9/06, Stefano B. <[EMAIL PROTECTED]> wrote: hi, I'm working in the implementation of a datawarehouse on Postgres. For analisys of aggregated data I'd like to use some OLAP tools like for example, Mondrian, OR use the meterialized view (if better). My questions: is there any documentation about warehouse (with OLAP or materialized view) on Postgres? Which is better? OLAP tools (I think) or materialized view? Any information is welcome! Thanks You'll obviously have to identify all possible changes to your normalized data that affect the data warehouse consistency. As far as I know, pgsql doesn't directly support materialized views, so you'll write one or more update_warehouse functions and do one of the following: 1.) set up a collection of triggers to keep track of all changes since the last synchronization. Set up a periodic task (probably using pgAgent) which will invoke the update_warehouse functions which will than update the needed records. 2.) set up a collection of triggers directly calling your update_functions The first option is what probably 99% users need because of the implicit nature of the queries run against warehouse data. An hour, day or even week of the latest data very often makes no difference when analyzing an OLAP cube, but the exact tolerance level obviously depends on the exact queries analysts really run, the resources available/needed to refresh the warehouse, the nature of the data etc. The second option is nice in that it keeps the relational data in sync with the warehouse, but this can only be implemented in specific systems where the update load is tolerable, changes limited in scope and update triggers highly focused on the scope of the change. I can imagine circumstances when you'd need such a setup, but most of the time it's just a theoretical possibility. I have implemented the second approach using the very good Mondrian OLAP server and stunning JRubik analysis interface in one of my systems (small database, several dozen MB, less than a milion records, total) at the price of a ~2 sec delay when updating a row - an action that happens about 50-60 times per day. The reason was very non-technical: we needed to be able to say "you can analyze data in real time". Hope this helps. Cheers, t.n.a. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Tuning to speed select
Hi, I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM and an IDE hard drive. My big table has around 9 million records. Is there a tuning parameter I can change to increase speed of selects? Clearly, there's already some buffering going on since selecting an indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 seconds on the second try (from pgsql). cowpea=> explain analyze select bs_fk from blast_result where si_fk=11843254; QUERY PLAN Index Scan using si_fk_index on blast_result (cost=0.00..22874.87 rows=58118 width=4) (actual time=112.249..17472.935 rows=50283 loops=1) Index Cond: (si_fk = 11843254) Total runtime: 17642.522 ms (3 rows) cowpea=> explain analyze select bs_fk from blast_result where si_fk=11843254; QUERY PLAN Index Scan using si_fk_index on blast_result (cost=0.00..22874.87 rows=58118 width=4) (actual time=0.178..341.643 rows=50283 loops=1) Index Cond: (si_fk = 11843254) Total runtime: 505.011 ms (3 rows) cowpea=> Thanks, Tom -- Tom Laudeman [EMAIL PROTECTED] (434) 924-2456 http://www.people.virginia.edu/~twl8n/ http://laudeman.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] psql/readline clears screen
On Aug 9, 2006, at 9:02 , Merlin Moncure wrote: is there a way to configure psql/readline so that it doesn't clear the screen after browsing a query with 'q'? Assuming you are using less as your PAGER, then see: http://www.greenwoodsoftware.com/less/#tite One can add the -X option to your LESS or PAGER environment variables. -M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql/readline clears screen
On Wed, Aug 09, 2006 at 09:02:55AM -0400, Merlin Moncure wrote: > is there a way to configure psql/readline so that it doesn't clear the > screen after browsing a query with 'q'? You don't say the OS, but you may be seeing the effects of less working on a terminal with a seperate interactive screen (for example xterm). What's happening is that when less exits, it switches back to the main screen, thus hiding what you were just looking at. From reading the manpage, setting the pager to "less -X" should solve this problem. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] psql/readline clears screen
is there a way to configure psql/readline so that it doesn't clear the screen after browsing a query with 'q'? regards, merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DB connectivity from a client machine
Jasbinder Bali wrote: In my database server, I'm using packaged version of postgres. I installed it using: "yum install postgres" and this inturn searches for the rmp. So, its a packaged version of postgresql. Now as far as my client is concerned, that has the middle tier component written in C, i don't have any postgres there. Do you mean to say that I need to install the client version of postgres in the m/c hosting the middle tier component and trying to connect to the postgres server? No but you may need to install the libs. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] sizing of a server
The best way of finding out is to do some testing - use a standard desktop machine (or laptop) and see how that performs. Make sure you have a realistic mix and amount of data though. If I had to guess, I'd buy a cheap server with some SATA disks (at least 2 so you can mirror them) and as much RAM as was left in my budget. Yeah, this sounds like a dualcore amd64 with a areca 4 drive sata raid 10 array. Put 2/4 gig of ram in it, and you will think it is just flying. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Data warehouse & OLAP
hi, I'm working in the implementation of a datawarehouse on Postgres. For analisys of aggregated data I'd like to use some OLAP tools like for example, Mondrian, OR use the meterialized view (if better). My questions: is there any documentation about warehouse (with OLAP or materialized view) on Postgres? Which is better? OLAP tools (I think) or materialized view? Any information is welcome! Thanks Stefano
Re: [GENERAL] DB connectivity from a client machine
On Tue, Aug 08, 2006 at 05:25:12PM -0400, Jasbinder Bali wrote: > Why I'm asking this is (though generally m/c hosting the middle tier > component can connect to the m/c hosting the Db without any DB client > installed) because i tried to run ecpg in the middle tier m/c and it doesn't > work. Well it would definitely not work coz ecpg comes with postgres. This is about the third time you claim ecpg doesn't work. Would you please be so kind to explain what doesn't work? Of course ecpg can be run on a machine without a PostgreSQL server. And programs compiled with ecpg also do not need a server on the same machine, why should they? This is why Debian for instance has a seperate ecpg package. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need help with quote escaping in exim for postgresql
On Fri, Jul 07, 2006 at 03:48:00PM +0200, Marc Haber wrote: > I am the maintainer of Debian's packages for exim4, a powerful and > versatile Mail Transfer Agent developed in Cambridge and in wide use > throughout the Free Software Community (http://www.exim.org/). > > One of our daemon flavours has PostgreSQL support. Our security guys > have found a flaw in exim regarding quote escaping for PostgreSQL. The > bug is filed in Debian's BTS as http://bugs.debian.org/369351 and was > transferred to exim's Bugzilla installation as > http://www.exim.org/bugzilla/show_bug.cgi?id=107. I would like to thank anybody who commented in this thread. Special thanks go to Florian Weimer, who has done the task of communicating with Philip Hazel, the exim author, and working out a fix which has since been released as part of exim 4.63. Greetings Marc -- - Marc Haber | "I don't trust Computers. They | Mailadresse im Header Mannheim, Germany | lose things."Winona Ryder | Fon: *49 621 72739834 Nordisch by Nature | How to make an American Quilt | Fax: *49 621 72739835 ---(end of broadcast)--- TIP 1: 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] sizing of a server
[EMAIL PROTECTED] wrote: I would like to create a database server with postgres web application. My DB size will be 20GB. I will write 1 a day with until 10 to 15 simultanous write. How could I size my server (memory, CPU, ...) ? The only reliable way is to do some testing. Now 10,000 rows a day isn't many and I doubt it will be 10-15 simultaneous writes (although it might be 10-15 simultaneous connections). The key questions will be how much of that 20GB gets used regularly, and what level of performance you'll need. The best way of finding out is to do some testing - use a standard desktop machine (or laptop) and see how that performs. Make sure you have a realistic mix and amount of data though. If I had to guess, I'd buy a cheap server with some SATA disks (at least 2 so you can mirror them) and as much RAM as was left in my budget. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] read only transaction, temporary tables
Carl R. Brune wrote: I should have added that I want to make further use of the temporary table after the COMMIT -- the rollback approach you propose makes it go away. In which case the transaction isn't READONLY. You have two options: CREATE TEMPORARY TABLE ... AS SELECT ... BEGIN READONLY; ... COMMIT; Or, create a user with only-read permissions on your database and connect as that user. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
