Re: [GENERAL] Vacuum-full very slow
On Thu, 2007-04-26 at 00:13 +0200, Listmail wrote: By the way, about indexes : When you have a small table (say, for a website, maybe a few tens of megabytes max...) reindexing it takes just a few seconds, maybe 10-20 seconds. It could be interesting, performance-wise, to tell postgres not to bother about crash-survivability of indexes on this table. Like temporary tables. Write nothing to WAL. If it crashes, on recovery, postgres would reindex the table. btree indexing is so fast on postgres that I'd definitely use this feature. I'd rather trade a minute of recovery versus less disk IO for index update. You could even do that for whole tables (like, web sessions table) which hold perishable data... That optimisation on mine/Heikki's todo for the next release. In some cases it can speed up recovery, as well as mainline performance. -- Simon Riggs EnterpriseDB http://www.enterprisedb.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
Re: [GENERAL] pg_connect sometimes works sometimes not
Richard Huxton wrote: Did you try pg_last_error()? pg_last_error() does not seem to work. It requires connection as parameter, so if pg_connect() fails - it has nothing to operate on. Or am I missing something? Are you logging connection attempts/failures? Details in the manuals. Checked the manual, but found only parameter to log the successful connections. Is there such to log the failed connection attempts (incl. the reason)? Thank you, Iv ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where to find kind code for STATISTIC_KIND GEOMETRY?
On Wed, 2007-04-25 at 17:09 -0700, [EMAIL PROTECTED] wrote: It is mentioned in postgresql-8.2.3\src\include\catalog\pg_statistic.h file that the values between 100-199 are reserved for assignment by the PostGIS project. Is PostgreSQL reserving these values? Do I did to reserve values like PotGIS is doing? You just need to ask. We're keen to help everyone integrate. Code related issues are usually discussed on pgsql-hackers. It's possible that there's a requirements overlap between ESRI and PostGIS, so it would be better if you could discuss it between you so we don't have to reserve a second range of numbers for identical purposes. I do understand there may be some licencing issues there. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Schema as versioning strategy
Owen Hartnett wrote: Hi: I'm a new user of Postgresql (8.2.3), and I'm very happy with both the performance and operation of the system. My compliments to you the many authors who keep this database running and useful. My question is: I want to freeze a snapshot of the database every year (think of end of year tax records). However, I want this frozen version (and all the previous frozen versions) available to the database user as read-only. My thinking is to copy the entire public schema (which is where all the current data lives) into a new schema, named 2007 (2008, etc.) Is this a valid plan. I had thought of using a different database, but that would require multiple opens. I looked to see if there were an easy way to script doing an exact schema copy, but I haven't found anything like it in the docs. This is not heavy usage, nor is there a large amount of data (current pg_dump backups are around 30 Megabytes. Am I on the right track, or would you suggest a different strategy? I get the impression that table partitioning with constraint exclusion would fit your purpose nicely. Effectively the data is split into separate tables with a check constraint on a specific year each, while the total dataset is still available through the common inherited base table. If possible (haven't used this myself yet) the big benefit is that your data ends up in the right table, even if you are a little late starting with your next years data. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Schema as versioning strategy
Jonathan Vanasco wrote: On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote: Owen Hartnett wrote: I want to freeze a snapshot of the database every year (think of end of year tax records). However, I want this frozen version (and all the previous frozen versions) available to the database user as read-only. My thinking is to copy the entire public schema (which is where all the current data lives) into a new schema, named 2007 (2008, etc.) Sounds perfectly reasonable. You could either do it as a series of: CREATE TABLE archive2007.foo AS SELECT * FROM public.foo; or do a pg_dump of schema public, tweak the file to change the schema names and restore it. the create table method won't copy the constraints + fkeys . Shouldn't matter for an archive though, since you'd not want anyone to have permissions. Still, pg_dump is my preference. Apart from anything else, you can keep a copy of the dump around too. -- Richard Huxton Archonet Ltd ---(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] pg_connect sometimes works sometimes not
[EMAIL PROTECTED] wrote:
Richard Huxton wrote:
Did you try pg_last_error()?
pg_last_error() does not seem to work. It requires connection as
parameter, so if pg_connect() fails - it has nothing to operate on.
Or am I missing something?
No, I was. I've gone back and re-read your original message.
I'm a bit surprised you're not seeing an error message when the
connection fails. Try some code like this:
?php
ini_set('track_errors','on');
$conn = @pg_connect(host=localhost dbname=nosuchdb user=richardh);
echo Connection result: ;
print_r($conn);
echo hr;
if ($conn===false) {
echo Connection failed: ;
print_r($php_errormsg);
echo hr;
}
?
Without the track_errors and @ on the front of pg_connect you should
get a php error. You might want an error-handling function.
Are you logging connection attempts/failures? Details in the manuals.
Checked the manual, but found only parameter to log the successful
connections.
Is there such to log the failed connection attempts (incl. the reason)?
It's an error, so you'll see an error logged.
2007-04-26 09:16:00 BST nosuchdb 1 FATAL: database nosuchdb does not
exist
Of course, if you're connecting to the wrong port, or wrong machine then
the server will never see the connection, so it can't log that.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_connect sometimes works sometimes not
Richard Huxton wrote: Try some code like this: OK I'll try it now and write back. Thanks! Iv ---(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]
Hello, I'm searching for instructions on installing PostgreSQL 8.1.4 on SLES 9 SP2. Can someone please point me to a web-site / document that has the proper set of instructions? Cheers _ Tried the new MSN Messenger? Its cool! Download now. http://messenger.msn.com/Download/Default.aspx?mkt=en-in ---(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: [ADMIN] [GENERAL] pg_buffercache view
Hello! Do you know which could be the reasons that could conduce an application to not release the shared buffers, even after the application was shut down? I noticed that only if a pg_ctl restart command is issued some of the buffers are set free. Thank you very much With best regards, Sorin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Fw: PostgreSQL Performance Tuning
Dear, We are facing performance tuning problem while using PostgreSQL Database over the network on a linux OS. Our Database consists of more than 500 tables with an average of 10K records per table with an average of 20 users accessing the database simultaneously over the network. Each table has indexes and we are querying the database using Hibernate. The biggest problem is while insertion, updating and fetching of records, ie the database performance is very slow. It take a long time to respond in the above scenario. Please provide me with the tuning of the database. I am attaching my postgresql.conf file for the reference of our current configuration Please replay me ASAP Regards, Shohab Abdullah Software Engineer, Manufacturing SBU-POWAI Larsen and Toubro Infotech Ltd.| 4th floor, LT Technology Centre, Saki Vihar Road, Powai, Mumbai-400072 (: +91-22-67767366 | (: +91-9870247322 Visit us at : http://www.lntinfotech.com ”I cannot predict future, I cannot change past, I have just the present moment, I must treat it as my last The information contained in this email has been classified: [ X] LT Infotech General Business [] LT Infotech Internal Use Only [] LT Infotech Confidential [] LT Infotech Proprietary This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. __ gifjDhRI0b8FX.gif Description: GIF image gif4eeTSOPwZN.gif Description: GIF image jpgpgfiWUJUOR.jpg Description: JPEG image postgresql.conf Description: Binary data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fw: PostgreSQL Performance Tuning
Please try to post to one list at a time. I've replied to this on the -performance list. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] tsearch2 punctuation question
For example:
select to_tsvector('cat,dog apple/orange');
to_tsvector
--
'cat':1 'dog':2 'apple/orange':3
(1 row)
Is there a setting that allows me to specify that strings containing
the '/' should be parsed into separate words? As is, I can't find
'apple' or 'orange'.
Thanks,
John
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(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: [ADMIN] [GENERAL] pg_buffercache view
In response to Sorin N. Ciolofan [EMAIL PROTECTED]: Hello! Do you know which could be the reasons that could conduce an application to not release the shared buffers, even after the application was shut down? I noticed that only if a pg_ctl restart command is issued some of the buffers are set free. The reason would be by design. If the server flushes its cache every time the application restarts, the cache isn't going to be very effective. If PostgreSQL is using more shared buffers than you're comfortable with, reduce the shared_buffers setting in the config. That will allow the OS to decide how to use the memory instead. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgadmin-support] questions about cursors
Anton, Wrong mailing list. You should send this type of query to pgsql-general@postgresql.org in the future. The documentation is confusing, though. Try This: CREATE OR REPLACE FUNCTION database_correction() RETURNS double precision AS $BODY$ DECLARE mycursor CURSOR FOR select distinct(fund_id) from NAV_values_bfb_history; iterator integer; BEGIN open mycursor; FETCH mycursor INTO iterator; while found Loop -- some computations here FETCH mycursor INTO iterator; END LOOP; CLOSE mycursor; END; Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax Hi, I am trying to use cursors and I am really frustrated already. Do I need to install an extension? 1. Problem number one is that what ever I use in front of the fetch command it is not being accepted, it gives a syntax error. If I use a number ,all or forward it gives an error again?? I want to do something like the code below: CREATE OR REPLACE FUNCTION database_correction() RETURNS double precision AS $BODY$ DECLARE mycursor CURSOR FOR select distinct(fund_id) from NAV_values_bfb_history; iterator integer; BEGIN open mycursor; FETCH mycursor INTO iterator; --fetch next from mycursor --gives an error WHILE (FETCH next from mycursor) LOOP -- some computations here END LOOP; CLOSE mycursor; END; 2. What is the right way to check that the cursor has ended. In sqlserver there is a variable @@fetch_status. I have to make here some comparison in the while clause, but I am not sure what it should be. I could not find a single example for cursor in a loop. I will greatly appreciate any help, pgsql is my database of choice. Cheers, Anton ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] [GENERAL] pg_buffercache view
I don't know the algorithm on which Postgre uses the shared buffers but I'd like to find the principles behind it. Let's assume the following scenario: I've set shared_buffers=3000 At the starting of Postgres there are 115 buffers used by database A After the execution of some processing caused by a java methodA1() invocation, 2850 buffers are used by A. What happens next if these 2850 buffers remains used even if the methodA1() finished its execution? Suppose that now a methodA2() invocation occurs and this method works with database A, too. Will be the 2850 buffers reused or will postgre throw an out of shared memory exception? What happens if a methodB() invocation occurs, assuming that this method tries to work with database B? How Postgre decides the allocation of shared_buffers? Thanks Sorin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran Sent: Thursday, April 26, 2007 3:32 PM To: Sorin N. Ciolofan Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [ADMIN] [GENERAL] pg_buffercache view In response to Sorin N. Ciolofan [EMAIL PROTECTED]: Hello! Do you know which could be the reasons that could conduce an application to not release the shared buffers, even after the application was shut down? I noticed that only if a pg_ctl restart command is issued some of the buffers are set free. The reason would be by design. If the server flushes its cache every time the application restarts, the cache isn't going to be very effective. If PostgreSQL is using more shared buffers than you're comfortable with, reduce the shared_buffers setting in the config. That will allow the OS to decide how to use the memory instead. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Number of Queries
Is there any function to count the number of query run in one sentence? i.e. select * from mytable; return 1 select * from mytable;select * from mytable2; return 2 -BEGIN GEEK CODE BLOCK- Version: 3.12 GSC d- s:++ a- C++ UL/B+++$ !P L+++ E--- W+ N o-- K? w++ O? M- V- PS PE++(-) Y+ PGP-@ t 5 X R$ tv- b+++ DI++ D++ G++@ e h*--- r-- z? -END GEEK CODE BLOCK-- __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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
Re: [ADMIN] [GENERAL] pg_buffercache view
In response to Sorin N. Ciolofan [EMAIL PROTECTED]: I don't know the algorithm on which Postgre uses the shared buffers but I'd like to find the principles behind it. Let's assume the following scenario: I've set shared_buffers=3000 At the starting of Postgres there are 115 buffers used by database A After the execution of some processing caused by a java methodA1() invocation, 2850 buffers are used by A. What happens next if these 2850 buffers remains used even if the methodA1() finished its execution? They'll be reused the next time a query needs the same data, or discarded when the buffer space is needed for something else. Suppose that now a methodA2() invocation occurs and this method works with database A, too. Will be the 2850 buffers reused yes or will postgre throw an out of shared memory exception? no What happens if a methodB() invocation occurs, assuming that this method tries to work with database B? Buffers will be allocated/reallocate/reused as best fits the usage pattern of the server. How Postgres decides the allocation of shared_buffers? They're buffers. They follow the design of just about any other type of buffer: http://foldoc.org/index.cgi?query=bufferaction=Search Buffers are used to decouple processes so that the reader and writer may operate at different speeds or on different sized blocks of data. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran Sent: Thursday, April 26, 2007 3:32 PM To: Sorin N. Ciolofan Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [ADMIN] [GENERAL] pg_buffercache view In response to Sorin N. Ciolofan [EMAIL PROTECTED]: Hello! Do you know which could be the reasons that could conduce an application to not release the shared buffers, even after the application was shut down? I noticed that only if a pg_ctl restart command is issued some of the buffers are set free. The reason would be by design. If the server flushes its cache every time the application restarts, the cache isn't going to be very effective. If PostgreSQL is using more shared buffers than you're comfortable with, reduce the shared_buffers setting in the config. That will allow the OS to decide how to use the memory instead. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Feature request - have postgresql log warning when new sub-release comes out.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/25/07 21:52, Bill Moran wrote: [snip] If you switch to FreeBSD, you can easily have this done automatically with existing tools. ... Actually, I've a feeling that it would be trivial to do with just about any existing packaging system ... Or Debian, the Universal Operating System. And if you don't want to move up to a good OS, you could always parse http://www.postgresql.org/versions.xml for the exact information you need. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGMKk1S9HxQb37XmcRAjZAAKCsgXoDofYQJGixA1vV0/IUr0tPjACeJeWR ZbLeGYpEwiwEZ7Q1ELrqOuU= =SM1D -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Schema as versioning strategy
At 9:23 AM +0100 4/26/07, Richard Huxton wrote: Jonathan Vanasco wrote: On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote: Owen Hartnett wrote: I want to freeze a snapshot of the database every year (think of end of year tax records). However, I want this frozen version (and all the previous frozen versions) available to the database user as read-only. My thinking is to copy the entire public schema (which is where all the current data lives) into a new schema, named 2007 (2008, etc.) Sounds perfectly reasonable. You could either do it as a series of: CREATE TABLE archive2007.foo AS SELECT * FROM public.foo; or do a pg_dump of schema public, tweak the file to change the schema names and restore it. the create table method won't copy the constraints + fkeys . Shouldn't matter for an archive though, since you'd not want anyone to have permissions. Still, pg_dump is my preference. Apart from anything else, you can keep a copy of the dump around too. Thanks to everyone for all the replies. You've been most helpful. It looks like pg_dump is the way to go, though I'll have to think about it because I'm ultimately looking for a mechanical process that will automatically tweak the schema names. I don't want to have to visit clients every year to archive their data. Since the pg_dump file might change, my program may have to be version dependent. -Owen ---(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] Feature request - have postgresql log warning when new sub-release comes out.
Actually, I've a feeling that it would be trivial to do with just about any existing packaging system ... Yes pretty much every version of Linux, and FreeBSD, heck even Solaris if you are willing to run 8.1. J -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] HP/Pgsql/DBD::Pg issue
After a reboot (and usually after an OS patch) on our HP-UX 11.23 64-bit Itanium DB servers, our libpq/DBD::Pg libraries cease to work. Instead, they give the standard message you get when the DB cluster is not running. But we *know* it is running and all access paths are working. We have found a workaround by switching from 64-bit perl to 32-bit perl, build a 32-bit pgsql, and rebuild the perl DBD module using 32-bit perl and linking with the 32-bit pgsql. But the fact we're having to do that is a problem for us. I don't understand this problem and am at a loss as to where to look. Any ideas? TIA. Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] HP/Pgsql/DBD::Pg issue
On Thursday 26 April 2007 8:50 am, Ed L. wrote: After a reboot (and usually after an OS patch) on our HP-UX 11.23 64-bit Itanium DB servers, our libpq/DBD::Pg libraries cease to work. Instead, they give the standard message you get when the DB cluster is not running. But we *know* it is running and all access paths are working. We have found a workaround by switching from 64-bit perl to 32-bit perl, build a 32-bit pgsql, and rebuild the perl DBD module using 32-bit perl and linking with the 32-bit pgsql. But the fact we're having to do that is a problem for us. I don't understand this problem and am at a loss as to where to look. Any ideas? I should add that it is only these client apps that are running on the DB server itself. DBD apps connecting remotely don't have any problems. TIA. Ed ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tsearch2 punctuation question
On Thu, 26 Apr 2007, John DeSoi wrote:
For example:
select to_tsvector('cat,dog apple/orange');
to_tsvector
--
'cat':1 'dog':2 'apple/orange':3
(1 row)
Is there a setting that allows me to specify that strings containing the '/'
should be parsed into separate words? As is, I can't find 'apple' or
'orange'.
There is no such settings.
You can write your parser or dictionary for 'file' token type. We have
howto, see
http://mira.sai.msu.su/~megera/pgsql/ftsdoc/appendixes.html
If you want simple parser, probable better to write one. Probably, the
simple way is to write dictionary, which will return
{apple/orange, apple,orange}.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] a math question
On 4/26/2007, Chris [EMAIL PROTECTED] wrote: tom wrote: In pgsql I have to modify this a bit with 'cast (s_msgs as double precision)' or 'cast(s_msgs as real)' in order to get floating point math. ( cast(s_msgs as double precision)/S_msgs) and so on... Question: Is there a better way to get floating point math out of a set of integers? Nope. The way they treat math isn't new. cast one as real and the rest will follow. Any idea if it's going to be better or even something that can realistically be benchmarked? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] HP/Pgsql/DBD::Pg issue
Ed L. [EMAIL PROTECTED] writes: After a reboot (and usually after an OS patch) on our HP-UX 11.23 64-bit Itanium DB servers, our libpq/DBD::Pg libraries cease to work. Instead, they give the standard message you get when the DB cluster is not running. Try ktrace'ing the client to see what it's doing at the kernel-call level. (I think HPUX's equivalent is just called trace btw.) 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] tsearch2 punctuation question
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Is there a setting that allows me to specify that strings containing
the '/' should be parsed into separate words? As is, I can't find
'apple' or 'orange'.
No setting, I think you would have to mess with tsearch2 dictionaries. A
far easier approach is to have your application simply split the words
apart, or even write a wrapper function to do it for you within Postgres, e.g.
CREATE OR REPLACE FUNCTION wordsplit(text) RETURNS text LANGUAGE plperl
AS $_$
my $string = shift;
$string =~ s/\W/ /g;
return $string;
$_$;
SELECT to_tsvector(wordsplit('cat,dog apple/orange'));
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200704261140
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFGMMikvJuQZxSWSsgRAwq4AKDJv4D6UDKZngU2vZt+cPgr6gGsnwCgmJET
arG3n5+2pXxR+wedZ2LjZYU=
=BPs4
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Upgrade Process
Unlike my previous upgrade from the 7.4.x series to the 8.1.x series, I now have all data in /var/lib/pgsql/data. Currently running 8.1.4 on my Slackware system and want to upgrade to 8.2.4, so I'm checking my procedure before inadvertently creating major problems for myself. What I believe should work -- and I'd like conformation or corrections, please -- is the following: 1) Run 'pg_dumpall pg8.1.4.sql' as user postgres. 2) Stop the running postmaster as root. 3) Upgrade the Slackware package to 8.2.4 as root. 4) Restart the postmaster as root. 5) Run 'psql -f pg8.1.4.sql postgres' as user postgres. Have I missed a critical step? The upgrade will replace the existing files with the new ones in the same directories. TIA, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] Feature request - have postgresql log warning when new sub-release comes out.
On Thursday 26. April 2007 17:10, Joshua D. Drake wrote: Actually, I've a feeling that it would be trivial to do with just about any existing packaging system ... Yes pretty much every version of Linux, and FreeBSD, heck even Solaris if you are willing to run 8.1. Gentoo is still on version 8.1.8, though, and even that is soft-masked (stable is at 8.0.12). Seems like a problem with getting 8.2.x to build on this platform: http://forums.gentoo.org/viewtopic-t-534835-highlight-postgresql.html -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Fw: PostgreSQL Performance Tuning
Hello! I would do the following (in that order): 1.) Check for a performant application logic and application design (e.g. degree of granularity of the Java Hibernate Mapping, are there some object iterators with hundreds of objects, etc.) 2.) Check the hibernate generated queries and whether the query is suitable or not. Also do a explain query do see the query plan. Sometimes a manually generated is much more efficient than hibernate ones. 3.) Optimize the database e.g. postgresql. Ciao, Gerhard -- http://www.wiesinger.com/ On Thu, 26 Apr 2007, Shohab Abdullah wrote: Dear, We are facing performance tuning problem while using PostgreSQL Database over the network on a linux OS. Our Database consists of more than 500 tables with an average of 10K records per table with an average of 20 users accessing the database simultaneously over the network. Each table has indexes and we are querying the database using Hibernate. The biggest problem is while insertion, updating and fetching of records, ie the database performance is very slow. It take a long time to respond in the above scenario. Please provide me with the tuning of the database. I am attaching my postgresql.conf file for the reference of our current configuration Please replay me ASAP Regards, Shohab Abdullah Software Engineer, Manufacturing SBU-POWAI Larsen and Toubro Infotech Ltd.| 4th floor, LT Technology Centre, Saki Vihar Road, Powai, Mumbai-400072 (: +91-22-67767366 | (: +91-9870247322 Visit us at : http://www.lntinfotech.com ÿÿI cannot predict future, I cannot change past, I have just the present moment, I must treat it as my last The information contained in this email has been classified: [ X] LT Infotech General Business [] LT Infotech Internal Use Only [] LT Infotech Confidential [] LT Infotech Proprietary This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. __ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] DIfferent plans for explicit versus implicit join using link table
Hi - I have a table of words and a table linking words in various ways: create table allWords ( wordIDserial PRIMARY KEY, word text ); create unique index ix_allwords_word ON allwords (word); create table allWordRelations ( word1ID integer references allWords, word2ID integer references allWords, pos1 integer references posTypes, pos2 integer references posTypes, relID integer references allRelationTypes, confidencefloat, primary key (word1ID, word2ID, pos1, pos2, relID) ); create index ix_allWordRelations_word1_pos1 on allWordRelations (word1ID, pos1); create index ix_allWordRelations_word2_pos2 on allWordRelations (word2ID, pos2); I have two queries for looking up related words which I think should be equivalent, but 7.4.8 comes up with very different plans. The first query joins the word table to itself explicitly via the relations table - this is very fast. The second query uses an IN against the link table in the where clause, and is very slow. I'm sure I can affect this by adding indexes, but I'm mainly trying to understand what difference the planner is seeing. EXPLAIN ANALYZE output is below - can anyone explain? Are my two queries subtly different in terms of NULLs, or something like that? Thanks. - John Burger MITRE explain analyze select w2.word from allwords w1 join allwordrelations as r on (w1.wordid = r.word1id) join allwords w2 on (w2.wordid = r.word2id) where w1.word = 'dogging'; QUERY PLAN Nested Loop (cost=0.00..579.05 rows=81 width=15) (actual time=0.607..30.509 rows=59 loops=1) - Nested Loop (cost=0.00..333.94 rows=81 width=4) (actual time=0.564..29.032 rows=59 loops=1) - Index Scan using ix_allwords_word on allwords w1 (cost=0.00..3.49 rows=1 width=4) (actual time=0.326..0.329 rows=1 loops=1) Index Cond: (word = 'dogging'::text) - Index Scan using ix_allwordrelations_word1_pos1 on allwordrelations r (cost=0.00..329.36 rows=87 width=8) (actual time=0.220..28.564 rows=59 loops=1) Index Cond: (outer.wordid = r.word1id) - Index Scan using allwords_pkey on allwords w2 (cost=0.00..3.01 rows=1 width=19) (actual time=0.018..0.020 rows=1 loops=59) Index Cond: (w2.wordid = outer.word2id) Total runtime: 30.713 ms explain analyze select w2.word from allwords w1, allwords w2 where (w1.wordid, w2.wordid) in (select word1id, word2id from allwordrelations ) and w1.word = 'dogging'; QUERY PLAN - Nested Loop (cost=760422.86..817628.29 rows=1 width=15) (actual time=99277.403..111291.862 rows=59 loops=1) - Hash Join (cost=760422.86..817625.27 rows=1 width=4) (actual time=99277.110..111270.093 rows=59 loops=1) Hash Cond: (outer.word1id = inner.wordid) - Unique (cost=760419.36..794740.32 rows=4576128 width=8) (actual time=96713.791..107843.446 rows=4302242 loops=1) - Sort (cost=760419.36..771859.68 rows=4576128 width=8) (actual time=96713.785..102973.088 rows=4576035 loops=1) Sort Key: allwordrelations.word1id, allwordrelations.word2id - Seq Scan on allwordrelations (cost=0.00..79409.28 rows=4576128 width=8) (actual time=0.008..8668.255 rows=4576035 loops=1) - Hash (cost=3.49..3.49 rows=1 width=4) (actual time=0.078..0.078 rows=0 loops=1) - Index Scan using ix_allwords_word on allwords w1 (cost=0.00..3.49 rows=1 width=4) (actual time=0.067..0.070 rows=1 loops=1) Index Cond: (word = 'dogging'::text) - Index Scan using allwords_pkey on allwords w2 (cost=0.00..3.01 rows=1 width=19) (actual time=0.360..0.363 rows=1 loops=59) Index Cond: (w2.wordid = outer.word2id) Total runtime: 111292.449 ms ---(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] Feature request - have postgresql log warning when new sub-release comes out.
Leif B. Kristensen wrote: On Thursday 26. April 2007 17:10, Joshua D. Drake wrote: Actually, I've a feeling that it would be trivial to do with just about any existing packaging system ... Yes pretty much every version of Linux, and FreeBSD, heck even Solaris if you are willing to run 8.1. Gentoo is still on version 8.1.8, though, and even that is soft-masked (stable is at 8.0.12). Seems like a problem with getting 8.2.x to build on this platform: http://forums.gentoo.org/viewtopic-t-534835-highlight-postgresql.html I run 8.2.x on a Gentoo/x86_64 development box (just did the upgrade to 8.2.4 yesterday) using the postgresql-experimental overlay (via layman) and have run into no problems. Everything has compiled, installed/upgraded and been run with no hiccups along the way, nor any hacky workarounds. The 8.2 series isn't in the main portage tree yet because, as I understand it (and I could certainly be mistaken), the contributors maintaining the ebuilds are reworking the slotting setup as well as cleaning up the distinctions between server/library/client-only installs. Granted, I'm not advising a mission-critical server that happens to be running Gentoo use a portage overlay explicitly marked experimental for its RDBMS package management -- just pointing out that there is a pretty straight-forward way to get the 8.2 series through portage if you're willing to use an overlay for it. -Jon -- Senior Systems Developer Media Matters for America http://mediamatters.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] unique constraint on 2 columns
On Fri, 2007-04-20 at 15:52, Jonathan Vanasco wrote: I need a certain unique constraint in pg that i can't figure out. Given: create table test_a ( id serial , name_1 varchar(32) , name_2 varchar(32) ); I need name_1 and name_2 to both be unique so that: name_1 never appears in name_1 or name_2 name_2 never appears in name_2 or name_1 After reading the responses, and pretty much agreeing that you should probably redesign your table, I can think of a fairly good performing yet simple solution. create a new table, say test_names (name varchar(32) primary key); Then you can FK name_1 and name_2 to test_names(name), although I'm not sure that's stricly necessary for this exercise, it just sorta of feels right. Then create a trigger that that will insert / delete the matching entries in test_names(name) each time you insert / update / delete from test_a with both name_1 and name_2, and if an insert to test_names fails, so that if the insert to test_a fails as well. Seems like a giant hackish kluge, but it has the advantage of working with your current schema and requiring no code changes. But keep in mind, we have to live tomorrow with the hackish kludges we make today. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Business days
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
- -- I need to get the a total number of business days (from monday to
- -- friday) between two dates.
- -- Someone can help me please.
A simplistic approach that counts a business day as being Monday
through Friday would be something like this:
CREATE OR REPLACE FUNCTION bizdays(date,date)
RETURNS BIGINT
LANGUAGE SQL AS
$_$
SELECT count(*) FROM
(SELECT extract('dow' FROM $1+x) AS dow
FROM generate_series(0,$2-$1) x) AS foo
WHERE dow BETWEEN 1 AND 5;
$_$;
CREATE OR REPLACE FUNCTION bizdays(text,text)
RETURNS BIGINT LANGUAGE SQL AS
$_$
SELECT bizdays($1::date,$2::date);
$_$;
SELECT bizdays('20070401','20070407');
However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up. A possible solution is to write a plperlu function that
makes a call to Date::Manip, which can tell you the number of
business days between two date while excluding holidays, and which
allows you to specify exactly which days are considered a holiday.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200704261426
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFGMO9ivJuQZxSWSsgRA8HXAKDSY7vylo/EqQ+fjjwvlrJDdg/S2QCfcaM9
OKi3YW1IWOAc0Nfi9xBjuTc=
=aIqg
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Re: Feature request - have postgresql log warning when new sub-release comes out.
Joshua D. Drake wrote: Ron Mayer wrote: How about if PostgreSQL periodically check for updates on the internet and log WARNINGs as soon as it sees it's not running the newest minor version for a branch. ... uhmmm gah, errm no... e why? :) Mostly because it seems like a near FAQ here that someone posts questions about people running very old postgresqls where the answers are that was fixed in the latest minor version. Regarding people saying that their OS package manager can do this for them - I note that the people who have this problem the worst seem to be the people running older postgresqls, and your OS vendor may not be keeping the major version number of their postgresql the same as yours. For example, apt-cache search here isn't showing me 8.0 (though it does show 7.4, 8.1, and 8.2). I could see a contrib module that was an agent that did that but not as part of actual core. I was thinking it would protect the more ignorant users who didn't even know about contrib. I imagine anyone who did know enough to install a contrib module would also know how to write such a script without it. No big deal, though - if others don't think there's a need, then I'm not going to push for it. ---(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] Business days
- -- I need to get the a total number of business days (from monday to - -- friday) between two dates. - -- Someone can help me please. A simplistic approach that counts a business day as being Monday through Friday would be something like this: However, you quickly run into the problem of holidays. While you could construct a helper table listing all the holidays, ones that don't fall on the same day every year (e.g. Easter) will trip you up. Er, isn't Easter usually on a Sunday? Anyway, I also found this, the first hit if you google sql holidays: http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html The big ugly union might need to be munged a bit, but most of the non- weekend US holidays seem to be there. - John D. Burger MITRE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Feature request - have postgresql log warning when new sub-release comes out.
On 4/25/07, Ron Mayer [EMAIL PROTECTED] wrote: Carlos Moreno wrote: Tom Lane wrote: Well, if you can't update major versions that's understandable; that's why we're still maintaining the old branches. But there is no excuse for not running a reasonably recent sub-release within your branch. Slammer..bug in Microsucks SQL Serverfix...had been available Feature request. How about if PostgreSQL periodically check for updates on the internet and log WARNINGs as soon as it sees it's not running the newest minor version for a branch. Ideally, it could be set so the time-of-day's configurable to avoid setting off pagers in the middle of the night. I might not lurk on the mailinglists enough to notice every dot release; but I sure would notice if pagers went off with warnings in the log files from production servers. Is that a possible TODO? (The thread started on the performance mailing lists but I moved it to general since it drifted off topic). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings what about the distros that do backporting for the bug fixes ? those would be saying you are with a outdated PostgreSQL version -- Leonel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where to find kind code for STATISTIC_KIND GEOMETRY?
DEBUG_GEOMETRY_STATS is specific to POSTGIS Geographic Implementation System package..Have you tried their discussion group at [EMAIL PROTECTED] M-- This email message and any files transmitted with it contain confidential information intended only for the person(s) to whom this email message is addressed. If you have received this email message in error, please notify the sender immediately by telephone or email and destroy the original message without making a copy. Thank you. - Original Message - From: [EMAIL PROTECTED] To: postgresql-general pgsql-general@postgresql.org Sent: Wednesday, April 25, 2007 2:02 PM Subject: [GENERAL] Where to find kind code for STATISTIC_KIND GEOMETRY? Hi All, I am looking for the kind code for STATISTIC_KIND GEOMETRY to calculate the selectivity/stats for geometry type (st_geometry). According to PostgreSQL (pg_statistic.h) * The present allocation of kind codes is: * * 1-99: reserved for assignment by the core PostgreSQL project * (values in this range will be documented in this file) * 100-199: reserved for assignment by the PostGIS project * (values to be documented in PostGIS documentation) * 200-: reserved for future public assignments * * For private use you may choose a kind code at random in the range * 1-3. However, for code that is to be widely disseminated it is * better to obtain a publicly defined kind code by request from the * PostgreSQL Global Development Group. */ Wondering where I can find the kind codes for this new st_geometry type? Thanks. Ale Raza ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PostgreSQL Performance Tuning
Dear, We are facing performance tuning problem while using PostgreSQL Database over the network on a linux OS. Our Database consists of more than 500 tables with an average of 10K records per table with an average of 20 users accessing the database simultaneously over the network. Each table has indexes and we are querying the database using Hibernate. The biggest problem is while insertion, updating and fetching of records, ie the database performance is very slow. It take a long time to respond in the above scenario. Please provide me with the tuning of the database. I am attaching my postgresql.conf file for the reference of our current configuration Please replay me ASAP Regards, Shohab Abdullah Software Engineer, Manufacturing SBU-POWAI Larsen and Toubro Infotech Ltd.| 4th floor, LT Technology Centre, Saki Vihar Road, Powai, Mumbai-400072 (: +91-22-67767366 | (: +91-9870247322 Visit us at : http://www.lntinfotech.com ”I cannot predict future, I cannot change past, I have just the present moment, I must treat it as my last The information contained in this email has been classified: [ X] LT Infotech General Business [] LT Infotech Internal Use Only [] LT Infotech Confidential [] LT Infotech Proprietary This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. __ jpganGLWTYKJS.jpg Description: JPEG image postgresql.conf Description: Binary data ---(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] Schema as versioning strategy
On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote: Owen Hartnett wrote: I want to freeze a snapshot of the database every year (think of end of year tax records). However, I want this frozen version (and all the previous frozen versions) available to the database user as read-only. My thinking is to copy the entire public schema (which is where all the current data lives) into a new schema, named 2007 (2008, etc.) Sounds perfectly reasonable. You could either do it as a series of: CREATE TABLE archive2007.foo AS SELECT * FROM public.foo; or do a pg_dump of schema public, tweak the file to change the schema names and restore it. the create table method won't copy the constraints + fkeys . i think you're best off with a pgdump ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] WARNING Bison install not found, or unsupported Bison version.
I'm trying to build PostgreSQL 8.2 on Windows XP PRO. I've already downloaded and configured all the dependencies, including bison and flex. However, when I try to make it I receive the following error: WARNING Bison install not found, or unsupported Bison version. Attempting to build without. even though bison and flex are installed! (the latest version from gnuwin32). I'm trying to compile it with VS2005 (src/tools/msvc) Any feedback would be much appreaciated, Marcelo.
Re: [GENERAL] Re: Feature request - have postgresql log warning when new sub-release comes out.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/26/07 13:38, Ron Mayer wrote: Joshua D. Drake wrote: Ron Mayer wrote: How about if PostgreSQL periodically check for updates on the internet and log WARNINGs as soon as it sees it's not running the newest minor version for a branch. ... uhmmm gah, errm no... e why? :) Mostly because it seems like a near FAQ here that someone posts questions about people running very old postgresqls where the answers are that was fixed in the latest minor version. Regarding people saying that their OS package manager can do this for them - I note that the people who have this problem the worst seem to be the people running older postgresqls, and your OS vendor may not be keeping the major version number of their postgresql the same as yours. For example, apt-cache search here isn't showing me 8.0 (though it does show 7.4, 8.1, and 8.2). For example: Debian. It's Stable releases only get *security* patches, nothing related to features or performance. I could see a contrib module that was an agent that did that but not as part of actual core. I was thinking it would protect the more ignorant users who didn't even know about contrib. I imagine anyone who did know enough to install a contrib module would also know how to write such a script without it. No big deal, though - if others don't think there's a need, then I'm not going to push for it. A *tiny* Perl/Python script to parse http://www.postgresql.org/versions.xml is all you need. Putting it in cron and emailing when someone a version changes seems useful. Ok, it's official: you're elected to implement it! - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGMQJ/S9HxQb37XmcRAgUEAKDWKzM8scO7Mc8uB26iqIo8WnJGmwCg6e4w vRuaSXH0sMhtnNZbYsuDKmc= =wGYf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Upgrade Process
On Thursday 26 April 2007 9:47 am, Rich Shepard wrote: Unlike my previous upgrade from the 7.4.x series to the 8.1.x series, I now have all data in /var/lib/pgsql/data. Currently running 8.1.4 on my Slackware system and want to upgrade to 8.2.4, so I'm checking my procedure before inadvertently creating major problems for myself. What I believe should work -- and I'd like conformation or corrections, please -- is the following: 1) Run 'pg_dumpall pg8.1.4.sql' as user postgres. Generally it is a better idea to dump the old version with the new versions pg_dump,pg_dumpall commands. The new versions know more about the old versions of the database than the other way around. 2) Stop the running postmaster as root. 3) Upgrade the Slackware package to 8.2.4 as root. 4) Restart the postmaster as root. 5) Run 'psql -f pg8.1.4.sql postgres' as user postgres. Have I missed a critical step? The upgrade will replace the existing files with the new ones in the same directories. TIA, Rich I generally copy the old version(while it is shutdown) to another directory and then install the new version. I modify the postgresql.conf in the directory containing the old version so that Postgres listens on a different port and start it up. I can then use the pg_dump(all) commands from the new version to pull from the old version. The catch is that you need enough room for both copies of the database. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Compiling PostgreSQL 8.2 on Windows using msvc2k5
Hello! I'd like to learn more about PostgreSQL (8.x) internal architecture so as to build C extensions and Stored Procedures in C. I think that I nice way to start is trying to compile PostgreSQL from the source. I'm on Windows XP PRO. I've found this article: http://developer.postgresql.org/pgdocs/postgres/x19444.html. I've downloaded all the dependencies and have put them on C:\prog\pgsql\depend (I discovered the default path was this the first time the compiler complained about missing libs. Didn't find a way to change this path though.) I have gone to /src/tools/msvc and ran build. However, I still receive tons of errors and warnings. My main doubt here is about the dependencies. I'm not used to the process of compiling third party source code. I've checked out the cvs to get the source rather than getting the source somewhere else. Does this cvs release need all the dependencies listed on the article above or there is something that is optional? Is there an easier way to get all these dependencies? I didn't find instructions on where to put them (I discovered by accident that the compiler expected them to be on C:\prog\pgsql\depend). Note: I had to modify the pgbison.bat (commented the part where it checks for the version 1.875) so it would run bison over the .y files.). If anyone could put me on the right direction I would be grateful!
Re: [GENERAL] Compiling PostgreSQL 8.2 on Windows using msvc2k5
Marcelo de Moraes Serpa wrote: Hello! I'd like to learn more about PostgreSQL (8.x) internal architecture so as to build C extensions and Stored Procedures in C. I think that I nice way to start is trying to compile PostgreSQL from the source. I'm on Windows XP PRO. I've found this article: http://developer.postgresql.org/pgdocs/postgres/x19444.html. I've downloaded all the dependencies and have put them on C:\prog\pgsql\depend (I discovered the default path was this the first time the compiler complained about missing libs. Didn't find a way to change this path though.) You can choose the path for the dependencies freely. Per the documentation you refer to: Before you build, edit the file config.pl to reflect the configuration options you want set, including the paths to libraries used.. So just change the path if you want them to live somewhere else. I have gone to /src/tools/msvc and ran build. However, I still receive tons of errors and warnings. My main doubt here is about the dependencies. I'm not used to the process of compiling third party source code. I've checked out the cvs to get the source rather than getting the source somewhere else. Does this cvs release need all the dependencies listed on the article above or there is something that is optional? The list states which are optional. Is there an easier way to get all these dependencies? I didn't find instructions on where to put them The article you mention above has information about where you can download them. You can put them anywhere you want. (I discovered by accident that the compiler expected them to be on C:\prog\pgsql\depend). That's just the default, you can change it in config.pl. Note: I had to modify the pgbison.bat (commented the part where it checks for the version 1.875) so it would run bison over the .y files.). There is a reason that check is there, and that is that it *will* break with the versions of bison that don't pass that check. So put the check back in and download a supported version of bison (according to the article you link to, you need either 1.875 or 2.2-or-later, I'm assuming you have 2.0 or 2.1 since it complains) //Magnus ---(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] DIfferent plans for explicit versus implicit join using link table
John D. Burger [EMAIL PROTECTED] writes: I have two queries for looking up related words which I think should be equivalent, but 7.4.8 comes up with very different plans. They're not at all equivalent: explain analyze select w2.word from allwords w1 join allwordrelations as r on (w1.wordid = r.word1id) join allwords w2 on (w2.wordid = r.word2id) where w1.word = 'dogging'; explain analyze select w2.word from allwords w1, allwords w2 where (w1.wordid, w2.wordid) in (select word1id, word2id from allwordrelations ) and w1.word = 'dogging'; If there are duplicate word1id,word2id entries in allwordrelations, the first query will produce duplicate outputs; the second will not. If there were a unique constraint on (word1id, word2id), in theory the planner could prove that the IN form could be simplified to a plain join, but there is no such logic in HEAD let alone 7.4, and in any case you've not got such a constraint. The plan that gets chosen is to forcibly unique-ify the (word1id, word2id) data (via a sort | uniq-like pipeline) and then do a normal join with that. Which is expensive because allwordrelations is big. But the alternative is probably even worse: without that allwordrelations has to be joined to w1 and w2 simultaneously, meaning that the unconstrained cartesian product of w1 and w2 has to be formed first. 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] Business days
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 However, you quickly run into the problem of holidays. While you could construct a helper table listing all the holidays, ones that don't fall on the same day every year (e.g. Easter) will trip you up. Er, isn't Easter usually on a Sunday? I meant the same numerical date, e.g. Christmas is always December 25th, and so is a little easier programatically than the rules for Easter. If you meant that Sunday is never a business day, then yes, it was a bad example. :) Anyway, I also found this, the first hit if you google sql holidays: http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html The big ugly union might need to be munged a bit, but most of the non- weekend US holidays seem to be there. Sure, that's an alternative, but it seems a bit too much reinventing an already existing wheel. I was amused to see the script had the ill-fated Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that describes the rules for each holiday, and then a function that reads it on the fly. Perhaps a project for another day... - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200704261706 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD4DBQFGMRUnvJuQZxSWSsgRAwn3AJ9o1CCb2x3Asn1U70xyphetZ6a2XgCY5fuG coAVQiUyFWqKyJWCpJBanA== =gmZi -END PGP SIGNATURE- ---(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] NFS vs. PostgreSQL on Solaris
I've got an interesting case study that I don't fully understand from a postgres perspective, and I'm hoping that someone in the community might help me understand what led to the outcome and whether it's easily prevented or not. The setup: PostgreSQL 8.2.3 on Solaris 10 x86 postgresql.conf: shared_buffers = 20 This had been a box that had been completely dedicated to postgres. A new project required that the box start to also share a directory over NFS. In preparation for this, nfsd had been running for quite some time, although today was the first day that production mount points on other boxes were added. The first external mount point was created this morning, and all seemed quiet. The second external mount point was then added this afternoon. That second mount point caused postgres great consternation. Within several few seconds of enabling the second external NFS mount point, postgres began reporting out of memory errors. An INSERT generated the following: TopMemoryContext: 118832 total in 10 blocks; 17512 free (24 chunks); 101320 used TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 used Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used MessageContext: 262144 total in 6 blocks; 75208 free (5 chunks); 186936 used smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416 used TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used PortalHeapMemory: 1024 total in 1 blocks; 400 free (0 chunks); 624 used ExecutorState: 147648 total in 7 blocks; 59816 free (11 chunks); 87832 used HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used HashBatchContext: 25174100 total in 13 blocks; 7754600 free (16 chunks); 17419500 used TupleSort: 4448280 total in 10 blocks; 1888 free (25 chunks); 4446392 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used AggContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used CacheMemoryContext: 659000 total in 19 blocks; 140248 free (1 chunks); 518752 us ed This was followed by a number of statements about individual relations and, eventually, this: MdSmgr: 8192 total in 1 blocks; 7120 free (0 chunks); 1072 used LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used 2007-04-26 14:47:42 CDT 15175 :LOG: could not fork new process for connection: Not enough space 2007-04-26 14:47:42 CDT 15175 :LOG: could not fork new process for connection: Not enough space 2007-04-26 14:47:42 CDT 15175 :LOG: could not fork new process for connection: Not enough space 2007-04-26 14:47:42 CDT 15175 :LOG: could not fork new process for connection: Not enough space 2007-04-26 14:47:42 CDT 15175 :LOG: could not fork new process for connection: Not enough space 2007-04-26 14:47:42 CDT 15175 :LOG: could not fork new process for connection: Not enough space Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used 2007-04-26 14:47:42 CDT 17018 10.0.4.168(46723):ERROR: out of memory 2007-04-26 14:47:42 CDT 17018 10.0.4.168(46723):DETAIL: Failed on request of size 257. This happened intermittently for the next several minutes, eventually building to FATAL: 2007-04-26 14:50:12 CDT 18421 10.0.4.168(44414):FATAL: out of memory 2007-04-26 14:50:12 CDT 18421 10.0.4.168(44414):DETAIL: Failed on request of size 1864. The FATAL error was preceded by a similar report to the original ERRORs. I don't know what distinguishes the ERROR out of memory from the FATAL version. Then, eventually, without any evidence of other things on
Re: [GENERAL] Upgrade Process
On Thu, 26 Apr 2007, Adrian Klaver wrote: Generally it is a better idea to dump the old version with the new versions pg_dump,pg_dumpall commands. The new versions know more about the old versions of the database than the other way around. Hi, Adrian! I wondered about this. I generally copy the old version (while it is shutdown) to another directory and then install the new version. I modify the postgresql.conf in the directory containing the old version so that Postgres listens on a different port and start it up. I can then use the pg_dump(all) commands from the new version to pull from the old version. The catch is that you need enough room for both copies of the database. OK. I'll do this. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] Processing a work queue
Anyone have any ideas on how to handle a work queue? I've been thinking about optimizing this process for quite a while. Basically, my queue table consists of a few-hundred-thousand records describing things to do. To pare things to the minimum, a queue record can be considered to have a status (available, assigned, completed), a priority, and a description-of-work. A process will grab an item from the queue, mark it as in-progress, process it, and, depending on success, update the item as completed or as available with an updated priority. There may be upwards of a thousand worker processes and the work of each process may be completed in anywhere from a few seconds to nearly an hour. I expect the system as a whole to be handling a few-dozen queue items per second. My original plan to fetch work was: begin; select item-id, item-info from the-queue where available order by priority limit 1 for update; update the-queue set status = 'assigned' where item-id = previously-selected-item-id; commit; This does not produce desirable results. In the case where requests for work overlap, the first query will complete. The second query will block until the first completes and then apparently re-evaluate the condition and toss the record thus returning zero-rows. Plan 1a: Check for tuples returned and re-run query if zero. This will go into an infinite loop whenever there is nothing in the queue and cause undesirable thrashing if there is too much contention. Plan 2: Lock the table, run the query/update, unlock the table. Functions fine but work halts when any operation interferes with obtaining the table-level lock; Plan 3: Same as plan 1 but use a higher limit, say 100, then just choose and update the first tuple. The second query will block till the first completes, and then return 99 records. If limit is set to the number of workers, every request should return some work to be done, if any is available. It's a kludge, but does anyone see any significant drawbacks? Plan 4: Add an intermediary dispatcher with which the workers will communicate via SOAP/XML-RPC/? But if dispatcher is allowed to run multiple processes we are back to needing to resolving database query issues. Plan 5: I could, potentially, reverse everything and have the workers announce availability and wait for the dispatcher to send work. Fixes the database issue but creates some others. So from the standpoint of the database query part, anyone have any ideas/suggestions on how to handle a work queue? Cheers, Steve ---(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] Processing a work queue
On 4/27/07, Steve Crawford [EMAIL PROTECTED] wrote: Anyone have any ideas on how to handle a work queue? I've been thinking about optimizing this process for quite a while. I have been using PostgreSQL for the exact same thing, except I have not yet reached the stage where I need to process queue items in parallel. :) Anyway, this question has been covered several times, and I believe this post by Tom Lane delineates the canonical recipe: http://archives.postgresql.org/pgsql-general/2003-05/msg00351.php Alexander. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] NFS vs. PostgreSQL on Solaris
Thomas F. O'Connell [EMAIL PROTECTED] writes: 1. What aspect of postgres' memory usage would create an out of memory condition? I'm guessing you ran the box out of swap space --- look into what other processes got started as a result of adding the NFS mount, and how much memory they wanted to eat. 3. What would cause postgres to die from a signal 11? I've also got a core file if that's necessary for further forensics. Send gdb backtrace, please. 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] [NOVICE] column and table names
Try this: select column_name from information_schema.columns where table_name = 'tablename' order by ordinal_position; On Apr 26, 2007, at 6:11 PM, stephen wrote: Thanks, that worked ;-( but is there any way of ordering the column names by their natural order in the table - same as the order in the view? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Processing a work queue
On 4/27/07, Steve Crawford [EMAIL PROTECTED] wrote:
Anyone have any ideas on how to handle a work queue? I've been thinking
about optimizing this process for quite a while.
Basically, my queue table consists of a few-hundred-thousand records
describing things to do. To pare things to the minimum, a queue record
can be considered to have a status (available, assigned, completed), a
priority, and a description-of-work.
A process will grab an item from the queue, mark it as in-progress,
process it, and, depending on success, update the item as completed or
as available with an updated priority. There may be upwards of a
thousand worker processes and the work of each process may be
completed in anywhere from a few seconds to nearly an hour. I expect the
system as a whole to be handling a few-dozen queue items per second.
My original plan to fetch work was:
begin;
select item-id, item-info
from the-queue
where available
order by priority
limit 1
for update;
update the-queue
set status = 'assigned'
where item-id = previously-selected-item-id;
commit;
how about this:
create table job(job_id int, [...])
create sequence worker;
your worker threads can do something like:
select * from job join
(
select nextval('worker') as requested_job
) on job_id = requested_job
and
(
(select (w.last_value, w.is_called) (j.last_value, j.is_called)
from worker w, job_id_seq j)
)
and then sleep appropriately if there is nothing to do. Of course, if
the job fails you have to put it back on the queue. No locking
required! This relies on false being true...safer to break out to a
case stmt but im just trying to be clever :-)
This has couple of advantages but is also pretty fragile. I'm not
necessarily suggesting it but it was a fun way to think about the
problem.
merlin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] dropping role w/dependent objects
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Ed L. wrote: On Wednesday April 4 2007 5:37 pm, Bruce Momjian wrote: Perhaps this could be added to the TODO list? I won't get to it anytime soon. Yes. What should the TODO text be? See if the attached patch is acceptable. If not, perhaps the TODO text should be: Enable end user to identify dependent objects when the following error is encountered: ERROR: role mygroup cannot be dropped because some objects depend on it DETAIL: 227 objects in this database [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Processing a work queue
On 4/27/07, Merlin Moncure [EMAIL PROTECTED] wrote:
how about this:
create table job(job_id int, [...])
create sequence worker;
couple typos: here is an example that works:
create table job(job_id serial);
create sequence worker;
-- get next available job
create function next_job() returns job as
$$
select job from job join
(
select nextval('worker') as requested_job
) q on job_id = requested_job
and
(
(select (w.last_value, w.is_called) (j.last_value, j.is_called)
from worker w, job_job_id_seq j)
);
$$ language sql;
select next_job();
again, remembering that sequences are not rolled back on transaction
failure, you have to think really carefully about failure conditions
before going with something like this.
merlin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] plperl functions not re-entrant?
Martijn van Oosterhout wrote:
On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote:
What I have noticed is that once the innermost instance exits, none of
the outer instances execute any further, suggesting that the plperl
routine is not re-entrant (if I am using that term correctly).
Doesn't sound right, do you have a test case?
I've finally gotten back around to this. Here is my test case. There
are three tables with one row in each.
Level_one: one row
Level_two: one row
Level_thr: one row
The idea is that an AFTER STATEMENT UPDATE trigger on Level_thr calls a
plperl routine. The plperl routine has a hardcoded 3-pass loop that
updates table Level_two.
The Level_Two table also has an AFTER STATEMENT UPDATE trigger that
calls the same plperl routine. The routine this time goes into its
3-pass loop and updates Level_one.
The result is that there should be 9 passes altogether, and the
accumulator in table level_one should have the value 9. Except it only
has the value 5, because the values from these two invocations of the
routine are leaking into each other.
Here is the code to reproduce:
/*
* This code builds the three tables, they are
* all the same and all simple
*/
create table level_one ( col1 char(5),total int);
create table level_two ( col1 char(5),total int);
create table level_thr ( col1 char(5),total int);
/*
* Here is the re-entrant code. For this example it
* is simplified and hard-coded.
*/
create or replace function ReEntrantTester() returns trigger as
$BODY$
if($_TD-{relname} eq'level_thr') {
$table_to_update = 'level_two' ;
}
else {
$table_to_update = 'level_one' ;
}
elog(NOTICE,We are in .$_TD-{relname}. and we'd hit
$table_to_update);
$increment =1;
for(my $i=1; $i = 3; $i++) {
elog(NOTICE,Updating $table_to_update, pass $i of 3, adding
$increment);
$qu=UPDATE $table_to_update SET total = total + $increment;
elog(NOTICE,$qu);
spi_exec_query($qu);
}
return;
$BODY$
language plperl SECURITY DEFINER;
/*
* Now create two statement level triggers on level 3
* and level 2 tables that each rolls up to the next
* higher level.
*/
CREATE TRIGGER level_thr_aft_stm
AFTER UPDATE ON level_thr
FOR EACH STATEMENT EXECUTE PROCEDURE ReEntrantTester();
CREATE TRIGGER level_two_aft_stm
AFTER UPDATE ON level_two
FOR EACH STATEMENT EXECUTE PROCEDURE ReEntrantTester();
/*
* This code clears the tables to get ready for a run
*/
delete from level_one;
insert into level_one (col1,total) values ('LEV-1',0);
delete from level_two;
insert into level_two (col1,total) values ('LEV-2',0);
delete from level_thr;
insert into level_thr (col1,total) values ('LEV-3',0);
/*
* Before running the test, make sure the value in
* the level_one table is zero:
*/
select * from level_one;
/*
* PULL THE TRIGGER: This is the code that shows if it
* works or not. If the perl routine is re-entrant, then
* the value in level_one will be 9. Else it will not.
*/
UPDATE level_thr SET total=99;
The update that I get is this:
NOTICE: We are in level_thr and we'd hit level_two
NOTICE: Updating level_two, pass 1 of 3, adding 1
NOTICE: UPDATE level_two SET total = total + 1
NOTICE: We are in level_two and we'd hit level_one
CONTEXT: SQL statement UPDATE level_two SET total = total + 1
NOTICE: Updating level_one, pass 1 of 3, adding 1
CONTEXT: SQL statement UPDATE level_two SET total = total + 1
NOTICE: UPDATE level_one SET total = total + 1
CONTEXT: SQL statement UPDATE level_one SET total = total + 1
NOTICE: Updating level_one, pass 2 of 3, adding 1
CONTEXT: SQL statement UPDATE level_one SET total = total + 1
NOTICE: UPDATE level_one SET total = total + 1
CONTEXT: SQL statement UPDATE level_one SET total = total + 1
NOTICE: Updating level_one, pass 3 of 3, adding 1
CONTEXT: SQL statement UPDATE level_one SET total = total + 1
NOTICE: UPDATE level_one SET total = total + 1
CONTEXT: SQL statement UPDATE level_one SET total = total + 1
NOTICE: Updating level_one, pass 2 of 3, adding 1
NOTICE: UPDATE level_one SET total = total + 1
NOTICE: Updating level_one, pass 3 of 3, adding 1
NOTICE: UPDATE level_one SET total = total + 1
Notice that the outermost loop does not finish correctly, it has somehow
changed its mind on what table to update, is not actually executing the
queries, but still has kept track of its iterations.
SELECT * FROM level_one gives:
LEV-1;5
--
Kenneth Downs
Secure Data Software, Inc.
www.secdat.comwww.andromeda-project.org
631-379-7200 Fax: 631-689-0527
[GENERAL] PostgreSQL upgrade server A - server B
Hello, Forgive me if this has been discussed before (or if it sounds absurd) Upgrading large postgres databases (100GB+) takes awfully long time when doing dump/restore. I was wondering if this process can be optimized by directly dumping to a new version of Pg database directly on another server without having to dump to the filesystem and then restore it. pg_dump on new server might look something like, pg_dump options -h old server -h new_server dbname or can it be used as-is by piping it? pg_dump options -h old server dbname | pg_restore Thanks!
Re: [GENERAL] PostgreSQL upgrade server A - server B
On Apr 26, 2007, at 20:09 , CAJ CAJ wrote: Upgrading large postgres databases (100GB+) takes awfully long time when doing dump/restore. I was wondering if this process can be optimized by directly dumping to a new version of Pg database directly on another server without having to dump to the filesystem and then restore it. From the fine documentation in the section entitled Migration Between Releases http://www.postgresql.org/docs/8.2/interactive/migration.html The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like pg_dumpall -p 5432 | psql -d postgres -p 6543 to transfer your data. Hope that helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL upgrade server A - server B
Upgrading large postgres databases (100GB+) takes awfully long time when doing dump/restore. I was wondering if this process can be optimized by directly dumping to a new version of Pg database directly on another server without having to dump to the filesystem and then restore it. From the fine documentation in the section entitled Migration Between Releases http://www.postgresql.org/docs/8.2/interactive/migration.html The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like pg_dumpall -p 5432 | psql -d postgres -p 6543 to transfer your data. Hope that helps. Gee thanks... I guess i didn't RTFM!
Re: [GENERAL] DIfferent plans for explicit versus implicit join using link table
Tom Lane replied: I have two queries for looking up related words which I think should be equivalent, but 7.4.8 comes up with very different plans. They're not at all equivalent: If there are duplicate word1id,word2id entries in allwordrelations, the first query will produce duplicate outputs; the second will not. Ah, that should have been my second guess - whenever I fail to get stuff like this, it's usually to do with either duplicates or NULLs. If there were a unique constraint on (word1id, word2id), in theory the planner could prove that the IN form could be simplified to a plain join, but there is no such logic in HEAD let alone 7.4, and in any case you've not got such a constraint. But such would reflect the reality of my data, so it should be there. The plan that gets chosen is to forcibly unique-ify the (word1id, word2id) data (via a sort | uniq-like pipeline) and then do a normal join with that. Which is expensive because allwordrelations is big. But the alternative is probably even worse: without that allwordrelations has to be joined to w1 and w2 simultaneously, meaning that the unconstrained cartesian product of w1 and w2 has to be formed first. Hmm, but wouldn't it at least filter one side per my where clause: w1.word = 'dogging'? Anyway, thanks, the incremental enlightenment continues. - John Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DIfferent plans for explicit versus implicit join using link table
John D. Burger [EMAIL PROTECTED] writes: Tom Lane replied: But the alternative is probably even worse: without that allwordrelations has to be joined to w1 and w2 simultaneously, meaning that the unconstrained cartesian product of w1 and w2 has to be formed first. Hmm, but wouldn't it at least filter one side per my where clause: w1.word = 'dogging'? Ah, right, it would do that --- but you still then have to join each of those rows to every row of w2 before you can do the IN check, and each of those IN checks would be an index probe into allwordrelations, which is not that cheap. (Or at least 7.4 doesn't think so --- it does not have any understanding about multiple index probes on the inside of a nestloop being cheaper than single probes due to caching of the upper index levels. You really ought to think about getting onto a newer version; 8.2 is quite a lot smarter than 7.4.) 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] Processing a work queue
Steve Crawford wrote: Anyone have any ideas on how to handle a work queue? I've been thinking about optimizing this process for quite a while. I use a variant of The Tom Lane Solution previously pointed to, your Plan 1 is very similar. This does not produce desirable results. In the case where requests for work overlap, the first query will complete. The second query will block until the first completes and then apparently re-evaluate the condition and toss the record thus returning zero-rows. I have no experience with this, but I think you can do SELECT FOR UPDATE NOWAIT to avoid the blocking. Plan 1a: Check for tuples returned and re-run query if zero. This will go into an infinite loop whenever there is nothing in the queue and cause undesirable thrashing if there is too much contention. So either sleep a bit, as in Tom's solution, or use NOTIFY/LISTEN, which is what I do. I have a trigger like this on my queue: create or replace function notify_new_work() returns trigger as ' BEGIN NOTIFY WORK; RETURN NULL; END; ' language 'plpgsql'; create trigger notify_new_work after insert on work_queue for each statement execute procedure notify_new_work(); My workers do LISTEN WORK after connecting, and then do a (UNIX) select on the connection socket when they get zero results from the (SQL) select. This puts them to sleep until the next NOTIFY fires. How to get the socket and do the (UNIX) select will depend on your client library and language. - John Burger MITRE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL upgrade server A - server Bx
see pg_migrator project which could help you. Oleg On Thu, 26 Apr 2007, CAJ CAJ wrote: Hello, Forgive me if this has been discussed before (or if it sounds absurd) Upgrading large postgres databases (100GB+) takes awfully long time when doing dump/restore. I was wondering if this process can be optimized by directly dumping to a new version of Pg database directly on another server without having to dump to the filesystem and then restore it. pg_dump on new server might look something like, pg_dump options -h old server -h new_server dbname or can it be used as-is by piping it? pg_dump options -h old server dbname | pg_restore Thanks! Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
