Re: [GENERAL] Dangers of fsync = off
On Fri, May 04, 2007 at 08:54:10AM -0600, Joel Dice wrote: My next question is this: what are the dangers of turning fsync off in the context of a high-availablilty cluster using asynchronous replication? My real question is why you want to turn it off. If you're using a battery-backed cache on your disk controller, then fsync ought to be pretty close to free. Are you sure that turning it off will deliver the benefit you think it will? on Y. Thus, database corruption on X is irrelevant since our first step is to drop them. Not if the corruption introduces problems for replication, which is indeed possible. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Database performance comparison paper.
On Sat, Feb 17, 2007 at 12:02:08AM +0100, Leif B. Kristensen wrote: There ought to be a proper name for this kind of pseudo-technical Gonzo journalism. There is, but it's not the sort of word one uses in polite company ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL on Solaris: Changing Compilers During Point Upgrade
On Tue, Feb 06, 2007 at 09:43:01AM -0600, Thomas F. O'Connell wrote: DETAIL: Table has type character varying, but query expects character varying. In another thread, someone else is reporting this too. I'm wondering whether something went wrong in the 8.2.2 release. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Hardware
On Tue, Feb 06, 2007 at 10:59:21AM -0500, Walter Vaughan wrote: Is this still true in regards to Xeon's? I was looking at a server with Quad Core Xeon 2 5335 @ 2.0GHz. Multi-core Xeons are not as affected, and are somewhat different under the hood. So no, you're probably ok there. Are RAID 1 or 1+0 or 0+1 equal in speed, performance, downtime in regards to postgresql. Is it a coin toss? Well, 1 isn't equivalent to 1+0 or 0+1 in terms of capacity, because it's a straight mirror of two drives. I hate 0+1, because you lose half the array in the event any disk in the side fails. So I always use 1+0 if I can. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replication choices
On Thu, Jan 25, 2007 at 12:17:52PM -0800, Ben wrote: familiar with Slony, and from what I understand, using Slony with bad networks leads to bad problems. I'm also not sure that Slony supports replicating from multiple sources to the same postgres install, even if each replication process is writing to a different schema. Yes, you can have multiple origins into the same database, without a problem. I'd be worried for sure about the network unreliability, though. You might, however, be able to do this usefully using the log shipping features of Slony. I would _not_ worry about the outbound replication from the centre, assuming that the changes are infrequent. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] replication choices
On Wed, Jan 31, 2007 at 03:17:40PM -0800, Ben wrote: the remote sites back to the central site, each remote site needs to have a normal slony node first, which I don't have the hardware for. An answer for this, though a dirty kludge, is to replicate to another database in the same cluster. This is really no more load than the single replication user, although it is expensive at the disk level. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Compilation Error AIX
On Thu, Feb 01, 2007 at 10:10:57AM -0600, Hiltibidal, Robert wrote: Also, I see you're using the IBM make. Don't do that. You need to use gmake. # make -v GNU Make 3.80 Copyright (C) 2002 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. Ok, now what are you handing to ./configure? A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] DBMS Engines and Performance
On Wed, Jan 31, 2007 at 06:19:33AM -0800, Rich Shepard wrote: There is also ZohoCRM, which _is_ postgres based, but comes with version 8.0.something and no ability to use what we already have installed. That's another poor development decision. Why would I -- or anyone else -- want to intstall a second, older version of the dbms on their system, just to run a single application. This problem seems way easier to me to fix than the MySQL-to-Postgres port challenge. Not that the latter is hard, but it's time consuming, and you end up with (as I already mentioned) all sorts of ugly hairs that relate to MySQL features that are really just there to cover up missing pieces of implementation. (Or rather, used-to-be-missing. MySQL has come a long way in the past couple releases, no matter what anyone thinks of their marketing approach of FUD FUD FUD.) -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DBMS Engines and Performance
On Tue, Jan 30, 2007 at 11:36:52AM -0800, Rich Shepard wrote: However, what puzzles me is this statement: PostgreSQL has continued to fall behind other database engines in both performance and features, so I don't see compelling reason to work on it in my very limited free time. While the claim is utter bosh, what is probably true is that an application that has been designed exclusively to work well with MySQL will just not work very well with Postgres. That's my experience, in any case. Generally, when you get an application that was designed exclusively to work with MySQL, they're using all the furry little bits in MySQL that aren't really very SQL-like. Then, the port is commenced, and the developers discover that many of the MySQL tricks aren't really SQL at all, and that they have a bunch of new syntax to learn. Instead of regarding this as a failing of MySQL to implement consistently the standard SQL, such developers often regard their discovery of proof that only MySQL is a good product, that everything else is garbage, and that all those other systems should just be ignored. The alternative approach is that the developers try to come up with Yet Another Abstraction Layer, so that they can leave the code in their system that is there to deal with the huge numbers of historically missing features in MySQL. Even as MySQL has added some of those features, the application designers haven't caught up. The above is not universally true, but I have seen or read such sentiments often enough to realise that there are plenty of application developers who don't know anything about their database technology. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Can a database be restored successfully after new installation.
On Fri, Feb 02, 2007 at 11:27:38PM +0530, Rohit Prakash Khare wrote: If I have a backup of pgsql database and I take this dump file to another machine where postgresql is freshly installed with standard settings. Will I be able to restore the database? How did you backup? pg_dump? Sure, oughta work fine, assuming you have all the necessary support in your target system. (e.g. if you're using plpgsql, for instance, you need to have performed createlang for it, c.) A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Compilation Error AIX
On Thu, Feb 01, 2007 at 09:33:59AM -0600, Hiltibidal, Robert wrote: I am getting this error You really need to take questions about compiling to the -general list. I've put a Reply-To to that list, and have moved this discussion there. Also, it's really not a good idea to send emails to individuals whose addresses you've pulled from the lists. To begin with, what gcc version are you using? Also, I see you're using the IBM make. Don't do that. You need to use gmake. This instruction is in the documentation. I think you really need to read the docs. Regards, A make -C port all make[3]: Entering directory `/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend/port' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I../../../src/include -c -o dynloader.o dynloader.c In file included from /usr/include/xcoff.h:134, from /usr/include/a.out.h:42, from dynloader.c:10: /usr/local/include/dbug.h:38: error: syntax error before '_VARARGS' make[3]: *** [dynloader.o] Error 1 make[3]: Leaving directory `/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend/port' make[2]: *** [port-recursive] Error 2 make[2]: Leaving directory `/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend' make[1]: *** [all] Error 2 make[1]: Leaving directory `/db2/logs/downloads/postgres/postgresql-8.2.1/src' make: *** [all] Error 2 I found this reference on the IBM web site. The article says: GNU C Compiler (GCC) returns an error message if you try to #include varargs.h. Use stdarg.h instead. http://www-128.ibm.com/developerworks/eserver/articles/linux_s390/ Any ideas? Thanks! -Rob PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:[EMAIL PROTECTED] Thank you. -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] porting time calcs to PG
On Thu, Dec 07, 2006 at 04:44:35PM -0700, [EMAIL PROTECTED] wrote: fields. The WHERE clause that I use in SQL Server is: getdate() + ((2100 + 5 + (9*Points)) / 86400) = DueTime Where the numbers are actually parameters passed in to the function. Other than changine getdate() to now(), I'm not sure how to change the + interval to be effective. All the docs I see use something like interval '1 hour' - not sure how to put a calculated value in the quotes. Is this possible? Sure. Something like SELECT CURRENT_TIMESTAMP + (((2100 + 5 + (9*Points)) / 86400) || 'seconds')::interval = DueTime oughta work. -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] vacuum: out of memory error
On Fri, Nov 24, 2006 at 11:59:16AM +0100, Jakub Ouhrabka wrote: I've done little research in mailing list archives and I found possible cause: table corruption caused by flaky hardware. Does it sound about right? Are there any other possible causes? It sounds about right, yes; but the other possible cause is a software bug. In the absence of data proving you have no hardware problems, though, I think you'll find that people are singularly unwilling to investigate software bugs in this case. What can be corrupted? Anything. How can I check it? You can try stepping through the table in question and seeing if you run into problems anywhere. By binary search, you should be able to narrow it pretty quickly. How can I correct it? Well, the corrupt rows are lost. The usual method is restore from backup. What are possible consequences of this corruption? You can't read the data. But you already knew that: it's why your vacuum is blowing up. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] per-row security
On Mon, Nov 06, 2006 at 01:40:18PM -0800, Marc Munro wrote: You will of course be replicating the underlying tables and not the views, so your replication user will have to have full access to the unsecured data. This is natural and should not be a concern but may be worth explicitly documenting. In Slony, the replication user has to be a superuser anyway, so it would have access to that data no matter what. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Pgsql on Solaris
On Tue, Oct 31, 2006 at 10:43:55AM -0700, [EMAIL PROTECTED] wrote: How widespread is the use of PostgreSQL on Solaris? I am beginning Well, one of the core members (Josh Berkus) actually works for Sun, and Sun is officially supporting PostgreSQL in some capacity. Moreover, I ran our Postgres installations on Solaris for years, and would have continued to do so if I coulda got Sun to be nice to me as a customer. (There's more to that story, though, most of which I can't discuss.) I believe there are actually packaged binaries available for Solaris, but I always built my own using gcc. So It Worked For Me(tm) is about all I can tell you. I had to do a lot of web searching to learn how to get past a compilation problem, apparently fixed for the current release; and now It appears (based on the reply below) that the configure setup is not ready to recognize 64-bit solaris environment; the I don't see how my reply says anything of the sort. All you asked was how you did that, and I asked you what I think are reasonable questions, like do you have a compiler that produces 64 bit binaries? I certainly don't know any of that from what you've told me so far. Posting the exact error message you're getting might lead you to more productive responses from people using Solaris today. Or maybe you can use the packages, and you don't need to build it. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Replicating changes
On Fri, Oct 27, 2006 at 03:59:02PM +0200, Alban Hertroys wrote: 2.) Use one of the existing replication systems. We're currently under the impression that (we've looked at Slony-I particularly) the slave system is supposed to be another (postgresql?) database. This wouldn't fit our needs, but maybe we're overlooking something? Slony doesn't do this now, but it always seemed to me that it ought to be possible to do something like this, especially with the log-shipping components in Slony. If you wanted to develop an interface to another system in collaboration with the Slony developers, I'm sure people would welcome it. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] password cookie
On Thu, Oct 26, 2006 at 12:27:49AM +0200, Willy-Bas Loos wrote: or will not receive those, because of the rights granted to him. These granted rights and roles will be determined by the regular postgres functionality (and some views). Ah, that's a different matter. My suggestion is don't do that. I tried to do it once, years ago, and regretted it deeply. Of course, my code was awful, and yours might be better. But in my view, that's a security problem just waiting to happen. You're better off to have one user in your application that does the authentication for you. You can use Kerberos or something to authenticate it; much easier to lock down one such user carefully, that comes only from boxes under your control, than to secure many users' accounts. If you want to do it this way, I sure wouldn't use cookies to store the password. I think you're asking for a compromise that way. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Compiling ELF 64-bit on Solaris
On Thu, Oct 26, 2006 at 12:40:41PM -0700, [EMAIL PROTECTED] wrote: How can I get the make to generate ELF 64-bit executables on Solaris 10? We're on Fujitsu hardware; uname -a displays this: SunOS 5.10 Generic_118822-26 sun4us sparc FJSV,GPUZC-M Well, to start with, are you using a compiler that can generate 64 bit binaries? How about your libs? A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] benchmark suite
On Tue, Oct 24, 2006 at 12:57:08PM -0400, Ray Stell wrote: I find the following comment in dbt2 users guide: The test kit currently only supports SAP DB but work is currently being done to support PostgresSQL. In the source tree of dbt2-0.39 has the file ./README-POSTGRESQL. Is this the entry point doc that a postgresql user should start with to begin to use this product? I'm pretty sure that the docs are what are out of date here, if for no other reason that this is in the release notes: v0.38 - Various fixes with all scripts. - Improved configure.ac to use pg_config for PostgreSQL. - Updated PostgreSQL scripts to work with PostgreSQL 8.1 and newer. - Updated PostgreSQL stored functions for 8.1 and newer. I'd give it a whirl and see what happens; I haven't tried that one in a while. People who know may more about it than I do are around here, though, and probably can give you a better answer. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] problem with using O_DIRECT
On Thu, Oct 12, 2006 at 12:13:02AM -0400, Ye Qin wrote: psql: could not connect to server: Connection refused Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Any advice? Presumably, the server doesn't start? What does your logfile say? (Not every method works on every platform, and you might have run into an incompatible combination.) A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] List of supported 64bit OS
On Thu, Oct 12, 2006 at 01:46:53PM +0300, Stanislaw Tristan wrote: Thanks for the answer! We'll order a 2 x Opteron2xxx series (Dual Core) and the memory will be 16-32 Gb. This server is only for DB - non other services such as hosting, mail and so on. You didn't discuss your disc configuration or interface. That's going to be an important component. Think about it carefully. I'm not system integrator, but the project manager and interesting about: - existing the free OS that ideally supports hardware above in conjunction with PostgreSQL 8.x that will use dual core and big memory I would look at FreeBSD, myself. But look _very carefully_ at the hardware it actually supports before buying anything. People get burned all the time by buying hardware that is not well supported, and then having to go through contortions to make the operating system work well. Choose the OS first, then buy the hardware to suit, not the other way 'round. (You do this, effectively, when you buy Sun or IBM RS/6000 gear, don't forget.) A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath. --Damien Katz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] more anti-postgresql FUD
On Fri, Oct 13, 2006 at 01:35:51PM -0400, Tom Lane wrote: looked reasonably robust --- ie, both safe and not full of unsupportable assumptions about knowing exactly where everything actually is on the disk platter. It'd still be interesting if anyone gets a new idea... Might it be the case that WAL is the one area where, for Postgres, the cost of using raw disk could conceivably be worth the benefit? (I.e. you end up having to write a domain-specific filesystemish thing that is optimised for exactly your cases)? (And before you ask me, no I'm not volunteering :( ) A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Create Index on Date portion of timestamp
On Thu, Oct 12, 2006 at 06:40:22PM -0700, Niederland wrote: I am using postgresql 8.1.4. Is there anyway to create an index equivalent to: CREATE INDEX i1 ON t1 USING btree (ts::Date); So that indexes are used for queries when the field is cast to a date. I didn't try it, but you ought to be able to create a functional index on the to_date() of the column. I don't know if that will solve your cast issue, but you could rewrite the CAST into the to_date form to get around that. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] STABLE functions
On Thu, Oct 12, 2006 at 10:34:30AM -0400, Tom Lane wrote: catalogs. I can only recall people ever asking for this feature in connection with the user/group catalogs, so covering those might be enough in practice; that'd certainly be lots less invasive than trying to make it work everywhere. The Slony-I project has heard a lot of agitating for automatic support of DDL. As near as I can tell, that either requires triggers on system catalogs or else triggers on statements like CREATE TABLE, ALTER TABLE, c. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] more anti-postgresql FUD
On Tue, Oct 10, 2006 at 02:50:44PM -0400, Tom Lane wrote: Some days I think database independence is a myth. On the day when you don't, please tell me what application you found where it isn't. I want to buy the developers a drink. Or maybe a bar. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance and Generic Config after install
On Mon, Oct 02, 2006 at 02:40:03PM -0400, Oisin Glynn wrote: As an aside to the [GENERAL] Advantages of PostgreSQL thread going on today, I have wondered why the initial on install config of PostgreSQL is (according to most posts) very conservative. I can see how this Actually, that's a bit of a hangover in much the way the reputation of MySQL as having no support for ACID is: the last couple of releases of Postgres try to make at least some effort at estimating sane but safe basic configuration for the system when it's installed. That said, there is something of a problem in auto-configuring a cost-based planner and optimiser: some of the tuning is likely to be extremely sensitive to other things you're doing on the box, which means that you need to do a good, careful job for optimal performance. I often hear people complaining about this feature of Postgres in comparison to MySQL. But it's not a reasonable comparison, because MySQL basically uses a rule-based optimiser. And systems like DB2 and Oracle, that use a cost-based optimiser, are often far from perfect after a fresh install, too. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] benchmark suite
On Fri, Sep 29, 2006 at 07:27:49PM +0530, km wrote: Is there any good benchmark suite for testing postgresql performance? I suggest looking at the excellent software provided by OSDL. http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/ A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] benchmark suite
On Fri, Sep 29, 2006 at 07:58:01PM +0530, km wrote: ya i have tried the dbt1 (database test 1 - which is what i require) from the ODSL site but couldnt compile as i got the error: cache.c: In function `main': cache.c:134: error: `sname2' undeclared (first use in this function) Is this the very first indication of something being wrong? It sure looks to me like you're missing some headers. -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dead Lock problem with 8.1.3
On Wed, Sep 27, 2006 at 06:46:42PM +0200, Kai Hessing wrote: Deadlock means it hangs up and doesn't terminate through timeout. No, it doesn't. Deadlock means, for the two deadlocked queries, both cannot possibly finish because each waits on a lock that the other one holds. You can cause such deadlocks in your application, too, of course, but they're not database deadlocks. Also. . . There is no output. It just takes forever. . . .define forever. Is it doing any work? Do you see i/o? Is it in SELECT WAITING state? A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [HACKERS] PostgreSQL HA questions
First, I'm moving this to -general, because this is way off topic for -hackers as near as I can tell. On Tue, Sep 26, 2006 at 10:39:18PM +0200, Dragan Zubac wrote: 1. Is it possible for multiply PostgreSQL instances (engines,cores) to use same DATA space? No. In fact, this is a very good way to cause corruption. What you _can_ do is set up a watchdog process that allows a different machine to take over the filesystem on a shared disk array, for instance, and come back up in recovery mode. So your outage is roughly as long as the time to notice your primary node failed, plus the time to recover from database crash. There are various software packages that will allow you to do this. NOT ALL OF THEM WORK WELL. Go back and read that sentence again. No, I am not saying this because of any painful experiences I have ever had ;-) 2. Becouse of vaccuming issues,is it possible to create such a client process which will use two identical tables,and on receiving a signal,it will switch between those tables.For example,first a client application uses table1,after some time,send a signal to process,it will switch using table2,so You can freely vacuum table1 or whatsoever.After vacuuming done,table 1 will sinchronize with table2 and keep up-to-date until You send next signal to application,which will switch using table1,so You can vacuum table2. It isn't clear to me why you think you need to do this: vacuum doesn't block your queries anyway. If the idea is that you have a table that you'd rather TRUNCATE and not have to vacuum, however, that makes sense. There are several strategies for this. My colleague Chris Browne seems really to like this kind of functionality, and has discussed it more than once on the -general list. I think you can find his detailed outlines of how to do this sort of thing by searching for rotor tables. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Good books about PL/PGSQL programming?
On Thu, Sep 21, 2006 at 10:33:41AM +0200, [EMAIL PROTECTED] wrote: Are there any good and recommendable books about PL/PGSQL programming? I think the Douglas book is rather good for this. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Replication and PITR
On Fri, Sep 22, 2006 at 07:58:41AM +0200, Bo Lorentsen wrote: Interesting note ... do you know how fare PG would be from being able to be in read-only state when receiving PITR data ? Is it a complex problem or a simple one to solve ? I don't know that it's even possible. The PITR replica is essentially a node in permanent crash-recovery mode until it's caught up. Think of Oracle's comparable product -- you can't read from those replicas either. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replication and PITR
On Tue, Sep 26, 2006 at 08:21:44AM +0200, Bo Lorentsen wrote: seems like they have some kind of statement queue (no trigger setup) and a transfer protocol all integrated in the server, and that makes it simpel. There is no understanding regarding transactions, as far as I have seen. Note that, the last time I looked at it, there was no interlock to ensure that your statement queue (which is basically just a log of statements as executed on the master) was not accidentally blown away by your cleanup process before your target replicas were up to date. This might have improved recently, but when I looked at it MySQL's async replication was high on the ease of use and low on the works in sticky situations. As I say, they may have fixed it; but I advise people to look very carefully at how it works before deciding it is adequate. The important thing to remember about database replicas is that you're _already_ planning for the small percentage of cases where things break. Therefore, an 80/20 solution is not good enough: the thing has to work when most things have broken, or it's no use to you. That makes sense ... then the only thing to worry about is where these baches are written. On the same disk as the master database or on the client side, or will it be advisable to use a NFS mount between these to machines to balance the disk writing ? No. I suggest you have a look at the docs, and take these questions to the (again functioning) Slony list, where people can advise about that. The short answer is that the things to write are stored in the origin for the table (don't think of it as a database replica, because you can have different tables originating in different nodes). You can _also_ write sets out to disk, if you like. Someone (my colleagues, in fact) appear to have a nasty bug in that functionality that they can't nail down; nobody else has reproduced it. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] in failed sql transaction
On Mon, Sep 25, 2006 at 05:40:56PM +0530, Gurjeet Singh wrote: I sure like PG's following of the standards, but usability should not be lost sight of. One man's meal is another man's poison. For me, with a small number of exceptions, the standards conformance _is_ what makes PostgreSQL so usable. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql rising
On Wed, Sep 20, 2006 at 10:10:56AM -0500, Tony Caduto wrote: For a high level corp manager all they ever hear about is MS SQL Server, Oracle and DB2, and the more it costs the more they think it is what they need :-) I think that description is false. At a certain point in the management hierarchy, the only way anyone has the ability to evaluate something is on the basis of reputation. PostgreSQL is building its reputation, but it doesn't have the marketing budget of those three. Therefore, it's safer to pick the thing that has a better reputation, and that makes those reputations stronger still. So what we need is a spotless reputation -- which we're building. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Child program using parent program's transaction?
On Wed, Sep 06, 2006 at 03:21:04PM -0700, Wayne Conrad wrote: Today, it would once again be convenient to have an exec'd program do its work in the context of its parent program's transaction. So, before I once again decide that I don't actually want to do that, can you tell me... is it possible? And, would any sane person do it? This isn't exactly the same thing, but I've seen something similar done with threads. What I have to say about it is that it appears to be easy to do when you decide to do it, but it turns out to be hard to do well in practice. I cannot count the number of bugs I've come across because of poor handling of this sort of situation. In particular, in my experience, what you start to realise is that the handoff period is super ultra critical, and subject to all sorts of nasty race conditions; so you start locking things up in an effort to avoid the race. In no time at all, the whole application has ground to a halt while everything goes through this serialised global choke-point. It is at this point that you decide there's a reason the system doesn't do this sort of thing out of the box ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] On DNS for postgresql.org
On Wed, Sep 06, 2006 at 06:23:06PM -0700, Steve Atkins wrote: DNS clue might be relevant. We're not, though. Rather I'm saying that publicly criticizing people who volunteer services to a project, about things that are not related to the services they're providing is at best a little impolite. Actually, the real problem (as a couple people pointed out to me privately, for which I am thankful) is that I did it on the wrong list. But for the record: I wasn't trying to be critical; I was trying to solve a problem. If I appeared to be attacking anyone, I do apologise. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replace NULL values
On Thu, Sep 07, 2006 at 01:39:06PM +0200, Stefan Schwarzer wrote: You're right with the is versus = for NULL values. Unfortunately the coding for the mapserver does not allow an IS statement. There's a hack for this; you need to turn it on in the config file. I think it's called allow_null_equals. It's ugly, but it might solve this problem for you. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plz unsubscribe me
On Wed, Aug 30, 2006 at 02:11:11PM -0400, Tom Lane wrote: Actually, what I'd like to see done is to get majordomo to bounce list messages containing unsubscribe in the subject, with an explanatory message about the right way to unsubscribe. There's no reason the rest of us should be bothered. Or what about automatically unsubscribing at that point? (In spite of other ways it's awful, mailman does that rather well.) A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Estimate on gborg rescue?
Hi, I just checked, and gborg is still dead. In fact, the hostname is no longer valid (there's no A record). Do we have even an estimate for when it will be back? Can the estimator please publish that somewhere in big flashing letters or something? I can totally appreciate that this is a volunteer project, that people have other things happen to them, and that the project cannot always be the central issue in people's lives. Nevertheless, the gborg site has been down for a long time. We have argued for ages that there is no need to keep things in the core distribution because we have these seamless additional modular components that snap together to form a system. Not much of an argument when people can't even get to the sites supporting those additional components, because -- oops! -- the site doesn't even exist in DNS. On another note, these are listed as authorities for postgresql.org: postgresql.org. 7848IN NS ns-a.lerctr.org. postgresql.org. 7848IN NS ns-b.lerctr.org. But they're not responding with answers or even authority for them. So the general DNS is messed up too. If I can be of any assistance whatsoever, please feel free to hit me up. My office phone is +1 416 673 4110, in case someone needs it. I'm even on call this week, so you should be able to reach me more or less any time. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] On DNS for postgresql.org
Hi, Now that the DNS is back (thanks!), I thought I'd ask why the ra bit is set on the responses. Are those servers providing recursion to the whole Net? (They seem to be.) If so, that's a Bad Thing. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Estimate on gborg rescue?
On Wed, Sep 06, 2006 at 02:42:36PM +0200, Magnus Hagander wrote: Unable to get at Marc right now, but I believe these issues are much related. Hosts are dropping out of the hub.org nameservers one by one as well, probably as TTL expires. It just happens that the full zone has expired from lerctr.org by now, because for some reason the primary is broken. Do we need additional DNS hands? That happens to be a thing I know a little about. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] On DNS for postgresql.org
On Wed, Sep 06, 2006 at 09:59:29AM -0700, Steve Atkins wrote: There's not anything like universal agreement on whether that's a bad thing, or not. Uh, well, there sure is right now among TLD operators. Wide-open recursion is being used in a denial of service attack that causes orders-of-magnitude amplification traffic against the target servers. In fact, there are some who are blacklisting open recursive servers, and there's an effort afoot to get the news out: http://tools.ietf.org/wg/dnsop/draft-ietf-dnsop-reflectors-are-evil/ (Another draft is expected Real Soon Now, with a less-inflammatory filename.) Also the servers are volunteer provided, so it's not really anyones business other than the server owners. Given that the entire postgresql.org infrastructure just went off the air because of what sure looked to me like an error in administration, I submit that it _is_ others' business how the infrastructure is managed A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Estimate on gborg rescue?
On Wed, Sep 06, 2006 at 07:09:14AM -0700, Joshua D. Drake wrote: The exact same person physically responsible for the health of Gborg is also responisble for PostgreSQL.Org. This has nothing to do with a modular component argument. Well, it does at the moment, because gborg has been down forever whereas www.postgresql.org stayed up. Although now that the DNS is in serious panting mode, I think we may have a different set of issues. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] problem with postgres SQL db
On Thu, Aug 17, 2006 at 10:52:24PM +0200, Rodion Raskolnikov wrote: now i dont remember SYNTAX what we use when we export db. You need to use the _latest_ pg_dump. If you used the 7.2 pg_dump, it for sure won't work. There are functional changes between 7.2 and 8.1 that might make this upgrade a little tough. Test everything very carefully. It is possible that it will go more smoothly if you first upgrade to 7.4, and then dump from that to 8.1. I can't say for sure. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GPL Licensed Files in 8.1.4
On Wed, May 31, 2006 at 10:50:46AM -0400, Charles Comiskey wrote: PostgreSQL 8.1.4 appears to have 2 GPL licensed files according to licenses embedded in the source. In both cases, the files have had a I should like to point out (in addition to Peter E's comments) that these are both files in contrib/. Nothing in the main code tree is GPL, as far as I know, and nobody that I know of expects a complete PostgreSQL installation to include everything in contrib/ At the same time, it strikes me that at least the userlock stuff, and maybe dbmirror as well, are candidates for pgfoundry rather than contrib/ A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath. --Damien Katz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Backwards index scan
On Tue, Jun 06, 2006 at 12:27:33PM -0400, Carlos Oliva wrote: Thank for your response Alan. This indeed corrects the problem as long as we configure the database to enable_seqscan=false. If you have to do that, something is still wrong. Do you have accurate statistics? Is the planner mistaken about something? A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Making query without trigger
On Fri, May 26, 2006 at 12:02:44PM +0500, [EMAIL PROTECTED] wrote: I have trigger on updating the table. Sometimes i need to make queries without calling that trigger. How can I solve this? You need your function to decide whether the don't do anything conditions apply, and then not do anything. The trigger still fires, but it doesn't do anything. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Strange error while executing query from front end:
On Mon, Jan 16, 2006 at 09:27:30AM +0530, Mavinakuli, Prasanna (STSD) wrote: Hi, I am getting following strange errors while executing queries. 1) ERROR: xlog flush request 0/108EA5F8 is not satisfied --- flushed only to 0/1813C60 If I had to guess, I'd say you're having a hard disk failure of some kind. In particular, this tells you that a flush of the write ahead log isn't completing. That's a Bad Thing. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Plans for 8.2?
On Fri, Jan 13, 2006 at 10:39:06PM -, Greg Sabino Mullane wrote: I would presume that at least packaged with PG (in the contrib section) would be a good start. A prominent, east to find link to Slony on But in Slony's case, that'd be a regression, not an improvement. It is designed, on purpose, as a bolt-on. That's a feature, not a bug. It allows you to do version upgrades with just a few minutes' switchover time, to begin with, which is something that we don't otherwise have. If we want to fix the in the tarball, or it's not real, we need to continue to make packages easy to install. Nobody thinks that the DBI is some sort of stupid tacky not-ready tool just because every installation of Perl doesn't have it automatically. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] undefined behaviour for sub-transactions?
On Wed, Nov 30, 2005 at 02:58:15PM -0700, Michael Fuhr wrote: Shouldn't that be 8.0 and later? That's when savepoints were introduced. Or are you referring to something else? Doh. Indeed. I was _thinking_ os something else, but not referring to something else. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] undefined behaviour for sub-transactions?
On Tue, Nov 29, 2005 at 07:44:05PM +, Tim Bunce wrote: On Tue, Nov 29, 2005 at 10:50:01AM -0800, Tyler MacDonald wrote: PostgreSQL, doing a SELECT on a table that doesn't exist poisons the rest of the transaction, whereas under MySQL and SQLite2 the transaction is allowed to continue. PostgreSQL is non-standard (and inconvenient) in this respect. The inconvenience I'll grant, but the non-standard claim I think needs some justification. When the database encounters an error in a transaction, it is supposed to report an error. An error in a transaction causes the whole transaction to fail: that's what the atomicity rule of ACID means, I think. I actually am sort of unconvinced that SQLite's transactions are real ones -- I just did some playing around with it, and it seems that any error allows you to commit anyway. Certainly, MySQL's support of transactions is occasionally pretty dodgy, unless you use the strict mode. But it's worth knowing that in Pg 8.1 and later, you can wrap such things in a subtransaction and get out of it that way. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Rebranding PostgreSQL
On Wed, Nov 16, 2005 at 03:26:19PM -0800, Joshua D. Drake wrote: That's easy. The same reason people used to buy Mammoth PostgreSQL (not the replicator version). Well, yeah-no. Mammoth was coming from someone who was explicitly in the business of selling support for it, and was selling to people who already had picked PostgreSQL. But the OP was suggesting this was a way around the We don't use nuttin' but O-ra-cle 'round here crowd; and I don't see how Magic Blackbox Database is somehow better than Postgres to those people. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Strange slow database
On Fri, Nov 18, 2005 at 10:05:47AM +1300, David Mitchell wrote: Strangely, this slowdown is on all 12 servers. Perhaps this is a configuration issue? If anyone has any ideas we'd love to hear them. It sounds like a use-pattern issue. Did something in your use change? Any time you get repeatable change like that, look for the common changed factor. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cursors or Offset, Limit?
On Tue, Nov 15, 2005 at 01:44:32PM -0500, Jerry LeVan wrote: What are some of the tradeoffs between using a cursor and using the limit/offset method of selecting rows to display? OFFSET actually has to scan all the preceding rows every time (plus to get it consistently, you need to do an ORDER BY), so it's fast at first but possibly painfully slow in the last rows (especially on a large table). A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Rebranding PostgreSQL
On Wed, Nov 16, 2005 at 10:09:51AM -0800, [EMAIL PROTECTED] wrote: So, is it a little shady to want to slide PostgreSQL in under the radar? I'm simply trying to downplay what it is...it's my take that what they don't know won't hurt them. I appreciate what you're trying to do. At the same time, why do you think your customers will be more willing to go for John's Database than some community product called PostgreSQL? (And yes, I suspect there _are_ such people.) A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Replicating databases
On Wed, Nov 02, 2005 at 05:23:34PM -0600, Jim C. Nasby wrote: It seems kludgey this way, though. What you really need is multimaster with conflict resolution, because you can depend on your Isn't Slony2 supposed to do just that? Well, to the extent that slony 2 ever is going to do anything, our original aim was multimaster replication in a single data centre. Some folks have been working on various approaches that seem to be bearing real bitter fruit, though, and I don't know how hopeful I am these days for a general-purpose tool that will do that. It's certainly a laudable goal, though, and if people want to work on such a target, I'd sure like to know about it. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replicating databases
On Wed, Nov 02, 2005 at 03:44:26PM -0800, Marc Munro wrote: experts there may suggest a better solution. I have seen talk of disabling the standard slony triggers to allow this sort of thing but whether that is more or less nasty is questionable. FWIW, I don't think that's the question; it's more like whether it'd be merely horribly nasty or likely to break in unexpected and really painful ways. ;-) But the discussion around that surely should move to the Slony list. -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replicating databases
On Thu, Nov 03, 2005 at 08:16:01AM -0800, codeWarrior wrote: It doesnt sound to me like replication is the right answer to this problem... You are setting yourself up to try and defeat one of the major purposes of a database in a client-server system -- namely -- centralized storage. While I have a certain amount of sympathy for this view, it's often the case that centralised storage isn't quite what you want. After all, if always-fast is more important than always-right, we prefer caches and such like. DNS is the obvious example there. And if always-works is more important than always-fast or always-right, then you have a very powerful incentive to keep things local. That said, this case does sort of sound like money might be better spent on improved communications that a humungous amount of work to Rube up a Goldberg for getting all the data in every store. But maybe we don't have the whole picture: maybe communications links aren't stable in some of these stores, and can't be made so economically. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Replicating databases
On Wed, Nov 02, 2005 at 12:06:36PM +, Carlos Benkendorf wrote: I would appreciate suggestions about how the best way to implement such soluction. Slony-1? SQL scripts? Maybe a combination. My natural inclination would be to try to do this with some tricky views+rules so that each store could write into its own table (then everybody could replicate, and in fact you could have the other store data updated, but maybe not as fast as real time). The problem is that in the central database, this is going to turn out to be a big, nasty UNION if there are more than a handful of stores. But, you could do this with some batch processing in the night at each store, such that you pulled local data into a special local table (into which you'd write, depending on your local store id) and the non-local table. Again, you could use a view with rules to allow writing into these local tables. Then during the batch processing at night, you could merge all these changes together, and prepare special sets to push out to the stores so that they could see everyone else's day old data. It seems kludgey this way, though. What you really need is multimaster with conflict resolution, because you can depend on your application to cause no conflicts. Slony is designed to prevent you from writing into the replicated tables. Some of the other master-slave ones don't have that restriction, but they're sort of dangerous for the same reason. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to cluster Postgresql
On Fri, Oct 21, 2005 at 04:01:12PM +, Franck Coppola wrote: would be interested too : i don't feel very confident with slony). Why don't you? (The Slony developers would like to know, I think.) A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
On Wed, Oct 19, 2005 at 01:02:15PM -0300, Marc G. Fournier wrote: that idiocy is that a string with two blank characters is not equal to a string with a single blank character in Oracle. 'a ' is not equal to 'a '. 'a ' is not equal to 'a'. Port that to another database. Seen the JOIN syntax? *sigh* Wait, I've lost something here, apparently ... but that is the case with PostgreSQL as well: ams=# select ' a' = ' a'; Well, you didn't pick the same example, because leading blanks are significant in the char() datatype: andrewtest=# SELECT 'a '::char='a'::char; ?column? -- t (1 ligne) But is it the case that Oracle doesn't treat that one any differently from this: andrewtest=# SELECT 'a'||NULL::char='a'::char; ?column? -- (1 ligne) If that's the case, it's pretty odd. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Select all invalid e-mail addresses
On Thu, Oct 20, 2005 at 06:10:40PM +0300, Andrus wrote: From this thread I got the regular expression [snipped] Note that that regular expression, which appears to be validating TLDs as well, is incredibly fragile. John Klensin has actually written an RFC about this very problem. Among other problems, what do you do when a country code ceases to be? (There's a similar problem that the naming bodies struggke with from time to time.) I suggest that if you want to validate TLDs, you pull them off when you write the data in your database, and use a lookup table to make sure they're valid (you can keep the table up to date regularly by checking the official IANA registry for them). At least that way you don't have to change a regex every time ICANN decides to add another TLD. (The regex is wrong anyway, I think: it doesn't have .mobi, which has been announced although isn't taking registrations yet, and it doesn't appear to have arpa, either.) A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Select all invalid e-mail addresses
On Thu, Oct 20, 2005 at 11:22:25AM -0400, Brian Mathis wrote: That's why I think the better term for this is well formed. Validity can only be determined by sending to it, but you can tell if an address at least In fact, it can only be determined by sending to it over and over again, because whether a mail address works may change over time (and may have nothing to do with the poor schmuck whose email administrators don't know how to spell MX record). A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Select all invalid e-mail addresses
On Thu, Oct 20, 2005 at 09:54:13PM +0300, Andrus wrote: How to write a WHERE clause which selects e-mail addresses which are surely wrong ? Then I think the validating function someone else sent here (http://www.databasejournal.com/img/email_val.sql) is a good start. You probably want the opposite behaviour -- emailinvalidate(), I guess -- but that seems like a good obviously wrong tester. It might not be fast, though -- that loop at the special character check looks pretty painful. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select all invalid e-mail addresses
On Thu, Oct 20, 2005 at 12:34:39PM -0700, Steve Atkins wrote: While there are valid deliverable email addresses in .arpa, you really don't want to be accepting them from end users... You know, as someone who has been bitten hundreds of times by the decision of some application designer who thought s/he knew better than I what my email address could possibly be, I respectfully submit that you're mistaken. We call it a bug when other databases accept dates like '-00-00'; but we'd just as surely call it a bug if PostgreSQL refused to accept valid leap year dates or leap seconds. It's one thing to say you should not accept known-bad data; it's quite another to refuse data that is improbable but nevertheless perfectly good. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Where to concentrate (was: [pgsql-advocacy] [GENERAL] Oracle buys Innobase)
I think this probably belongs back on -advocacy, so I'm cc:ing there so we can move it. On Tue, Oct 18, 2005 at 03:16:23PM -0700, Chris Travers wrote: Interesting. So they are willing to appear ill-informed in public but better informed in private? To what end? That seems strange to me To the end of dismissing the serious-but-free competition in public. If Oracle is talking to the computer press, they have enough experience to know just how much they can play with stating the way the world is, and have it quoted verbatim as revealed truth. Apart from database weenies like us, people reading the Oracle pronouncement conflating PostgreSQL and other database systems will just think it's true. After all, Oracle said it, and the press guy from InfoWorld must have checked it out, right? If you think I'm being unduly cynical, note that the Gartner comments in their consulting for ICANN in the .org reassignment basically argued that PostgreSQL was a significant risk because it wasn't Oracle. There's nothing _wrong_ with that way of thinking -- corporations are mostly about stability, which means following conventional (==safe) wisdom. But that mindset is something that Oracle is skilled at exploiting, and I'm not surprised they do it against PostgreSQL (even if their behaviour sounds irrational to someone who really knows the capabilities of the various systems). But that isn't really why I replied to this :) This is one reason why I would like to see some of us push PostgreSQL into a role of *the* RDBMS to study for RDBMS theory. Unfortunately this means a lot of documentation written by experts interested in really teaching beginners the right way to do things I don't consider myself qualified to do this by myself. I like this idea. I wonder how to get it moving. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] On multi-master
On Sat, Oct 15, 2005 at 06:04:54PM -0700, Chris Travers wrote: Out of curiosity, what is wrong with requiring client SSL certs to access the system and only issuing them to the PGPool system (or using a different CA if you need to issue client certs to the end users)? This Hmm, I like this, although client SSL certs still didn't work with JDBC last I checked, so it won't solve all the problems. But you're right, this would mostly solve the problem I was thinking of, provided it was described correctly to the (mostly-clueless) technology rule-producers. place (though deliberate circumvention is always an issue when both sides are open source and the DBA has access to all systems-- after all, Open source has nothing to do with it, of course. Malicious attack by technical staff is something virtually no technology can guarantee against. The best you can usually get is adequate logging (and probably log monitoring) -- and we already provide that. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] On multi-master
On Sat, Oct 15, 2005 at 05:58:20PM -0700, Chris Travers wrote: Repeat after me... Data integrity, like security, is a process, not a product. I understand that. The problem is not that _I_ don't understand it, but that the market for real, industrial multi-master replication is so far relatively small, and it looks like the market for RAC. Which means that one has to present the complete package to people who don't know what a Postgres is, and don't want to. It is _of course_ not impossible to provide for this. But one needs canned, this is standard ways to do these things, or else the people who are in a position to authorise Postgres use instead of something else are not going to do it. They don't want to be guinea pigs, and they can afford not to be. (Note that I'm not suggesting this is really a technical problem. It's just a social problem to which we have to present technology.) The sad truth of the matter is that if you want to alter the social circumstances in favour of some new, unconventional approach, your unconventional approach has to be _better than_ the existing convention, not merely as good as. And in the market we're talking about cheaper is not the main consideration for better than. I think other arguments are useful -- access to source (and therefore auditability) is an obvious one -- but one needs to establish a well-known set of practices around these things if one wishes to be taken seriously for this kind of application. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
On Mon, Oct 17, 2005 at 11:18:19AM -0700, Chris Travers wrote: I.e. I see no evidence that Oracle is taking the PostgreSQL threat seriously, and the FUD campaign is more evidence that they don't (there are plenty of areas where Oracle has an edge over PostgreSQL-- the idea that PostgreSQL doesn't support transactions can only indicate that this was a cursory and hasty attack and maybe even a wakeup call for them, or maybe they got us mixed up with MySQL w/MyISAM). The real question is whether after the .org campaign occurred, we are now a higher-profile target that is taken more seriously. Personally, I would doubt it for reasons mentioned below. (I'm using this as an example of more than one such comment in this thread). The claim in the .org bid response (which can be found at http://forum.icann.org/org-eval/gartner-report/msg0.html) starts like this: PostgreSQL, like many other open source database products, has been in the market for many years with very little adoption. Unlike the open-source operating system market, the open-source database market has been unsuccessful due to the complexity of customer requirements and sophistication of the technology needed. PostgreSQL is used primarily in the embedded system market because it lacks the transactional features, high availability, security and manageability of any commercial enterprise database. Note the slide at the beginning of that from PostgreSQL to open source database products. That trick is consistent with several other things I've seen from Oracle, including Ellison, on this topic. The idea is to lump everything into the open source class, and then attack the technically weakest member of that class. It's good rhetoric, so I don't think anyone should believe, for a second, that this is some kind of know-nothing answer from Oracle. It's a good strategy. I'll also note that I've spoken to people inside IBM's DB2 division who have, as part of their job, keeping tabs on PostgreSQL. This is why I think we should avoid worrying about MySQL: it gives others an opportunity to lump us into the open source pile, and dismiss the whole thing on the basis of the missing features in MySQL. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] On multi-master
On Tue, Oct 18, 2005 at 04:37:23PM +0100, Alex Stapleton wrote: Release a cheaper / free alternative and people will use it because they will have almost no reason not to. This means that cheaper and as good as does have a place in the market even if it's not a conventional solution. It just needs evidence and evangelism. The current market should not be the principal target. I agree with this; but I'm always concerned about something that's _almost_ as good as the competition, but not quite there, being pointed at as being as good as the competition. That way lies an invitation to the point-and-laugh responses that MySQL's so-called cluster system has garnered: it's too dangerous to use for many systems where the data is important enough, because the failure mode is near-complete catastrophe. See another thread, where I talk about the strategy some companies may be using of lumping PostgreSQL in with other products, and then attacking the other product. Irrelevance may be fallacious, but it makes for depressingly successful marketing. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
On Tue, Oct 18, 2005 at 01:19:53PM -0700, Chris Travers wrote: Ok. but it is still a lazy approach and indicates that Oracle has not singled us out for special treatment. Again, this was not the case with MySQL as of 2000 at the latest. I may be more paranoid, but that may be because our use of PostgreSQL was real unpopular in the original Oracle shop where the registry software was developed (the technical side of Afilias was originally called Liberty RMS, and was a subsidiary of TUCOWS. I was hired originally by them. Afilias bought Liberty not long after the .info registry went live, however, and we've always been a better fit here than we were at TUCOWS). I do know, however, that Oracle doesn't publicly talk about PostgreSQL, but they have plenty to say in private about it to their existing customers. And it's not nearly as ill-informed as the public comments suggest. I think it is important to eventually capture the image of PostgreSQL as *the* FOSS RDBMS (which MySQL currently still holds among too many developers). But that is the extent of my concern with them. Sure. But if you build a reputation as an industrial-strength system that happens to be free, you can go after the FOSS area without much additional effort; whereas if you concentrate first on being free, you then have the later problem of moving from free to enterprise grade. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] On multi-master
On Fri, Oct 14, 2005 at 07:48:00AM +0900, Tatsuo Ishii wrote: Why pgpool should bother? pgpool supposes every transaction should go through pgpool. Your example sounds like someone logs into M2 and tries to shut down it. But because there's no enforcement of every transaction should go through pgpool, it's not enough for the managers who are ultimately responsible for deciding on system design. In the hypothetical case, we're aiming at multimaster systems that are there for reliability, not performance. Decreasing the reliance on fault-tolerant hardware by increasing the potential for human error does not solve that problem. I don't know what you kind of problem you are talking about, but... If you find problems, please post it to pgpool-general and let's solve it. That's the open source way. We have been (my colleague Brad is the one who's been working on this). But for something to qualify for real production-grade use, it needs to be rock solid stable in heavy use for a considerable period of time. We're not there yet, is all I'm suggesting. (This principle is why it's also a good thing that Red Hat Enterprise isn't always completely up to date with the community sources.) A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] On multi-master
On Fri, Oct 14, 2005 at 11:54:19PM +0900, Tatsuo Ishii wrote: Enforcement? There would be plenty of ways to achieve that. For example, you could set pg_hba.conf so that on ly the host where pgpool is running on could connect to the host where postmaster is running on. That just changes the problem to someone logging in from that host. (This isn't a theoretical problem, by the way; it's an objection that I've heard from people.) Right. It's your freedom that you do not use pgpool until you think it's solid enough. And my managers approve it :) I should note, for the record, that I'm extremely impressed with pgpool. I just think we have some room to grow before we can say we have something to really compete with the commercial multimaster systems. a -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] On multi-master
On Fri, Oct 14, 2005 at 10:20:41AM -0500, Scott Marlowe wrote: USers accessing machines behind the scenes is a VERY bad idea. It's not a pgpool bug, is a user bug. :) The problem with this glib answer is that we are talking about systems where such a user bug can cost people millions of dollars. They want the _machine_ to prevent the user bug. That's what they think they're buying, and my understanding is that some of the other systems provide greater protection. Remember, a five-nines system means five minutes of downtime, all told, per year. People who really need that are willing to pay for it, because it's worth it. Most of the time, it isn't, and most so called five-nines systems really aren't. (There is no way you could really claim reliable five nines performance on the in-memory-only MySQL system, for instance: it'd be too risky, unless you could guarantee you'd never exceed your memory. Who's willing to guarantee the data set won't grow unexpectedly?) That said, using pgpool for higher-reliability, we-checked-it-real- good systems isn't a bad idea; on the contrary. Just let's not pretend it's something that it isn't really. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] On multi-master
On Sat, Oct 15, 2005 at 01:33:22AM +0900, Tatsuo Ishii wrote: BTW, the reason why I myself stick with pgpool is there's no perfect or acceptable replication solution for PostgreSQL (please do not talk about RAC or MySQL Cluster. I hate them:-). And that's part of why we're looking at it, too. Slony-I has a target, but it's not this one. and has truly high-avilabilty). Maybe Slony-II is one of the hope, but I have no idea how the performance is... Well, since it doesn't exist except in prototype yet, I think the performance is pretty bad :-) A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] On multi-master
On Fri, Oct 14, 2005 at 11:16:36AM -0500, Scott Marlowe wrote: You're users shouldn't be able to do that. If they can, you've set up your system wrong. Only the DBA should have access to that machine. And DBAs aren't users? Oftentimes, a big goal is to protect against operator error. DBAs who are called to resolve a problem at 2 AM are _exactly_ the people we're protecting against. No, you can never completely lock down a system to protect against root doing 'rm -rf /'. But you can make it harder, and this approach doesn't do that well enough to be able to advertise that you can't muck with the system by accident. (Note that erserver had this problem, too; it's one of the things we were at some pains to prevent in Slony-I. We didn't get it perfect, though, and there are gaps in that system as a result. It'd be even more dangerous in a multimaster system.) And again, this is not to say there are any flies on pgpool. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [pgsql-advocacy] Oracle buys Innobase
On Fri, Oct 14, 2005 at 01:02:00PM -0700, Ron Mayer wrote: I'd suspect that any single postgresql-support company that had a similar customer list would get offers from Oracle as well PostgreSQL support companies don't have the leverage that Oracle and MySQL do to get their clients to come out of the closet. There _are_ such customer lists, but the license for PostgreSQL doesn't entail that those customers be used as marketing fodder. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cluster/redundancy question
On Tue, Oct 11, 2005 at 11:38:22AM -0500, Scott Marlowe wrote: Don't get me wrong, if replication is one of the things you need, then consider it, but if you're putting bad data into your database, what good is replicating it gonna do ya? But if real, ORAC-style clustering is what you need, buy Oracle. The limitations on MySQL's implementation give me the willies. As Tom Waits said, The large print giveth, and the small print taketh away. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Considering an upgrade...
On Wed, Oct 12, 2005 at 06:15:36PM -0400, Robert Treat wrote: IIRC Slony wont run on anything 7.3.6, so you would have to do an in place upgrade to 7.3.x as appropriate, and then you could set up slony to propogate to the 8.x system. You should do that anyway, because on older releases, you really don't want to lag the last dot-release -- there are usually some big bugs that have been shaken out. One thing that is really nice about using the Slony trick, by the way, is that when you switchover to your upgraded system, you make your old database into a replica too. That means that you have the ability to roll back to the old release if, once you get the new system into production use, you happen to find something you'd overlooked. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
On multi-master (was: [GENERAL] Limitations of PostgreSQL)
On Thu, Oct 13, 2005 at 10:46:29AM -0500, Scott Marlowe wrote: choice in another. So, multi-master replication isn't likely to become a plug in module for postgresql any time soon. It's not even a thing, so it can't become a plug-in. Consider just two kinds of multi-master: 1. Oracle's RAC. This is a shared-disk, engine-failover kind of multi-master. It provides a certain amount of scaling, but nothing I've seen or heard suggests that the license cost couldn't just as easily and effectively be thrown at larger hardware for better scaling. The really big reason to use RAC is five-nines situations: you're trying to make sure that even unlikely failures of your machines never cause the database to stop working (for suitably lawyer-understood values of stop. RAC remastering is not a zero-cost, nor even invisible, operation. But from an application perspective, it can be made to look like database is slow as opposed to database crashed). 2. Disconnected sales forces with local copies of some portion of the sales database. This is completely distributed database use, with potential for conflicts and an associated need for conflict resolution strategies. These are different sorts of systems addressing completely different use cases. But they're both potentially marketed as multi-master. Often, a manager asking for multi-master thinks s/he is buying all of this; which desire is probably impossible to satisfy with one piece of software (as opposed to one thing all called by the same name by the marketing department). A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question about stored procedures
On Thu, Oct 13, 2005 at 03:00:32PM +0200, Stephane Bortzmeyer wrote: Note that a common trick, when you want to do X and you cannot do it directly from PostgreSQL (or are unwilling to force the sysadmin to install stuff like plWhatever - for instance, I was never able to make plPython run on my NetBSD machines), is to put data in a table and to have an auxiliary daemon which connects to the database and read in the table what it must do. From the point of view of security, that's also a good idea. If you have a bug in your trusted function, and it can write on the filesystem, and somebody can manage to get their string to be passed into your function, then they maybe can inject things like rm -r $PGDATA. Which would hurt. That isn't to say, Never do this. It's just to say that you have a real dangerous tool there in your hand, so don't be waving it about carelessly. A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cluster/redundancy question
On Thu, Oct 13, 2005 at 10:06:51AM -0600, Aly S.P Dharshi wrote: Andrew, I disagree, I wouldn't want to contend with all the complexities and kludge of Oracle thank you very much. If there was a way to get PostgreSQL to do better than the current clustering methods, then why not, it would be a big win for us. I'm not saying we _shouldn't_ go after such functionality (I have someone reporting to me at work who is in fact doing so). I'm saying that if you want that functionality today, you can buy it from one place, and that's Oracle. Answers that rely on pretty-good, mostly works, most of the time, if you use the right table handlers always and make sure that nobody inserts dates like '2005-02-30', do not qualify as a place to buy it from, for the record. And if pretty close is a good enough answer for you, you don't need this complex technology at all. You can use async systems in most cases. A PS -- I think MySQL has plenty of good features. It's a fine product, loads better than it was in the old days. But the misfeature of different storage engines, none of which actually achieves all the other features, is an administration mistake waiting to happen. It's what really bothers me about their clustered offering. Others might make a different trade-off. Me, I don't like to be in water over my head when I'm awakened in the middle of the night. -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] On multi-master
On Thu, Oct 13, 2005 at 10:53:51AM -0700, Chris Travers wrote: Now, what about PgPool as a multimaster sync replication solution? Sure it is statement level But is there any reason why you cannot have multiple PgPool instances running against a number of DB servers? Well, to begin with, you have a serious race condition: pgpool begins T1 on M1 and M2. Someone logs into M2 and does some work in T2. M1 completes the work of T1. M2 completes the work of T2. pgpool issues COMMIT. M1 replies with the COMMIT. M2 detects a deadlock when T2 tries to COMMIT. Now what? There's nothing to prevent this in the system, as near as I can see, so it's just not bullet proof enough for the cases where people really, really need only five minutes of down time a year. If you _really_ needed that, you'd be willing (and able) to pay the costs. Of course, we can do what we can to make those costs go down. :- But they're not that low yet. Also, there is still (or was last I checked) a limitation on the number of machines pgpool could address, and there are some stability and reliability issues we've seen. It's a great piece of code, don't get me wrong; but it's not stable enough yet to bet millions of dollars on. ObNit: ORAC isn't really synchronous; it just looks that way. This is multimaster async replication. But it can be further broken down into four types: Sure; I think you could break it even smaller sub-types, if you worked at it, too. For example, an async system that tolerates farily brief interruptions in two-way communications is very different from the one where your sales force (or your Palm) shows up after a week and dumps a whole bunch of new conflicts on your lap. This second case is something Slony wouldn't tolerate; but I think a relatively-high availability would probably work with some multi-way conflict resolution, if someone were willing to build it. That wasn't the itch Afilias needed scratching, because of the kinds of problems we have to solve (to begin with, exactly one person may be the registrant of record of a domain name at any one time, so conflict resolution is just not allowed in our problem set: we have to maintain global uniqueness). But we did have some discussions about how one might file the corners of the hole to make it square enough for the peg. I think it's possible, if someone volunteers to do the work (maybe in a sub-project, maybe as a co-operative project). I don't have the problem, so I can't justify the staff time. So if someone _else_ has the problem, maybe s/he can. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] On multi-master
On Thu, Oct 13, 2005 at 02:30:11PM -0400, Tom Lane wrote: This particular issue is fixable as of 8.1: pgpool should be using 2-phase commit. Sure, but if we're talking about what people can put in production today, I don't think 8.1 + pgpool with 2-phase commit is on the list. (We got started down this road, I think, because someone was wanting a multi-master system right away.) But I think it will be soon. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] On multi-master
On Thu, Oct 13, 2005 at 12:01:19PM -0700, Chris Travers wrote: Stages 1 and 4 could be handled by Slony, while 2 and 3 would require custom triggers. In essence this is really master/slave that appears multimaster. You will have tradeoffs here in granularity of conflict resolution versus performance. This sounds very similar to an idea Josh Berkus sketched for me while getting off a train at OSCON. So I bet I didn't get all the details :) But I think it's likely worth pursuing. Why would Slony have to do your conflict resolution at all? Why not just use it to replicate update logs and data sets, and leave conflict resolution to custom triggers? Conflict resolution is really going to That would probably work. The real thing Slony can't stand is really long periods of disconnection. You might be able to hack the log shipping approach to help there, though. In any case, it's probably a subject for the Slony lists, once a full-ish proposal is ready. Well, I want to thank Afilias for such a useful tool. Again, if we all Afilias didn't exactly contribute it -- it was always intended to be a community project, and we just contributed some seeds (read Jan and later Chris) to get it growing. That seems to have worked -- I think there is now as much contribution from non-Afilias folks as Afilias folks, and I think Slony is better for it. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Mon, Oct 10, 2005 at 09:20:47AM -0600, Michael Fuhr wrote: project. I hope their employers appreciate what they've got. Well, I can tell you that Afilias does. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] License question[VASCL:A1077160A86]
On Wed, Oct 05, 2005 at 05:34:25PM -0500, Jim C. Nasby wrote: Yes, because libpg.so is licensed under the BSD license. Note that you can do this in a COPYRIGHT file. It just has to be in all copies, whatever that means. AFAIK, this would only apply if he was actually distributing libpq.so, which would be a bad thing for technical reasons anyway. Well, yes, except I suppose I sort of thought it was going to be linked statically or something -- how do you rely on your users having the library installed? But now that I think about it, I suppose this is really a question prompted by TheirDB's decision to understand derivative program in a mighty extended way. I seem to be echoing Emily a lot these days. Never mind. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 10:30:26AM -0500, Scott Marlowe wrote: Information Schema: MySQL's support of this looks fairly extensive. But PostgreSQL's is pretty good, too, last I looked. Instance Manager: Uniquely MySQL. It allows things like starting and stopping the database remotely. What does Instance Manager buy you that ssh doesn't? (For bonus points, what does ssh get you that Instance Manager doesn't? Hint: I have a Symbian UIQ phone. Google for PuTTY.) XA Transactions: MySQL's are pretty primitive, and PostgreSQL's XA may not be much further ahead there. XA transactions need some form of management for partial transactions. MySQL's answer here was to just refuse to commit on any member if any other member failed to be prepared for commit. This is possibly the least useful implementation of XA there could be, as the primary reason I've seen for it is to allow an application to have n db servers, and to kick one out if it starts misbehaving and run on the remaining n-1 servers. Note that right now, PostgreSQL's XA has, as far as I know, no real conflict management. But I'm guessing PostgreSQL will have a better fleshed out XA interface before MySQL. Well, to be fair, one of the Open Group's XA targets is actual distributed data sets, and not just reliability through redundancy. So MySQL's implementation appears to be enough to support the former in some ways. What seems more troublesome to me is that if a machine fails after the PREPARE step succeds, and then the client disconnects, the transaction is automatically rolled back and can't be recovered. I haven't figured out yet whether this is merely dodgy, or an outright violation of the spec. A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 01:19:33PM -0600, Aly S.P Dharshi wrote: Okay that is a fair statement to make, hence restating: - What is the status of those items listed on the PostgreSQL gotchas Some of them are for sure gotchas. For users of tranditionally-non-toy (as opposed to recently-non-toy) systems, I'd think that there are three serious issues: the column alias (because it strictly violates the SQL definition), the folding to lower case (same reason), and the UNICODE==UTF-8 issue (because it violates the UNICODE definition). - Are they valid statements ? - If they are bugs are they resolved ? Some of them are valid and resolved (the site actually notes as much in many cases). One thing I do note is that there's a workaround for the have-to-dump issue: you can use Slony to upgrade (by design). It won't work for everyone, but it's a big step up in many cases. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] License question[VASCL:A1077160A86]
On Wed, Oct 05, 2005 at 09:49:06PM +1000, Neil Dugan wrote: If I was to develop a 'C' project that only used the libpg.so library and the rest was my own stuff would I need to preserve the copyright to somehow? Yes, because libpg.so is licensed under the BSD license. Note that you can do this in a COPYRIGHT file. It just has to be in all copies, whatever that means. People are actually slightly oversimplifying, because when you distribute you also have to distribute two paragraphs. The license is available, among other places, from this URL: http://www.postgresql.org/about/licence It has _got_ to be the easiest piece of legalese you'll ever encounter. Read it, and do what it says. There's no long preamble about rights; there's no provision that you've accepted it just by having installed the software that was necessary to read the EULA; there's no provision that breaking the Magic Plastic Wrap has donated your 1st born to the fires below; there's no provision that, even though you just paid a million dollars, you can't get your money back if it doesn't work. But it does say that, if you use the software, you have to warn your users somehow that some of the code is written by someone else, and that it's not UC's fault if it doesn't work (so you can't sue them). A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql replication
On Thu, Aug 25, 2005 at 01:44:15PM +0200, Bohdan Linda wrote: there are some other db solutions which have good performance when doing this kind of replication across the world. Bluntly, No. -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgresql replication
On Wed, Aug 24, 2005 at 11:54:42AM -0400, Chris Browne wrote: There is a Slony-II project ongoing that is trying to construct a more-or-less synchronous multimaster replication system (where part of the cleverness involves trying to get as much taking place in an asynchronous fashion as possible) that would almost certainly be of no use to your use case. Just to emphasise this point: assuming we ever get Slony-II to work, it is all but guaranteed to be useless for cases like the one that started this thread: it'll simply require very fast network connections to work. I've had more than one person ask me when multi-site multimaster is coming, and my answer is always, Have you started work on it yet? I think there might be a way to hack up Slony-I to do it -- Josh Berkus gave me a quick outline while at OSCON that made me think it possible -- but AFAIK, nobody is actually doing that work. It's worth noting that single-origin master-slave async replication is tricky, but by no means impossible. Multi-master _anything_ is hard, no question about it; and it more or less always imposes some overhead that you won't like. The question is merely whether you want to pay that price. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] WHERE
On Mon, May 09, 2005 at 12:58:06PM -0700, Dann Corbit wrote: Temp tables go away after the transaction completes. Connection, actually, no? A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql replication
On Thu, May 05, 2005 at 03:21:40PM +0100, Peter Wilson wrote: Why is this documentation on 'Christoper B. Browns homepage rather than the Slony web pages? The 'official' Slony documentation I had available was at : http://gborg.postgresql.org/project/slony1/genpage.php?howto_idx and it *really* didn't help with the problems I had. I think it's already been acknowledged that the 1.0.5 release's documentation wasn't as comprehensive as people liked; but that is, as has also been noted, because nobody had run into the problems you had yet (i.e. the documentation was as complete at release time as the users had been able to make it). Nevertheless, it is not true that the documentation isn't on the slony pages. By my count, including the header bar, the admin guide is the 10th link on the page. It's near the top. These updated docs are also in the currently-beta version of the software. (I don't want to get into a flamewar over this; I just think it's unfair to claim the docs aren't there.) Having now taken a look at the documentation you reference, it's still not wonderfully comprehensive. The problem I had was that despite the fact that my tables had primary keys the Slony configuration refused to recognise them. The documentation says simply that primary or candidate primary keys are a requirements. This is also not true. The documentation for SET ADD TABLE tells you that there is a KEY option to tell slonik what the key field is. Automatic detection won't always work, because not all primary keys are so noted in a PostgreSQL database. What is worth mentioning, though, is that Slony is not as simple to set up as some of the alternatives, like dbmirror. This is because items like controlled switchover, failover, cascading, target promotion, and log shipping are all designed-in features of Slony. The same is not true of dbmirror, or erserver. My experience tells me that such features are very hard to add in retrospect. Indeed, the very reason Afilias sponsored development of Slony was that we concluded the features we wanted could be more easily built from the ground up than added to any of the alternatives we then had available. (This isn't to say everybody should use Slony; just that it's important to realise that additional features come at the cost of additional complexity. My bet is that anyone really needing high availability will end up needing some of those additional features.) A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slony v. DBMirror
On Fri, May 06, 2005 at 09:01:58AM -0400, Jeff - wrote: slave and hten fire up dbmirror. Although it might work if you install the dbmirror triggers, then dump restore. It's a little trickier than that, but yes, it might work. Somewhere in my mail archives, I have a very angry rant about this problem and erserver (the current community version, approximately). I looked at dbmirror, and it took me no time to conclude it had the same issues. It wouldn't be a problem for everyone. Slony uses the COPY interface to read/load data. This is the same method used by pg_dump so the only throttle will be the network. Not quite, because your schema needs to be complete on the target system (in particular, you need your unique keys to stay, although you can get rid of some other indexes to speed things up). A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Slony v. DBMirror
On Thu, May 05, 2005 at 03:35:27PM +0100, Peter Wilson wrote: Looking at Slony now, can someone tell me what the benefits of Slony are over DBmirror? As far as I can see: + both are async Master-multiple slaves + both (I think) can do cascaded replication This isn't really true of dbmirror. You can do it, but if you lose the intermediate system, you also lose the cascaded ones (the downstream one doesn't know about the state of the top-level origin). Slony is designed to cover that case (it was one of several criteria we had to satisfy). + neither replicate schema changes This is sort of false, too. Slony has a facility for injecting the schema changes at just the right spot in the replication sets, so that you really can push your schema changes through Slony. (This isn't to say you should regularly do this -- it's designed for production systems, where schema changes should be relatively rare.) + nether do automatic switch-over Any async replication system which routinely does automatic _fail_ over is, in my opinion, a product not suited for production. This is a decision that generally needs to be made at Layer 9 or so -- when you kill a data source, you are potentially walking away from data. (Naturally, some special cases will call for such fail over anyway. It's possible to set up Slony for this, of course, just dangerous. You'd need some external scripts, but they're not impossible to build. There's been a recent discussion of this topic on the slony lists.) Slony _does_ have pretty good facilities for controlled switch over (as well as a good mechanism for fail over, where you abandon the old origin). With the right arrangements with pgpool, you ought to be able to do a controlled switch over of a data origin without a client outage. This is part of the system by design. That design turns out to be harder than you'd think. Slony also replicates sequences, has extensive guards against data loss, and can deal with triggers that need to be working or not depending on the current use of a table in a set. The data loss problem due to unreliable nodes is quite a bit nastier than it might seem. The problem isn't just to replicate. The problem is to replicate in a provably reliable way. All slony seems to offer is a different configuration system and the ability to automatically propogate configuration changes. It seems this could be added to DBmirror pretty easily so why a whole new project? We looked at the options when we launched the project, believe me. At the time, we were using erserver, the development of which we'd also subsidized (starting in 2001). We learned a lot from the troubles we had with that system (some of which were addressed in what is the current commercial erserver code), and the result was that we concluded we could not backport several of the features we wanted into either erserver or dbmirror (aside from the frustrating-but-mostly-works original Java engine in the first-released community erserver, there isn't much to distinguish dbmirror and the community erserver). If you want to see the results of our investigation, and (by inference) the criteria we used to decide what would satisfy our requirements, you can see Jan's concept paper; it's at http://developer.postgresql.org/~wieck/slony1/Slony-I-concept.pdf. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Slony v. DBMirror
On Fri, May 06, 2005 at 05:42:38PM +0100, Peter Wilson wrote: I got the lack of schema changes from the Slony documentation. This seems odd. I see Brad already told you what to look for; but putting schema changes in through slonik was _always_ part of the design. What's always been true (and what will forever remain so, by design) is that you can't simply issue ALTER TABLE n commands on the origin for table n. FYI DBmirror with Postgres version 8 also replicates sequences. Oh, this is interesting. Glad to hear it. Can it guarantee that a sequence won't roll back in some really bad case, if you fail over to the target? Figuring out how to do that was one of Jan's homework projects, IIRC. ;-) A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql replication
On Wed, May 04, 2005 at 10:06:03PM -0400, Vlad wrote: so at the point any of slony and pgcluster works for me, but before I start messing with any, I wanted to hear real users opninon about those (or different) packages :) Well, Afilias (my employer) sponsored/sponsors Slony development. We use it for the .info and .org top level domains, among other systems. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] do I need replication or something else?
On Tue, Mar 29, 2005 at 04:06:57PM -0600, Caleb Simonyi-Gindele wrote: Yes, we use it successfully with the SQL Server edition of our product. Does anyone know if this is available with Postgre? Caleb Out of the box, the answer is, No. It is not an insurmountable problem, however, and I can think of a nifty way to attempt this with the currently-beta Slony-I software. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 8: explain analyze is your friend
