Re: [GENERAL] Dynamically generating DDL for postgresql object
Aha ... makes sense. Thank you, Tom. Mark -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, June 16, 2007 11:21 AM To: Mark Soper Cc: 'John DeSoi'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Dynamically generating DDL for postgresql object Mark Soper [EMAIL PROTECTED] writes: Interestingly, it doesn't include some of the information displayed in pgAdmin (i.e. Match type, On Update, On Delete) - pg_get_constraintdef is aware that those values are the default ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PHP sucks!! - was: persistent db connections in PHP
On Jun 16, 2007, at 3:38 PM, John Smith wrote: guys, love both tools but php @ 2.5 *billion* google results is far more popular than postgresql @ 25 million google results. *if* somebody's gotto adapt it's not php. php does what it does best in a way that stuffy academics don't get. I would oppose the notion that someone needs to adapt to meet a lowest common denominator. That's a willing sacrifice of yourself. I've been around a lot of different languages to different levels of involvement and have formed a theory that hasn't been disputed in 5 years. Popular languages have nothing to do with how good they are. They have to do with the lower entry barrier. Examples: Java developers are crawling out of the woodwork. That's because I can go to the local community college and get a couple classes done. Run over to some training company like New Horizons, and walk away with a Java Certified Programmer paper in a month. Now I can walk into a company and say, I'm a Java developer, see!. Unfortunately they also think Java is the only language anything can be programmed in. Similarly the MCSE (Must Consult Someone Else) is a easy piece of paper to obtain that says you are Software Engineer. I took the tests 8 years ago, they're crap. MySQL is insanely easy to set up. But there are a lot of MySQL users who really don't understand the concept of a database and tend to treat it like a flat file or spreadsheet. I got in a shouting match at a Rails conference because they didn't see why you should put things like UNIQUE indexes or Referential Integrity in their database since they have the Rails functions of .exists? and .unique? (or something like that...) Easy to use, but it requires smarts to use it well. Contrary to these examples: Perl programmers who actually do this for a period of time tend to be extremely good at general programming concepts. Perl has a high entry barrier. There is a LOT to learn before you can do much of significance. Few of them are the idiots that the other fast track certification languages can provide. It's certification by war stories. C/C++ programmers who do this beyond their BSCSE courses are the same way. They know their stuff and can be trusted to do it well. Again, not a low barrier language. It is my opinion that MySQL and PHP both fall into the category of low entry barrier applications. They are easy to set up, easy to get hello world and you can find lots of company supported training programs. But this has nothing to do with a language/application being good or safe. But at the same time, I think the communities do not suffer from having higher entry barriers to them. If everything was as popular as Windows then there would be a lot of AOL addresses signed up on this list, something I'm OK with not having. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PHP sucks!! - was: persistent db connections in PHP
On Jun 16, 2007, at 4:12 PM, Uwe C. Schroeder wrote: On Saturday 16 June 2007, John Smith wrote: guys, love both tools but php @ 2.5 *billion* google results is far more popular than postgresql @ 25 million google results. *if* somebody's gotto adapt it's not php. php does what it does best in a way that stuffy academics don't get. Mhhh - what does PHP have to do with Postgresql? Lots of pages just end in .php, which is why the google results are so high - guess what, the tool html hits 3.2 billion :-) That's me, sorry. I forgot I put: AddHandler cgi-script .html in my web server... ---(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] PHP sucks!! - was: persistent db connections in PHP
On Jun 16, 2007, at 7:51 PM, Leif B. Kristensen wrote: On Saturday 16. June 2007 23:34, Erick Papadakis wrote: How much value you derive from a language depends on how you use it. After playing for years with Perl, and now with Python and Ruby, I think PHP is still where it's at. I too have played around with Perl and Python, and use both of them for special jobs, but I'm writing my dynamic web pages in PHP. In hindsight, I might have been better off writing them in Perl, but I was put off by the lousy HTML generated by the CGI module. It doesn't even close paragraphs. PHP doesn't have anything like the CGI.pm, but I'm quite content with hand coding where every single HTML tag should go. Have you tried: print $q-p(This is a paragraph); recently? Does a nice job of closing paragraphs. ---(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] What O/S or hardware feature would be useful for databases?
On 06/17/07 00:19, Greg Smith wrote: On Sat, 16 Jun 2007, Ron Johnson wrote: Anyway... databases are always(?) IO bound. I'd try to figure out how to make a bigger hose (or more hoses) between the spindles and the mobo. What I keep waiting for is the drives with flash memory built-in to mature. I would love to get reliable writes that use the drive's cache for instant fsyncs, instead of right now where you have to push all that to the controller level. But drive-based flash memory will always be a fixed size, and only for that drive. Controller-based cache is expandable and caches the whole RAID set (besides being battery-backed). And if you *still* need more cache, rip out that controller and put in a more expensive one, or transition to plain SCSI cards and a storage controller. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] pgadmin3 1.6.3 problem with geom fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, I've installed pgadmin3 1.6.3 from rpm, under Fedora 7 It runs ok, *except* when the tables have geometry fields! :O When one tries to open/view the table it takes **forever** to display the table's records! (?) This is what I get when ps aux: doria 21930 89.7 20.9 672256 434116 pts/2 R+ 20:28 1:53 pgadmin3 ^ | Look at this processor hogging! It's becoming apparent that besides (perhaps) having something to do with (either) geos, proj, postgis, it might also something to do with gtk+, wxWidgets btw: I compiled geos-3.0.0rc4, proj-4.5.0, postgis-1.2.1 from source My system has wxGTK-2.8.3-2.fc7 installed. Some testing: Some tables (with geom fields) do show after some time. trying to scroll the list of records freezes pgadmin, however... I've tried to downgrade pgadmin3 1.6.3 to 1.6.2 and it exhibits the same behaviour... Any thoughts highly appreciated since I rely heavily on pgadmin3 for my work! :) Kind regards, Pedro Doria Meunier -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Remi - http://enigmail.mozdev.org iD8DBQFGdVZW2FH5GXCfxAsRAsDvAJ9P8QnzN/ebOzV+aQWVzIEZqPy+8QCcDZBh GRrKkEP91pUtu3pd2XeZCrU= =XMIa -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] initdb
I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found I've googled for this but found nothing useful. Regards, John -- War is God's way of teaching Americans geography Ambrose Bierce (1842 - 1914) ---(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] about cursors
On lau, 2007-06-16 at 18:58 -0700, Ottavio Campana wrote: Martijn van Oosterhout wrote: Cursors are attached to the transactio and session, if either ends, the cursor dies with it... Have a nice day, another question: since they live in a transaction, how can they be used in web apps? as a rule, cursors are not used for web apps. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PHP sucks!! - was: persistent db connections in PHP
On 6/17/07, Tom Allison [EMAIL PROTECTED] wrote: Perl has a high entry barrier. yeah right ;). perl is time-tested better for sys admin than the web. and we all know how sys admins want to use the same code for the web but it doesn't cut right. *if anything* php is 'enhanced perl' for the web, atleast that's how it started. it's always a balance between usability and functionality. the market, the enterprise adoption etc, all tell us that php strikes a fine balance. like going to a client meeting where i got to sell postgresql in spanish/french. i am not going to fight with them about why english is better *if* it is, i am going to give them the best they want and close the deal. btw i got a database/language in my basement. it's the best in the world but only i can use it. but i am also not going to cry when the market doesn't adopt it. jzs On 6/17/07, Tom Allison [EMAIL PROTECTED] wrote: On Jun 16, 2007, at 7:51 PM, Leif B. Kristensen wrote: On Saturday 16. June 2007 23:34, Erick Papadakis wrote: How much value you derive from a language depends on how you use it. After playing for years with Perl, and now with Python and Ruby, I think PHP is still where it's at. I too have played around with Perl and Python, and use both of them for special jobs, but I'm writing my dynamic web pages in PHP. In hindsight, I might have been better off writing them in Perl, but I was put off by the lousy HTML generated by the CGI module. It doesn't even close paragraphs. PHP doesn't have anything like the CGI.pm, but I'm quite content with hand coding where every single HTML tag should go. Have you tried: print $q-p(This is a paragraph); recently? Does a nice job of closing paragraphs.
Re: [GENERAL] initdb
On 6/18/07, John K Masters [EMAIL PROTECTED] wrote: I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found I've googled for this but found nothing useful. You'll need to find a debian way to add the path to your binary to the users (systems?) PATH ... or use the fully qualified path to the executable. /usr/loca/pgsql/bin/initdb -D /usr/local/pgsql/data HIH -- Cheers Andrej ---(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] initdb
On sun, 2007-06-17 at 19:59 +0100, John K Masters wrote: I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found i believe the debian packages supplies a wrapper for this operation. try: man pg_createcluster gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] initdb
On sun, 2007-06-17 at 21:50 +, Ragnar wrote: On sun, 2007-06-17 at 19:59 +0100, John K Masters wrote: I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found i believe the debian packages supplies a wrapper for this operation. try: man pg_createcluster sorry, of course you are not using the debian package. Andrej's answer is the correct one. gnari ---(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] INSERT ... RETURNING in v8.2
Tom Allison escribió:
Holy Crud!
you mean to tell me I can replace:
insert into table(string) values(('one'),('two'),('three'));
select idx from table where string in ('one','two','three');
with
insert into table(string) values(('one'),('two'),('three')) returning
idx;
?
I realize that this is an extension to standard SQL but it sure would
save me a lot.
You are wrong -- you can do it, but it is not an extension. It is in
the standard.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(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] INSERT ... RETURNING in v8.2
Alvaro Herrera [EMAIL PROTECTED] writes:
Tom Allison escribió:
insert into table(string) values(('one'),('two'),('three')) returning
idx;
I realize that this is an extension to standard SQL but it sure would
save me a lot.
You are wrong -- you can do it, but it is not an extension. It is in
the standard.
Uh, I don't even see RETURNING as a reserved word in SQL2003.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] VACUUM ANALYZE extremely slow
A long time ago, in a galaxy far, far away, Sergei Shelukhin [EMAIL PROTECTED] wrote: This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM and virtually no workload at the moment. Maintenance work mem is set to 512 Mb. Is there any way to speed up ANALYZE? Without it all the queries run so slow that I want to cry after a couple of hours of operation and with it system has to go down for hours per day and that is unacceptable. The same database running on mysql on basically the same server used to run optimize table on every table every half an hour without any problem, I am actually pondering scraping half the work on the conversion and stuff and going back to mysql but I wonder if there's some way to improve it. That does seem surprisingly slow fo that sort of hardware. One thing I would suggest: Try JUST doing ANALYZE, with no VACUUM involved. That will merely do statistical sampling on the tables, and should complete quite quickly, even for large tables. That ought to fix the statistics problem. Those tables that see frequent UPDATE/DELETE requests will still need to be vacuumed, but fixing the stats doesn't require this. -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://linuxdatabases.info/info/postgresql.html Learning is weightless, a treasure you can always carry easily. -- Chinese Proverb ---(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] initdb
Hi Jhon Diferent distros put file in diferent path try /usr/local/pgsql/initdb bla bla bla or /var/lib/postgres/bin/initdb bla bla bla or locate initdb for locate that file best regards mdc --- John K Masters [EMAIL PROTECTED] escribió: I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found I've googled for this but found nothing useful. Regards, John -- War is God's way of teaching Americans geography Ambrose Bierce (1842 - 1914) ---(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 __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(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] VACUUM ANALYZE extremely slow
Sergei Shelukhin [EMAIL PROTECTED] wrote: This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. Please post an example query and its EXPLAIN ANALYZE output. The pgsql-performance mailing list is a good place to discuss performance problems. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of RAM and virtually no workload at the moment. Maintenance work mem is set to 512 Mb. What other non-default configuration settings do you have? What version of PostgreSQL are you using and on what OS? What kind of disks and controllers do you have? Is there any way to speed up ANALYZE? Without it all the queries run so slow that I want to cry after a couple of hours of operation and with it system has to go down for hours per day and that is unacceptable. Why does the system have to go down? Are you running VACUUM FULL ANALYZE? If so then drop the FULL and do an ordinary VACUUM ANALYZE instead -- it should run faster and it doesn't require exclusive access to the table. As Christopher Browne mentioned, a bare ANALYZE (without VACUUM) should be fast even on large tables so if necessary you could run ANALYZE more often than VACUUM ANALYZE. Have you enabled autovacuum (or contrib/pg_autovacuum in 8.0 and earlier)? I sometimes prefer to run VACUUM ANALYZE manually but for many databases autovacuum is a good way to maintain statistics and clean up dead rows automatically. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Normal distribution et al.?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Are there any statistical libraries for postgresql? I'd like to do something along the lines of: UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; - -- Kind regards, Jan Danielsson -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (NetBSD) iD8DBQFGdff2uPlHKFfKXTYRCmofAJ9Ni7CnVZdXuV13ppgKlU0ZFIG4zgCgkQF3 NzMyvu4td86RBlw4R+CX2C4= =qgWi -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Normal distribution et al.?
On 6/18/07, Jan Danielsson [EMAIL PROTECTED] wrote: UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; Something like this? http://www.joeconway.com/plr/ -- Cheers, Andrej ---(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] Normal distribution et al.?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA512 Andrej Ricnik-Bay wrote: On 6/18/07, Jan Danielsson [EMAIL PROTECTED] wrote: UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; Something like this? http://www.joeconway.com/plr/ That looks too good to be true. Many thanks! - -- Kind regards, Jan Danielsson -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (NetBSD) iD8DBQFGdf65uPlHKFfKXTYRCiLVAKCTvgHVsWkH1wtTiqg4w8ieZeoR4wCeLuxn Wo7L+94t2/4nP0gixz0q5fo= =Koyh -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Normal distribution et al.?
On Mon, Jun 18, 2007 at 05:11:51AM +0200, Jan Danielsson wrote: Are there any statistical libraries for postgresql? I'd like to do something along the lines of: UPDATE foo SET value=value+normdistsample(10, 0.2) WHERE id=1; Somebody else has mentioned PL/R. For this particular example see also normal_rand() in contrib/tablefunc. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
