Re: [HACKERS] PostgreSQL pre-fork speedup
We used to run persistent connection until the DB servers got maxed out because of too many idle connections sucking up all the memory. Web servers run different loads than database servers and persistent connections are notorious for crashing your DB. Connection pooling (eg. SQLRelay) didn't work either because we needed to connect to hundreds of DB servers from each web server. Imagine having 200+ open connections on the web server and how many more of these connections remain idle. The situation gets worse when you multiply by an even greater number of web servers connected to all these database servers. Do the math! We're talking large server farm here, not 2 or 3 machines. Saving that X ms can be substantial for large number of simultaneous connections and shouldn't be neglected, otherwise why have persistent connection or connection pooling in the first place. Imagine every query uses up that X ms of time just for connecting/forking. It adds up to a lot from experience. I think pre-forking can be beneficial and is a lot simpler than to rewrite a multi-threaded DB server. Pre-forking would not consume as much memory as persistent connections because it scales with the database load and NOT with the web server load. I'm guessing pre-forking will benefit more on systems where launching a new process is expensive (Win32, certain UNIXes). Here's a snippet from one of the Apache's conferences: Traditionally TCP/IP servers fork a new child to handle incoming requests from clients. However, in the situation of a busy web site, the overhead of forking a huge number of children will simply suffocate the server. As a consequence, Apache uses a different technique. It forks a fixed number of children right from the beginning. The children service incoming requests independently, using different address spaces. Apache can dynamically control the number of children it forks based on current load. This design has worked well and proved to be both reliable and efficient; one of its best features is that the server can survive the death of children and is also reliable. It is also more efficient than the canonical UNIX model of forking a new child for every request. Beside solving my own problems, having a pre-fork solution will benefit PostgreSQL too. MySQL is reputated for having a fast connection and people know it because you cannot avoid simple queries (e.g. counters, session retrieval, etc). The truth of the matter is many people still operate on connect/query/disconnect model running simple queries and if you can satisfy these people then it can be a big marketing win for PostgreSQL. Many web hosting companies out there don't allow persistent connection, which is where MySQL shines. Over and over again, we hear people say how MySQL is fast for the Web because it can connect and execute simple queries quickly. Take for instance http://www-css.fnal.gov/dsg/external/freeware/pgsql-vs-mysql.html MySQL handles connections very fast, thus making it suitable to use MySQL for Web - if you have hundreds of CGIs connecting/disconnecting all the time you'd like to avoid long startup procedures. and http://www-css.fnal.gov/dsg/external/freeware/Repl_mysql_vs_psql.html MySQL handles connections and simple SELECTs very fast. Likely, PostgreSQL is just as fast but if people don't see that on the first try running a simple query, then MySQL already won the war when it comes to speed. Other benchmark I came across: http://www.randomnetworks.com/joseph/blog/?eid=101 __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] inconsistent owners in newly created databases?
Dear Tom, However, I feel that the owner should own the public schema and maybe some other stuff to be carefully selected, without bluring that important distinction? From a definitional standpoint I don't have a problem with that. Good. From an implementation standpoint, I fear it would be much more trouble than it is worth. What is worth is having a sound tool with as few little surprises as possible. There are a lot of little surprises in pg. None of them worth the trouble, but collectively, this would make pg adoption easier. I've some time for this kind of small scale fix, but as this is not the main agenda here around, and it is very difficult to pass even small things. You can't easily connect to another database. That's what I derived from browsing the source code. Possibly it would work to have this housekeeping done in the first backend to connect to the new database, but I don't think it could be done directly by CREATE DATABASE. Well, this describe basically my feeling about the implementation. If I have some time I may send a proof of concept implementation. I though of the following lines: - in createdb, add a some todo file into the new database directory with the set of sql commands to be executed on the first connection. - on a connection in postgres backend, (1) check for this todo file (2) if it exists, lock something appropriate [or could be directly in (1)?] if the file [still] exists, execute these commands as a super user remove the file unlock (3) back to normal processing This approach would it make easy to change/update the housekeeping updates if necessary. The set of appropriate sql commands in still to be discussed... Another heavier but more general approach would be to add a boolean to pg_database to tell whether the first connection housekeeping was performed, and maybe to maintain the set of queries to be executed in another system table, so that it could be updated by modules that would need it. This would be some kind of sql-hook for database creation events. I'm rather inclined to try the former option;-) -- Fabien Coelho - [EMAIL PROTECTED] ---(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] I need Help
El Mar 04 May 2004 01:50, Christopher Kings-Lynne escribió: I installed postgresql 7.4 in my computer, I'm using redhat 9.0 . I installed pgadmin III but I can't to conecct to the server. The port 5432 is not open. You need to set tcpip_socket = true in your postgresql.conf. And in the case of RH check that the firewall isn't blocking that port. P.D.: Olivia, I would suggest you to use the pgsql-general list for this kind of questions, were you are likely to get more and maybe even better answers. If you have problems with your english, try one of the local speeking lists. -- 08:30:02 up 5 days, 13:52, 2 users, load average: 0.93, 0.86, 0.79 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(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] inconsistent owners in newly created databases?
Fabien COELHO [EMAIL PROTECTED] writes: Another heavier but more general approach would be to add a boolean to pg_database to tell whether the first connection housekeeping was performed, I was envisioning a bool column added to pg_database, and having the set of operations just hard-coded into the backend. The only input information the process needs is the DB owner's ID, which it can of course get from the pg_database row. I doubt that reading a file of SQL commands is easier --- the file would have to be created somehow, and since stuff would have to be interpolated into it (owner's ID) you'd end up with a lot of mechanism that's very different from anything else in the backend. One definitional issue that remains to be resolved is just what is the public schema anyway?. It is not a built-in object in the same sense that pg_catalog is. It could be deleted, or even deleted and re-created. So you certainly have to be prepared for the possibility of it not being there (strike one for the simple file of SQL commands). What I want to know is whether we want to forcibly change owner of any random schema that happens to be named public? Or should we insist on it having the original OID? Or some other way of identifying it? In the same vein: we probably need to alter the ACL for public so that its privileges appear to flow from the object owner and not from the postgres user. What do we do if the ACL is in a non-default state? regards, tom lane ---(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] Weird prepared stmt behavior
On Tue, May 04, 2004 at 12:03:16AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Right. But note that Tom wants to distinguish between statements created via PREPARE (which would rollback) from those created via a Prepare message (which wouldn't). Actually, no, I'd prefer not to make such a distinction; I'd be happy with SQL-level PREPARE being nontransactional. I'd be willing to put up with that distinction if someone shows it's needed, but so far there's not been a really good argument advanced for it, has there? Will do. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) I would rather have GNU than GNOT. (ccchips, lwn.net/Articles/37595/) ---(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] Call for 7.5 feature completion
On Sat, May 01, 2004 at 09:03:03AM +0200, Hans-Jürgen Schönig wrote: If a feature freeze is made on August 1st or even later it would be ok because nobody is doing major database changes in summer anyway. You seem to forget that half of the world is not in summer on August 1st. I admit that 90% of the developers are not on _this_ half of the world though ;-) My point is that not necessarily development will stop at that time. Moreover, if there is nobody to test the beta, what good does to have a feature freeze? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Just treat us the way you want to be treated + some extra allowance for ignorance.(Michael Brusser) ---(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] inconsistent owners in newly created databases?
Dear Tom, Another heavier but more general approach would be to add a boolean to pg_database to tell whether the first connection housekeeping was performed, I was envisioning a bool column added to pg_database, and having the set of operations just hard-coded into the backend. Why not. indeed it simplifies as it avoids the intermediate file. Also, if there is no objection to modify a system catalog, it is fine for me. The only input information the process needs is the DB owner's ID, which it can of course get from the pg_database row. Sure. I doubt that reading a file of SQL commands is easier. I agree, you're idea is simpler. One definitional issue that remains to be resolved is just what is the public schema anyway?. What I want to know is whether we want to forcibly change owner of any random schema that happens to be named public? Or should we insist on it having the original OID? Or some other way of identifying it? In the same vein: we probably need to alter the ACL for public so that its privileges appear to flow from the object owner and not from the postgres user. I was thinking about something fuzzy enough as: UPDATE pg_catalog.pg_namespace SET nspowner=datdba, nspacl=NULL -- NULL means default rights... FROM pg_catalog.pg_database WHERE nspname NOT LIKE ALL(ARRAY['pg_%','information_schema']) AND datname=CURRENT_DATABASE(); But it can be changed to anything else... What do we do if the ACL is in a non-default state? Well, one could suggest to fix the aclitem grantor to the owner, but as an array of opaque type it is not very easy to manipulate from a query. Or it could be a feature that non system schemas belong to the owner and are initialized with the default rights, just as they would have been if created by the owner with a CREATE SCHEMA... The later is simple and makes sense anyway for a newly created database. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hacking postgres backend process
On Wed, Apr 28, 2004 at 08:26:09AM -0700, Carl E. McMillin wrote: I posted this subject on General discussion-list but got no takers. I'll restate my query and be as brief as I possible. What are the issues/dangers involved in putting an external process-execution call in instance of main postgres-backend thread of execution? I'm not sure of all the issues it has, but as you probably already know, a C function has access to anything inside the server process. This means it can corrupt private structures, look memory and data bypassing privileges, etc; and if you get an uncaught SIGSEGV the backend will die and the postmaster will terminate all running backends. Basically if you are in constant fear you are in the right shift of mind to do it ... check every return code, make sure you don't write unassigned memory, make sure the function wears its mithril shirt at all times, etc. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) If it wasn't for my companion, I believe I'd be having the time of my life (John Dunbar) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] inconsistent owners in newly created databases?
Fabien COELHO [EMAIL PROTECTED] writes: I was thinking about something fuzzy enough as: UPDATE pg_catalog.pg_namespace SET nspowner=datdba, nspacl=NULL -- NULL means default rights... The later is simple and makes sense anyway for a newly created database. No, I don't think it does. The DBA presently can set up a site-wide policy about use of public by altering its permissions in template1. For example, he might revoke create access from most users. People will be surprised if that fails to carry over to created databases. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] The features I'm waiting for.
For me, the only features I'm likely to use in the upcoming releases are nested transactions. While PITR is a great selling point, and the Windows Port is something I do look forward to, having to do half my job programming windows boxes, nested transactions are a feature I can genuinely use in my daily (maybe weekly??? :-) life. While a focus on things that make postgresql more market acceptable are important, the things that make it more feature complete to me as a user are the things I'd gladly wait an extra month or two for. But I'm not programming any of the code, so I'm just sayin'... ---(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] inconsistent owners in newly created databases?
Dear Tom, UPDATE pg_catalog.pg_namespace SET nspowner=datdba, nspacl=NULL -- NULL means default rights... The later is simple and makes sense anyway for a newly created database. No, I don't think it does. The DBA presently can set up a site-wide policy about use of public by altering its permissions in template1. For example, he might revoke create access from most users. People will be surprised if that fails to carry over to created databases. Ok, I understand that. So that would mean switching all grantors to the owner in the aclitem array? Maybe some function would be useful for that, so as to stick to SQL: UPDATE pg_namespace SET nspowner = datdba, nspacl = aclitems_switch_grantor(nspacl, datdba) FROM ... WHERE ...; but I'm not sure adding such an horrible user function in pg_proc would be welcome, as aclitem accessors were removed two days ago. The alternative is to do it in C within the backend, but I would have liked the plain SQL better. Just a mater of taste, I guess. Pg backend philosophy: why writing SQL if you can do it in C? ;-) I'll have a look at it if I have time, maybe over the week-end. Thanks for your insight. -- Fabien Coelho. ---(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] The features I'm waiting for.
scott.marlowe wrote: For me, the only features I'm likely to use in the upcoming releases are nested transactions. While PITR is a great selling point, and the Windows Port is something I do look forward to, having to do half my job programming windows boxes, nested transactions are a feature I can genuinely use in my daily (maybe weekly??? :-) life. While a focus on things that make postgresql more market acceptable are important, the things that make it more feature complete to me as a user are the things I'd gladly wait an extra month or two for. But I'm not programming any of the code, so I'm just sayin'... I'm sure everybody has their own favorite feature. But I can say quite confidently that the upcoming release contains the most number of highly anticipated features ever. Nested transaction, 2-phase commit, Windows port... I mean these are all major stuffs. They are paving the way of deployments of Postgres in new areas and applications. Plus don't forget all the other sweet goodies like autovacuum and PITR. But the next release could also be the buggies version ever, due to the number of these new features. :-) -- dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] The features I'm waiting for.
On Tue, 4 May 2004, David Garamond wrote: scott.marlowe wrote: For me, the only features I'm likely to use in the upcoming releases are nested transactions. While PITR is a great selling point, and the Windows Port is something I do look forward to, having to do half my job programming windows boxes, nested transactions are a feature I can genuinely use in my daily (maybe weekly??? :-) life. While a focus on things that make postgresql more market acceptable are important, the things that make it more feature complete to me as a user are the things I'd gladly wait an extra month or two for. But I'm not programming any of the code, so I'm just sayin'... I'm sure everybody has their own favorite feature. But I can say quite confidently that the upcoming release contains the most number of highly anticipated features ever. Nested transaction, 2-phase commit, Windows port... I mean these are all major stuffs. They are paving the way of deployments of Postgres in new areas and applications. Plus don't forget all the other sweet goodies like autovacuum and PITR. But the next release could also be the buggies version ever, due to the number of these new features. :-) Agreed. My only real point was that userland features are the reason I upgrade. Simple stuff like the inclusion of regex in the substring function in version 7.3 or 7.4 was awesome. It was small and simple, and made my life MUCH easier. And while I might not be hacking the code, I'm quite willing to be a beta tester. :-0 So, thanks to all the hackers, for doing such a great job. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] The features I'm waiting for.
David Garamond wrote: scott.marlowe wrote: For me, the only features I'm likely to use in the upcoming releases are nested transactions. While PITR is a great selling point, and the Windows Port is something I do look forward to, having to do half my job programming windows boxes, nested transactions are a feature I can genuinely use in my daily (maybe weekly??? :-) life. While a focus on things that make postgresql more market acceptable are important, the things that make it more feature complete to me as a user are the things I'd gladly wait an extra month or two for. But I'm not programming any of the code, so I'm just sayin'... I'm sure everybody has their own favorite feature. But I can say quite confidently that the upcoming release contains the most number of highly anticipated features ever. Nested transaction, 2-phase commit, Windows port... I mean these are all major stuffs. They are paving the way of deployments of Postgres in new areas and applications. Plus don't forget all the other sweet goodies like autovacuum and PITR. But the next release could also be the buggies version ever, due to the number of these new features. :-) The point is that if we stay to the June 1 feature freeze, you will not have all those features in 7.5, only a few of them. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] The features I'm waiting for.
David Garamond wrote: scott.marlowe wrote: I'm sure everybody has their own favorite feature. But I can say quite confidently that the upcoming release contains the most number of highly anticipated features ever. Nested transaction, 2-phase commit, Windows port... I mean these are all major stuffs. They are paving the way of deployments of Postgres in new areas and applications. Plus don't forget all the other sweet goodies like autovacuum and PITR. But the next release could also be the buggies version ever, due to the number of these new features. :-) If you randomly flipped 20% of the bits in the postgres binary you'd not find it to be more buggy than the Postgres95/early 6.x series... Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Resource allocation?
Kind people, I'm not sure where in the docs to start looking for this, but here's the situation: I'd like to be able to run large batch-process jobs for caching, but limit the total resource utilization on them, something like nice(1). Better still would be some way to put a max on total resources be able to allocate from that. Does PostgreSQL have facilities for such a thing? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Robert Treat wrote: On Tuesday 27 April 2004 15:12, Alvaro Herrera wrote: You know, that's kind of the point of all things related to MySQL. It's better than nothing. PostgreSQL doesn't do things because it's better than nothing. snip (Same as how MySQL guesses the result of a modulo operation, and gets it wrong. They don't care and you can read that on the manual. In Postgres, this is a bug.) Hey Alvaro, are you familiar with worse is better philosphy in software development and how that leads to adoption rates? It basically states that simplicity is the ultimate design goal over correctness, consitency, and completness. Because of this more people are able to quickly adopt a technology, which allows the incorrectness/inconsistency/incompletness to be address by new comers and gradually bring the software up to higher standards. I was reading some blogs the other day that applied this to PHP's adoption rate over Java and .net, but your comment made me think this really applies to my$ql and postgresql as well. check out http://www.sitepoint.com/forums/showpost.php?p=1121502postcount=2 for a bit more. Interesting analysis. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Tuesday 27 April 2004 15:12, Alvaro Herrera wrote:
You know, that's kind of the point of all things related to MySQL.
It's better than nothing. PostgreSQL doesn't do things because it's
better than nothing. snip
(Same as how MySQL guesses the result of a modulo operation, and gets it
wrong. They don't care and you can read that on the manual. In
Postgres, this is a bug.)
Hey Alvaro,
are you familiar with worse is better philosphy in software development and
how that leads to adoption rates? It basically states that simplicity is the
ultimate design goal over correctness, consitency, and completness. Because
of this more people are able to quickly adopt a technology, which allows the
incorrectness/inconsistency/incompletness to be address by new comers and
gradually bring the software up to higher standards. I was reading some
blogs the other day that applied this to PHP's adoption rate over Java and
.net, but your comment made me think this really applies to my$ql and
postgresql as well. check out
http://www.sitepoint.com/forums/showpost.php?p=1121502postcount=2 for a bit
more.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Tue, May 04, 2004 at 03:06:53PM -0400, Robert Treat wrote: On Tuesday 27 April 2004 15:12, Alvaro Herrera wrote: You know, that's kind of the point of all things related to MySQL. It's better than nothing. PostgreSQL doesn't do things because it's better than nothing. snip (Same as how MySQL guesses the result of a modulo operation, and gets it wrong. They don't care and you can read that on the manual. In Postgres, this is a bug.) Hey Alvaro, are you familiar with worse is better philosphy in software development and how that leads to adoption rates? Yeah, I've read about it. I'm not sure which side of the do I sit on, though. The wikipedia entry may be a good read: http://en.wikipedia.org/wiki/Worse_is_better Note that it puts correctness and consistency after simplicity, but this not means that they are completely put away. I think SQL (as in SQL standard) is not modelled after this idea: SQL tries to be complete rather than simple. I may be wrong though. Certainly MySQL does away with completeness and tries to achieve simplicity, while the opposite could be said of Postgres. Fortunately, Postgres has apparently caught up with developer mass, so it may yet be able to win against MySQL ... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Linux transformó mi computadora, de una `máquina para hacer cosas', en un aparato realmente entretenido, sobre el cual cada día aprendo algo nuevo (Jaime Salinas) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Robert Treat [EMAIL PROTECTED] writes: On Tuesday 27 April 2004 15:12, Alvaro Herrera wrote: You know, that's kind of the point of all things related to MySQL. It's better than nothing. PostgreSQL doesn't do things because it's better than nothing. snip (Same as how MySQL guesses the result of a modulo operation, and gets it wrong. They don't care and you can read that on the manual. In Postgres, this is a bug.) Hey Alvaro, are you familiar with worse is better philosphy in software development and how that leads to adoption rates? It basically states that simplicity is the ultimate design goal over correctness, consitency, and completness. Because of this more people are able to quickly adopt a technology, which allows the incorrectness/inconsistency/incompletness to be address by new comers and gradually bring the software up to higher standards. I was reading some blogs the other day that applied this to PHP's adoption rate over Java and .net, but your comment made me think this really applies to my$ql and postgresql as well. check out http://www.sitepoint.com/forums/showpost.php?p=1121502postcount=2 for a bit more. The problem with the Worse is Better philosophy is that it almost totally overlooks price, which is arguably the most important factor in deciding which technologies get adopted. The real trick is being good enough at the lowest price. When MySQL became the de-facto web database (back in the Postgres95 and Postgres 6.X days) PostgreSQL simply wasn't good enough for most sites. PostgreSQL, in those days, was slow, buggy, and decidedly non-standard (anyone else remember PostQUEL). On the plus side I personally don't think that Free Software databases have really hit their stride yet, and I believe that when they do PostgreSQL is going to be front and center. MySQL is a pretty handy datastore, but PostgreSQL is a far more useful tool for creating complex applications. Jason ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PITR logging control program
On Fri, 2004-04-30 at 04:02, Bruce Momjian wrote: Simon Riggs wrote: Agreed we want to allow the superuser control over writing of the archive logs. The question is how do they get access to that. Is it by running a client program continuously or calling an interface script from the backend? My point was that having the backend call the program has improved reliablity and control over when to write, and easier administration. Agreed. We've both suggested ways that can occur, though I suggest this is much less of a priority, for now. Not no, just not now. Another case is server start/stop. You want to start/stop the archive logger to match the database server, particularly if you reboot the server. I know Informix used a client program for logging, and it was a pain to administer. pg_arch is just icing on top of the API. The API is the real deal here. I'm not bothered if pg_arch is not accepted, as long as we can adopt the API. As noted previously, my original mind was to split the API away from the pg_arch application to make it clearer what was what. Once that has been done, I encourage others to improve pg_arch - but also to use the API to interface with other BAR prodiucts. If you're using PostgreSQL for serious business then you will be using a serious BAR product as well. There are many FOSS alternatives... The API's purpose is to allow larger, pre-existing BAR products to know when and how to retrieve data from PostgreSQL. Those products don't and won't run underneath postmaster, so although I agree with Peter's original train of thought, I also agree with Tom's suggestion that we need an API more than we need an archiver process. I would be happy with an exteral program if it was started/stoped by the postmaster (or via GUC change) and received a signal when a WAL file was written. That is exactly what has been written. The PostgreSQL side of the API is written directly into the backend, in xlog.c and is therefore activated by postmaster controlled code. That then sends a signal to the process that will do the archiving - the Archiver side of the XLogArchive API has it as an in-process library. (The signal is, in fact, a zero-length file written to disk because there are many reasons why an external archiver may not be ready to archive or even up and running to receive a signal). The only difference is that there is some confusion as to the role and importance of pg_arch. OK, I have finalized my thinking on this. We both agree that a pg_arch client-side program certainly works for PITR logging. The big question in my mind is whether a client-side program is what we want to use long-term, and whether we want to release a 7.5 that uses it and then change it in 7.6 to something more integrated into the backend. Let me add this is a little different from pg_autovacuum. With that, you could put it in cron and be done with it. With pg_arch, there is a routine that has to be used to do PITR, and if we change the process in 7.6, I am afraid there will be confusion. Let me also add that I am not terribly worried about having the feature to restore to an arbitrary point in time for 7.5. I would much rather have a good PITR solution that works cleanly in 7.5 and add it to 7.6, than to have retore to an arbitrary point but have a strained implementation that we have to revisit for 7.6. Here are my ideas. (I talked to Tom about this and am including his ideas too.) Basically, the archiver that scans the xlog directory to identify files to be archived should be a subprocess of the postmaster. You already have that code and it can be moved into the backend. Here is my implementation idea. First, your pg_arch code runs in the backend and is started just like the statistics process. It has to be started whether PITR is being used or not, but will be inactive if PITR isn't enabled. This must be done because we can't have a backend start this process later in case they turn on PITR after server start. The process id of the archive process is stored in shared memory. When PITR is turned on, each backend that complete a WAL file sends a signal to the archiver process. The archiver wakes up on the signal and scans the directory, finds files that need archiving, and either does a 'cp' or runs a user-defined program (like scp) to transfer the file to the archive location. In GUC we add: pitr = true/false pitr_location = 'directory, [EMAIL PROTECTED]:/dir, etc' pitr_transfer = 'cp, scp, etc' The archiver program updates its config values when someone changes these values via postgresql.conf (and uses pg_ctl reload). These can only be modified from postgresql.conf. Changing them via SET has to be disabled because they are cluster-level settings, not per session, like port number or checkpoint_segments.
[HACKERS] More Hashing questions
Regarding inter-data-type hash joins, would it make sense to make float
datatypes to hash to the same value as integral data types for integral
values?
Conveniently this would cover the existing special case of -0 and +0 hashing
to the same value. Something like this?
Datum
hashfloat4(PG_FUNCTION_ARGS)
{
float4 key = PG_GETARG_FLOAT4(0);
if (key == (int32)key)
PG_RETURN_UINT32(~(int32)key);
return hash_any((unsigned char *) key, sizeof(key));
}
Incidentally, why do all the floatfoo functions invert the value? It doesn't
seem like that affects the quality of the hash at all.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Hacking postgres backend process
...Basically if you are in constant fear you are in the right shift of mind to do it ... check every return code, make sure you don't write unassigned memory, make sure the function wears its mithril shirt at all times, etc. Hehe! Thanks for the warning. Do you know of anyone that's managed to successfully work these control-structures in with the C api? I've heard some good words apropos PL/Perl to control external processes, but I've also heard there are notable limitations (say absence) with set-returning functions in PL/Perl (tho perhaps under construction). Carl |};-) -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 6:29 AM To: Carl E. McMillin Cc: [EMAIL PROTECTED]; Bob Subject: Re: [HACKERS] Hacking postgres backend process On Wed, Apr 28, 2004 at 08:26:09AM -0700, Carl E. McMillin wrote: I posted this subject on General discussion-list but got no takers. I'll restate my query and be as brief as I possible. What are the issues/dangers involved in putting an external process-execution call in instance of main postgres-backend thread of execution? I'm not sure of all the issues it has, but as you probably already know, a C function has access to anything inside the server process. This means it can corrupt private structures, look memory and data bypassing privileges, etc; and if you get an uncaught SIGSEGV the backend will die and the postmaster will terminate all running backends. Basically if you are in constant fear you are in the right shift of mind to do it ... check every return code, make sure you don't write unassigned memory, make sure the function wears its mithril shirt at all times, etc. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) If it wasn't for my companion, I believe I'd be having the time of my life (John Dunbar) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Multiple Xids in PGPROC?
Hackers, I've whacked the subtrans patch enough so that the simple tests (i.e. non concurrent) for tuple visibility work. I can create a table and populate it in subtransactions, rollback or commit them selectively and get the desired effect at the end. Naturally, catalog entries also behave [somewhat] sanely. Oh, I made pg_subtrans work too. (Though whether it's relatively bug-free is yet to be proven.) I'm now looking at changing the concurrent visibility rules, i.e. utils/time/tqual.c. It seems to me one of the most important parts is making TransactionIdIsInProgress() behave, that is, yield true for every committed and in-progress subtransaction of a current transaction tree. (Not only the topmost Xid, which is what it currently does.) So, the big question is, how do we do this? The most obvious way (to me) is to keep the whole array inside the PGPROC struct. This would be nice because it would only need little modification to access/transam/varsup.c. The main downside is that it potentially requires a lot of shared memory. Can we afford that? I am already keeping the list of committed Xids in a backend-local list, but of course this is not visible to other backends. Does anyone have a better idea? This is crucial. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Voy a acabar con todos los humanos / con los humanos yo acabaré voy a acabar con todos / con todos los humanos acabaré (Bender) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
Alvaro Herrera [EMAIL PROTECTED] writes: So, the big question is, how do we do this? The most obvious way (to me) is to keep the whole array inside the PGPROC struct. ... The main downside is that it potentially requires a lot of shared memory. Can we afford that? No. Shared memory is fixed size, therefore the above is guaranteed to fail. I thought we had devised a solution that did not require expansible shared memory for this. Bruce, Manfred, do you recall how that went? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Multiple Xids in PGPROC?
Alvaro Herrera wrote: Hackers, I've whacked the subtrans patch enough so that the simple tests (i.e. non concurrent) for tuple visibility work. I can create a table and populate it in subtransactions, rollback or commit them selectively and get the desired effect at the end. Naturally, catalog entries also behave [somewhat] sanely. Oh, I made pg_subtrans work too. (Though whether it's relatively bug-free is yet to be proven.) I'm now looking at changing the concurrent visibility rules, i.e. utils/time/tqual.c. It seems to me one of the most important parts is making TransactionIdIsInProgress() behave, that is, yield true for every committed and in-progress subtransaction of a current transaction tree. (Not only the topmost Xid, which is what it currently does.) So, the big question is, how do we do this? The most obvious way (to me) is to keep the whole array inside the PGPROC struct. This would be nice because it would only need little modification to access/transam/varsup.c. The main downside is that it potentially requires a lot of shared memory. Can we afford that? I am already keeping the list of committed Xids in a backend-local list, but of course this is not visible to other backends. I remember going through this. Other backends will use pg_subtrans to know what transactions are in progress. They have to do the standard lookups to find the status of the parent transaction. The backend-local list of xids is needed so the commit can clean up those subtransaction xids so that later transactions don't have to use pg_subtrans. Does this help? Sorry I haven't gotten your patches in yet. Tom is working on some other back patches. Also, do you have a plan to handle some of the more complex issues like locking in subtransactions? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Multiple Xids in PGPROC?
I remember going through this. Other backends will use pg_subtrans to know what transactions are in progress. They have to do the standard lookups to find the status of the parent transaction. The backend-local list of xids is needed so the commit can clean up those subtransaction xids so that later transactions don't have to use pg_subtrans. Is there some solution whereby the common case (99.999% of transactions won't be subtransactoins) is fast, and the uncommon case of being in a subtransaction is slower? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multiple Xids in PGPROC?
Christopher Kings-Lynne wrote: I remember going through this. Other backends will use pg_subtrans to know what transactions are in progress. They have to do the standard lookups to find the status of the parent transaction. The backend-local list of xids is needed so the commit can clean up those subtransaction xids so that later transactions don't have to use pg_subtrans. Is there some solution whereby the common case (99.999% of transactions won't be subtransactoins) is fast, and the uncommon case of being in a subtransaction is slower? Yes, we use an unreserved clog status to indicate a pg_subtrans lookup is required. In non-subtrans cases, no pg_subtrans lookup is required. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Multiple Xids in PGPROC?
I hope not, because for many of us there will be as many (if not more) subtransactions than standard transactions. How can that possibly be true? Every statement executed in postgres is a transaction how many subtransactions are really needed and how can they be as common as normal transactions? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Multiple Xids in PGPROC?
Yup.. And some of us intend on wrapping every single statement in a subtransaction so we can rollback on an error without aborting the main transaction. Point there being main transaction. What i'm saying is that the vast majority of your transactions will be single statements. eg. single selects, single updates, etc. In fact, I would be surprised if tools like psql went very long without doing the same thing so users can recover from spelling mistakes. If the user does an explicit BEGIN, then perhaps we might, but how often does the user do an explicit BEGIN? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple Xids in PGPROC?
On Wed, 2004-05-05 at 00:45, Christopher Kings-Lynne wrote: Yup.. And some of us intend on wrapping every single statement in a subtransaction so we can rollback on an error without aborting the main transaction. Point there being main transaction. What i'm saying is that the vast majority of your transactions will be single statements. eg. single selects, single updates, etc. Actually, they're not. A vast majority of my transactions are over 5 statements -- each of which is eagerly anticipating being wrapped in a subtransaction. In fact, I would be surprised if tools like psql went very long without doing the same thing so users can recover from spelling mistakes. If the user does an explicit BEGIN, then perhaps we might, but how often does the user do an explicit BEGIN? What user? Users aren't allowed in production. Strictly code. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] I need Help
Yes it is possible for both to be on the same pc. Please send mail to the general or novice list if you need more help. On Mon, 2004-05-03 at 11:05, olivia jurado wrote: Hi. I'm from Panama. I don't speak english very well but I'm learning english. I Need help. I installed postgresql 7.4 in my computer, I'm using redhat 9.0 . I installed pgadmin III but I can't to conecct to the server. The port 5432 is not open. I have one computer. If possible to use server and client in this same machine. please help me. thanks _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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
