Re: [GENERAL] Male/female
While this thread is tangentially interesting, due to the magic of relational relationships, the point is really moot. If you are really worried about various gender states in the future, just create a table called "gender" and reference it and update it as necessary- done. -M ---(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] DBI module for postgres 1.4.3
On Dec 7, 2006, at 6:00 , Albe Laurenz wrote: Jasbinder Singh Bali wrote: Whats the difference between a module and a bundle as i can see while downloading DBI from CPAN website. What exactly needs to be downloaded . I'm kind of not sure about it. I have never heard of a 'bundle' in context with Perl but that may be because I know little about Perl. DBI can be got from http://www.cpan.org/modules/by-module/DBI/DBI-1.53.tar.gz and DBD-Pg is on http://www.cpan.org/modules/by-module/DBD/DBD-Pg-1.49.tar.gz A "bundle" is merely a collection of modules that are often used together. For example, installing "Bundle::DBI" installs a bunch of DBI drivers and tools. http://search.cpan.org/~timb/DBI-1.53/lib/Bundle/DBI.pm -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] database name aliases?
On Nov 6, 2006, at 19:29 , Reece Hart wrote: On Tue, 2006-11-07 at 04:54 +0500, Talha Khan wrote: You can do this by using a script. I mean whenever you are creating a new version of the database. you can do that by running a script that creates a database and then stores the name of that database in an environmental variable . Now you can run psql by connecting to a database through that variable. I think I understand the gist of your proposal, but psql was just one client example. In principle, I'd want the same alias to be exposed to psql, perl DBI, odbc, jdbc, etc. I really think this should be done in the database itself to ensure consistency. I partition production, testing, development areas by using schemas. Using ALTER SCHEMA X RENAME TO Y, it's trivial to shuffle around the names as you like.Cheers,M
Re: [GENERAL] PostgreSQL in an embedded platform
On Nov 6, 2006, at 5:59 , Ben wrote: Hello Folks, i've been a postgresql user for a lot of years, mainly using it on common x86 hw; now for a personal project of mine i wish to know your opinion on porting it on an embedded platform. I'm currently using PostgreSQL in my home automation project (x86 platform) not for storing huge amount of data but for handling events (with triggers and SP), use an high level programming language (PgSQL with some custom functions), handling data and states (with integrity) and so on. So this is a small DB, not so much data on it, a combination of Cron, Apache/PHP, PostgreSQL and some custom programming can handle it all, i rely mostly on PgSQL for high level customization despite of its original scope; strange application isn't it ? PostgreSQL is the core system, it's informed on everything happens on real environment and it does what it need to do. I'm currently evaluating to port the whole platform into an embedded system, i didn't chosen it yet but i'm looking for at something related to Linksys APs hw ports, maybe some fancy hw like that. Cron and PHP are portable and available, i can replace Apache with some light HTTPD, but what about PostgreSQL ? Is there anyone using it on small non x86 hw ? limited resources (let's say you got a compressed linux kernel and software in less than 8-4Mb ROM) and fully featured with triggers, SPs and so on ? I know mostly of you are using it in a massive storage cluster or HA system, but i greatly appreciated this DB even for its internals (Object Oriented, Stored Procedures, SQL custom Programming, triggers, integrity and so on), these are the main features i choose it instead of MySQL and others I have asked about this in the past. However, your machine is clearly much smaller than mine. We have a 1U network appliance with 256 MB RAM and a flash backing store. We currently use MySQL over a RAM fs that syncs every 10 minutes (!). I would much rather use postgresql and of course it works over the RAM fs, but we lose the point of using postgresql- reliability. I'm pretty sure that your box is simply too small. For starters, postgresql likes to start with a 16 MB WAL. That's not to say that it's impossible to run on your machine- all of the assumptions postgresql makes about a "minimal" setup can be further reduced, but what you end up with is neither reliable nor speedy. My opinion is that trying to get postgresql to run under your embedded system constraints would be a waste of time. You might try SQLite. Unfortunately, it is missing some features which we would need- such as foreign keys (!) and full alter table support. http://sqlite.org/omitted.html Good luck with your project! -M ---(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] UNICODE and UTF-8
On Nov 4, 2006, at 11:34 , Martijn van Oosterhout wrote: On Sat, Nov 04, 2006 at 04:23:02PM +0100, Alain Roger wrote: however, when i do this, my encoding is in UTF-8 via phpAdmin. UTF8 is a part of UNICODE, but as i'm not sure on how many bits is UNICODE, how can i setup my local DB to UNICODE value as my provider has ? As far a postgres is concerned, UTF8 is UNICODE. IIRC some versions said one name, some the other, but they mean the same thing. So maybe you have a different version than your provider? Somewhere around release 8, the encoding "UTF8" was made to mean what "UNICODE" meant before. "Unicode" is not an encoding so "UTF-8" is the proper terminology. Cheers, M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is there anyway to...
On Nov 2, 2006, at 15:00 , Richard Troy wrote: On Thu, 2 Nov 2006, AgentM wrote: Just some commentary... This is exactly the sort of thing cron is for. Duplicating that functionality in the RDBMS would be silly IMO. I don't see why you could consider cron to be "dirty" for this application... I actually tried to come up with something for this. There are plenty of good reasons to have some timer functionality in the database: 1) it makes regular database-oriented tasks OS portable 2) your cron user needs specific permissions + authorization to access the database whereas postgres could handle "sudo"-like behavior transparently 3) there are triggers other than time that could be handy- on vacuum, on db start, on db quit, on NOTIFY Unfortunately, the limitation I came across was for 2). There is no way to use "set session authorization" or "set role" safely because the wrapped code could always exit from the sandbox. So my timer only works for db superusers. -M ...This type of need is exactly what custom written daemons are for. They're surely database and OS portable (or can be, at least), there's no need for any super-user capability of any kind, you can use any kind of trigger you like, and there's no permission leakage problem, either... I guess all you need is functioning nohup capability (which Windows systems may have trouble with, I don't know). Sure- I wrote a custom daemon. But it has general usefulness. Instead of ten clients listening on ten notifications (and holding open connections for little reason), I would like to have one connection handle all the notification events- based on which notification or timer event, it could call a different stored procedure with different roles. That way, I wouldn't need one connection open for ever user that needs to listen and react. That simply doesn't scale. Cheers, M ---(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] Is there anyway to...
On Nov 2, 2006, at 14:02 , Glen Parker wrote: louis gonzales wrote: Hey Brian, Yeah I had considered this, using cron, I just feel like that is too dirty. Actually I didn't see Andreas' post, can someone forward that? I'm running this application on Solaris 9. Ultimately what I want to know is, is there something that is internal to postgresql that can be used that doesn't need external action, to make it do some task? Some built in function that can be set to do some simple task on a daily - or other time - interval, where all of the defined users may not have any activity with the database for day's or week's at a time, but this builtin function still operates? Am I making any sense with how I'm asking this? I could of course have cron do a scheduled task of checking/incrementing/ decrementing and define triggers to occur when one of the cron delivered actions sets the appropriate trigger off, but are there other methods that are standard in the industry or are we stuck with this type of external influence? Just some commentary... This is exactly the sort of thing cron is for. Duplicating that functionality in the RDBMS would be silly IMO. I don't see why you could consider cron to be "dirty" for this application... I actually tried to come up with something for this. There are plenty of good reasons to have some timer functionality in the database: 1) it makes regular database-oriented tasks OS portable 2) your cron user needs specific permissions + authorization to access the database whereas postgres could handle "sudo"-like behavior transparently 3) there are triggers other than time that could be handy- on vacuum, on db start, on db quit, on NOTIFY Unfortunately, the limitation I came across was for 2). There is no way to use "set session authorization" or "set role" safely because the wrapped code could always exit from the sandbox. So my timer only works for db superusers. -M ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] timestamp as primary key?
On Oct 19, 2006, at 10:30 , John D. Burger wrote: cckramer wrote: I have table for online chat system that keep messages sent between users. Question: is it okay to use timestamp as primary key, or there is possibility of collision? (Meaning two processes may INSERT into table within same millisecond.) It is a web application. tometzky wrote: If your insert fail you can always try again after some random short time. But then the timestamp field does not accurately represent the actual time of the event. If you really want a primary key, and you really don't want to just use a sequence default, I would make the key a composite: PRIMARY KEY (user_id_from, user_id_to, message_time) This should cut way down on the possibility of key collision. Only if each message is contained in its own transaction since now() is effectively a constant throughout a transaction. In this case, I would choose a surrogate key since it is likely that the table will be referenced. -M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A query planner that learns
On Oct 17, 2006, at 10:46 , Madison Kelly wrote: Brian Mathis wrote: I also am NAL, but I know enough about the patent system (in the US) to know that ignorance *IS* a defense. If you are ignorant of the patent, you only have to pay the damages. If you knew about the patent and did it anyway, you have to pay *triple* damages. Ignorance will save you lots of money. You may not like it, but that's the way it is. I got that part. :) If you _do_ end up in court, plausible deniability helps. My position though is that it is better, in the long term, to be aware of the patents and take the time to work around them so that *no* damages need to be paid. Or, as might be that chance in this case, to get a written "okay" from the patent holder for the use of the methods protected by the patent in a given program. Colour me funny, but wouldn't staying out of the courts in the first place not be the best option? That would be a nice, but naïve, approach. It is likely that (without admitting any guilt by any party) postgresql already steps on some patents. In fact, any project you can think of likely steps on some patents. There are patents on network communication: the "getting a message from a server to client" sort of thing. If you spent the next twenty years searching through patents and creating patches for postgresql to circumvent the patents, not only would you turn postgresql into a shriveled raisin of its current self, you would be exposing postgresql to greater damages than if you had never looked at the patents. ***Please- for the safety of everyone on these lists- do not mention or link to any specific patents. This mailing list has a publicly- accessible archive which could be used against me or you (at least in the USA).*** Best regards, M ---(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] A query planner that learns
On Oct 16, 2006, at 16:17 , Madison Kelly wrote: Alvaro Herrera wrote: Jochem van Dieten wrote: Scott Marlowe wrote: While all the talk of a hinting system over in hackers and perform is good, and I have a few queries that could live with a simple hint system pop up now and again, I keep thinking that a query planner that learns >from its mistakes over time is far more desirable. Is it reasonable or possible for the system to have a way to look at query plans it's run and look for obvious mistakes its made, like being off by a factor of 10 or more in estimations, and slowly learn to apply its own hints? Technically it is very feasible. But I think you might want to check US Patent 6,763,359 before you start writing any code. I think it would be a very good idea if you guys stopped looking at the US patent database. It does no good to anyone. There's no way we can avoid stomping on a patent or another -- there are patents for everything. Hasn't IBM release a pile of it's patents for use (or at least stated they won't sue) to OSS projects? If so, is this patent covered by that "amnesty"? Simply ignoring patents because "there is a patent for everything" is a recipe for disaster. Companies like MS are running out of ways to tear open OSS and they are certainly not above (below?) suing the heck out of OSS projects for patent infringement. What's needed is reform in the USPO. Call you congress (wo)man and complain, but don't flaunt the law; you will lose. Alvaro's advice is sound. If the patent holder can prove that a developer looked at a patent (for example, from an email in a mailing list archive) and the project proceeded with the implementation regardless, malice can been shown and "damages" can be substantially higher. You're screwed either way but your safest bet is to never look at patents. Disclaimer: I am not a lawyer- I don't even like lawyers. -M ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] more anti-postgresql FUD
On Oct 13, 2006, at 14:36 , Joshua D. Drake wrote: Stephen Frost wrote: * Alexander Staubo ([EMAIL PROTECTED]) wrote: What formula did you use to get to that number? Is there a generic way on Linux to turn off (controller-based?) write caching? Just a side-note, but if you've got a pretty good expectation that you won't be without power for 24 consecutive hours ever you can get a controller with a battery-backed write cache (some will do better than 24 hours too). For the performance concerned... :) No to mention if you are *that* concerned you could buy a generator for 500 bucks that will keep the machine alive if you absolutely have to. There is nothing wrong with write back cache as long as you have the infrastructure to support it. Why does the battery have to be at that level? It's seems like a reasonable poor man's solution would be to have a standard $50 UPS plugged in and have the UPS signal postgresql to shut down and sync. Then, theoretically, it would be safe to run with fsync=off. The level of risk seems the same no? -M ---(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] A query planner that learns
On Oct 13, 2006, at 11:47 , John D. Burger wrote: Erik Jones wrote: Forgive me if I'm way off here as I'm not all that familiar with the internals of postgres, but isn't this what the genetic query optimizer discussed the one of the manual's appendixes is supposed to do. No - it's not an "optimizer" in that sense. When there are a small enough set of tables involved, the planner uses a dynamic programming algorithm to explore the entire space of all possible plans. But the space grows exponentially (I think) with the number of tables - when this would take too long, the planner switches to a genetic algorithm approach, which explores a small fraction of the plan space, in a guided manner. But with both approaches, the planner is just using the static statistics gathered by ANALYZE to estimate the cost of each candidate plan, and these statistics are based on sampling your data - they may be wrong, or at least misleading. (In particular, the statistic for total number of unique values is frequently =way= off, per a recent thread here. I have been reading about this, idly thinking about how to improve the estimate.) The idea of a learning planner, I suppose, would be one that examines cases where these statistics lead to very misguided expectations. The simplest version of a "learning" planner could simply bump up the statistics targets on certain columns. A slightly more sophisticated idea would be for some of the statistics to optionally use parametric modeling (this column is a Gaussian, let's estimate the mean and variance, this one is a Beta distribution ...). Then the smarter planner could spend some cycles applying more sophisticated statistical modeling to problematic tables/columns. One simple first step would be to run an ANALYZE whenever a sequential scan is executed. Is there a reason not to do this? It could be controlled by a GUC variable in case someone wants repeatable plans. Further down the line, statistics could be collected during the execution of any query- updating histograms on delete and update, as well. -M ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [PERFORM] Postgre 8.0 Installation - Issues
On Oct 10, 2006, at 10:34 , Bill Moran wrote: I had an almost-gf once... Me too! -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Database Transfer between machines(again)
On Oct 5, 2006, at 19:10 , Brian J. Erickson wrote: And since it's a text file, can't someone fix it with $EDITOR? I tried to edit the file, but I get the Input/Output error. The recommendatation was to re-install the OS. However, I DO NOT want to lose my database, so I am tring to backup the database. You can boot from any rescue CD, mount the partition, copy the database directory away and then copy it back once you have reinstalled. This is safe because it is on the same machine. It is not safe to copy the database to some arbitrary computer and expect it to run. Make sure to match the database version. Good luck! -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql rising
On Sep 19, 2006, at 23:57 , Merlin Moncure wrote: I have seen a steady progressive rise in the number of postgresql related jobs and the quality of those jobs. Major companies are apparently rolling out critical infrastructure on postgresql...Vonage is one example: (http://jobsearch.monster.com/getjob.asp?JobID=47975237&AVSDM=2006% 2D09%2D15+13%3A07%3A10&Logo=1&JobTitle=PostgreSQL+Databa%2E%2E% 2E&q=postgresql&cy=us&JSNONREG=1&Image1.x=0&Image1.y=0&dcjvlid=380). Salaries for a capable pg dba are really attractive, I have seen several in the 6 figure range. If you are reading this list and you like making money, this is amazing news folks. I am seeing a confluence of many factors leading to serious penetration into the enterprise market. Around 5 years ago after being mostly a c/c++ developer I decided postgresql was where it was at. Learning the database and becoming productive with it has been professionally rewarding on many levels. It's really exciting watching the community evolve. I have noticed the same. One thing you didn't mention is how postgresql gets into such companies. I highly doubt there is a new general managerial acceptance of postgresql itself- I haven't had any of my management mention it from management magazines- rather it seems to be a grassroots effort by developers who started out using a free LAMP stack, know the benefits, and then bring that experience to the workplace. -M ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL slammed by PHP creator
On Sep 14, 2006, at 21:25 , Bruce Momjian wrote: I am not going to ask how you got to 135MPH. Obviously he was running MySQL under the hood. -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL slammed by PHP creator
On Sep 14, 2006, at 11:27 , Arturo Perez wrote: Hi all, Any response to this: http://www.internetnews.com/dev-news/article.php/3631831 From the FA: One performance enhancement that Lerdorf suggested based on code analysis was to use MySQL instead of PostgreSQL for the database. "If you can fit your problem into what MySQL can handle it's very fast," Lerdorf said. "You can gain quite a bit of performance." For the items that MySQL doesn't handle as well as PostgreSQL, Lerdorf noted that some features can be emulated in PHP itself, and you still end up with a net performance boost. I wasn't able to find anything the "article" worth discussing. If you give up A, C, I, and D, of course you get better performance- just like you can get better performance from a wheel-less Yugo if you slide it down a luge track. -M ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql mentioned on Newsforge MySQL article
On Aug 30, 2006, at 16:11 , Martijn van Oosterhout wrote: On Wed, Aug 30, 2006 at 01:54:56PM -0400, Robert Treat wrote: Seems they are fast where it counts... namely getting a team of developers on top of the contest so they come out shining. Too bad people in this community don't see the value of paying people to do advocacy related development. I don't remember this ever being mentioned on the lists? I never heard of it. Maybe if it had been mentioned here it would've got more attention? It's true there isn't a group here looking for competitions to enter... Perhaps because PostgreSQL is too busy competing with itself? Is anyone aware of any other open-source project that has added so many new features in the past year? -M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL on system with root as only user
On Aug 30, 2006, at 13:58 , Brandon Aiken wrote: I haven't got any numbers (or a USB stick I can test with at the moment) but USB is going to be a bottleneck for read and write performance. Unless you're accessing very small amounts of data or running small queries, I would expect performance to be pretty poor. Indeed, that's pretty much our scenario- a rack-mounted Linux box storing mostly inconsequential data (auth creds) with a flash drive- quasi-embedded, I guess. If your data set is so small, why do you need a full RDBMS instead of flat data/text files or SQLite? If you're not concerned about disk media failure, why do you need a transactional DB? It seems like putting a deadbolt on a screen door. MySQL and SQLite have terrible concurrent performance with transactions. We would also like to take advantage of database-level replication instead of our fragile home-grown thing for load-sharing and failover. It might work just great for your device, of course, but I would not expect it to scale well at all. There are some things I can do- for example, fsync should probably just be off. I guess I should simply go ahead and try it. Thanks for the discussion! -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL on system with root as only user
On Aug 30, 2006, at 12:03 , Brandon Aiken wrote: Unless it's a read-only database, I would never recommend using flash media for an RDBMS. Well, it's an embedded device, so you can pretty much be certain that it's not storing sales information. If the flash goes belly up, then the device has failed anyway, so the lost info is the least of the problems. Unless it's a small database, I would never recommend using USB as a storage interface for an RDBMS. Why? Could you provide more details? I would be interested in any performance numbers anyone has collected. -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL on system with root as only user
On Aug 30, 2006, at 9:01 , Chris Mair wrote: If you know what you're doing and you want to disable that feature you need to recompile from source and disable the uid checks in src/backend/main/main.c. Unless you're working in the embedded space or some such thing, I don't think it's a good idea, anyway. Has anyone actually used PostgreSQL on an embedded system? I am genuinely curious. How about db performance from a flash drive? -M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] counting days
Perhaps you are trying to count business days? Those vary around the world and you would certainly need a table to hold the holidays and such. If you just want to count the number of non-weekend-days, then get the interval as days and then it's a simple matter of math: 2*daysInterval/7+(daysInterval % 7 == 6 ? 1 : 0) This holds assuming you are using dates and not timestamps- you may wish to subtract 1 depending on what the dates represent. -M On Aug 29, 2006, at 14:35 , garry saddington wrote: I need to count the days between two dates that are not saturdays or sundays. I have read the manual and searched the lists but I am struggling. I can count the days but am finding difficulty excluding sat and sun from the count. I need this without reference to any tables. Does anyone have any pointers please. Regards ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SQL:2003 Window Functions for postgresql 8.3?
On Aug 24, 2006, at 14:11 , Alvaro Herrera wrote: Karen Hill wrote: It would be really great if PostgreSQL supported SQL:2003 Window functions. I know that oracle and sql server have them already, so it would make postgres competitive in that area. I know there is a feature freeze for 8.2, is it doable for 8.3? The sooner you start writing a patch, the sooner you will be done ;-) I agree it would be nice to have them, but currently I don't think there's anyone working on'em. Could someone elaborate on the window functions? This page http:// en.wikipedia.org/wiki/SELECT has some examples but they make it seem like the functions are an overly-verbose LIMIT statement. So what's the benefit? -M ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best approach for a "gap-less" sequence
Just in case no one else has brought it up- 8.1+ supports 2PC and savepoints, so one alternative would be to run your standard insertion operations in a prepared transaction or savepoint block. If you get so far as being able to prepare the transaction/complete the savepoint block, you should be able to snag a sequence id and commit everything. -M ---(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] Best approach for a "gap-less" sequence
Since the gapless numbers are purely for the benefit of the tax people, you could build your db with regular sequences as primary keys and then regularly (or just before tax-time) insert into a table which maps the gapless sequence to the real primary key. -M ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] psql/readline clears screen
On Aug 9, 2006, at 9:02 , Merlin Moncure wrote: is there a way to configure psql/readline so that it doesn't clear the screen after browsing a query with 'q'? Assuming you are using less as your PAGER, then see: http://www.greenwoodsoftware.com/less/#tite One can add the -X option to your LESS or PAGER environment variables. -M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
