[GENERAL] Server installation problem using freebsd ports
Hi, This might be a very small problem but I am stuck. When I try to install postgresql server 8.3.3 on freebsd using ports, I am getting - postgresql-server-8.3.3 cannot install: unknown PostgreSQL version: 83 I was able to install client. This has something to do with pgsql_ver. What's wrong here? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] weird query runtime results...
On 2008-09-09 05:07, Kashmir wrote: querying data from august 1st - august 25: Total runtime: 26762.999 ms now querying data from august 1st - august 29: Total runtime: 20123.584 ms Any idea why these analysises look so different? the only query-difference is the 2nd timestamp value, it is a little higher in the 2nd query... Did you realize that this second query returns over 6 seconds faster than first? If you query a significant portion of data a table scan is faster than using indexes as a drive does not need to seek that much. You can control when it will switch to seq-scan tuning seq_page_cost/random_page_cost parameters in postgresql.conf. But test also other queries common for your usage. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3 on FreeBSD 6.3, sudden performance degradations
On 2008-09-08 11:46, Ivan Zolotukhin wrote: vmstat 5 procs memory page disk faults cpu r b w avmfre flt re pi po fr sr am0 insycs us sy id 28 77 0 2328792 793424 34813 0 0 0 4351 0 41 1913 21230 20337 14 86 0 I do not know FreeBSD but can you check what is this flt stat? Is this page fault? That would be a lot of page faults for 5 seconds, which could mean that this server is memory starved and is swapping a lot. Check for a process using insane amounts of memory. Maybe you have tuned Postgres too aggressive. Show us the output of free when there is a slowdown. Once more - I do not know FreeBSD and just guessing what this flt stat is. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 2:02 AM, Darren Weber [EMAIL PROTECTED] wrote: I'm new to using PostgreSQL on mac OS X. I used a binary installer for PostgreSQL 8.3 on mac OS X 10.5, which installs into /Library/PostgreSQL/[version]/ I'm building a lot of software that links to libpq and most of the builds fail or the run-time fails, because it cannot find the PostgreSQL libraries by default. It seems the dynamic link loader doesn't search this path by default to locate dynamic libraries, like libpq.5.dylib. Can you fix this issue for the binary installer? Hmm, it seems this is a side-effect of not rewriting the shared library paths at installation time. Because the library ID is just the filename, the linker doesn't write the full path to the binaries you compile. We changed from the old behaviour after it became apparent that the utilities we needed to rewrite the paths are on available on machines with XCode installed. I would suggest doing one of the following: sudo ln -s /Library/PostgreSQL/8.3/lib/libpq.5.dylib /usr/lib/libpq.5.dylib which will put a symlink to the library in /usr/lib, where the dynamic loader will find it, or: export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/Library/PostgreSQL/8.3/lib which will tell the dynamic linker to look in the PG lib directory. A third possible fix would be to use install_name_tool to rewrite the shared library path in the executable you've built. I'll look at a solution for the installer - it'll probably have to be the symlink unless anyone else has a better idea... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3 on FreeBSD 6.3, sudden performance degradations
On 2008-09-09 09:30, Tomasz Ostrowski wrote: On 2008-09-08 11:46, Ivan Zolotukhin wrote: vmstat 5 procs memory page disk faults cpu r b w avmfre flt re pi po fr sr am0 insycs us sy id 28 77 0 2328792 793424 34813 0 0 0 4351 0 41 1913 21230 20337 14 86 0 I do not know FreeBSD but can you check what is this flt stat? Is this page fault? That would be a lot of page faults for 5 seconds, which could mean that this server is memory starved and is swapping a lot. Discard this. Just found a man page for FreeBSD vmstat and indeed flt is a page fault, but because there's no pi (pages paged in) or po (pages paged out) the system is not trashing. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Install Postgres on a SAN volume?
Greg Smith wrote: On Mon, 8 Sep 2008, William Garrison wrote: 2) We could install PostgreSQL onto the C: drive and then configure the data folder to be on the SAN volume (Z:) Do that. You really don't want to get into the situation where you can't run anything related to the PostgreSQL service just because the SAN isn't available. You may have internal SAN fans that will swear that never happens, but it does. Also, it allows installing a later PostgreSQL version upgrade on another system and testing against the SAN data files in a way that said system could become the new server. There's all kinds of systems management reasons you should separate the database application from the database files. The counter-argument is that keeping the database software on the same drive will ensure you always run the same version in say a two node failover cluster. But that's a fairly specific use-case. For the general use-case, I agree with Greg's recommendation. So I am assured it is fast. Compared to what? The same amount spent on direct storage would be widly faster. Counterpoint: SAN is already in place... The thing to remember about SANs is that they are complicated, and there are many ways you can misconfigure them so that their database performance sucks. Make sure you actually benchmark the SAN and compare it to direct connected disks to see if it's acting sanely; don't just believe what people tell you. I personally can't understand why anybody would spend SAN $ and then hobble the whole thing by running PostgreSQL on Windows. The Win32 port is functional, but it's really not fast. As the guy who did much of the work on the Win32 port, I have to +1 this several times over ;-) PostgreSQL on Win32 will always be slower than it's on Unix, for architectural reasons. This difference can increase drastically under high load. These are facts, not just the general recommendation from Unix people to stay away from Windows, btw :-) It is really nice because it supports instant snapshots so we can, in theory, snapshot a volume and re-mount it elsewhere. You'll still need to setup basic PITR recovery to know you got a useful snapshot. See http://lethargy.org/~jesus/archives/92-PostgreSQL-warm-standby-on-ZFS-crack.html for a nice intro to that that uses ZFS as the snapshot implementation. Say what? As long as your SAN guarantees an atomic snapshot of all your data (which every SAN I've ever heard of guarantees if you're on a single volume - entry level SANs often don't have the functionality to do multi-volume atomic snapshots, though), you don't need to set up PITR for simple backups, AFAIK. It's just simple crash recovery... It's still good idea, though, since it gives you the PIT part of PITR, which you don't get with just a snapshot. //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Install Postgres on a SAN volume?
On Tue, 9 Sep 2008, Magnus Hagander wrote: As long as your SAN guarantees an atomic snapshot of all your data (which every SAN I've ever heard of guarantees if you're on a single volume - entry level SANs often don't have the functionality to do multi-volume atomic snapshots, though), you don't need to set up PITR for simple backups It's all those ifs in there that leave me still recommending it. It's certainly possible to get a consistant snapshot with the right hardware and setup. What concerns me about recommending that without a long list of caveats is the kinds of corruption you'd get if all those conditions aren't perfect will of course not ever happen during testing. Murphy says that it will happen only when you find yourself really needing that snapshot to work one day. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3 on FreeBSD 6.3, sudden performance degradations
On Mon, 8 Sep 2008, Ivan Zolotukhin wrote: Yep, update_process_title = off if it is important. Have you considered turning it on so you can see what processes are most involved in the spike? Normally in your situation I'd try to capture what the output from top was during the problem period and match that with what the processes involved are doing at the time, and you'll need that to see inside the PG processes. If the event is random then this data can be hard to capture, you might have to write some scripts to save top output in batch mode and the output from ps. Did you confirm that the slow periods aren't checkpoint-related? I've seen some wacky stuff on Linux before where the system percentage went up dramatically because the background disk I/O process got hyperactive. Again, the way you can usually figure that out is to look at what top is showing during that period. I suspect you'll find some daemon going crazy. The vmstat and iostat info you included are certainly strange but I don't know enough about FreeBSD to know exactly what would cause that. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Install Postgres on a SAN volume?
Greg Smith wrote: On Tue, 9 Sep 2008, Magnus Hagander wrote: As long as your SAN guarantees an atomic snapshot of all your data (which every SAN I've ever heard of guarantees if you're on a single volume - entry level SANs often don't have the functionality to do multi-volume atomic snapshots, though), you don't need to set up PITR for simple backups It's all those ifs in there that leave me still recommending it. It's certainly possible to get a consistant snapshot with the right hardware and setup. What concerns me about recommending that without a long list of caveats is the kinds of corruption you'd get if all those conditions aren't perfect will of course not ever happen during testing. Murphy says that it will happen only when you find yourself really needing that snapshot to work one day. Well, I agree one should be careful, but I don't see the risk if you just change all those ifs into a single one, which is if all your data *and* WAL is on the same SAN LUN. (heck, you don't need hardware to do it, you can do software snapshot just fine - as long as you keep all your stuff on the same mountpoint there as well) //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed
On Mon, 8 Sep 2008, Markova, Nina wrote: I wasn't able to find 125077-02 patch for SPARC, and contacted SUN last week - they are very slow. It wasn't released; according to http://www.mail-archive.com/[EMAIL PROTECTED]/msg00199.html that fix made its way into 120011-09 instead. I'm not so sure you actually need this though. http://bugs.opensolaris.org/view_bug.do;jsessionid=1ce76d3cbf20747f874eb1a300df?bug_id=6546052 suggests there's actually a bug in how the checkinstall script looks for the file that prevent it from installing in a zone. Looks just like your report. That says the s10u4_07 update fixes the problem. I don't know enough about Solaris packaging to suggest exactly what to do here, but that should get you moving in the right direction rather than chasing a non-existant patch. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 64-bit Compile Failure on Solaris 10 with OpenSSL
On Sat, 6 Sep 2008, Randal T. Rioux wrote: On Sat, September 6, 2008 8:21 pm, Tom Lane wrote: I'm not a Solaris user, but I'd be kinda surprised if Solaris' own libraries were built with gcc --- Sun has their own compiler no? They released the instructions for how they were building their official PostgreSQL packages using their Sun Studio compiler at one point at http://wikis.sun.com/display/DBonSolaris/BuildingPostgreSQL bash-3.00# ldd /usr/local/lib/sparcv9/libgcc_s.so.1 I note that they explicitly include a few directories using --with-libs in that example. May you need to add /usr/local/lib/sparcv9 similarly? There's also some LD_OPTIONS magic you may need. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 64-bit Compile Failure on Solaris 10 with OpenSSL
Tom Lane napsal(a): I'm not a Solaris user I can sent you a installation media ;-), if you want. , but I'd be kinda surprised if Solaris' own libraries were built with gcc --- Sun has their own compiler no? Yes, sun studio. It is not installed by default, but it is possible to download it from http://developers.sun.com/sunstudio/downloads/. It is highly recommended to use sun studio compiler on SPARC. GCC is slower (~30%) and generates slower and sometimes buggy code on SPARC. Solaris 10 is compiled with Sun Studio 10, however integrated PostgreSQL is compiled with Sun Studio 11. Only gcc is compiled with gcc :-). Zdenek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Very weird problem of order by in postgresql
On Tue, Sep 09, 2008 at 09:04:02AM +0800, Peter Cai wrote: Oh, you are right! Although the 'locale' command show that the 2 has the same locale settings. The sort result of the same file is different. Ubuntu is right, centos is wrong?? Note that glibc uses the information in /usr/share/i18n/locales to build the relevent tables. Maybe comparing those directories might help. There is also a patch to use ICU for collation instead of the OS. That way you're not tied to any changes your OS might do. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed
Markova, Nina napsal(a): Hi, I am working on installing postgres ina zone - zoneadm install command finished with errors : Error: /usr/lib/libkrb5.so.1 not found. Please install the patch for 6381288 (we should expose the krb5 api). The patch needed is 125077-02 for SPARC and 125078-02 for x86 systems. pkgadd: ERROR: checkinstall script did not complete successfully I wasn't able to find 125077-02 patch for SPARC, and contacted SUN last week - they are very slow. Had anybody encountered the same problem? How have you solved it? Do you by chance have patch in question handy, so you can email it to me. What version of postgreSQL and Solaris (include update version) do you use? Do you use official solaris'es packages? Kerberos 5 has been integrated in Solaris 10 update 4. Zdenek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 64-bit Compile Failure on Solaris 10 with OpenSSL
Randal T. Rioux napsal(a): I've battled this for a while. I'm finally breaking down and asking for help. If you're answer to this is why 64-bit then don't answer. You wouldn't understand. Same if you say why don't you use packages. Here is my scenerio: - Sun 420R x450Mhz UltraSPARC-II / 4GB RAM - Solaris 10 05/08 - OpenSSL 0.9.8h - PostgreSQL 8.3.3 - GCC 3.4.6 - GNU Make 3.81 Three questions (yeah, you forbided ask, but ...) 1) Why 64 64bit code on SPARC is slower, because SPARC uses 4byte instructions and processing 64bit data needs more instructions. It is good only if you need more then 4GB share memory. When you use sunstudio compiler with best optimization 64bit application has 1%-5% performance degradation. 2) Why you don't use package You can use Solaris'es packages, which are integrated and optimized for Solaris. 3) Why you don't use build-in libssl? Integrated libssl is not only copy of original open ssl. It has lot of improvements and it uses crypto hardware accelerator if you have it (for example Niagara 2). See more: http://blogs.sun.com/janp/ http://blogs.sun.com/janp/entry/on_openssl_versions_in_solaris Zdenek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL TPC-H test result?
I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html saying that PostgreSQL can't give the correct result of the some TPC-H queries, I wonder is there any official statements about this, because it will affect our plane of using PostgreSQL as an alternative because it's usability. BTW I don't think PostgreSQL performances worse because the default configuration usually can't use enough resources of the computer, as as memory.
Re: [GENERAL] Automated Backup On Windows
Greg Smith wrote: On Mon, 8 Sep 2008, justin wrote: I would have added it to the postgresqldocs.org webstie but can't create an account for my self.Tried following theses instructions http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows but never could get it to work That page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available. Well I still can't create an account all it says is Login no create account button or screen anywhere? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
On Tue, Sep 9, 2008 at 1:32 PM, justin [EMAIL PROTECTED] wrote: Greg Smith wrote: On Mon, 8 Sep 2008, justin wrote: I would have added it to the postgresqldocs.org webstie but can't create an account for my self.Tried following theses instructions http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows but never could get it to work That page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available. Well I still can't create an account all it says is Login no create account button or screen anywhere? See the bottom section of the front page: http://wiki.postgresql.org/ -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, Sep 09, 2008 at 07:59:49PM +0800, Amber wrote: I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html Given that the point of that study is to prove something about performance, one should be leery of any claims based on an out of the box comparison. Particularly since the box their own product comes out of is compiled from CVS checkout. Their argument seems to be that people can learn how to drive CVS and to compile software under active development, but can't read the manual that comes with Postgres (and a release of Postgres well over a year old, at that). I didn't get any further in reading the claims, because it's obviously nothing more than a marketing effort using the principle that deriding everyone else will make them look better. Whether they have a good product is another question entirely. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
Dave Page wrote: On Tue, Sep 9, 2008 at 1:32 PM, justin [EMAIL PROTECTED] wrote: Greg Smith wrote: On Mon, 8 Sep 2008, justin wrote: I would have added it to the postgresqldocs.org webstie but can't create an account for my self.Tried following theses instructions http://www.postgresqldocs.org/wiki/Automated_Backup_on_Windows but never could get it to work That page has been moved to http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows and you shouldn't have a problem getting an account there. The postgresqldocs site ended up being temporary and shutdown once the official Wiki was available. Well I still can't create an account all it says is Login no create account button or screen anywhere? See the bottom section of the front page: http://wiki.postgresql.org/ It might actual help if i read the page :-[ . I kept looking for CREATE ACCOUNT next to login not at the bottom of the page.
Re: [GENERAL] Automated Backup On Windows
how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
sorry forgot the link http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows justin wrote: how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 3:46 AM, Dave Page [EMAIL PROTECTED] wrote: I'll look at a solution for the installer - it'll probably have to be the symlink unless anyone else has a better idea... Another option would be to create them as actual frameworks; instead of libpq you'd have /Library/Frameworks/pq.framework (and all the files/directories as required) and then people could compile with -framework pq and the OSX linker would do the write thing without having to dirty a system library path (/usr/lib) with symlinks. -- Benjamin Reed a.k.a. Ranger Rick Fink, KDE, and Mac OS X development Blog: http://www.raccoonfink.com/ Music: http://music.raccoonfink.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 2:23 PM, Benjamin Reed [EMAIL PROTECTED] wrote: On Tue, Sep 9, 2008 at 3:46 AM, Dave Page [EMAIL PROTECTED] wrote: I'll look at a solution for the installer - it'll probably have to be the symlink unless anyone else has a better idea... Another option would be to create them as actual frameworks; instead of libpq you'd have /Library/Frameworks/pq.framework (and all the files/directories as required) and then people could compile with -framework pq and the OSX linker would do the write thing without having to dirty a system library path (/usr/lib) with symlinks. Agreed, though I think that's something that should be handled by the PostgreSQL build system. Plus, I doubt it would be directly supported by many apps for some time - pgAdmin certainly wouldn't like it, nor would Slony. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed
Thanks all. I have the /usr/lib/libkrb5.so.1 but somehow the checkinstall script can't find it (it is s10u4). As a result in the non-global zone these SUNWpostgr-82-client and SUNWpostgr-82-lib are missing, they exist in the global zone though. Nina -Original Message- From: James Gates [mailto:[EMAIL PROTECTED] Sent: September 9, 2008 09:43 To: Markova, Nina Cc: Greg Smith; pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed bug_id 6546052 is already fixed in the PostgreSQL 8.2 packages on the Solaris S10 update 4 DVD. The Fixed in Build: s10u4_07 reference in the bug means that it was fixed in build 7 of S10u4, which is prior to general release. If the bug was fixed after release of S10u4 (and could therefore affect you), it would be fixed in a build of a later update. So I don't know of any other bugs with this particular checkinstall script. It's looking for the /usr/lib/libkrb5.so.1 library, which was introduced with Solaris 10 in update 4. The library was also made available in (sparc) patch 125077-02, which was never released, but replaced by 120011-09. So if you are running S10u4 (or later) then you *should* have /usr/lib/libkrb5.so.1. If you do, maybe there *is* another problem with the package checkinstall script. Did you get the PostgreSQL 8.2 packages from somewhere other than the S10u4 DVD? If you're running S10u3 (or earlier), then you need to install patch 120011-09 (or later) to get /usr/lib/libkrb5.so.1. Greg Smith wrote: On Mon, 8 Sep 2008, Markova, Nina wrote: I wasn't able to find 125077-02 patch for SPARC, and contacted SUN last week - they are very slow. It wasn't released; according to http://www.mail-archive.com/[EMAIL PROTECTED]/msg00199.html that fix made its way into 120011-09 instead. I'm not so sure you actually need this though. http://bugs.opensolaris.org/view_bug.do;jsessionid=1ce76d3cbf20747f874eb 1a300df?bug_id=6546052 suggests there's actually a bug in how the checkinstall script looks for the file that prevent it from installing in a zone. Looks just like your report. That says the s10u4_07 update fixes the problem. I don't know enough about Solaris packaging to suggest exactly what to do here, but that should get you moving in the right direction rather than chasing a non-existant patch. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Jim GatesSun Microsystems Nashua, NH, USA http://sun.com/postgresql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries. -- From: Andrew Sullivan [EMAIL PROTECTED] Sent: Tuesday, September 09, 2008 8:39 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL TPC-H test result? On Tue, Sep 09, 2008 at 07:59:49PM +0800, Amber wrote: I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html Given that the point of that study is to prove something about performance, one should be leery of any claims based on an out of the box comparison. Particularly since the box their own product comes out of is compiled from CVS checkout. Their argument seems to be that people can learn how to drive CVS and to compile software under active development, but can't read the manual that comes with Postgres (and a release of Postgres well over a year old, at that). I didn't get any further in reading the claims, because it's obviously nothing more than a marketing effort using the principle that deriding everyone else will make them look better. Whether they have a good product is another question entirely. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem starting PostgreSQL in Windows 2003
Hi, After successfully installing PostgreSQL 8.3.3-1 on Windows 2003, the service won't start. The error I see in the Windows Event Viewer is could not open process token: error code 5. Any help is appreciated. Thanks, Moshe. The information contained in this message is proprietary to the sender, protected from disclosure, and may be privileged. The information is intended to be conveyed only to the designated recipient(s) of the message. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, use, distribution or copying of this communication is strictly prohibited and may be unlawful. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals computer viruses.
[GENERAL] 3 postgres processes
Hi, After issuning initdb and starting the postgres server, I checked for processes running, expecting to see just one: postgres 4926 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres postgres 4924 1 0 14:44:52 pts/1 0:00 /usr/postgres/8.2/bin/postgres postgres 4929 4850 0 14:44:56 pts/1 0:00 grep postgres postgres 4928 4850 0 14:44:56 pts/1 0:00 ps -ef postgres 4927 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres Only one of them is in postmaster.pid. What the other ones are for? more /pg_data/postmaster.pid 4924 /pg_data 543200131 Thanks, -'\/\/\/`v^v^v^v^v^v^v Nina Markova, Database Analyst/ Analyst de base de données (613) 992-3753 facsimile / télécopieur (613) 992-8836 [EMAIL PROTECTED] Geological Survey of Canada / Commission géologique du Canada Natural Resources Canada/ Ressources naturelles Canada Government of Canada/ Gouvernement du Canada -'\/\/\/`v^v^v^v^v^v^v -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed
bug_id 6546052 is already fixed in the PostgreSQL 8.2 packages on the Solaris S10 update 4 DVD. The Fixed in Build: s10u4_07 reference in the bug means that it was fixed in build 7 of S10u4, which is prior to general release. If the bug was fixed after release of S10u4 (and could therefore affect you), it would be fixed in a build of a later update. So I don't know of any other bugs with this particular checkinstall script. It's looking for the /usr/lib/libkrb5.so.1 library, which was introduced with Solaris 10 in update 4. The library was also made available in (sparc) patch 125077-02, which was never released, but replaced by 120011-09. So if you are running S10u4 (or later) then you *should* have /usr/lib/libkrb5.so.1. If you do, maybe there *is* another problem with the package checkinstall script. Did you get the PostgreSQL 8.2 packages from somewhere other than the S10u4 DVD? If you're running S10u3 (or earlier), then you need to install patch 120011-09 (or later) to get /usr/lib/libkrb5.so.1. Greg Smith wrote: On Mon, 8 Sep 2008, Markova, Nina wrote: I wasn't able to find 125077-02 patch for SPARC, and contacted SUN last week - they are very slow. It wasn't released; according to http://www.mail-archive.com/[EMAIL PROTECTED]/msg00199.html that fix made its way into 120011-09 instead. I'm not so sure you actually need this though. http://bugs.opensolaris.org/view_bug.do;jsessionid=1ce76d3cbf20747f874eb1a300df?bug_id=6546052 suggests there's actually a bug in how the checkinstall script looks for the file that prevent it from installing in a zone. Looks just like your report. That says the s10u4_07 update fixes the problem. I don't know enough about Solaris packaging to suggest exactly what to do here, but that should get you moving in the right direction rather than chasing a non-existant patch. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Jim GatesSun Microsystems Nashua, NH, USA http://sun.com/postgresql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about indexes
Hi all. I usually create indexes accordingly to the queries used in my software. This means the more often than not I have composited indexes over more than one column. What'd be in PGSQL (v8.3+) the pros and cons of having instead only one-column indexes? Thanks in advance. RMP.
[GENERAL] Various intermittent bugs/instability - how to debug?
Dear PostgreSQL community, I hope you can help me with a problem I'm having - I'm stuck and don't know how to debug this further. I have a rather large nightly process that imports a lot of data from the OpenStreetMap project into a PostGIS database, then proceeds doing all sorts of things - creating spatial indexes, computing bounding boxes, doing simplification of geometries, that kind of stuff. The whole job usually takes about five hours. I'm running this on a Quad-Core Linux (Ubuntu, PostgreSQL 8.3) machine with 8 GB RAM. Every other night, the process aborts with some strange error message, and never at the same position: ERROR: invalid page header in block 166406 of relation node_tags ERROR: could not open segment 2 of relation 1663/24253056/24253895 (target block 1421295656): No such file or directory ERROR: Unknown geometry type: 10 When I continue the process after the failure, it will usually work. I know you all think hardware problem now. Of course this was my first guess as well. I ran a memory test for a night, no results; I downgraded do failsafe defaults for all BIOS timings, again no change. Ran cpuburn and all sorts of other things to grill the hardware - nothing. Then I bought an entirely new machine; similar setup, but using a Gigabyte instead of Asus mainboard, different chipset, slightly faster Quad-Core processor, and again 8 GB RAM and Ubuntu Hardy with PostgresSQL 8.3 and matching PostGIS. Believe it or not, this machine shows the *same* problems. It is not 100% reproducible, sometimes the job works fully, but every other day it just breaks down with one of the funny messages like above. No memtest errors here either. Both machines are consumer quality, i.e. normal Intel processors and not the server (Xeon) stock. I am at a loss - how can I proceed? This looks like a hardware problem alright, but so simliar problems on two so different machines? Is there something wrong with Intel's Quad-Core CPUs? What could I do to have a better chance of reproducing the error and ultimately identifying the component responsible? Is there some kind of PostgresSQL load test, something like cpuburn for PostgreSQL? Have there been other reports of intermittent problems like mine, and does anybody have any blind guesses...? Thanks Frederik -- Frederik Ramm www.geofabrik.de Geofabrik GmbH Handelsregister: HRB Mannheim 703657 Rueppurrer Strasse 4Geschaeftsfuehrung: Frederik Ramm 76137 Karlsruhe Tel: 0721-1803560-0 [EMAIL PROTECTED] Fax: 0721-1803560-9 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Various intermittent bugs/instability - how to debug?
Frederik Ramm wrote: Dear PostgreSQL community, Every other night, the process aborts with some strange error message, and never at the same position: ERROR: invalid page header in block 166406 of relation node_tags ERROR: could not open segment 2 of relation 1663/24253056/24253895 (target block 1421295656): No such file or directory What could I do to have a better chance of reproducing the error and ultimately identifying the component responsible? Is there some kind of PostgresSQL load test, something like cpuburn for PostgreSQL? Have there been other reports of intermittent problems like mine, and does anybody have any blind guesses...? Hard drives. You said you replaced the machines and ran cpu burn etc... You didn't say you checked the hard drives (or replaced them). Joshua D. Drake Thanks Frederik -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
justin wrote: how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top Please use subsections to separate both methods. Also it'd be good to mention that PGPASSWORD and .pgpass work with either method ... How about adding the page to the Windows category? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Various intermittent bugs/instability - how to debug?
Hi, Joshua D. Drake wrote: Hard drives. You said you replaced the machines and ran cpu burn etc... You didn't say you checked the hard drives (or replaced them). The new machine has new hard drives. On the old machine I had a hardware RAID5 array made up of 6x500 GB SATA with an Areca RAID controller and Linux LVM; the new machine has 3x750 GB SATA plugged directly into the main board (no RAID, no LVM). So even the drives are quite different, and it's different drivers in both cases... Bye Frederik -- Frederik Ramm www.geofabrik.de Geofabrik GmbH Handelsregister: HRB Mannheim 703657 Rueppurrer Strasse 4Geschaeftsfuehrung: Frederik Ramm 76137 Karlsruhe Tel: 0721-1803560-0 [EMAIL PROTECTED] Fax: 0721-1803560-9 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, Sep 9, 2008 at 7:06 AM, Amber [EMAIL PROTECTED] wrote: Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries. It would be nice to know about the data, queries, and the expected results of their tests just so we could see this for ourselves. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, Sep 9, 2008 at 10:06 AM, Amber [EMAIL PROTECTED] wrote: Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries. PostgreSQL, at least in terms of the open source databases, is probably your best bet if you are all concerned about correctness. Do not give any credence to a vendor published benchmark unless the test is published and can be independently verifed. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL process architecture question.
We know PostgreSQL uses one dedicated server process to serve one client connection, what we want to know is whether PostgreSQL use multiple threads inside agents processes to take advantage of multiple CPUs. In our site we have only a few concurrent connections, so what occurs inside agent process is very important to us.
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, Sep 09, 2008 at 10:06:01PM +0800, Amber wrote: Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries. I have never heard a reputable source claim this. I have grave doubts about their claim: they don't specify what implementation of TPC-H they use. They don't actually have the right, AIUI, to claim they tested under TPC-H, since their results aren't listed anywhere on http://www.tpc.org/tpch/results/tpch_results.asp?orderby=dbms. It could well be that they made up something that kinda does something like TPC-H, tailored to how their database works, and then claimed others can't do the job. That's nice marketing material, but it's not a meaningful test result. Without access to the methodology, you should be wary of accepting any of the conclusions. There is, I understand, an implementation of something like TPC-H that you could use to test it yourself. http://osdldbt.sourceforge.net/. DBT-3 is supposed to be that workload. Please note that the license does not allow you to publish competitive tests for marketing reasons. but you could see for yourself whether the claim is true that way. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Install Postgres on a SAN volume?
On Tuesday 09 September 2008 04:37:09 Magnus Hagander wrote: Greg Smith wrote: On Tue, 9 Sep 2008, Magnus Hagander wrote: As long as your SAN guarantees an atomic snapshot of all your data (which every SAN I've ever heard of guarantees if you're on a single volume - entry level SANs often don't have the functionality to do multi-volume atomic snapshots, though), you don't need to set up PITR for simple backups It's all those ifs in there that leave me still recommending it. It's certainly possible to get a consistant snapshot with the right hardware and setup. What concerns me about recommending that without a long list of caveats is the kinds of corruption you'd get if all those conditions aren't perfect will of course not ever happen during testing. Murphy says that it will happen only when you find yourself really needing that snapshot to work one day. Well, I agree one should be careful, but I don't see the risk if you just change all those ifs into a single one, which is if all your data *and* WAL is on the same SAN LUN. (heck, you don't need hardware to do it, you can do software snapshot just fine - as long as you keep all your stuff on the same mountpoint there as well) That's pretty key, but there can be advantages to doing it using the pitr tools, and I think in most cases it would be hard to argue it isn't safer. As a counter example to theo's zfs based post, I posted a linux/lvm script that can work as the basis of a simple snapshot backup tool, available at http://people.planetpostgresql.org/xzilla/index.php?/archives/344-ossdb-snapshot,-lvm-database-snapshot-tool.html And yes, I prefer working on the zfs based one :-) -- Robert Treat http://www.omniti.com Database: Scalability: Consulting: -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 3 postgres processes
--- On Tue, 9/9/08, Markova, Nina [EMAIL PROTECTED] wrote: From: Markova, Nina [EMAIL PROTECTED] Subject: [GENERAL] 3 postgres processes To: pgsql-general@postgresql.org Date: Tuesday, September 9, 2008, 2:50 PM Hi, After issuning initdb and starting the postgres server, I checked for processes running, expecting to see just one: postgres 4926 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres postgres 4924 1 0 14:44:52 pts/1 0:00 /usr/postgres/8.2/bin/postgres postgres 4929 4850 0 14:44:56 pts/1 0:00 grep postgres postgres 4928 4850 0 14:44:56 pts/1 0:00 ps -ef postgres 4927 4924 0 14:44:52 ? 0:00 /usr/postgres/8.2/bin/postgres Only one of them is in postmaster.pid. What the other ones are for? more /pg_data/postmaster.pid 4924 /pg_data 543200131 Thanks, use ps auxw to see more details of the process i think the processes are one of this writer process wal writer process autovacuum launcher process stats collector process -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Various intermittent bugs/instability - how to debug?
On Tue, Sep 9, 2008 at 8:50 AM, Frederik Ramm [EMAIL PROTECTED] wrote: Dear PostgreSQL community, I hope you can help me with a problem I'm having - I'm stuck and don't know how to debug this further. I have a rather large nightly process that imports a lot of data from the OpenStreetMap project into a PostGIS database, then proceeds doing all sorts of things - creating spatial indexes, computing bounding boxes, doing simplification of geometries, that kind of stuff. The whole job usually takes about five hours. I'm running this on a Quad-Core Linux (Ubuntu, PostgreSQL 8.3) machine with 8 GB RAM. Every other night, the process aborts with some strange error message, and never at the same position: ERROR: invalid page header in block 166406 of relation node_tags ERROR: could not open segment 2 of relation 1663/24253056/24253895 (target block 1421295656): No such file or directory ERROR: Unknown geometry type: 10 When I continue the process after the failure, it will usually work. I know you all think hardware problem now. Of course this was my first guess as well. I ran a memory test for a night, no results; I downgraded do failsafe defaults for all BIOS timings, again no change. Ran cpuburn and all sorts of other things to grill the hardware - nothing. You definitely are suffering from db corruption, and given the number and differing type of errors, it would seem unlikely that pgsql has a load of bugs only you are seeing. OTOH, if the bug is hidden deep in postgis or something, then who knows... I'd definitely run something like bonnie++ for a few days and see if it gets HD errors or not. And definitely run memtest86 for a day or so and make sure you're not getting any errors there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL process architecture question.
On Tue, Sep 9, 2008 at 9:35 AM, Amber [EMAIL PROTECTED] wrote: We know PostgreSQL uses one dedicated server process to serve one client connection, what we want to know is whether PostgreSQL use multiple threads inside agents processes to take advantage of multiple CPUs. In our site we have only a few concurrent connections, so what occurs inside agent process is very important to us. No it doesn't. One connection gets one process which uses one CPU at a time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL process architecture question.
On Tue, Sep 09, 2008 at 11:35:56PM +0800, Amber wrote: We know PostgreSQL uses one dedicated server process to serve one client connection, what we want to know is whether PostgreSQL use multiple threads inside agents processes to take advantage of multiple CPUs. No. Note that threading is not automatically necessary to get more than one processor to work on a single query. But at the moment, Postgres doesn't do that either. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Various intermittent bugs/instability - how to debug?
Also, I'd try running a large, long running pgbench test to see if you get any errors. If such a simple schema / operation in large scale gets errors it's almost certainly a hardware failure. Have you had power removed while the system was running? With a lot of direct attached SATA drives you can get corruption from that as well due to linux / sata often not obeying write barriers. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
On Tue, Sep 9, 2008 at 2:12 PM, justin [EMAIL PROTECTED] wrote: how does this page look to you guys/gals. I have never added anything to a WIKI before so any comments?? I left the original author stuff untouched my edit is appended to the top Looks tidy enough to me :-). PgAdmin should be pgAdmin though, but that's just me being picky :-p -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tuesday 09 September 2008 10:06:01 Amber wrote: From: Andrew Sullivan [EMAIL PROTECTED] Sent: Tuesday, September 09, 2008 8:39 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL TPC-H test result? On Tue, Sep 09, 2008 at 07:59:49PM +0800, Amber wrote: I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html Given that the point of that study is to prove something about performance, one should be leery of any claims based on an out of the box comparison. Particularly since the box their own product comes out of is compiled from CVS checkout. Their argument seems to be that people can learn how to drive CVS and to compile software under active development, but can't read the manual that comes with Postgres (and a release of Postgres well over a year old, at that). I didn't get any further in reading the claims, because it's obviously nothing more than a marketing effort using the principle that deriding everyone else will make them look better. Whether they have a good product is another question entirely. Yes, we don't care about the performance results, but we do care about the point that PostgreSQL can't give the correct results of TPC-H queries. Given the point of those benchmarks is to make other systems look bad, I think you have to take them with a grain of salt. Since we don't know what the errors/results were, and no information is giving, we are left to wonder if this is a problem with the software or the tester. The site would have us believe the former, but I think I would lean toward the latter... case in point, I did a quick google and turned up this link: http://www.it.iitb.ac.in/~chetanv/personal/acads/db/report_html/node10.html. It isn't terribly informative, but it doesindicate one thing, someone else was able to run query #6 correctly, while the above site claims it returns an error. Now when I look at query#6 from that site, I notice it shows the following syntax: interval '1' year. when I saw that, it jumped out at me as something that could be an issue, and it is: pagila=# select now() - interval '1' year, now() - interval '1 year'; ?column?| ?column? ---+--- 2008-09-09 11:28:46.938209-04 | 2007-09-09 11:28:46.938209-04 (1 row) Now, I'm not sure if there is an issue that monet supports the first syntax and so when they ran thier test on postgres this query produced wrong results, but that seems possible. In this case I would wonder if the first syntax is sql compliant, but it doesn't really matter, the tpc-h allows for changes to queries to support syntax variations between databases; I'm pretty sure I could make suttle changes to break other databases as well. Incidentally, I poked Mark Wong, who used to work at the OSDL (big linux kernel hacking shop), and he noted he has successfully run the tpc-h tests before on postgres. In the end, I can't speak to what the issues are wrt monet and postgres and thier tpc-h benchmarks, but personally I don't think they are worth worring about. -- Robert Treat http://www.omniti.com Database: Scalability: Consulting: -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL process architecture question.
On Tue, 09 Sep 2008 10:07:32 -0600, Scott Marlowe wrote: On Tue, Sep 9, 2008 at 9:35 AM, Amber [EMAIL PROTECTED] wrote: We know PostgreSQL uses one dedicated server process to serve one client connection, what we want to know is whether PostgreSQL use multiple threads inside agents processes to take advantage of multiple CPUs. In our site we have only a few concurrent connections, so what occurs inside agent process is very important to us. No it doesn't. One connection gets one process which uses one CPU at a time. I understand the history/technical reasons/motivation for this, yet want to ask if anybody has thought about using OpenMP for careful parallelization of per-process work sections? Scanning large (e.g. already locked) arrays, parallel sweeps or calculations might benefit from parallelizatoin without requiring a full-out threaded design. Such an approach could retain the per-process isolation model yet still reap multicore benefits. To boot OpenMP is pretty easy to use and comes with gcc. Since I don't know much about PG's internals and their data dependencies etc. this might well be a dumb idea, but I figured asking couldn't hurt. :) regards Holger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 12:46 AM, Dave Page [EMAIL PROTECTED] wrote: On Tue, Sep 9, 2008 at 2:02 AM, Darren Weber [EMAIL PROTECTED] wrote: I'm new to using PostgreSQL on mac OS X. I used a binary installer for PostgreSQL 8.3 on mac OS X 10.5, which installs into /Library/PostgreSQL/[version]/ I'm building a lot of software that links to libpq and most of the builds fail or the run-time fails, because it cannot find the PostgreSQL libraries by default. It seems the dynamic link loader doesn't search this path by default to locate dynamic libraries, like libpq.5.dylib. Can you fix this issue for the binary installer? Hmm, it seems this is a side-effect of not rewriting the shared library paths at installation time. Because the library ID is just the filename, the linker doesn't write the full path to the binaries you compile. We changed from the old behaviour after it became apparent that the utilities we needed to rewrite the paths are on available on machines with XCode installed. I would suggest doing one of the following: sudo ln -s /Library/PostgreSQL/8.3/lib/libpq.5.dylib /usr/lib/libpq.5.dylib which will put a symlink to the library in /usr/lib, where the dynamic loader will find it, or: export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/Library/PostgreSQL/8.3/lib which will tell the dynamic linker to look in the PG lib directory. A third possible fix would be to use install_name_tool to rewrite the shared library path in the executable you've built. I'll look at a solution for the installer - it'll probably have to be the symlink unless anyone else has a better idea... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com I guess the symlinks from /usr/lib to /Library/PostgreSQL/lib would have to happen for many items (and sub-directories). I'm still new to OS X, so the whole unix/NeXT integration issue is a black box to me. At this point, I'm leaning on unix but I'm getting tangled up in binary installers that create some nice .app bundles. That's great for that particular package, but I'm discovering that OS X is confusing me when it comes to building (ie compiling) many useful packages from source (most of them assume a unix build environment). I'm using macports, but they decided to use /opt for all the installations and I'm not entirely clear about how that integrates with the OS X unix system (in some cases it seems to conflict). Yadda, yadda, yadda For one thing, I've discovered that setting DYLD_LIBRARY_PATH is not a great idea on OS X. For one, if you set it in your shell login profiles (.bashrc, .profile, .cshrc or whatever), most applications that are started from the 'Finder' will not see that setting (so it's kinda useless unless you want to work from the terminal all day). Also, some discussion forums indicate that it can screw up some applications. From reading 'man dyld', I gather that setting this variable is useful for testing new libraries rather than a permanent library solution. Today, I'm going to follow instructions here: http://developer.apple.com/internet/opensource/postgres.html If that doesn't work for me, I'll fall back on macports. However, both of these solutions involve building from source and they may not generate the .app utilities. Best, Darren -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Various intermittent bugs/instability - how to debug?
On Tue, Sep 09, 2008 at 04:50:53PM +0200, Frederik Ramm wrote: I have a rather large nightly process that imports a lot of data from the OpenStreetMap project into a PostGIS database, then proceeds doing all sorts of things - creating spatial indexes, computing bounding boxes, doing simplification of geometries, that kind of stuff. The whole job usually takes about five hours. Which program is this? ERROR: invalid page header in block 166406 of relation node_tags After this happens, can you find the relfilenode of the node_tags table and extract the relevent block? Something like: dd if=dboid/relfilenode bs=8k skip=166406 count=1 of=/tmp/block What sort of data does it contain? ERROR: could not open segment 2 of relation 1663/24253056/24253895 (target block 1421295656): No such file or directory Does any file of that name exist? ls 1663/24253056/24253895* Hope this helps, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] How to upload data to postgres
Hi again, I need to load data from Ingres database to Postgres database. What's the easiest way? Thanks, Nina
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 6:35 PM, Darren Weber [EMAIL PROTECTED] wrote: I guess the symlinks from /usr/lib to /Library/PostgreSQL/lib would have to happen for many items (and sub-directories). No, just one link to libpq.5.dylib. There's nothing else in there you'd should need to link with, except for very tightly integrated packages such as Slony, which you'd probably want to install in the PostgreSQL tree anyway (if not, your probably the sort of person who would be building the server from source as well anyway). I'm still new to OS X, so the whole unix/NeXT integration issue is a black box to me. At this point, I'm leaning on unix but I'm getting tangled up in binary installers that create some nice .app bundles. That's great for that particular package, but I'm discovering that OS X is confusing me when it comes to building (ie compiling) many useful packages from source (most of them assume a unix build environment). I'm using macports, but they decided to use /opt for all the installations and I'm not entirely clear about how that integrates with the OS X unix system (in some cases it seems to conflict). Yadda, yadda, yadda Yeah, I use Macports as well. For the most part, it doesn't integrate with OS X - that's why it's all kept under /opt. For one thing, I've discovered that setting DYLD_LIBRARY_PATH is not a great idea on OS X. For one, if you set it in your shell login profiles (.bashrc, .profile, .cshrc or whatever), most applications that are started from the 'Finder' will not see that setting (so it's kinda useless unless you want to work from the terminal all day). Yup. Also, some discussion forums indicate that it can screw up some applications. From reading 'man dyld', I gather that setting this variable is useful for testing new libraries rather than a permanent library solution. Today, I'm going to follow instructions here: http://developer.apple.com/internet/opensource/postgres.html That's very old. I'd stick with the installer or MacPorts. If that doesn't work for me, I'll fall back on macports. However, both of these solutions involve building from source and they may not generate the .app utilities. Oh, they won't. You won't find many apps in MacPorts that do I'd wager - just a few GUI ones which don't use X11 (such as pgAdmin). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed
Then there would seem to be another problem with the checkinstall script and non-global zones (although this was tested). I'll look into it, but I need to reproduce it. Can you tell me exactly what you've done so far (commands that you used to create the non-global zone, commands you're using to install the PostgreSQL packages, where you got the packages, etc). If you're calling the pkgadd command directly, can you please run it again and add the -v argument (this will trace the checkinstall script). Markova, Nina wrote: Thanks all. I have the /usr/lib/libkrb5.so.1 but somehow the checkinstall script can't find it (it is s10u4). As a result in the non-global zone these SUNWpostgr-82-client and SUNWpostgr-82-lib are missing, they exist in the global zone though. Nina -Original Message- From: James Gates [mailto:[EMAIL PROTECTED] Sent: September 9, 2008 09:43 To: Markova, Nina Cc: Greg Smith; pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres in a solaris zone - patch 125077-02 needed bug_id 6546052 is already fixed in the PostgreSQL 8.2 packages on the Solaris S10 update 4 DVD. The Fixed in Build: s10u4_07 reference in the bug means that it was fixed in build 7 of S10u4, which is prior to general release. If the bug was fixed after release of S10u4 (and could therefore affect you), it would be fixed in a build of a later update. So I don't know of any other bugs with this particular checkinstall script. It's looking for the /usr/lib/libkrb5.so.1 library, which was introduced with Solaris 10 in update 4. The library was also made available in (sparc) patch 125077-02, which was never released, but replaced by 120011-09. So if you are running S10u4 (or later) then you *should* have /usr/lib/libkrb5.so.1. If you do, maybe there *is* another problem with the package checkinstall script. Did you get the PostgreSQL 8.2 packages from somewhere other than the S10u4 DVD? If you're running S10u3 (or earlier), then you need to install patch 120011-09 (or later) to get /usr/lib/libkrb5.so.1. Greg Smith wrote: On Mon, 8 Sep 2008, Markova, Nina wrote: I wasn't able to find 125077-02 patch for SPARC, and contacted SUN last week - they are very slow. It wasn't released; according to http://www.mail-archive.com/[EMAIL PROTECTED]/msg00199.html that fix made its way into 120011-09 instead. I'm not so sure you actually need this though. http://bugs.opensolaris.org/view_bug.do;jsessionid=1ce76d3cbf20747f874eb 1a300df?bug_id=6546052 suggests there's actually a bug in how the checkinstall script looks for the file that prevent it from installing in a zone. Looks just like your report. That says the s10u4_07 update fixes the problem. I don't know enough about Solaris packaging to suggest exactly what to do here, but that should get you moving in the right direction rather than chasing a non-existant patch. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Jim GatesSun Microsystems Nashua, NH, USA http://sun.com/postgresql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 2:46 PM, Dave Page [EMAIL PROTECTED] wrote: For one thing, I've discovered that setting DYLD_LIBRARY_PATH is not a great idea on OS X. For one, if you set it in your shell login profiles (.bashrc, .profile, .cshrc or whatever), most applications that are started from the 'Finder' will not see that setting (so it's kinda useless unless you want to work from the terminal all day). Yup. Correct. If you need the behavior of LD_LIBRARY_PATH on linux/solaris, use DYLD_FALLBACK_LIBRARY_PATH instead. DYLD_LIBRARY_PATH doesn't do what you think. It's the nuclear option. Also, some discussion forums indicate that it can screw up some applications. From reading 'man dyld', I gather that setting this variable is useful for testing new libraries rather than a permanent library solution. Today, I'm going to follow instructions here: http://developer.apple.com/internet/opensource/postgres.html That's very old. I'd stick with the installer or MacPorts. Or Fink. ;) -- Benjamin Reed a.k.a. Ranger Rick Fink, KDE, and Mac OS X development Blog: http://www.raccoonfink.com/ Music: http://music.raccoonfink.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
My 02c, Pg does itself no favours by sticking with such pessimistic defaults, and a novice user wanting to try it out will find tweaking the pg configuration files for performance quite complicated. Given the general increase in typical hardware specs these days, perhaps the default pg specs could be set for higher spec systems? Or perhaps the standard install could come with 2 or 3 versions of the config files, the user can simply rename/invoke the one that fits their system best? I figure (somewhat simplistically) that most settings are more related to available memory than anything else, so perhaps config files for typical 1Gb, 4Gb 8Gb systems could be provided out of the box to make initial installs simpler? Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Andrew Sullivan [EMAIL PROTECTED] 09/10/08 3:47 AM On Tue, Sep 09, 2008 at 07:59:49PM +0800, Amber wrote: I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html Given that the point of that study is to prove something about performance, one should be leery of any claims based on an out of the box comparison. Particularly since the box their own product comes out of is compiled from CVS checkout. Their argument seems to be that people can learn how to drive CVS and to compile software under active development, but can't read the manual that comes with Postgres (and a release of Postgres well over a year old, at that). I didn't get any further in reading the claims, because it's obviously nothing more than a marketing effort using the principle that deriding everyone else will make them look better. Whether they have a good product is another question entirely. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Wed, Sep 10, 2008 at 07:16:35AM +1200, Brent Wood wrote: Given the general increase in typical hardware specs these days, perhaps the default pg specs could be set for higher spec systems? Given the default shmem configuration on operating systems these days, upping the default will likely cause postgresql to not run at all. For some reason the shmem defaults in OSes have not been increased in line with the hardware specs, not sure what can be done about that. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
Benjamin Reed [EMAIL PROTECTED] writes: On Tue, Sep 9, 2008 at 3:46 AM, Dave Page [EMAIL PROTECTED] wrote: I'll look at a solution for the installer - it'll probably have to be the symlink unless anyone else has a better idea... Another option would be to create them as actual frameworks; instead of libpq you'd have /Library/Frameworks/pq.framework (and all the files/directories as required) and then people could compile with -framework pq and the OSX linker would do the write thing without having to dirty a system library path (/usr/lib) with symlinks. Uh, it's not apparent to me why dropping stuff into /Library is any better than dropping it into /usr/lib? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Various intermittent bugs/instability - how to debug?
Scott Marlowe [EMAIL PROTECTED] writes: On Tue, Sep 9, 2008 at 8:50 AM, Frederik Ramm I hope you can help me with a problem I'm having - I'm stuck and don't know how to debug this further. You definitely are suffering from db corruption, and given the number and differing type of errors, it would seem unlikely that pgsql has a load of bugs only you are seeing. OTOH, if the bug is hidden deep in postgis or something, then who knows... Yeah, I would wonder about a postgis bug. Another thing to look into is kernel bugs --- I gather that these boxes are running more or less the same kernel? If there's something about your hardware that the kernel doesn't quite grok, all sorts of fun could ensue. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Wed, Sep 10, 2008 at 07:16:35AM +1200, Brent Wood wrote: Pg does itself no favours by sticking with such pessimistic defaults, and a novice user wanting to try it out will find tweaking the pg configuration files for performance quite complicated. You do know that at install time, Pg does some elementary investigation of the system to see what it can set its defaults to, right? In addition, every time this comes up I find it perplexing. The idea seems to be that novices in databases should be excused from learning about their system and should expect a nearly-optimally tuned system out of the box. But there are so many variables involved in database tuning as to make such a claim hard to swallow. For instance . . . fits their system best? I figure (somewhat simplistically) that most settings are more related to available memory than anything else, so . . . your figuring here is indeed simplistic. Every day I see requests for help from people who have followed the rule of thumb 1/4 of memory for shared_buffers, except that they're also running apache+jakarta, MySQL, and a mail server on the same box. They wonder why the stock advice is so wrong. It's wrong because a general-purpose tool is almost never going to come pre-set for every possible workload you might want to throw at it. So even how much memory there is on the machine is a question that is harder to answer than it might seem. Disk layout, data access patterns, even the filesystem you choose can make significant differences in how the system performs. Finally, part of the reason people make these claims is because they tend to hold Postgres up against toy systems that are _not_ designed to scale up. A certain well known database product, for instance, has been struggling for the last several years to turn itself into a full-featured, high-volume, safe transactional system. But the seams keep showing, because it just wasn't designed for this workload in the first place. But it sure is fast out of the box on a single-user system! A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 11:55 AM, Benjamin Reed [EMAIL PROTECTED] wrote: On Tue, Sep 9, 2008 at 2:46 PM, Dave Page [EMAIL PROTECTED] wrote: For one thing, I've discovered that setting DYLD_LIBRARY_PATH is not a great idea on OS X. For one, if you set it in your shell login profiles (.bashrc, .profile, .cshrc or whatever), most applications that are started from the 'Finder' will not see that setting (so it's kinda useless unless you want to work from the terminal all day). Yup. Correct. If you need the behavior of LD_LIBRARY_PATH on linux/solaris, use DYLD_FALLBACK_LIBRARY_PATH instead. DYLD_LIBRARY_PATH doesn't do what you think. It's the nuclear option. Also, some discussion forums indicate that it can screw up some applications. From reading 'man dyld', I gather that setting this variable is useful for testing new libraries rather than a permanent library solution. Today, I'm going to follow instructions here: http://developer.apple.com/internet/opensource/postgres.html That's very old. I'd stick with the installer or MacPorts. Or Fink. ;) -- Benjamin Reed a.k.a. Ranger Rick Fink, KDE, and Mac OS X development Blog: http://www.raccoonfink.com/ Music: http://music.raccoonfink.com/ Yeah, I also found that fink conflicts with macports. I do like the idea of using the Debian repository and package management system. For some reason, which escapes me now, I went with macports (maybe it was just that macports gave me an emacs.app - poor reason actually). Yet another learning curve for OSX. All this confusion makes me appreciate the beauty of Debian systems (eg, Ubuntu), with regard to package management. I hope all this mucking around with OSX is going to pay-off sooner or later. I guess the best suggestion (maybe the best solution) in this thread to date is to hack that symlink and hope the build system (and run-time links) will work everything out from there. Using the binary installer is easier and provides more GUI apps than doing the source build. I've done a quick, standard source build and install into /usr/local/pgsql/, can this co-exist with the binary installation into /Library/PostgreSQL/[version]? FYI, just to illustrate some of the confusion I can see. For starters, we need gmake. Well: [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# gmake -sh: gmake: command not found [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# make --version GNU Make 3.81 Copyright (C) 2006 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. This program built for powerpc-apple-darwin9.0 [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# ls -l /usr/bin/make lrwxr-xr-x 1 root wheel 7 2008-08-22 17:43 /usr/bin/make - gnumake* OK, but it's curious that I'm running OS X (10.5; Darwin 9.4.0) on a mac pro with dual quad-core zeons and the make program was built for Darwin 9.0 on a powerpc! (Looking to the heavens, I wonder how the hell can that work?) It does work, but maybe I should build it to get the architecture right (maybe everything should be built from the ground up!), so: [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# port search gmake gmake devel/gmake3.81 GNU Make [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# port install gmake --- Fetching gmake --- Attempting to fetch make-3.81.tar.bz2 from http://ftp.gnu.org/gnu/make --- Verifying checksum(s) for gmake --- Extracting gmake --- Configuring gmake --- Building gmake with target all --- Staging gmake into destroot --- Installing gmake 3.81_0 --- Activating gmake 3.81_0 --- Cleaning gmake [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# which gmake /opt/local/bin/gmake [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# gmake --version GNU Make 3.81 Copyright (C) 2006 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. This program built for i386-apple-darwin9.4.0 [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# which gmake /opt/local/bin/gmake A bit more intrigue; I'm reading the options for building postgreSQL 8.3.3 and I check the system for libperl and libpython, ie: [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# locate libperl /System/Library/Perl/5.8.8/darwin-thread-multi-2level/CORE/libperl.dylib /System/Library/Perl/lib/5.8/libperl.dylib /opt/local/lib/perl5/5.8.8/darwin-2level/CORE/libperl.a /opt/local/var/macports/software/perl5.8/5.8.8_3+darwin_9/opt/local/lib/perl5/5.8.8/darwin-2level/CORE/libperl.a /usr/libexec/httpd/libperl.so [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# locate libpython /Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator2.0.sdk/usr/lib/libpython.dylib
Re: [GENERAL] PostgreSQL TPC-H test result?
Robert Treat [EMAIL PROTECTED] writes: http://www.it.iitb.ac.in/~chetanv/personal/acads/db/report_html/node10.html. It isn't terribly informative, but it doesindicate one thing, someone else was able to run query #6 correctly, while the above site claims it returns an error. Now when I look at query#6 from that site, I notice it shows the following syntax: interval '1' year. when I saw that, it jumped out at me as something that could be an issue, and it is: Yeah. This is SQL spec syntax, but it's not fully implemented in Postgres: the grammar supports it but the info doesn't get propagated to interval_in, and interval_in wouldn't know what to do even if it did have the information that there was a YEAR qualifier after the literal. That's probably not good because it *looks* like we support the syntax, but in fact produce non-spec-compliant results. I think it might be better if we threw an error. Or someone could try to make it work, but given that no one has taken the slightest interest since Tom Lockhart left the project, I wouldn't hold my breath waiting for that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LEFT JOIN issue
I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I believe to be an Ubuntu Heron server. The sql below demonstrates the issue. What I expect to see is no rows in the output, i.e. the LEFT JOIN should pair the two rows together, and the WHERE clause should decide that the joined row doesn't match, and should yield no output. What happens is that the planner appears to apply the WHERE clause early, the left table doesn't yield any rows, and the row from the right table is output by itself. This only appears to happen when both sides of the OR are present, and the idx_beta_datereceived index is present. Remove any one, and it works like I expect. Dave create table alpha ( alphaid bigint not null, betaid bigint null, itemcode char(1) not null ); ALTER TABLE ONLY alpha ADD CONSTRAINT pk_alpha PRIMARY KEY (alphaid); create table beta ( betaid bigint not null, datereceived date null ); ALTER TABLE ONLY beta ADD CONSTRAINT pk_beta PRIMARY KEY (betaid); create index idx_alpha_betaid on alpha(betaid); insert into alpha values (22044, 92359002, 'U'); insert into beta values (92359002, '2008-08-11'); CREATE INDEX idx_beta_datereceived ON beta USING btree (datereceived); analyze alpha; analyze beta; explain select alpha.alphaid as aid , alpha.betaid as alphabetaid , beta.betaid as betaid , beta.datereceived , alpha.itemcode from alpha left join beta on beta.betaid = alpha.betaid where alpha.alphaid = 22044 and (beta.datereceived IS NULL OR ( beta.betaid IS NULL AND alpha.itemcode='U' ) ); QUERY PLAN --- Nested Loop Left Join (cost=0.00..2.04 rows=1 width=30) Join Filter: (beta.betaid = alpha.betaid) Filter: ((beta.datereceived IS NULL) OR ((beta.betaid IS NULL) AND (alpha.itemcode = 'U'::bpchar))) - Seq Scan on alpha (cost=0.00..1.01 rows=1 width=18) Filter: (alphaid = 22044) - Seq Scan on beta (cost=0.00..1.01 rows=1 width=12) Filter: ((beta.datereceived IS NULL) OR (beta.betaid IS NULL)) (7 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 9:14 PM, Darren Weber [EMAIL PROTECTED] wrote: Yeah, I also found that fink conflicts with macports. I do like the idea of using the Debian repository and package management system. For some reason, which escapes me now, I went with macports (maybe it was just that macports gave me an emacs.app - poor reason actually). Yet another learning curve for OSX. All this confusion makes me appreciate the beauty of Debian systems (eg, Ubuntu), with regard to package management. I hope all this mucking around with OSX is going to pay-off sooner or later. It will. My advice, is to pick one packaging system for your build-from-source addons, and stick with it. I prefer MacPorts, Benjamin is a Fink man. I guess the best suggestion (maybe the best solution) in this thread to date is to hack that symlink and hope the build system (and run-time links) will work everything out from there. Using the binary installer is easier and provides more GUI apps than doing the source build. I've done a quick, standard source build and install into /usr/local/pgsql/, can this co-exist with the binary installation into /Library/PostgreSQL/[version]? Yes. I regularly have half a dozen or more installs of PostgreSQL and Postgres Plus (EnterpriseDB's version of PostgreSQL) on the same box - including source and installer builds. FYI, just to illustrate some of the confusion I can see. For starters, we need gmake. Well: Use make from XCode. It is gmake. OK, but it's curious that I'm running OS X (10.5; Darwin 9.4.0) on a mac pro with dual quad-core zeons and the make program was built for Darwin 9.0 on a powerpc! (Looking to the heavens, I wonder how the hell can that work?) It does work, but maybe I should build it to get the architecture right (maybe everything should be built from the ground up!), so: Use the file command to check what type of binary it is. If it really is a PPC binary, then it'll be running under Rosetta (http://www.apple.com/rosetta/). Otherwise, it's probably a universal binary which contains PPC and Intel executables in the same file. A bit more intrigue; I'm reading the options for building postgreSQL 8.3.3 and I check the system for libperl and libpython, ie: ... Whoa, talk about a real supermarket full of the same libraries. I know that every-man and his dog has their own opinion on the pure installation system (maybe it's a bit like belief in one or many gods?). Anyhow, I have to figure out what the default search path is for the linker (ie, how to avoid total paranoia about configuring builds). You'll almost always use the stuff under /Developer/SDKs/MacOSX10.5.sdk, which is the Leopard SDK. Substitute in /opt if you need non-standard versions of anything, or additional libraries from MacPorts (or Fink). You've also got the Tiger SDK and at least some of the iphone SDK there. Looks like I've got my work cut out for me before I even begin to develop anything. It's really not that difficult - unless you need universal binaries, or want to target older versions of OSX, you won't normally see anything different from Linux for example. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 3:48 PM, Tom Lane [EMAIL PROTECTED] wrote: Uh, it's not apparent to me why dropping stuff into /Library is any better than dropping it into /usr/lib? Because /Library is the Apple framework equivalent of /usr/local in unix-world. Whereas /usr/lib should not really be touched by anyone but Apple (or the system vendor, for that matter, on any unix). /Library is searched automatically by the linker when doing framework-style builds, without needing -F to find a specific location for it. -- Benjamin Reed a.k.a. Ranger Rick Fink, KDE, and Mac OS X development Blog: http://www.raccoonfink.com/ Music: http://music.raccoonfink.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 4:14 PM, Darren Weber [EMAIL PROTECTED] wrote: Yeah, I also found that fink conflicts with macports. I do like the idea of using the Debian repository and package management system. For some reason, which escapes me now, I went with macports (maybe it was just that macports gave me an emacs.app - poor reason actually). Yet another learning curve for OSX. All this confusion makes me appreciate the beauty of Debian systems (eg, Ubuntu), with regard to package management. I hope all this mucking around with OSX is going to pay-off sooner or later. Generally they co-exist fine at runtime, but if you're building in one or the other, you should make sure it's counterpart is not enabled in your shell. That said, they're reasonably close to each other feature-wise, and the best bet is to pick which one you're more comfortable with (BSD ports style, or Linux/Debian style). -- Benjamin Reed a.k.a. Ranger Rick Fink, KDE, and Mac OS X development Blog: http://www.raccoonfink.com/ Music: http://music.raccoonfink.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server installation problem using freebsd ports
Hi, This might be a very small problem but I am stuck. When I try to install postgresql server 8.3.3 on freebsd using ports, I am getting - postgresql-server-8.3.3 cannot install: unknown PostgreSQL version: 83 There was no issue with client. This has something to do with pgsql_ver. What's wrong here? Thanks
[GENERAL] FW: How to upload data to postgres
So far I tried; 1) I have copied data from Ingres in ASCII (using Ingres copydb command). 2) created a table in a Postgres database 3) tried loading data into Potgres table - encounter problems. For 1) (the Ingres part) = Ingres used the following copy commands: copy site( sta= varchar(0)tab, ondate= varchar(0)tab, offdate= varchar(0)tab, lat= c0tab, lon= c0tab, elev= c0tab, regist_code= varchar(0)tab, vault_cond= varchar(0)tab, geology= varchar(0)tab, comment= varchar(0)tab, initials= varchar(0)tab, lddate= c0nl, nl= d0nl) into '/tmp/site.dba' Normally Ingres will use this command to copy data from a file: copy site( sta= varchar(0)tab, ondate= varchar(0)tab, offdate= varchar(0)tab, lat= c0tab, lon= c0tab, elev= c0tab, regist_code= varchar(0)tab, vault_cond= varchar(0)tab, geology= varchar(0)tab, comment= varchar(0)tab, initials= varchar(0)tab, lddate= c0nl, nl= d0nl) from '/vm04-0/home/postgres/test/site.dba' For 3) = - I got error when I tried to copy with Ingres-like copy command. - Then I tried to copy with simple 'copy site from '/vm04-0/home/postgres/test/site-c.dba' - ERROR: value too long for type character varying(5) - I had no luck either when used binary copying - postgres complained about signature: copy site from '/vm04-0/home/postgres/test/site.dba' with binary : ERROR: COPY file signature not recognized I have couple of questions as well. Q1: is there an equivalent of copydb in postgres (in Ingres copydb creates copy statements for all database tables in a single file) Q2: how to say in postgres that a field has no default values (in Ingres 'not default' is used - and this produced an error in postgres CREATE TABLE command) Create table site ( sta varchar(5) not null, ondate varchar(8) not null, offdate varchar(8) not null, lat float not null not default, - lon float not null not default ) Q3: How to specify storage structure of a table (again in Ingres 'modify' statement is used to specify btree, isam or hash structure). In the Postgres documentation I only saw how to create an index with a specific structure. In Ingres: modify site to isam unique on sta, ondate (means structure isam, primary key is on 2 fields - sta and ondate) Thanks in advance, Nina __ From: Markova, Nina Sent: September 9, 2008 14:32 To: pgsql-general@postgresql.org Subject: How to upload data to postgres Hi again, I need to load data from Ingres database to Postgres database. What's the easiest way? Thanks, Nina
Re: [GENERAL] LEFT JOIN issue
On Tuesday 09 September 2008, David Jaquay [EMAIL PROTECTED] wrote: I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I believe to be an Ubuntu Heron server. The sql below demonstrates the issue. explain doesn't execute the query. Show the output of the actual select - it doesn't return any rows on my 8.3.3 database. -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, Sep 9, 2008 at 2:07 PM, Andrew Sullivan [EMAIL PROTECTED] wrote: . . . your figuring here is indeed simplistic. Every day I see requests for help from people who have followed the rule of thumb 1/4 of memory for shared_buffers, except that they're also running apache+jakarta, MySQL, and a mail server on the same box. They wonder why the stock advice is so wrong. It's wrong because a general-purpose tool is almost never going to come pre-set for every possible workload you might want to throw at it. So even how much memory there is on the machine is a question that is harder to answer than it might seem. Disk layout, data access patterns, even the filesystem you choose can make significant differences in how the system performs. Just as common is the beginner showing up with an 8 core opteron server with 64 Gigs of ram trying to get fast write transactions on a single 7200 rpm 500G sata drive. Finally, part of the reason people make these claims is because they tend to hold Postgres up against toy systems that are _not_ designed to scale up. A certain well known database product, for instance, has been struggling for the last several years to turn itself into a full-featured, high-volume, safe transactional system. But the seams keep showing, because it just wasn't designed for this workload in the first place. But it sure is fast out of the box on a single-user system! reference tweakers.net http://tweakers.net/reviews/649/8/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron-pagina-8.html http://tweakers.net/reviews/661/6/database-test-intel-xeon-clovertown-x5355-pagina-6.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 1:31 PM, Dave Page [EMAIL PROTECTED] wrote: On Tue, Sep 9, 2008 at 9:14 PM, Darren Weber [EMAIL PROTECTED] wrote: Yeah, I also found that fink conflicts with macports. I do like the idea of using the Debian repository and package management system. For some reason, which escapes me now, I went with macports (maybe it was just that macports gave me an emacs.app - poor reason actually). Yet another learning curve for OSX. All this confusion makes me appreciate the beauty of Debian systems (eg, Ubuntu), with regard to package management. I hope all this mucking around with OSX is going to pay-off sooner or later. It will. My advice, is to pick one packaging system for your build-from-source addons, and stick with it. I prefer MacPorts, Benjamin is a Fink man. I guess the best suggestion (maybe the best solution) in this thread to date is to hack that symlink and hope the build system (and run-time links) will work everything out from there. Using the binary installer is easier and provides more GUI apps than doing the source build. I've done a quick, standard source build and install into /usr/local/pgsql/, can this co-exist with the binary installation into /Library/PostgreSQL/[version]? Yes. I regularly have half a dozen or more installs of PostgreSQL and Postgres Plus (EnterpriseDB's version of PostgreSQL) on the same box - including source and installer builds. FYI, just to illustrate some of the confusion I can see. For starters, we need gmake. Well: Use make from XCode. It is gmake. OK, but it's curious that I'm running OS X (10.5; Darwin 9.4.0) on a mac pro with dual quad-core zeons and the make program was built for Darwin 9.0 on a powerpc! (Looking to the heavens, I wonder how the hell can that work?) It does work, but maybe I should build it to get the architecture right (maybe everything should be built from the ground up!), so: Use the file command to check what type of binary it is. If it really is a PPC binary, then it'll be running under Rosetta (http://www.apple.com/rosetta/). Otherwise, it's probably a universal binary which contains PPC and Intel executables in the same file. A bit more intrigue; I'm reading the options for building postgreSQL 8.3.3 and I check the system for libperl and libpython, ie: ... Whoa, talk about a real supermarket full of the same libraries. I know that every-man and his dog has their own opinion on the pure installation system (maybe it's a bit like belief in one or many gods?). Anyhow, I have to figure out what the default search path is for the linker (ie, how to avoid total paranoia about configuring builds). You'll almost always use the stuff under /Developer/SDKs/MacOSX10.5.sdk, which is the Leopard SDK. Substitute in /opt if you need non-standard versions of anything, or additional libraries from MacPorts (or Fink). You've also got the Tiger SDK and at least some of the iphone SDK there. Looks like I've got my work cut out for me before I even begin to develop anything. It's really not that difficult - unless you need universal binaries, or want to target older versions of OSX, you won't normally see anything different from Linux for example. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com When building postgreSQL from source, I'm using a default installation path config and I want to be specific about what libraries are being linked, so it seems that I could use macports like this (assuming the required ports are installed and active): ./configure \ --with-includes=/opt/local/include \ --with-libraries=/opt/local/lib \ --with-perl --with-python --with-tcl On the other hand, I could use the /Developer SDK, like this: ./configure \ --with-includes=/Developer/SDKs/MacOSX10.5.sdk/usr/include \ --with-libraries=/Developer/SDKs/MacOSX10.5.sdk/usr/lib \ --with-perl --with-python --with-tcl The /Developer SDK for 10.5 seems to be symlinks to the /System frameworks (this is a 10.5 system), eg: [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# ls -l /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython.dylib lrwxr-xr-x 1 root wheel 16 2008-08-22 17:40 /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython.dylib - libpython2.dylib* [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# ls -l /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython2.dylib lrwxr-xr-x 1 root wheel 68 2008-08-22 17:40 /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython2.dylib - ../../System/Library/Frameworks/Python.framework/Versions/2.5/Python* -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LEFT JOIN issue
Just got an email saying that this has been fixed in 8.3.4, so I suppose I'll watch and wait for that. Thanks for looking, Alan (and anyone else who was watching this...) Dave On Tue, Sep 9, 2008 at 4:57 PM, Alan Hodgson [EMAIL PROTECTED] wrote: On Tuesday 09 September 2008, David Jaquay [EMAIL PROTECTED] wrote: I'm seeing a problem with a LEFT JOIN in 8.3.3, running on what I believe to be an Ubuntu Heron server. The sql below demonstrates the issue. explain doesn't execute the query. Show the output of the actual select - it doesn't return any rows on my 8.3.3 database. -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OS X library path issues for libpq (ver 8.3)
On Tue, Sep 9, 2008 at 2:03 PM, Darren Weber [EMAIL PROTECTED] wrote: On Tue, Sep 9, 2008 at 1:31 PM, Dave Page [EMAIL PROTECTED] wrote: On Tue, Sep 9, 2008 at 9:14 PM, Darren Weber [EMAIL PROTECTED] wrote: Yeah, I also found that fink conflicts with macports. I do like the idea of using the Debian repository and package management system. For some reason, which escapes me now, I went with macports (maybe it was just that macports gave me an emacs.app - poor reason actually). Yet another learning curve for OSX. All this confusion makes me appreciate the beauty of Debian systems (eg, Ubuntu), with regard to package management. I hope all this mucking around with OSX is going to pay-off sooner or later. It will. My advice, is to pick one packaging system for your build-from-source addons, and stick with it. I prefer MacPorts, Benjamin is a Fink man. I guess the best suggestion (maybe the best solution) in this thread to date is to hack that symlink and hope the build system (and run-time links) will work everything out from there. Using the binary installer is easier and provides more GUI apps than doing the source build. I've done a quick, standard source build and install into /usr/local/pgsql/, can this co-exist with the binary installation into /Library/PostgreSQL/[version]? Yes. I regularly have half a dozen or more installs of PostgreSQL and Postgres Plus (EnterpriseDB's version of PostgreSQL) on the same box - including source and installer builds. FYI, just to illustrate some of the confusion I can see. For starters, we need gmake. Well: Use make from XCode. It is gmake. OK, but it's curious that I'm running OS X (10.5; Darwin 9.4.0) on a mac pro with dual quad-core zeons and the make program was built for Darwin 9.0 on a powerpc! (Looking to the heavens, I wonder how the hell can that work?) It does work, but maybe I should build it to get the architecture right (maybe everything should be built from the ground up!), so: Use the file command to check what type of binary it is. If it really is a PPC binary, then it'll be running under Rosetta (http://www.apple.com/rosetta/). Otherwise, it's probably a universal binary which contains PPC and Intel executables in the same file. A bit more intrigue; I'm reading the options for building postgreSQL 8.3.3 and I check the system for libperl and libpython, ie: ... Whoa, talk about a real supermarket full of the same libraries. I know that every-man and his dog has their own opinion on the pure installation system (maybe it's a bit like belief in one or many gods?). Anyhow, I have to figure out what the default search path is for the linker (ie, how to avoid total paranoia about configuring builds). You'll almost always use the stuff under /Developer/SDKs/MacOSX10.5.sdk, which is the Leopard SDK. Substitute in /opt if you need non-standard versions of anything, or additional libraries from MacPorts (or Fink). You've also got the Tiger SDK and at least some of the iphone SDK there. Looks like I've got my work cut out for me before I even begin to develop anything. It's really not that difficult - unless you need universal binaries, or want to target older versions of OSX, you won't normally see anything different from Linux for example. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com When building postgreSQL from source, I'm using a default installation path config and I want to be specific about what libraries are being linked, so it seems that I could use macports like this (assuming the required ports are installed and active): ./configure \ --with-includes=/opt/local/include \ --with-libraries=/opt/local/lib \ --with-perl --with-python --with-tcl On the other hand, I could use the /Developer SDK, like this: ./configure \ --with-includes=/Developer/SDKs/MacOSX10.5.sdk/usr/include \ --with-libraries=/Developer/SDKs/MacOSX10.5.sdk/usr/lib \ --with-perl --with-python --with-tcl The /Developer SDK for 10.5 seems to be symlinks to the /System frameworks (this is a 10.5 system), eg: [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# ls -l /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython.dylib lrwxr-xr-x 1 root wheel 16 2008-08-22 17:40 /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython.dylib - libpython2.dylib* [ [EMAIL PROTECTED] /usr/src/postgresql-8.3.3 ]# ls -l /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython2.dylib lrwxr-xr-x 1 root wheel 68 2008-08-22 17:40 /Developer/SDKs/MacOSX10.5.sdk/usr/lib/libpython2.dylib - ../../System/Library/Frameworks/Python.framework/Versions/2.5/Python* Curious, even after using the /Developer includes and lib for configure, the config.log file contains the following: PATH: /opt/local/bin PATH: /opt/local/sbin PATH: /usr/bin PATH: /bin PATH: /usr/sbin PATH: /sbin PATH: /usr/local/bin PATH: /usr/X11/bin PATH: /opt/local/bin PATH: /usr/local/git/bin PATH: /usr/local/mysql/bin PATH:
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, 9 Sep 2008, Amber wrote: I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html saying that PostgreSQL can't give the correct result of the some TPC-H queries Jignesh Shah at Sun ran into that same problem. It's mentioned briefly in his presentation at http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_postgresql on pages 26 and 27. 5 of the 22 reference TCP-H queries (4, 5, 6, 10, 14) returned zero rows immediately for his tests. Looks like the MonetDB crew is saying it does that on queries 4,5,6,10,12,14,15 and that 20 takes too long to run to generate a result. Maybe 12/15/20 were fixed by changes in 8.3, or perhaps there were subtle errors there that Jignesh didn't catch--it's not like he did a formal submission run, was just kicking the tires. I suspect the difference on 20 was that his hardware and tuning was much better, so it probably did execute fast enough. While some of the MonetDB bashing in this thread was unwarranted, it is quite inappropriate that they published performance results here. Would be nice if someone in the community were to grab ahold of the TPC-H problems and try to shake them out. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
On Tue, 9 Sep 2008, Alvaro Herrera wrote: Please use subsections to separate both methods. How about adding the page to the Windows category? I just fixed both those. Justin, you might want to look at how I reformatted that to get an idea what the usual style is like. Also, people who write whole articles or large sections are sometimes credited there, you might want to add yourself to the author list for that page at http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance Also it'd be good to mention that PGPASSWORD and .pgpass work with either method ... I put a stub page in at http://wiki.postgresql.org/wiki/Pgpass to cover this whole area but never really filled it in. I'd prefer seeing that get fleshed out and then the automation page can just link to it, because this is a very FAQ. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
Greg Smith wrote: On Tue, 9 Sep 2008, Amber wrote: I read something from http://monetdb.cwi.nl/projects/monetdb/SQL/Benchmark/TPCH/index.html saying that PostgreSQL can't give the correct result of the some TPC-H queries Jignesh Shah at Sun ran into that same problem. It's mentioned briefly in his presentation at http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_postgresql on pages 26 and 27. 5 of the 22 reference TCP-H queries (4, 5, 6, 10, 14) returned zero rows immediately for his tests. Looks like the MonetDB crew is saying it does that on queries 4,5,6,10,12,14,15 and that 20 takes too long to run to generate a result. Maybe 12/15/20 were fixed by changes in 8.3, or perhaps there were subtle errors there that Jignesh didn't catch--it's not like he did a formal submission run, was just kicking the tires. I suspect the difference on 20 was that his hardware and tuning was much better, so it probably did execute fast enough. While some of the MonetDB bashing in this thread was unwarranted, it is quite inappropriate that they published performance results here. Would be nice if someone in the community were to grab ahold of the TPC-H problems and try to shake them out. Hmm This is the second time MonetDB has published PostgreSQL numbers. I think I will try to spend few days on TPC-H again on a much smaller scale (to match what MonetDB used) and start discussions on solving the problem.. Keep tuned. Regards, Jignesh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can interval take a value from a field?
Sometimes I think the hurdle to learning more advanced sql is figuring out how to phrase the search question. Well, I'm not having any luck there so here I come again to the list, hat in hand :-) I have a table with an id key and an expiration value in years trgexpd_trg_id | expiration_value +-- 240 | 1 year 749 | 3 years 4917 | 2 years and so on. I'm trying to use the expiration value as the value for an interval in a query that looks like: select ts_date as Transcript Date, ts_expiration_date as Current Expiration Date, expiration_value as Expiration Interval from transcript, training_expiration_value where ts_training_id = trgexpd_trg_id and ts_training_id in (select cda_training_number from cdas) and ts_expiration_date != ts_date + interval 'expiration_value'; and I'm getting the following error: ERROR: invalid input syntax for type interval: expiration_value Is there a way to use the value in expiration_value for the interval? Thanks, Jeff Ross -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Automated Backup On Windows
Please use subsections to separate both methods. How about adding the page to the Windows category? I just fixed both those. Justin, you might want to look at how I reformatted that to get an idea what the usual style is like. Also, people who write whole articles or large sections are sometimes credited there, you might want to add yourself to the author list for that page at http://wiki.postgresql.org/wiki/Database_Administration_and_Maintenance Thanks i was going to take this Alvaro suggestion and clean it up more. Also it'd be good to mention that PGPASSWORD and .pgpass work with either method ... I put a stub page in at http://wiki.postgresql.org/wiki/Pgpass to cover this whole area but never really filled it in. I'd prefer seeing that get fleshed out and then the automation page can just link to it, because this is a very FAQ. I've been trying to figure out how to get pgpass to work on windows with no luck as of yet. If i ever figure out and test pgpass on several other windows version then i'll do up a documentation on pgpass. There is some conflicting instructions on pgpass. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can interval take a value from a field?
On Tue, 2008-09-09 at 17:03 -0600, Jeff Ross wrote: select ts_date as Transcript Date, ts_expiration_date as Current Expiration Date, expiration_value as Expiration Interval from transcript, training_expiration_value where ts_training_id = trgexpd_trg_id and ts_training_id in (select cda_training_number from cdas) and ts_expiration_date != ts_date + interval 'expiration_value'; and I'm getting the following error: ERROR: invalid input syntax for type interval: expiration_value This error is saying that it is trying to convert the string 'expiration_value' to an interval. What you really want it to convert the string value held inside a variable named expiration_value to an interval. For that, you need to do expiration_value::interval Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OSX build of PostgreSQL 8.3.3 with macports
For the record, I've found the following kitchen sink options will build and install on OS X 10.5 with macports. sudo -i port install gmake port install gawk port install flex port install bison +yacc port install openldap port install openssl port install libxml port install libxml2 port install libxmldiff port install libxmlxx port install libxmlxx2 port install python25 port install perl5.8 +darwin_9 +threads +shared port install tcl +threads ## Some of these ports may replace a currently installed port. ## Use 'port deactivate XXX' on the currently installed port XXX. ## Then run the install again to enable the new port. ## Use 'port variants XXX' to identify install candidates. mkdir -p /usr/src cd /usr/src curl -O ftp://ftp5.us.postgresql.org/pub/PostgreSQL/source/v8.3.3/postgresql-8.3.3.tar.gz tar zxvf postgresql*.tar.gz cd postgresql-8.3.3 ./configure \ --with-includes=/opt/local/include \ --with-libraries=/opt/local/lib \ --with-perl --with-python --with-tcl \ --with-krb5 --with-gssapi --with-pam \ --with-ldap --with-openssl --enable-thread-safety \ --with-bonjour --with-libxml --with-libxslt \ --with-system-tzdata=/usr/share/zoneinfo ## Check config.log to confirm where it found libraries gmake # Wait for All of PostgreSQL successfully made. Ready to install. gmake install # Wait for PostgreSQL installation complete. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] syntax error on: GET DIAGNOSTICS xcount = ROW_COUNT;
Hi All, I am getting an error on GET DIAGNOSTICS statement in a PL/SQL function below. CREATE OR REPLACE FUNCTION test(lid int, OUT nid int) AS $$ DECLARE road_row road%ROWTYPE; BEGIN SELECT * INTO road_row FROM road WHERE link_id=lid; GET DIAGNOSTICS xcount = ROW_COUNT; IF xcount = 0 THEN RAISE DEBUG 'LID=% not found', lid; nid := -99; RETURN END IF .. The exact error message is: ERROR: syntax error at or near xcount at character I am must be doing something wrong somewhere. Thanks a lot for any help. _C_ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] syntax error on: GET DIAGNOSTICS xcount = ROW_COUNT;
[EMAIL PROTECTED] writes: I am getting an error on GET DIAGNOSTICS statement in a PL/SQL function below. CREATE OR REPLACE FUNCTION test(lid int, OUT nid int) AS $$ DECLARE road_row road%ROWTYPE; BEGIN SELECT * INTO road_row FROM road WHERE link_id=lid; GET DIAGNOSTICS xcount = ROW_COUNT; You need to have declared xcount as an integer variable. The exact error message is: ERROR: syntax error at or near xcount at character Hmm, not the most friendly error, I agree ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL TPC-H test result?
On Tue, Sep 9, 2008 at 3:37 PM, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Wed, Sep 10, 2008 at 07:16:35AM +1200, Brent Wood wrote: Given the general increase in typical hardware specs these days, perhaps the default pg specs could be set for higher spec systems? Given the default shmem configuration on operating systems these days, upping the default will likely cause postgresql to not run at all. And it wont change the results much either. Changing shared_buffers is very nuanced and can help or hurt performance, but it isn't tuning in the sense it's a level you can pull to make the database 'go faster' like magic. A lot of the obsessing about shared_buffers resolves around the fact that remarkably few people understand how memory works in modern operating systems. The 'big ticket' .conf items are those that affect syncing in write heavy enviroments (fsync, etc) and planner affecting settings (work_mem, effective_cache_size). That said, PostgreSQL requires very little tuning outside of the obvious tradeoffs between speed and safety. This is an ongoing process...in the old days I had to agonize about dealing with the stats collector...in modern terms there is much less to 'trade off' (hopefully less still with 8.4). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 64-bit Compile Failure on Solaris 10 with OpenSSL
On Tue, September 9, 2008 5:25 am, Zdenek Kotala wrote: Randal T. Rioux napsal(a): I've battled this for a while. I'm finally breaking down and asking for help. If you're answer to this is why 64-bit then don't answer. You wouldn't understand. Same if you say why don't you use packages. Here is my scenerio: - Sun 420R x450Mhz UltraSPARC-II / 4GB RAM - Solaris 10 05/08 - OpenSSL 0.9.8h - PostgreSQL 8.3.3 - GCC 3.4.6 - GNU Make 3.81 Three questions (yeah, you forbided ask, but ...) grumble grumble grumble... 1) Why 64 64bit code on SPARC is slower, because SPARC uses 4byte instructions and processing 64bit data needs more instructions. It is good only if you need more then 4GB share memory. When you use sunstudio compiler with best optimization 64bit application has 1%-5% performance degradation. A. Many databases use more than 4GB share memory. B. Re: SunStudio - that's why I'm using GCC. 2) Why you don't use package You can use Solaris'es packages, which are integrated and optimized for Solaris. Which are bloated with stuff I don't need and missing stuff I do. Not to mention terribly outdated. 3) Why you don't use build-in libssl? Integrated libssl is not only copy of original open ssl. It has lot of improvements and it uses crypto hardware accelerator if you have it (for example Niagara 2). But it is 32-bit. Thanks, Randy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FW: How to upload data to postgres
On Tuesday 09 September 2008 1:54:12 pm Markova, Nina wrote: So far I tried; 1) I have copied data from Ingres in ASCII (using Ingres copydb command). 2) created a table in a Postgres database 3) tried loading data into Potgres table - encounter problems. For 1) (the Ingres part) = Ingres used the following copy commands: copy site( sta= varchar(0)tab, ondate= varchar(0)tab, offdate= varchar(0)tab, lat= c0tab, lon= c0tab, elev= c0tab, regist_code= varchar(0)tab, vault_cond= varchar(0)tab, geology= varchar(0)tab, comment= varchar(0)tab, initials= varchar(0)tab, lddate= c0nl, nl= d0nl) into '/tmp/site.dba' Normally Ingres will use this command to copy data from a file: copy site( sta= varchar(0)tab, ondate= varchar(0)tab, offdate= varchar(0)tab, lat= c0tab, lon= c0tab, elev= c0tab, regist_code= varchar(0)tab, vault_cond= varchar(0)tab, geology= varchar(0)tab, comment= varchar(0)tab, initials= varchar(0)tab, lddate= c0nl, nl= d0nl) from '/vm04-0/home/postgres/test/site.dba' For 3) = - I got error when I tried to copy with Ingres-like copy command. - Then I tried to copy with simple 'copy site from '/vm04-0/home/postgres/test/site-c.dba' - ERROR: value too long for type character varying(5) The ERROR explains it. The value you are bringing over from the Ingres database is to long for a varchar(5) field. Instead of rehashing the documentation I will point you to the relevant section that pertains to Postgres COPY: http://www.postgresql.org/docs/8.3/interactive/sql-copy.html - I had no luck either when used binary copying - postgres complained about signature: copy site from '/vm04-0/home/postgres/test/site.dba' with binary ERROR: COPY file signature not recognized I have couple of questions as well. Q1: is there an equivalent of copydb in postgres (in Ingres copydb creates copy statements for all database tables in a single file) See pg_dump: http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html Q2: how to say in postgres that a field has no default values (in Ingres 'not default' is used - and this produced an error in postgres CREATE TABLE command) The CREATE TABLE only takes a DEFAULT clause. If you want no default don't specify anything: lat float not null, Since you specified NOT NULL you will have to specify some value on INSERT. Create table site ( sta varchar(5) not null, ondate varchar(8) not null, offdate varchar(8) not null, lat float not null not default, - lon float not null not default ) Q3: How to specify storage structure of a table (again in Ingres 'modify' statement is used to specify btree, isam or hash structure). In the Postgres documentation I only saw how to create an index with a specific structure. As far as I know this cannot be done in Postgres. The only way you can modify the storage parameters is : Storage Parameters The WITH clause can specify storage parameters for tables, and for indexes associated with a UNIQUE or PRIMARY KEY constraint. Storage parameters for indexes are documented in CREATE INDEX. The only storage parameter currently available for tables is: FILLFACTOR The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This only applies to later versions of Postgres. In Ingres: modify site to isam unique on sta, ondate (means structure isam, primary key is on 2 fields - sta and ondate) Thanks in advance, Nina __ From: Markova, Nina Sent: September 9, 2008 14:32 To: pgsql-general@postgresql.org Subject:How to upload data to postgres Hi again, I need to load data from Ingres database to Postgres database. What's the easiest way? Thanks, Nina -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 64-bit Compile Failure on Solaris 10 with OpenSSL
On Mon, September 8, 2008 9:38 am, Randal T. Rioux wrote: On Mon, September 8, 2008 12:06 am, Tom Lane wrote: Randal T. Rioux [EMAIL PROTECTED] writes: bash-3.00# ldd /usr/local/ssl/lib/libssl.so ... libgcc_s.so.1 = (file not found) Smoke, meet gun ... Now why would libssl.so not be linked to libgcc_s.so.1? Why would PostgreSQL care and not Apache? Well, it is linked, but the question is whether the dynamic linker can find it. Different systems do this in different ways and I'm not real familiar with how Solaris does it. The ideal thing to my mind is to embed a linker search path in libssl.so so that its dependencies can be found reliably, but I am not sure whether or how Solaris can do that. It may be that the reason Apache works is that it sets LD_LIBRARY_PATH or LD_RUN_PATH or some such environment variable that the dynamic linker pays attention to. I can't say that I find that a reliable or secure way to fix it, though. Well, that would explain this: -bash-3.00$ /usr/local/ssl/bin/openssl ld.so.1: openssl: fatal: libgcc_s.so.1: open failed: No such file or directory Killed Apache compiled with it, but yes - I looked through the scripts and configure logs and the paths were set by it correctly. I will mess with it more tonight and report my findings. Again, thanks for listening to me think out loud :-) Found a kludgy fix! cp /usr/local/lib/sparcv9/libgcc_s.so.1 /usr/sfw/lib/sparcv9/ Now, both OpenSSL and PostgreSQL work great. In 64-bit mode. If anyone has a less hack-ish solution, please share. Thanks! Randy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] remove indexes on a column?
I'd like to write a SQL script, possibly with some PL/pgSQL, that can find all indexes on a column -- so I can remove them, and set up exactly the indexes I want. (I know what indexes are *supposed* to be there, but depending on the migration history of the specific instance, the names may vary.) I tried writing this logic using the system catalogs (pg_index, etc.), and it works up to a point. But when some of the indexes involve expressions, e.g. CREATE INDEX foo_lower_value ON foo(lower(value)); it's not so easy to do the lookup. In this case, the column index is coded deep in an expression string (in nodeToString() representation), and I don't see how to parse that. Alternatively, I could take the brute-force approach: - create a new column with the same type - copy the values from the old column to the new - drop the old column, presumably killing all the indices - rename the new column to the old name But that involves a lot of data copying, table restructuring, etc. Is there a good way to do this? Thanks, Vance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] remove indexes on a column?
Vance Maverick [EMAIL PROTECTED] writes: I'd like to write a SQL script, possibly with some PL/pgSQL, that can find all indexes on a column -- so I can remove them, and set up exactly the indexes I want. Yeah, this seems a bit tricky if you have expression indexes involving the column. I concur that trying to parse the expressions is a bad idea --- even if your code works today, it'll probably break in future PG releases, because the nodetree representation is not very stable. What I'd look for is pg_depend entries showing indexes that depend on the column. Here's a hint: regression=# create table foo (f1 int); CREATE TABLE regression=# create index fooi on foo (abs(f1)); CREATE INDEX regression=# select * from pg_depend where refobjid = 'foo'::regclass; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -++--++--+-+- 1247 | 534605 |0 | 1259 | 534603 | 0 | i 1259 | 534606 |0 | 1259 | 534603 | 1 | a (2 rows) regression=# select 534606::regclass; regclass -- fooi (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgUS Memberships and Board Nominations Now Open
Greetings! A couple of related announcements: - You can now use the following URL to become a member of the United States PostgreSQL Association (PgUS): https://www.postgresql.us/join Note the special combo rate for PgUS professional membership and PostgreSQL Conference West (October 10-12) registration; you can view a partial list of the West talks here: http://www.postgresqlconference.org/west08/talks/ - We are now accepting nominations for PgUS board for the October 2008 elections; please submit nominations to: [EMAIL PROTECTED] I will contact the nominees (to see if they accept the nomination) and report weekly to pgus-general the list of current nominees. Nominations will close on September 30th. Per our bylaws, you'll need to be a member of PgUS to vote. Please let me know if you have any questions; get those nominations in ASAP! Thanks--- ---Michael Brewer Secretary, PgUS [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] remove indexes on a column?
Perfect! Looks like I can get the names of the existing indexes by doing SELECT dep.relname FROM pg_attribute col, pg_class tab, pg_depend pd, pg_class dep WHERE tab.relname = 'mytable' AND col.attname = 'mycolumn' AND col.attrelid = tab.oid AND pd.refobjid = tab.oid AND pd.refobjsubid = col.attnum AND pd.objid = dep.oid AND dep.relkind = 'i'; Thanks. Vance On Wed, 2008-09-10 at 00:23 -0400, Tom Lane wrote: Vance Maverick [EMAIL PROTECTED] writes: I'd like to write a SQL script, possibly with some PL/pgSQL, that can find all indexes on a column -- so I can remove them, and set up exactly the indexes I want. Yeah, this seems a bit tricky if you have expression indexes involving the column. I concur that trying to parse the expressions is a bad idea --- even if your code works today, it'll probably break in future PG releases, because the nodetree representation is not very stable. What I'd look for is pg_depend entries showing indexes that depend on the column. Here's a hint: regression=# create table foo (f1 int); CREATE TABLE regression=# create index fooi on foo (abs(f1)); CREATE INDEX regression=# select * from pg_depend where refobjid = 'foo'::regclass; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype -++--++--+-+- 1247 | 534605 |0 | 1259 | 534603 | 0 | i 1259 | 534606 |0 | 1259 | 534603 | 1 | a (2 rows) regression=# select 534606::regclass; regclass -- fooi (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] remove indexes on a column?
Vance Maverick [EMAIL PROTECTED] writes: Perfect! Looks like I can get the names of the existing indexes by doing SELECT dep.relname FROM pg_attribute col, pg_class tab, pg_depend pd, pg_class dep WHERE tab.relname = 'mytable' AND col.attname = 'mycolumn' AND col.attrelid = tab.oid AND pd.refobjid = tab.oid AND pd.refobjsubid = col.attnum AND pd.objid = dep.oid AND dep.relkind = 'i'; Too tired/lazy to check right now, but you should also look into what the pg_depend representation is for constraints: I have a feeling that a unique or primary key constraint yields a pg_depend structure with an indirect linkage through a pg_constraint entry. Also, the above query doesn't seem very schema-safe: what if there are multiple tables named mytable? Personally I'd try something like tab.oid = 'mytable'::regclass instead of the relname test. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL process architecture question.
That's it, we have 4 CPUs, each of which has 4 cores, that is we have 16 cores in total, but we have only 4 to 8 concurrent users, who regularly run complex queries. That is we can't use all our CPU resources in such a situation to speed up response time. To: pgsql-general@postgresql.org From: [EMAIL PROTECTED] Subject: Re: [GENERAL] PostgreSQL process architecture question. Date: Tue, 9 Sep 2008 18:30:17 +0200 On Tue, 09 Sep 2008 10:07:32 -0600, Scott Marlowe wrote: On Tue, Sep 9, 2008 at 9:35 AM, Amber [EMAIL PROTECTED] wrote: We know PostgreSQL uses one dedicated server process to serve one client connection, what we want to know is whether PostgreSQL use multiple threads inside agents processes to take advantage of multiple CPUs. In our site we have only a few concurrent connections, so what occurs inside agent process is very important to us.No it doesn't. One connection gets one process which uses one CPU at a time. I understand the history/technical reasons/motivation for this, yet want to ask if anybody has thought about using OpenMP for careful parallelization of per-process work sections? Scanning large (e.g. already locked) arrays, parallel sweeps or calculatio ns might benefit from parallelizatoin without requiring a full-out threaded design. Such an approach could retain the per-process isolation model yet still reap multicore benefits. To boot OpenMP is pretty easy to use and comes with gcc. Since I don't know much about PG's internals and their data dependencies etc. this might well be a dumb idea, but I figured asking couldn't hurt. :) regards Holger-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
