Re: [HACKERS] [ADMIN] after 9.2.4 patch vacuumdb -avz not analyzing all tables
Does this behavior only affect the 9.2 branch? Or was it ported to 9.1 or 9.0 or 8.4 as well? On Thu, Apr 11, 2013 at 7:48 PM, Kevin Grittner wrote: > Tom Lane wrote: > > > However I've got to say that both of those side-effects of > > exclusive-lock abandonment seem absolutely brain dead now that I > > see them. Why would we not bother to tell the stats collector > > what we've done? Why would we think we should not do ANALYZE > > when we were told to? > > > > Would someone care to step forward and defend this behavior? > > Because it's not going to be there very long otherwise. > > I'm pretty sure that nobody involved noticed the impact on VACUUM > ANALYZE command; all discussion was around autovacuum impact; and > Jan argued that this was leaving things in a status quo for that, > so I conceded the point and left it for a follow-on patch if > someone felt the behavior needed to change. Sorry for the miss. > > http://www.postgresql.org/message-id/50bb700e.8060...@yahoo.com > > As far as I'm concerned all effects on the explicit command were > unintended and should be reverted. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > > -- > Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- To understand recursion, one must first understand recursion.
Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheeler wrote: > On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: > >>> I prefer to dump all my data in a big text file and grep it for the >>> information I need. >> >> As long as you implement your own grep, that sounds about on par with >> the current trends! Go for it! > > Well, first you have to implement your own compiler. Also a lexer and a > parser. All that will be for naught unless you hand wire your own logic boards. I mean really, come on. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] MIT benchmarks pgsql multicore (up to 48)performance
On Mon, Oct 4, 2010 at 8:44 AM, Hakan Kocaman wrote: > Hi, > for whom it may concern: > http://pdos.csail.mit.edu/mosbench/ > They tested with 8.3.9, i wonder what results 9.0 would give. > Best regards and keep up the good work They mention that these tests were run on the older 8xxx series opterons which has much slower memory speed and HT speed as well. I wonder how much better the newer 6xxx series magny cours would have done on it... When I tested some simple benchmarks like pgbench, I got scalability right to 48 processes on our 48 core magny cours machines. Still, lots of room for improvement in kernel and pgsql. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Contrib modules documentation online
On 8/29/07, Mario Gonzalez <[EMAIL PROTECTED]> wrote: > On 29/08/2007, Neil Conway <[EMAIL PROTECTED]> wrote: > > > > I wonder if it would be possible to keep the master version of the > > contrib docs as SGML, and generate plaintext READMEs from it during the > > documentation build. > > > > Hello Neil, I think I'm doing something similar but not with README > files. Currently I'm writing the FAQ into Docbook XML, that's why we > can build the HTML and plain text at one. While I like the idea of the READMEs from contrib being in the docs, I can't tell you the number of times I've installed a contrib module in a dark ops center at 2am with no html browser handy (or at best a text based one) or with no access to external internet etc... and just needed a line or two from the README file that came with the contrib module. Could the contrib README files couldn't be generated from the same source as the docs (i.e. sgml) and then put into the appropriate contrib/module/ directory. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [DOCS] [HACKERS] Contrib modules documentation online
On 8/29/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Scott Marlowe escribió: > > > Could the contrib README files couldn't be generated from the same > > source as the docs (i.e. sgml) and then put into the appropriate > > contrib/module/ directory. > > Sure they can. We already do that for INSTALL for example. OK, s/Could/May/ up there. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Is "query" a reserved word in 8.3 plpgsql?
On Nov 9, 2007 5:14 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Todd A. Cook" <[EMAIL PROTECTED]> writes: > > I saw the item in the release notes about the new "return query" > > syntax in pl/pgsql, but I didn't see any note about "query" being > > reserved now. Perhaps an explicit mention should be added? > > Yeah, I got burnt by that too. I have a bad feeling that that keyword > is going to cause trouble for a lot of people. > > [ thinks for a bit... ] It might be possible to get rid of the keyword > and have RETURN QUERY be recognized by an ad-hoc strcmp test, much like > the various direction keywords in FETCH have been handled without making > them real keywords. It'd be a bit uglier but it'd avoid making QUERY > be effectively a reserved word. It's not uncommon to have auditing triggers store things in tables with fields named query in them. I know I have a few places that do this... Just sayin' ---(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: [HACKERS] [GENERAL] Is "query" a reserved word in 8.3 plpgsql?
On Nov 9, 2007 6:07 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > On Nov 9, 2007 5:14 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > >> [ thinks for a bit... ] It might be possible to get rid of the keyword > >> and have RETURN QUERY be recognized by an ad-hoc strcmp test, much like > >> the various direction keywords in FETCH have been handled without making > >> them real keywords. It'd be a bit uglier but it'd avoid making QUERY > >> be effectively a reserved word. > > > It's not uncommon to have auditing triggers store things in tables > > with fields named query in them. I know I have a few places that do > > this... > > It turned out to be a very easy change, so it's done: QUERY isn't a > reserved word anymore. Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Fwd: psql+krb5
Except that he posted a month ago and got no answers... On Tue, Dec 1, 2009 at 8:22 AM, Robert Haas wrote: > 2009/11/30 rahimeh khodadadi : >> >> >> -- Forwarded message -- >> From: rahimeh khodadadi >> Date: 2009/11/29 >> Subject: Re: psql+krb5 >> To: Denis Feklushkin > > Please review the guidelines for reporting a problem, which you can find here: > > http://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > It seems to me that you've done the exact opposite of nearly > everything suggested there, starting with cross-posting your email to > four mailing lists at least three of which are irrelevant to the > problem that you're attempting to solve. > > ...Robert > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh wrote: > I took your cue, and have formulated this solution for 8.3.1 : Is there a good reason you're running against a db version with known bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a version missing over a year of updates is not a best practice. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] ERROR: failed to find conversion function from "unknown" to text
On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh wrote: > On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe > wrote: >> >> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh >> wrote: >> > I took your cue, and have formulated this solution for 8.3.1 : >> >> Is there a good reason you're running against a db version with known >> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a >> version missing over a year of updates is not a best practice. > > That's just a development instance that I have kept for long; actual issue > was on EDB 8.3.0.12, which the customer is using. As noted in the PS of > previous mail, the solution that worked for PG 8.3.1 didn't work on EDB > 8.3.0.12, so had to come up with a different code for that! Ahh, ok. I was just worried you were ignoring updates. I don't know anything about the numbering scheme for EDB. What does 8.3.0.12 translate to in regular pgsql versions? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 10:19 PM, Tom Lane wrote: > Andrew Dunstan writes: >> Part of the motivation for allowing inline blocks was to allow for >> conditional logic. > > I don't think that argument really applies to this case, because the > complaint was about not being sure if plpgsql is installed. If it > isn't, you can hardly use a plpgsql DO block to fix it. > > (Is anyone up for revisiting the perennial topic of whether to install > plpgsql by default? Andrew's argument does suggest that DO might offer > a new consideration in that tradeoff.) One non-coding vote for yes. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 10:41 PM, Craig Ringer wrote: > Tom Lane wrote: > It'd be a HUGE benefit in deployment and update scripts to have PL/PgSQL > installed and available by default, at least to the superuser and to > the DB owner. Are there any known security problems with plpgsql? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On Tue, Nov 24, 2009 at 11:34 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Nov 24, 2009 at 12:28 PM, Tom Lane wrote: >>> But actually I thought we had more or less concluded that CREATE OR >>> REPLACE LANGUAGE would be acceptable (perhaps only if it's given >>> without any extra args?). > >> I'm not sure there's any value in that restriction - seems more >> confusing than helpful. > > The point would be to reduce the risk that you're changing the language > definition in a surprising way. Extra args would imply that you're > trying to install a non-default definition of the language. But if you'd installed it that way before, wouldn't you then need the arguments this time to have them match? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] high shared buffer and swap
On Mon, May 4, 2009 at 2:10 AM, Laurent Laborde wrote: > Friendly greetings ! > I found something "odd" (something that i can't explain) this weekend. > > An octocore server with 32GB of ram, running postgresql 8.3.6 > Running only postgresql, slony-I and pgbouncer. > > Just for testing purpose, i tried a setting with 26GB of shared_buffer. > > I quickly noticed that the performances wasn't very good and the > server started to swap slowly but surely. > (but still up to 2000query/second as reported by pgfouine) > > It used all the 2GB of swap. > I removed the server from production, added 10GB of swap and left it > for the weekend with only slony and postgresql up to keep it in sync > with the master database. > > This morning i found that the whole 12GB of swap were used : > Mem: 32892008k total, 32714728k used, 177280k free, 70872k buffers > Swap: 12582896k total, 12531812k used, 51084k free, 27047696k cached Try setting swappiness =0. But as someone else mentioned, I've alwas had better luck letting the OS do most of the caching anyway. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vote totals for SET in aborted transaction
I've been thinking this over and over, and it seems to me, that the way SETS in transactions SHOULD work is that they are all rolled back, period, whether the transaction successfully completes OR NOT. Transactions ensure that either all or none of the DATA in the database is changed. That nature is good. But does it make sense to apply transactional mechanics to SETtings? I don't think it does. SETtings aren't data operators, so they don't need to be rolled back / committed so to speak. Their purpose is to affect the way things like the database works in a more overreaching sense, not the data underneath it. For this reason, I propose that a transaction should "inherit" its environment, and that all changes EXCEPT for those affecting tuples should be rolled back after completion, leaving the environment the way we found it. If you need the environment changed, do it OUTSIDE the transaction. I would argue that the rollback on failure / don't rollback on completion is actually the worse possible way to handle this, because, again, this isn't about data, it's about environment. And I don't think things inside a transaction should be mucking with the environment around them when they're done. But that's just my opinion, I could be wrong. Scott Marlowe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL mission statement?
On 2 May 2002, Jason Earl wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > > On Wed, 1 May 2002, David Terrell wrote: > > > > > On Wed, May 01, 2002 at 02:24:30PM -0400, mlw wrote: > > > > Just out of curiosity, does PostgreSQL have a mission statement? > > > > > > > > If so, where could I find it? > > > > > > > > If not, does anyone see a need? > > > > > > "Provide a really good database and have fun doing it" > > > > Motto: "The best damned database money can't buy" > > I don't think that any of the PostgreSQL developers would want, in any > way shape or form, to suggest that you can't pay money for PostgreSQL. > Nor are they likely to limit themselves to competing with free > (libre/gratis) databases. True, but my point wasn't that you could pay for it, but that it couldn't be "bought" like so many other things (think politicians, OEMs, judges, etc...) But I was pretty much just foolin' around. :-) So how about: "Postgresql: Open Source, Open Standards, Open Development, Open Minds" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL mission statement?
On Wed, 1 May 2002, David Terrell wrote: > On Wed, May 01, 2002 at 02:24:30PM -0400, mlw wrote: > > Just out of curiosity, does PostgreSQL have a mission statement? > > > > If so, where could I find it? > > > > If not, does anyone see a need? > > "Provide a really good database and have fun doing it" Motto: "The best damned database money can't buy" :-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL mission statement?
On 2 May 2002, Hannu Krosing wrote: > The Politically Correct mission statement follows: > > The PostgreSQL community is committed to creating and maintaining a good > but not the best, mostly reliable, open-source multi-purpose standards > based database, and with it, promote free and open source software and > other worthy causes world wide and to not hurting anyones feelings in doing so. > We are also committed to not cheating our SOs, not charging too much for > our services nor eating too much and to recommending products of our > commercial competitors before ours in order to help them fullfil their > obligations to their stockholders. As a practicing polyamorist, I find the part about not cheating on our SOs highly offensive. :-) ---(end of broadcast)--- TIP 3: 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: [HACKERS] PostgreSQL mission statement?
On Thu, 2 May 2002, Jim Mercer wrote: > On Thu, May 02, 2002 at 08:41:30PM -0400, mlw wrote: > > A mission statement is like a tie. > > straw vote! > > who on the list wears ties? Does a skinny black tie count if I'm only wearing it to go out to a jazz club? :-) Not at work though. I think ties are designed to slow the flow of blood to the head so you'll think slow enough for marketeers to understand you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Issues tangential to win32 support
On Thu, 9 May 2002, Jan Wieck wrote: > > If postgresql IS going to eventually be multi-threaded, then the whole > > win32 port should probably be delayed until then, since it would solve > > many of the issues of fork() versus createprocess(). > > If multi-threading is the plan, then there is light at the > end of the tunnel ... the upcoming train... That's a bit extreme don't you think? I'm not fan of multi-threading as the one true way, and since I use linux as my server for postgresql, there is no gain for me in a multi-threaded model. In fact, I'd prefer postgresql stay multi-process for robustness. BUT, if there are plans to go multi-thread, or could be plans, then those should take priority over how to port to windows, since making postgresql multi-threaded will change it so much as to make the current "how do we port to windows" thread meaningless. One of the primary reasons given for avoiding cygwin is that postgresql runs so slowly under it on windows, but I submit that it probably won't run a heck of a lot faster if it was written as a native app as long as it's running as a multi-process design. Since there's probably no great gain to be had from moving it out from under cygwin, why bother? My vote would be to stay multi-process and Unix compatible. I have no real use for windows as a server, and do NOT want to sacrifice the performance / reliability I have with postgresql under Linux for a windows port. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostGres Doubt
On Mon, 10 Jun 2002, Dann Corbit wrote: > If you are going to completely replace the data in a table, drop the > table, create the table, and use the bulk copy interface. Actually, that's a bad habit to get into. Views disappear, as do triggers or constraints. Better to 'truncate table' or 'delete from table'. I know, I had a bear of a time with a nightly drop table;create table;copy data in script that I forgot about and built a nice new app on views. worked fine, came in the next morning, app was down... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Will postgress handle too big tables?
also, remember that for the cost of a single CPU oracle license you can build a crankin' postgresql server... memory and I/O are way more important than CPU power btw. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Hash and bools
During the discussion of bools and hash index and partial indexes and index growth and everything else, I tried to make a partial index on a bool field and got the error that "data type bool has no default operator for class hash..." So, can I cast something to make this work, or is it possible to make hash indexes work with bools. There are a few instances where a small percentage of a table is marked false while the rest is true, or vice versa, where a partial hash index would be nice to try, and may not have the ever expanding index problem that brtees have. -- "Force has no place where there is need of skill.", "Haste in every business brings failures.", "This is the bitterest pain among men, to have much knowledge but no power." -- Herodotus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Democracy and organisation : let's make a revolution
I'd have to say that personally, given a choice between expending effort to fix current know bugs and add known needed features, and expending effort to port to Windows, I'd pick the former, not the latter. I could personally care less if postgresql ever runs as a native window application, since I personally don't believe windows is a suitable OS for hosting a dbms. Note that the "portablility problems" in postgresql are and were introduced by Windows deciding to do everything different than every other OS. Postgresql is quite portable, when one is porting it to OSes that aren't windows, like VMS, MVS, or all the different flavors of Unix. Besides, in another 5 years, Windows as a server OS will likely be the shrinking percentage, while Linux/BSD et. al. will be growing. focus on the future, and let Windows wither and die (in the server room) as it should. On Tue, 25 Jun 2002, James Hubbard wrote: > I don't normally post to this list, but have a crazy suggestion that is a > little farfetched. > > Suggestion: > Fix the portability problems so that there is a Windows native version of > PostgreSQL. Then offer the Open Office organization PostgreSQL as the > project's database. This would increase the user base my leaps and bounds. > > The problem is that using and administrating PostgreSQL can be complex. Also, > some people may automatically assume that PostgreSQL is a low end database not > capable of doing more than being used as a backend for a free office app. Of > course we all know better. > > Maybe a PostgreSQL-Lite would be a better idea. One that condenses the main > code down to something easy, that a desktop user could use, but maintain the > strength of the core code. I suppose that means creating another project. > > Here are just a few links that I've come across recently: > How-to for using Open Office and unixODBC > http://www.unixodbc.org/doc/OOoMySQL.pdf > > Others are considering MySQL. > http://dba.openoffice.org/proposals/MySQL_OOo.html > > James Hubbard > > Dave Cramer wrote: > > IMO One of the big reasons that MySQL is viewed as being better is it's > > percieved simplicity. It has a large following because of this, and many > > of them are not experienced database users, in fact just the opposite. > > > > This large user base is perhaps the best marketing that an open source > > project can hope for. So I think that if we want to attract more users > > we should try to make postgres easier to use. The hard part is how to do > > this without sacrificing the integrity of the project. I think for > > starters when evaluating the next feature we want to work on we ask the > > following questions: > > > > 1) Does it make it easier to use for a non-dba ? > > 2) Does it facilitate making web-applications easier ( assuming that > > this is the largest user base ) ? > > 3) I'm sure there are others, but at the moment I can't come up with > > them. > > > > Then if faced with a choice of implementing something which is going to > > make postgres more technically complete or something which is going to > > appeal to more users we lean towards more users. Note I said lean! > > > > > > Dave > > > > On Tue, 2002-06-25 at 01:21, Tom Lane wrote: > > > >>Josh Berkus <[EMAIL PROTECTED]> writes: > >> > >>>Frankly, my feeling is, as a "geek-to-geek" product, PostgreSQL is already > >>>adequately marketed through our huge network of DBA users and code > >>>contributors. > >> > >>Well, mumble ... it seems to me that we are definitely suffering from > >>a "buzz gap" (cf missile gap, Dr Strangelove, etc) compared to MySQL. > >>That doesn't bother me in itself, but the long-term implications are > >>scary. If MySQL manages to attract a larger development community as > >>a consequence of more usage or better marketing, then eventually they > >>will be ahead of us on features and every other measure that counts. > >>Once we're number two with no prayer of catching up, how long will our > >>project remain viable? So, no matter how silly you might think > >>"MySQL is better" is today, you've got to consider the prospect that > >>it will become a self-fulfilling prophecy. > >> > >>So far I have not worried about that scenario too much, because Monty > >>has always treated the MySQL sources as his personal preserve; if he > >>hadn't written it or closely reviewed it, it didn't get in, and if it > >>didn't hew closely to his opinion of what's important, it didn't get in. > >>But I get the impression that he's loosened up of late. If MySQL stops > >>being limited by what one guy can do or review, their rate of progress > >>could improve dramatically. > >> > >>In short: we could use an organized marketing effort. I really > >>feel the lack of Great Bridge these days; there isn't anyone with > >>comparable willingness to expend marketing talent and dollars on > >>promoting Postgres as such. Not sure what to do about it. We've > >>sort of dismissed Jean-Michel's co
Re: [HACKERS] ANALYZE after restore
On 3 Apr 2002, Hannu Krosing wrote: > On Wed, 2002-04-03 at 06:52, Gavin Sherry wrote: > > On Wed, 3 Apr 2002, Christopher Kings-Lynne wrote: > > > > > Hi, > > > > > > Would it be an idea to have pg_dump append an ANALYZE; command to the end of > > > its dumps to assist newbies / inexperienced admins? > > > > I do not think this is desired behaviour. Firstly, pg_dump is not just for > > restoring data to the system. Presumably another flag would need to be > > added to pg_dump to prevent an ANALYZE being appended. > > Yes. > > > This is messing and, in my opinion, it goes against the 'does what it says> it >does' nature of Postgres. > > What does pg_dump say it does ? from man pg_dump: pg_dump - extract a PostgreSQL database into a script file or other archive file Pretty simple really. I've been using postgresql for about three years now, and it only took me about 15 minutes of reading the docs to find the vacuum and vacuum analyze command. It was far harder to figure out subselects, transactions, outer joins, unions, and a dozen other things than vacuum. I was a total database newbie back then, by the way. One of the things I liked about postgresql was that it wasn't stuffed full of marketing fluff to try and impress the PHBs at the top of the corporate ladder, but was full of useful extensibility and was very much a "do what it said it would" database. while I agree that postgresql could do with some automated housekeeping routines that would allow joe sixpack to grab it and go, no database that has real power is going to run very well without some administration, period. The last place to put house keeping is in the end of my data dumps. pg_dump's job is to dump the data from my database in a format that is as transportable as possible. not to hold my hand the next time I need to load data into my own database. While I fully support a switch like -z on pg_dump that puts an analyze on the end of my dumps if I so choose, I don't want them showing up automatically and me wondering if the data feeds I make for other will work. I can see junior dbas who don't understand vacuum and analyze recommending to people that they need to dump / restore their whole database once a week to get good performance if we add aht analyze switch to the end of the pg_dump file. NOT a good thing. :-) anywho, I don't post much here, cause I don't hack postgresql that much, but I love this database, and I don't want it filled up with useless marketing cruft like analyze being haphazardly tacked onto the pg_dump output, so my vote is a great big NO. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Issue with adding ORDER BY to EXCEPT.
On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote: > > SELECT encounter.encounter_id, encounter_d.encounter_d_id > > FROM encounter > > JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id > > EXCEPT > > SELECT encounter.encounter_id, encounter_d.encounter_d_id > > FROM encounter > > JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id > > JOIN p_l_d ON p_l_d.patient_id = encounter.patient_mpi > > WHERE encounter_d.encounter_id = encounter.encounter_id > > AND ((p_l_d.start_date <= encounter_d.from_date OR p_l_d.start_date IS > > NULL) > >AND (p_l_d.end_date >= encounter_d.from_date OR p_l_d.end_date IS NULL)) > > ORDER BY encounter.encounter_id, encounter_d.encounter_d_id > > > > > With the ORDER BY > > NOTICE: adding missing FROM-clause entry for table "encounter" > > NOTICE: adding missing FROM-clause entry for table "encounter_d" > > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the > > result columns > > > I suppose this is because the columns in the except are the same that > the ones in the main select and the order by get confused. > > i'm redirecting to hackers to know if this is a known bug or there is > something wrong in the select? i don't see anything wrong!! No, it's because to the order by, the column names are the ones given by the part after the period of the first select. If you do a plain select UNION select with no order by, you'll see the title for the columns is taken from the first select list column names. So, the order by needs to be "order by encounter_id, encounter_d_id ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] mirroring oracle database in pgsql
On Mon, 2005-06-06 at 14:52, Edward Peschko wrote: > hey all, > > > I'm trying to convince some people here to adopt either mysql or postgresql > as a relational database here.. However, we can't start from a clean slate; > we have a very mature oracle database that applications point to right now, > and so we need a migration path. I went to the mysql folks, and it looks > like its going to be quite a while before mysql is up to the task, so I > thought I'd try pgsql. If you've been using Oracle, PostgreSQL is likely to be a much better fit. MySQL's tendency to silently do stupid things (create a table as innodb, but spell it innobd, it will make an isam table and not tell you. insert data, roll back, find out that you can't roll back, the list goes on and on.) and lack of features you likely take for granted in Oracle will likely make Postgresql the better fit. You might want to look at either CJDBC or Daffodil for what you're thinking of. I'm not sure how well they'll work in a mixed environment, but they seem to be the leaders in client side clustering. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Frequently updated tables
On Wed, 2004-06-09 at 11:41, [EMAIL PROTECTED] wrote: > > On Wed, Jun 09, 2004 at 10:49:20PM +0800, Christopher Kings-Lynne wrote: > >> >I love PG, I've been using it since version 6x, and it has gotten > >> >fantastic over the years, and in many cases, I would choose it over > >> >Oracle, but for systems that need frequent updates, I have a lot of > >> >concerns. > >> > >> ...that's the price you pay for concurrency man... > > > > Also he said that the problem was solved with enough lazy VACUUM > > scheduling. I don't understand why he doesn't want to use that > > solution. > > > > Sigh, because vacuums take away from performance. Imagine a table that has > to be updated on the order of a few thousand times a minute. Think about > the drop in performance during the vacuum. > > On a one row table, vacuum is not so bad, but try some benchmarks on a > table with a goodly number of rows. Several points: All databases pay to clean up the mess they've made, so to speak. In PostgreSQL you get to choose when, instead of always paying the price at the end of transaction. Lazy vacuum does not impact performance nearly as much as the old full vacuum. With the sleep / delay patch that's been passed around on hackers its impact is virtually zero on the rest of the database Properly setup fsm settings, pg_autovacuum deamon, and an installation of the sleep / delay patch mentioned aobve makes this a non-issue. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Improving postgresql.conf
On Fri, 2004-06-11 at 11:02, Bruce Momjian wrote: > Gaetano Mendola wrote: > [ PGP not available, raw data follows ] > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > Bruce Momjian wrote: > > > > | Gaetano Mendola wrote: > > | > > |>Bruce Momjian wrote: > > |> > > |> > I understand your points below. However, the group has weighed in the > > |> > direction of clearly showing non-default values and not duplicating > > |> > documentation. We can change that, but you will need more folks > > |> > agreeing with your direction. > > |> > > |>I don't remember the behaviour but tell me what happen if > > |>I comment out a value changing the value. Kill UP the postmater. > > |>Recommenting that value and now re killing the postmaster. > > |> > > |>I believe that postmaster will not run with the default value. > > |>Who will look the configuration file will not understand the right > > |>reality. > > | > > | > > | If you comment a variable in postgresql.conf, it will use the > > | default value. > > > > That's not true at least with the version 7.4.2. > > > > Try yourself, I did the experiment changing the cpu_tuple_cost and > > commenting out the cpu_tuple_cost, after sending the SIGHUP to > > postmaster the value remain: 0.005 that is not the default value at > > all. > > Oh, sorry, you are right. Not sure if this is a bug or not. This point has come up before, and I think it's intended behavior. Stopping and restarting the database will, of course, make it load the defaults. ---(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: [pgsql-hackers-win32] [HACKERS] Tablespaces
On Fri, 2004-06-11 at 11:29, Dann Corbit wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Friday, June 11, 2004 9:39 AM > > To: Tom Lane > > Cc: Dann Corbit; Zeugswetter Andreas SB SD; > > [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg > > Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list > > Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces > > > > > > > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > >> I expect that one year after release, there will be ten > > times as many > > >> PostgreSQL systems on Win32 as all combined versions now on UNIX > > >> flavors > > > > > > I surely hope not. Especially not multi-gig databases. The folks > > > running those should know better than to use Windows, and > > if they do > > > not, I'll be happy to tell them so. > > I know better than to tell people to change their operating system. > Linux is a great OS, and people familiar with it will do exceedingly > well. But there are 40 million computers sold in a year, most of which > have some flavor of Windows installed. I think the more important part of Tom's point isn't that Windows in general sucks (even though it does) but that PostgreSQL ON Windows is a brand new thing, and if you're willing to put a multi-gig ERP system on it and bet the company, you shouldn't be in a data center, because right now it simply hasn't been tested enough. Now, setting up a unix box with postgresql for production and becoming a part of the windows testing effort in your spare time, until Windows proves itself ready and worthy, that makes sense. I'm no fan of microsoft or Bill Gates, for the reasons mentioned in books like "The Microsoft Files". But my main objection to putting a PostgreSQL on Windows server online right now would be the same one I would have against putting a MS SQL server on Windows online right now, neither one has ever been proven reliable. :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] email browser?
On Fri, 2004-06-18 at 08:24, Chris Browne wrote: > "Santo Quartarone" <[EMAIL PROTECTED]> writes: > > What's the safest email browser? > > less is pretty safe, more or less ;-). > > You didn't specify what sort of platform you wanted to use; the > choices vary, considerably, between platforms. I'd say pine is pretty darned safe... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] ALTER TABLE ... SET TABLESPACE
On Sun, 2004-06-20 at 17:15, Tatsuo Ishii wrote: > > > Also I think we need to enhance ALTER INDEX to assign new table spaces > > > for indexes. Assigning different tables spaces for tables and indexes > > > are essential to gain more I/O speed IMO. > > > > I thought about this. ALTER INDEX doesn't exist yet and I figured that, > > unlike the case of tables, its easy to drop and recreate indexes in new > > tablespaces. > > Oh you are right. I forgot about CREATE INDEX ... TABLESPACE. > > > I'm still stumped as to where I am corrupting memory with this patch > > though. (There was another bug: I wasn't detecting the case where users > > set tablespace to the tablespace that the table is already in). On a related note, will there be a way to have implicit index creation occur in a seperate table space automagically? I.e. create table test (id int4 primary key, n1 int unique); so that the indexes created in id and n1 here would have a different default namespace than the table? Just wondering. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] xeon processors
On Fri, 2004-06-25 at 14:13, Jaime Casanova wrote: > Hi all, > > Can anyone tell me if postgresql has problems with xeon processors? > If so, there is any fix or project of fix it? To PostgreSQL, there's no difference between a dual CPU machine with no hyperthreading, and a single CPU machine with hyperthreading. HOWEVER, there have been some issues with certain Operating Systems running slower with hyperthreading enabled than without it. Late model Linux kernels (2.6) seem to have gotten enough logic into the scheduler to get good performance on a hyperthreaded system. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Quick question regarding tablespaces
On Thu, 2004-07-01 at 18:54, Gavin Sherry wrote: > On Thu, 1 Jul 2004, Mike Rylander wrote: > > > On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: > > > Hi Mike, > > > > > > In this release, unfortunately not. > > > > That't too bad, but it's not that urgent I suppose. > > > > > > > > I had some idea early on of putting rand_page_cost in pg_tablespace and > > > having the planner have access to it for costing. I didn't actually get > > > around to it but. :-( > > > > Well, I haven't looked at the PG source before, but if you have some specific > > design ideas I would be glad to help out. I'm just not sure where (or when, > > with the official release coming (sort of) soon) to start, but with some > > pointers I'll do what I can! > > Well, it wont be in 7.5. Feel free to start looking at how > random_page_cost in cost_index(). It might be worthwhile introducing a per > tablespace performance factor so that we could could say that the cost of > fetching an index tuple from tablespace A is half that of fetching an > index tuple from tablespace B. That idea might not actually turn out to be > a very good one once I look at it closely though. How about having a per cluster / database / tablespace / table type setup that goes in a hierarchy, if they're there. I.e. if the database doesn't have it's own random_page_cost, it inherits from cluster, if a tablespace doesn't have one, it inherits from cluster->database, and so on to individual tables / indexes. It may be that it's easier to implement for them all now while doing it for tablespaces. Just wondering. I'm a user, not a hacker, so I have no idea how much that idea makes any sense, but I would certainly love to be able to set an index to have a random_page_cost effect of 1.1 while the table it lives in is 1.3, the tablespace 1.4, and so on. But not required, because it always inherits from the parent if it doesn't have one, like stats target. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
On Thu, 2004-07-01 at 22:14, Tom Lane wrote: > Mike Benoit <[EMAIL PROTECTED]> writes: > > On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote: > >> If we change the syntax, say by using SUBCOMMIT/SUBABORT for > >> subtransactions, then using a simple ABORT would abort the whole > >> transaction tree. > > > But then we're back to the application having to know if its in a > > regular transaction or a sub-transaction aren't we? To me that sounds > > just as bad. > > Someone (I forget who at this late hour) gave several cogent arguments > that that's *exactly* what we want. Please see the prior discussion... > > Right at the moment I think we have a consensus that we should use > SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not > say we've agreed to exactly those keywords, only that it's a good idea > to make them different from the outer-level BEGIN/END keywords.) > > There was also some talk of offering commands based around the notion of > savepoints, but I'm not sure that we have a consensus on that yet. Aren't subtransactions and their syntax defined by the SQL spec somewhere? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
On Tue, 2004-07-06 at 10:25, Alvaro Herrera wrote: > On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote: > > On Mon, 5 Jul 2004, Alvaro Herrera wrote: > > > > > > begin/end because they are already in an explicit/implicit transaction > > > > by default... How is the user/programmer to know when this is the case? > > > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > > you can't do is issue begin/end inside a function -- you always use > > > subbegin/subcommit in that case. > > > > I've not understood why we need new tokens for this case. Maybe you've > > explained it somewhere that I've missed. But surely the server know if you > > are in a transaction or not, and can differentiate on the first BEGIN and > > the next BEGIN. > > I think the best argument for this is that we need a command to abort > the whole transaction tree, and another to commit the whole transaction > tree. Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END), > because the spec says they work like that and it would be hell for an > interface like JDBC if they didn't. So it's out of the picture to use > those commands to end a subtransaction. Why not rollback all or commit all? I really really don't like subbegin and subcommit. I get the feeling they'll cause more problems we haven't foreseen yet, but I can't put my finger on it. They just don't feel like "postgresql" to me. I'd rather see extra syntax to handle exceptions, like rollback all or whatnot, than subbegin et. al. > > Now, it's clear we need new commands to end a subtransaction. Do we > also want a different command for begin? I think so, just to be > consistent. Sorry, but I respectfully disagree that it's clear. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
On Tue, 2004-07-06 at 23:36, Greg Stark wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > > Why not rollback all or commit all? > > > > I really really don't like subbegin and subcommit. I get the feeling > > they'll cause more problems we haven't foreseen yet, but I can't put my > > finger on it. > > Well I've already pointed out one problem. It makes it impossible to write > generic code or reuse existing code and embed it within a transaction. Code > meant to be a nested transaction within a larger transaction becomes > non-interchangeable with code meant to be run on its own. Would a rollback N / abort N where N is the number of levels to rollback / abort work? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
On Wed, 2004-07-07 at 00:16, Dennis Bjorklund wrote: > On Tue, 6 Jul 2004, Alvaro Herrera wrote: > > > We can later implement savepoints, which will have "SAVEPOINT foo" and > > "ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly > > different from a savepoint, so we can't use ROLLBACK TO in > > subtransactions because that has a different meaning in savepoints). > > What is the semantic difference? One is in the SQL spec? For that reason alone, we should probably eventually have the savepoint syntax work. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 2004-07-10 at 15:21, Josh Berkus wrote: > Bruce, > > > They have no way of reporting a failed query back to the user? How do > > people program in those environments? Right now any failed query aborts > > the transaction so it seems it would be pretty easy. > > Believe it or not, PHP4 doesn't. This is one of the reasons why coders in > other languages don't consider PHP a "real" programming language; the lack of > exception handling. However, given this limitation we can't really use NTs > in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. Uh, I think it can: http://www.php.net/manual/en/function.pg-result-error.php ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
On Sun, 2004-07-11 at 16:01, Josh Berkus wrote: > Scott, > > > Uh, I think it can: > > > > http://www.php.net/manual/en/function.pg-result-error.php > > Heh. I half-knew that if I pointed this out that someone would correct me > with a link to new code. In my defense, I will point out that the mentioned > PHP feature is less than 4 months old. Actually, it's part of PHP since 4.2.0, which was released on 22 April 2002. That's long enough most folks should know of it by now. (see http://www.php.net/releases.php) > > Not a real language indeed. :-) > > I hope you relize that that was said as someone who uses PHP for a lot > of projects ... I know you do, I'm just amazed at how many people will dog PHP when it's not the same language they downloaded and tried 5 years ago :-( ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] patch for allowing multiple -t options to pg_dump
On Sun, 2004-07-11 at 19:00, Bruce Momjian wrote: > Andreas Joseph Krogh wrote: > [ PGP not available, raw data follows ] > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > Hi, I've prepared a patch(against CVS HEAD of today) to pg_dump.c to > > make pg_dump understand multiple -t options for dumping multiple tables > > in one command. > > Eks: > > pg_dump -t table1 -t table2 -t table3 > > > > The patch is here: > > http://home.officenet.no/~andreak/pg_dump.patch > > > > Any comments, flames? > > > > Is it too late for it to make it into 7.5? I submitted a patch against > > 7.4 a while ago, but was then told it had to wait until 7.5, and, well, > > now it's 7.5-time:-) > > Uh, I see your patch posted on July 6. Is that the one? We started a > feature freeze on July 1. Is this a feature or a bug fix? I'd say it's more of a bug fix, since multiple -t switches seems like an obvious thing to support. Now, if we were adding wild card matching that would seem like an enhancement. I can't imagine this patch is more than a dozen lines or so. And I can't imagine it impacting anything else going on right now. ---(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: [HACKERS] check point segments leakage ?
Bruce said the other day open transactions can't cause this problem. I wonder what all can? On Tue, 2004-07-20 at 16:32, Joshua D. Drake wrote: > Hello, > > Perhaps you have an open transaction that isn't closing and thus the > pg_xlog continues to grow? > > Sincerely, > > Joshua D. Drake > > > Gaetano Mendola wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > Hi all, > > today I add 4 new columns to a table with 4E+06 rows, > > I also update to an initial value these new columns. > > > > The new columns are 3 INTEGER one of type DOUBLE. > > The table have also 5 indexes. > > > > Immediately after the operation my partition "data" had > > an usage increment of 1.2GB. > > I did a reindex and a vacuum full on that table and 600MB > > were freed. > > > > Now I have an increment of only 600 MB. > > > > I use a checkpoint_segments = 16 but in my pg_xlog I have > > 35 files. Why 35 files ? > > > > Where are lost my 600MB ? > > > > Also the load increased from 1 to 5 !! > > Any ideas ? > > > > I'm attaching boot graphs ( HD space usage and load ). > > > > Regards > > Gaetano Mendola > > > > > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.2.4 (MingW32) > > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > > > iD8DBQFA/Ydh7UpzwH2SGd4RAuhKAKCTftBGjBLSfR+OTy5vHlYpL46TXQCfc65/ > > VfepMM87dQKvg3rswhGUNL8= > > =HWHy > > -END PGP SIGNATURE- > > > > > > > > > > > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 3: 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: [HACKERS] Tutorial
On Thu, 2004-07-22 at 16:21, David Fetter wrote: > Kind people, > > I am writing a document patch for the tutorials section, and would > like to change the section on inheritance to reflect the fact that it > is not currently being developed, and has known serious bugs in > implementation. I'd call them deficiencies. If inheritance allowed one to specify a pk across inherited tables, but occasionally forgot to enforce it or something like that, that would be a bug. But I totally agree with adding that there are key features of an inheritance system that are not implemented, are not being worked on, and here's what they are... kind of approach. > I'm thinking that I should either change that section to a warning > about why this is an unsupported feature or remove it entirely, and > add some other tutorials, details TBD. Some candidates for these > would include: > > * JOINs > * set-returning functions > * ARRAYs > * version-dependant (I presume) hacks like ORDER BY ... LIMIT 1 vs MIN/MAX > * the perennial Stuff Dave Has Not Though Of. Sounds good. I've got some time off, so I'd be happy to write some of it too. Not a fan of arrays in pgsql so I'm not very familiar with using them. The version dependent hacks / kludges should probably be in some generic section on performance tuning queries or something like it. It may be well to have cross links from one set to the other where these are concerned, for instance the fact that in earlier versions, join order was constrained using SQL syntax would be under both joins and under version dependent kludges / performance tuning and vice versa. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] storage engine , mysql syntax CREATE TABLE t (i INT)
On Sun, 2004-07-25 at 22:23, Tom Lane wrote: > I don't think > it's either practical or interesting to try to introduce an equivalent > layering into Postgres. I can possibly see a use for a row locking storage system, i.e. non MVCC for some applications. But I can't see it being worth the amount of work it would require. Or is the locking model too high level to be handled this way? Just wondering. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] connect to 7.5 devel(win32) failed
Moving the -admin, please don't reply to -hackers on this. On Fri, 2004-07-30 at 00:04, Coloring Graph wrote: > I am has some trouble when connect to 7.5 devel PostgreSQL server, > see belows > > my setup: > === > os=Windows2000 > server version=the non-MSI snapshot at > http://www.hagander.net/pgsql/win32snap/ has been "downgraded" to the > latest known working snapshot > path=c:\postgres\bin;c:\postgres\lib > pgdata=c:\postgres\data > TZ=CST > > > note: > == > I has NO modify the configure file generated by initdb, > and has NO firewall installed in my server > > > > cmd to run postmaster(run as postgre user): > == > postmaster -D "C:/postgres/data" > > > > > result1: some lines of the log(postmaster) > === > LOG: select() failed in statistics buffer: > åääéåæåäåèäääæä > LOG: statistics collector process (PID 1876) was terminated by signal > 1 > result2: > === > When I use pgAdmin III to connect the PostgreSQL server, > a error is returned: Is pgadminIII using local domain sockets or TCP/IP sockets? Out of the box pgsql usually just answers local domain sockets, and I don't have a clue what windows supports in that way, etc... Since local domain sockets only work for processes on the same machine, this means that, after initial install, postgresql is not visible on anything other than the box it's installed on. You'll need to edit pg_hba.conf to give other machines permission to authenticate, as well as postgresql.conf to accept tcpip connections, then restart the server. Learning to admin on a pre-beta 1st port of windows could be a bit of work, so hang in there. These questions should probably go on -general or -admin first. ---(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: [HACKERS] shared buffer hash table corrupted
On Mon, 2004-08-02 at 00:09, Adrian Maier wrote: > Hello, > > On the production server I have PostgreSql 7.4.3 , on Mandrake Linux 9.2. > In the message log on 29 july I have received several "shared buffer > hash table > corrupted" errors . > What could cause this error ? (bad RAM maybe?) Most likely, although a bad CPU or cache can cause the same problems too. Most of the time it's RAM, as a bad CPU is generally much more likely to make the machine just crash all the time, not just have smaller issues like this. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] replication modules on postgres
On Mon, 2004-08-02 at 08:51, chinni wrote: > Hi all! > Some time back I discussed the inclusion of replication (e.g. > postgres-R) into postgres. > One of the technical reasons that I understand against such a move is > the application dependence of replication. PostgresR requires a large > amount of code change in postgres. > All this leads to a bitter taste in the minds of my managers who want > to use postgres but can't do without replication, and also they want > to only rely on the main dev path of postgres itself. > > This problem only offers one technically feasible alternative(AFAIK). > If the postgres maintainers would provide a standard API for pluggable > replication modules, then it would be possible for the enterprises to > pick up reliable replication modules from the market and use. Considering that all the other solutions do NOT require changes in the postgresql backend code, I'd say that the libpq IS the pluggable interface they already use. I.e. this is a non-problem. > This API obviously would have to be able to support the whole wide > variety of replication techniques, and hence requires a keen > understanding of all the issues involved. Actually, the simpler this interface, the better, as it is less likely to need to change from one version of pgsql to the next. Hence the use of the native communications protocol. Slony-I is pretty much done with stage one development, and should do what you need. Take a look at it and let us know where it's deficient, if anywhere... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Selecting a specific row
On Wed, 2004-08-04 at 16:11, Cason, Kenny wrote: > Is there an easy way to select, say, the 15th row in a table? I can't > use a sequence number because rows will sometimes be deleted resulting > in the 15th row now being a different row. I need to be able to select > the 15th row regardless of whether it is the same 15th row as the last > select. SQL itself has no natural ordering, so I'll assume you're doing something like this: select * from table order by seq_field Just add offset and limit to the end: select * from table order by seq_fields limit 1 offset 15; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] enforcing a join type
On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote: > Hi, > If I want the planner/optimizer to always choose merge join when it needs to > join relations. How can I do it ? >From my past experience, I'd guess what you're really trying to do is STOP the planner from choosing a nested_loop join, in which case it's quite easy: set enable_nestloop = off; select * from ... Of course, you could apply the same basic trick to all other join methods, and postgresql would then favor using the merge join. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] enforcing a join type
As this is not really a hacking issue, I'm moving it out of hackers and into general. Please post all replies there not in hackers. Anyway, I'm afraid I'd have to ask WHY you're trying to just disable it? Is the query planner making the wrong decision with good statistics, or are you getting bad statistics? Can you post an explain analyze of the query(s) that are making you want to make this change? Just turning off a join method isn't the way to fix PostgreSQL, getting it to pick the right one is. On Wed, 2004-08-04 at 17:26, Hicham G. Elmongui wrote: > I didn't mean about doing this from a front end. I want to disable > nested_loop and hash_join from the backend. > I tried to set the variables (enable_nestloop and enable_hashjoin) in > costsize.c, but this didn't do it. > Thanks, > --h > > > > > -Original Message- > From: Scott Marlowe [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 04, 2004 5:41 PM > To: Hicham G. Elmongui > Cc: [EMAIL PROTECTED] > Subject: Re: [HACKERS] enforcing a join type > > On Wed, 2004-08-04 at 14:53, Hicham G. Elmongui wrote: > > Hi, > > If I want the planner/optimizer to always choose merge join when it needs > to > > join relations. How can I do it ? > > >From my past experience, I'd guess what you're really trying to do is > STOP the planner from choosing a nested_loop join, in which case it's > quite easy: > > set enable_nestloop = off; > select * from ... > > Of course, you could apply the same basic trick to all other join > methods, and postgresql would then favor using the merge join. > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Ready for Beta ... ?
On Sun, 2004-08-08 at 09:58, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > The only open issue I see for beta1 is perhaps disabling vacuum delay. > > Given that Jan is clearly in the minority on that, I suggest we just > turn it off for beta1. We can always turn it on later if he manages > to convince more people. Does this mean the feature wont be in 8.0, or that it will be set to 0 page delay by default? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Windows binary in the beta directory?
Since this is the first release supporting Windows natively, and Windows people tend to not have any development environment by default, should there be a windows binary version of some sort into the beta directory, or is that something that will come along later with setup.exe type packaging or something? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] VACUUM DELAY
On Mon, 2004-08-09 at 05:19, Gaetano Mendola wrote: > Hi all, > I have seen the big debat about to have the delay > off or on by default. > > Why not enable it by default and introduce a new > parameter to vacuum command itself ? Something like: > > > VACUUM WITH DELAY 100; > > > this will permit to change easilly the delay in the maintainance > scripts. The problem, I believe, is that any delay at all results in a VERY slow vacuum run (like 3 to 5 times slower) and for some people, this will be such unexpected behaviour they may believe postgresql is broken, or just want the older, faster vacuum, especially in a development environment. Imagine an increase from 1 to 5 minutes on an otherwise duplicate database from a 7.4 machine. I'll personally be running the delay and autovacuum on any machine I'll be running, and I think once the autovacuum is integrated, it might make sense to have a vacuum command just toss an entry in a que saying "vacuum this table next scheduled run" and return immediately with a NOTICE: vacuum (on tablex) scheduled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] Need pointers to "standard" pg database(s) for
On Fri, 2006-02-17 at 10:51, Ron wrote: > I assume we have such? Depends on what you wanna do. For transactional systems, look at some of the stuff OSDL has done. For large geospatial type stuff, the government is a good source, like www.usgs.gov or the fcc transmitter database. There are other ones out there. Really depends on what you wanna test. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > Stephan Szabo wrote: > > > justify_days doesn't currently do anything with this result --- it > > > thinks its charter is only to reduce day components that are >= 30 days. > > > However, I think a good case could be made that it should normalize > > > negative days too; that is, the invariant on its result should be > > > 0 <= days < 30, not merely days < 30. > > > > What about cases like interval '1 month -99 days', should that turn into > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > I think it should be the later. It is best to have a single sign, and I > think it is possible in all cases: > > '2 mons -1 days' > > could be adjusted to '1 mons 29 days'. There's a part of me that thinks the WHOLE THING should be positive or negative: -(2 months 1 day) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-01 at 14:27, Bruce Momjian wrote: > Scott Marlowe wrote: > > On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > > > Stephan Szabo wrote: > > > > > justify_days doesn't currently do anything with this result --- it > > > > > thinks its charter is only to reduce day components that are >= 30 > > > > > days. > > > > > However, I think a good case could be made that it should normalize > > > > > negative days too; that is, the invariant on its result should be > > > > > 0 <= days < 30, not merely days < 30. > > > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > > > I think it should be the later. It is best to have a single sign, and I > > > think it is possible in all cases: > > > > > > '2 mons -1 days' > > > > > > could be adjusted to '1 mons 29 days'. > > > > There's a part of me that thinks the WHOLE THING should be positive or > > negative: > > > > -(2 months 1 day) > > But it isn't '-2 months, -1 day'. I think what you are saying is what I > am saying, that we should make the signs consistent. Pretty much. It just seems wrong to have different signs in what is essentially a single unit. We don't say 42 degrees, -12 minutes when measuring arc, do we? Then again, maybe some folks do. It just seems wrong to me. ---(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: [HACKERS] [SQL] Interval subtracting
On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote: > Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe: > > > > But it isn't '-2 months, -1 day'. I think what you are saying is what I > > > am saying, that we should make the signs consistent. > > > > Pretty much. It just seems wrong to have different signs in what is > > essentially a single unit. > > > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > > again, maybe some folks do. It just seems wrong to me. > > But we do say both "quarter past three" (3 hours 15 min) and "quarter to > four" (4 hours -15 min) when talking about time. But the military says 1515 or 1545 or 0315 or 0345, because if they get the time wrong they shell the wrong place and kill their own soldiers. I.e. getting it right is important to them. So they use exact language. I prefer the more exact way. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-08 at 06:07, Markus Schaber wrote: > Hi, Scott, > > Scott Marlowe wrote: > > >>But it isn't '-2 months, -1 day'. I think what you are saying is what I > >>am saying, that we should make the signs consistent. > > Pretty much. It just seems wrong to have different signs in what is > > essentially a single unit. > > > > We don't say 42 degrees, -12 minutes when measuring arc, do we? Then > > again, maybe some folks do. It just seems wrong to me. > > But we say "quarter to twelve", at least in some areas on this planet. > > The problem is that months have different lengths. '2 months - 1 day' > can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 > month 30 days', depending on the timestamp we apply the interval. I made this point before. In the military they say 1145 or 2345 instead of quarter to twelve, because 1: there are two "quarter to twelves" a day, and 2: It's easy to get it confused. For same reasons, i.e. a need for precision, I find it hard to accept the idea of mixing positive and negative units in the same interval. The plus or minus sign should be outside of the interval. Then, it's quite certain what you mean. If you say select '2006-06-12'::date - interval '1 month 2 days' there is no ambiguity. If you say: select '2006-06-12'::date + interval '-1 month -2 days' do you mean (1 month - 2 days) subtracted from the date, or do you mean to subtract 1 month, then 2 days from the date? Putting the + or - outside the interval seems to make the most sense to me. Allowing them inside makes no sense to me. And colloquialisms aren't really a good reason. :) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
On Thu, 2006-05-11 at 12:18, Jim C. Nasby wrote: > On Wed, May 10, 2006 at 08:31:54PM -0400, Tom Lane wrote: > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > On Tue, May 09, 2006 at 03:13:01PM -0400, Tom Lane wrote: > > >> PFC <[EMAIL PROTECTED]> writes: > > >>> Fun thing is, the rowcount from a temp table (which is the problem > > >>> here) > > >>> should be available without ANALYZE ; as the temp table is not > > >>> concurrent, > > >>> it would be simple to inc/decrement a counter on INSERT/DELETE... > > >> > > >> No, because MVCC rules still apply. > > > > > But can anything ever see more than one version of what's in the table? > > > > Yes, because there can be more than one active snapshot within a single > > transaction (think about volatile functions in particular). > > Any documentation on how snapshot's work? They're a big mystery to me. > :( http://www.postgresql.org/docs/8.1/interactive/mvcc.html Does the concurrency doc not cover this subject well enough (I'm not being sarcastic, it's a real question) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL on z/OS
On Wed, 2004-09-01 at 11:34, David Parker wrote: > I am not currently working on z/OS, and don't have access to a z/OS > environment, but I did a little work with getting OpenLDAP ported to > z/OS at my previous company. I assume you mean Unix System Services > (USS) under z/OS, rather than zLinux. Since zLinux is essentially Suse > ported to the Z architecture, I don't imagine there would be major > issues there. Given the fact that there is a S390 emulator out there (I've played with it a bit, it's pretty cool) is there a reasonable chance of them contributing to upgrade / rewrite it into a z/OS emulator? Such a thing would be useful for development. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OT moving from MS SQL to PostgreSQL
On Sun, 2004-10-03 at 06:33, stig erikson wrote: > Hello. > i have an slightly off topic question, but i hope that somebody might know. > > at the moment we have a database on a MS SQL 7 server. > This data will be transfered to PostgreSQL 7.4.5 or PostgreSQL 8 (when > it is released). so far so good. > > the question now arises, this current database is used in web > application made with ASP on IIS5. The idea is to move the database and > the application to a linux or unix environment. Is there a tool that can > be used convert ASP pages into PHP (or any other language suitable for > linux/unix), or should we prepare to rewrite most of the code? > > Is there a tool, some add-in to apache perhaps that can run ASP code on > linux/unix, this would help to have the system running while we recode > the application. There are a few tools I've seen that will try to convert ASP to PHP, but for the most part, they can't handle very complex code, so you're probably better off just rewriting it and learning PHP on the way. By the way, I have moved this over to -general, as this is quite off topic for -hackers. Next person to reply please remove the pgsql-hackers address from the CC list please. ---(end of broadcast)--- TIP 3: 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: [PATCHES] [HACKERS] ARC Memory Usage analysis
On Mon, 2004-10-25 at 23:53, Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> writes: > >> Another issue is what we do with the effective_cache_size value once we > >> have a number we trust. We can't readily change the size of the ARC > >> lists on the fly. > > > Huh? I thought effective_cache_size was just used as an factor the cost > > estimation equation. > > Today, that is true. Jan is speculating about using it as a parameter > of the ARC cache management algorithm ... and that worries me. Because it's so often set wrong I take it. But if it's set right, and it makes the the database faster to pay attention to it, then I'd be in favor of it. Or at least having a switch to turn on the ARC buffer's ability to look at it. Or is it some other issue, having to do with the idea of knowing effective cache size cause a positive effect overall on the ARC algorhythm? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
