Re: [HACKERS] PITR Phase 2 - Design Planning
On Thu, May 27, 2004 at 23:02:42 +, Peter Galbavy <[EMAIL PROTECTED]> wrote: > Bruno Wolff III wrote: > >For long running transactions where you want to recover as much as > >possible, > >one might also want to recover up until just before a specific transaction > >committed (as opposed to started). > > If your DB has died and you are recovering it, how do you reestablish a > session so that a transaction can complete ? Doesn't all client > connections assume that a transaction has failed if the connection to > the DB fails ? The context of my suggestion was for recovering up until a transaction which messed things up was committed. I did not want the problem transaction to be committed. If the problem transaction ran for a long time, there might be other transactions that I want to keep, if possible, that committed after the problem transaction started and before it ended. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Clustering system catalog indexes
Is it worth us marking any system catalog indexes as clusterable by default for performance? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Nasty security bug with clustering
No check is performed for being a superuser, the table owner or that it is a system table when marking an index for clustering: usa=> alter table pg_class cluster on "pg_class_oid_index"; ALTER TABLE usa=> select oid from pg_class where relname='pg_class_oid_index'; oid --- 16613 (1 row) usa=> select * from pg_index where indexrelid=16613; indexrelid | indrelid | indkey | indclass | indnatts | indisunique | indisprimary | indisclustered | indexprs | indpred +--++--+--+-+--++--+- 16613 | 1259 | -2 | 1989 |1 | t | f | t | | (1 row) Note how I managed to mark as clustered an index on a system catalog as a non-superuser... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.5 features
Bruce Momjian <[EMAIL PROTECTED]> writes: > Here are features that are being worked on, hopefully for 7.5: > o tablespaces (Gavin) > o nested transactions (Alvaro) > o two-phase commit (Heikki Linnakangas) > o integrated pg_autovacuum (O'Connor) > o PITR (Riggs) > o Win32 (Claudio, Magnus) Not to rain on the parade, but the *only* one of these I have any confidence in seeing for 7.5 is the last (Windows port). The others are still mostly or entirely handwaving, and I do not think we will hold up the release for them once the Windows port starts looking stable enough to beta-test. 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: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Alexey Borzov wrote: Hi! Tim Conrad wrote: My favourite part of it is: MySQL uses traditional row-level locking. PostgreSQL uses something called Multi Version Concurrency Control (MVCC) by default. MVCC is a little different from row-level locking in that transactions on the database are performed on a snapshot of the data and then serialized. New versions of PostgreSQL support standard row-level locking as an option, but MVCC is the preferred method. Nice that you point out that incorrectly stated something. Even nicer that you don't tell me what the correct answer would be. Unfortunanatly, that's the best I could come up with with doing research with the documentation I could find on the subject. MVCC does a lot more than can be easily contained in a sentance. The problem is that in MySQL 1) MyISAM does table-level locking; 2) BDB does row-level locking; 3) InnoDB does MVCC (mostly) like PostgreSQL. PostgreSQL does support row-level locking (SELECT ... FOR UPDATE), table-level locking (LOCK TABLE ...), though this does not *replace* MVCC, as one may understand from the quotation. MySQL's roadmap is complete bullshit. Subselects were first promised in 4.0, which was "not that far away" [1] back in 1998! Well, they are in 4.1, which is still alpha in 2004. I realize this. I also realize that having a nicely defined roadmap would give Postgres a hands up in this category. A hands up in *what* category? In bragging? Should PostgreSQL developers write something along the lines of "PostgreSQL 9i (available Really Soon Now) will also be able to make coffee"? Well, as you know about coffee now, why don't you add "make coffee" to your comparison table, with empty space in MySQL's and commercial DBMSs' columns and "in 9i" in PostgreSQL's one? Maybe. Just for jest-- If you read the Linux Coffee how-to, write a C module, get the right hardware, etc. Yes, PostgreSQL can make coffee! Of course, this would occur outside any sort of transactional control... Seriously, though... I think that it would be helpful to have a list of features which are under active development (not just the ToDo list which are features which we want to develop). We could also have contact info for leads (or maybe a contact via a web form, etc.) as well as status for that feature. As the lead in a project whose roadmap has changed many times due to paid contracts, I don't really see the value of published roadmaps in general. Best Wishes, Chris Travers ---(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] linked list rewrite
Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > Most of your suggestions are agreeable; a few minor quibbles follow. > > >> lfirstcell new function to get first cell, or NULL if none > > [...] > >> llastnode llastcell > > > What do you think of list_head() and list_tail() instead? > > No strong objection, though I thought it was a good idea to include > "cell" in the function names to suggest that what you are getting back > is just a cell and not a List. Anyone else have a preference? I never liked "cell" myself. It is too vague to me. But I see your point that list_head you would think returns the head of the list, not the first element in the list. Maybe list_first and list_last? -- 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] btbulkdelete
Manfred Koizar <[EMAIL PROTECTED]> writes: > Is there a special reason for scanning the leaf pages in *logical* > order, i.e. by following the opaque->btpo_next links? Yes. Read the README file concerning interlocking between indexscans and deletions. regards, tom lane ---(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] PITR Phase 2 - Design Planning
Simon Riggs wrote: > On Tue, 2004-04-27 at 21:56, Rod Taylor wrote: > > > Overall, I'd refer back to the points Bruce raised - you certainly do > > > need a way of finding out the time to recover to, and as others have > > > said also, time isn't the only desirable "recovery point". > > > > Wouldn't it be sufficient to simply use the transaction ID and ensure > > that all the parameters the user might want to use to find that ID can > > be made available in the log files? > > > > Yes, of course, all methods of locating a particular xlog file to stop > at are effectively equivalent. The discussion is mostly about what is > convenient for the user in a real recovery situation. > > >From all that has been said so far, I would implement: > > 1. Recovery to a specific txnid, which is fairly straightforward > 2. Recovery to a specific date/time > a) either by implementing a log inspection tool that shows the txnid for > a PIT > b) implementing recovery to a PIT directly > 3. Recovery to a named checkpoint What if we added transaction id to log_line_prefix? The user could then log all queries and find the xid where they want to stop, but of course that assumes they have enabled such logging, and they have access to the logs. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Is there any method to keep table in memory at startup
Hi thank you for such a useful information... but actually in my case if i keep table in disk it significantly degrades performance and even for a table of 10 rows it takes 1-2 minutes I think u r not beliving it ! am i right for example I create a table in which i use my customized data type say student create table student (Name INDCHAR //INDCHAR is customized data type age integer); now i give query like this select * from student order by name; it will search for it's comparator operator (<) and related function... in that function there is one lookup table if that table is in memory no problem! (oh but it can't be) if it is in disk my program makes connection to database and execute query which is just a select statement on a simple where condition of equality. then closes connection so every time less than operator(<) is called it does the same task.. what i feel in table of 10 rows how many times the < operator will be called(NO idea but must be > 10 times) is there any solution.. thanks in advance regards vinay scott.marlowe wrote: On Wed, 21 Apr 2004 [EMAIL PROTECTED] wrote: Hi I am working on a project in postgres..in which i designed customized data type and operations on it.it requires a look up table.. I have three options regarding this table... 1. Every time a query is executed it creates table assigns values and after execution destroys it...which is overhead.. 2. store table on disk in database and access it whenever required but it degrades the performance 3. whenever psql starts it can load the table in memory from database which is efficient way to do PostgreSQL has no facility to put tables into memory. Assuming this lookup table will be hit quite often, it WILL be in memory for selects. updates / deletes / inserts will have to get flushed out to disk of course. the Linux and BSD kernels are both quite good at keeping commonly used data in memory. I think you are mistaken in assuming that an "on disk" table will be significantly slower than if it was fixed in memory due to the very efficient cachine of the most common unix kernels. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
Andrew Payne wrote: > Also, Apache never had "MyApache", a more popular version that many believe > to be "free" and "open source". > > My point: Apache was successful in a situation that may not apply here. > > Does anyone know of an open source project that *has* successfully displaced > a market of mature, established products WITHOUT a commercial entity > providing marketing, support & direction? Linux. It doesn't have a single company behind it, but several. -- 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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Joshua D. Drake wrote:
I know both. :-).
Seriously - I'd like to raise my voice in favor of installing plpgsql
in template1 by default. I haven't heard any good reason not to (nor
even a bad reason).
If we install plPGSQL by default, we should install any other pl
language that was configured at runtime by default as well. This
includes plPerl, plTCL, and plPython.
Of course only if they were compiled in, but sense they are a part of
the core distribution we shouldn't favor one over the other.
Personally, plpgSQL is only useful to those who are coming from Oracle.
People are more likely to be comfortable with plPython or plPerl than
plpgSQL.
I disagree. I know python and perl and I have never touched Oracle, but
I find plpgsql to be more useful than the others. Mainly because
(1) It is strongly typed, has all the same types as postgresql itself,
consistent support for NULLS, etc.
(2) Upon a casual read of the docs I couldn't figure out how to read any
values from the database other than what was passed into the
pl{perl|python|php} function. I.e., how do you run a select? What client
libraries do you use? Maybe the others just need better docs.
Paul Tillotson
---(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] [pgsql-advocacy] What can we learn from MySQL?
On the other topics... I think the biggest service PGSQL could provide to the open source community is a resource that teaches people with no database experience the fundamentals of databases. If people had an understanding of what a RDBMS should be capable of and how it should be used, they wouldn't pick MySQL. I think that this is incredibly important. Many many developers choose MySQL because MySQL really does make the effort in this regard. This strategy has helped both MySQL and Red Hat become the commercial successes they are today. I believe that postgres is making an effort here. I learned SQL from the postgres docs found in the first few chapters here: http://www.postgresql.org/docs/7.4/static/tutorial.html Those, in my opinion, are excellent, and were way more informative to me than anything on the MySQL website (I tried reading there first). Maybe we are aiming for users who had a clue quotient much lower than I, but those attain an excellent balance between too short and simple to be useful and too long and complicated. Paul Tillotson ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] linked list rewrite
Neil Conway <[EMAIL PROTECTED]> writes: > Most of your suggestions are agreeable; a few minor quibbles follow. >> lfirstcell new function to get first cell, or NULL if none > [...] >> llastnodellastcell > What do you think of list_head() and list_tail() instead? No strong objection, though I thought it was a good idea to include "cell" in the function names to suggest that what you are getting back is just a cell and not a List. Anyone else have a preference? >> set_unionlist_union >> set_ptrUnion list_union_ptr >> list_union_int not currently used >> set_uniono list_union_oid > I don't see the need for anything more than set_difference() and > set_difference_ptr() -- if we're passed a T_IntList or a T_OidList, we > can examine the list tag and do the Right Thing automagically. Well, we could, but AFAIK there are no situations where the caller doesn't know exactly what he wants, and so a switch in the function body is just a waste of cycles. It is probable that these functions are slow enough and seldom-used enough that the extra switch doesn't matter much, so that argument may not have much force. I guess the real question in my mind is whether there is any true gain in symmetry or readability by doing it this way. The other functions such as the cons family *will* need to have the three variants, and so I question that it makes sense to make these different. Also the need for the _ptr variant makes it a bit asymmetric even just when looking at these functions. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Seriously - I'd like to raise my voice in favor of installing plpgsql in template1 by default. I haven't heard any good reason not to (nor even a bad reason). It has to work with older dumps that will try to recreate pl/pgsql themselves explicitly. I offered the same opinion a while back, and was told that plpgsql is a security hole. The we can make it not have USAGE privileges to public by default?? Chris ---(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] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
If anything, I'd rather see the JDBC and ODBC drivers reinstated in the release. More than 56% of the PostgreSQL users (according to the poll) uses JDBC today. ODBC is merely 18% but that might change significantly when the native Win32 port is released. I might have missed something altogether here, my apologies if that's the case, but looking at the coming 7.5 release, 75% of the users will be forced to download stuff from more than one location just to get their basic stuff running. Note that MySQL doesn't bundle them, but they are CLEARLY available for download on their site on all places where MySQL itself is available for download. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Joshua D. Drake wrote: If we install plPGSQL by default, we should install any other pl language that was configured at runtime by default as well. This includes plPerl, plTCL, and plPython. Of course only if they were compiled in, but sense they are a part of the core distribution we shouldn't favor one over the other. Personally, plpgSQL is only useful to those who are coming from Oracle. People are more likely to be comfortable with plPython or plPerl than plpgSQL. Joshua - as you know I'm a perl fan. But plpgsql is special, for a number of reasons, not least that it doesn't require any extra libraries, and should be installable on every postgresql system. I know people hate playing favourites, but you can take that too far. cheers andrew ---(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] PITR Phase 2 - Design Planning
Another idea would be to provide some means to roll a database forwards and backwards. If you're doing a recovery because you did something like an accidental UPDATE SET field = blah without a where clause, what you really care about is going up to the point right before that update. If there's a log viewer with enough detail, that would suffice; otherwise you'd need to test for some specific condition. Of course timestamps would still be useful in this scenario since they can get you close to the transaction in question. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Usability, MySQL, Postgresql.org, gborg, contrib,
On Tue, Apr 27, 2004 at 01:14:08PM -0700, Joshua D. Drake wrote: > If we install plPGSQL by default, we should install any other pl > language that was configured at runtime by default as well. This > includes plPerl, plTCL, and plPython. That certainly makes sense. > Of course only if they were compiled in, but sense they are a part of > the core distribution we shouldn't favor one over the other. > > Personally, plpgSQL is only useful to those who are coming from Oracle. > People are more likely to be comfortable with plPython or plPerl than > plpgSQL. I'll beg to differ. :) I started using PostgreSQL well over a year before Oracle. plpgSQL is SQL based; that's the key difference. I can't back this up, but I'd bet that the majority of professional DBAs would be far more comfortable in plpgSQL than in plPython or plPerl because they deal with SQL day in and day out, while they probably only use other languages for scripting (java being one possible exception). Ultimately I think it's fine that PostgreSQL doesn't favor any of the languages, but I think plpgSQL would be the only logical one to favor if that was going to be the case. But then again, I'm a professional DBA; I suspect someone who is more of a coder than a DBA might disagree. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OLAP versus Materialized Views?
On Tue, 2004-04-27 at 23:47, Jonathan Gardner wrote: > I've just discovered OLAP and it looks like a competing technology with > materialized views. Yes. Read up some more, but don't get sucked in by the marketing. > In a nutshell, OLAP seems to be "pre-storing" the results of potential > queries. When queries are made with those conditions, then the pre-stored > results are used. It seems most common for join conditions or aggregates > with conditions. (Corrections welcome...) > > This seems like a generally useful tool. I expect PostgreSQL should one day > have a feature like this. > > OLAP usage would basically be describing how the data fits together and > predicting what kinds of queries will be made. Then, internally the > implementation would decide what data should be cached. Perhaps it can do > some analysis of the data and the queries being run to come up with a fair > compromise. > > Materialized Views are a simpler concept, but of limited usefulness. Having > a bunch of materialized views that are very similar would be less efficient > overall than having OLAP configured properly. > > Questions: > (1) Has anyone been doing any work or serious thought on anything like this? > Yes. There is Relational OLAP (ROLAP) and Multidimensional OLAP (MOLAP). PostgreSQL can be used as a ROLAP server. > (2) Should I be focusing on OLAP rather than materialized views? With > materialized views, a few specific queries work really well, but related > queries get no benefit. Apparently, with OLAP, a whole class of queries > will get a huge boost. I'm not certain that OLAP and materialized views are > entirely different. No, your work is well received. > > (3) I can't seem to find the paper on the twelve laws of OLAP, but it gets a > lot of mentioning. Does anyone know where I can obtain it? Written by Ted Codd, but not nearly as interesting as his earlier work. > Future Thoughts: > OLAP relies on knowing how the data fits together and what kinds of queries > are being done. It could be possible, just by analyzing the queries being > run, to have OLAP automatically kick in without user intervention. ---(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] Usability, MySQL, Postgresql.org, gborg, contrib,
On Tue, Apr 27, 2004 at 01:32:44PM -0700, Dann Corbit wrote: > > From: Andrew Dunstan [mailto:[EMAIL PROTECTED] > > Seriously - I'd like to raise my voice in favor of installing > > plpgsql in template1 by default. I haven't heard any good reason not > > to (nor even a bad reason). > > I offered the same opinion a while back, and was told that plpgsql is a > security hole. Huh? Maybe we should fix it instead, because it's trusted. -- Alvaro Herrera () "Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] OLAP versus Materialized Views?
I've just discovered OLAP and it looks like a competing technology with materialized views. In a nutshell, OLAP seems to be "pre-storing" the results of potential queries. When queries are made with those conditions, then the pre-stored results are used. It seems most common for join conditions or aggregates with conditions. (Corrections welcome...) This seems like a generally useful tool. I expect PostgreSQL should one day have a feature like this. OLAP usage would basically be describing how the data fits together and predicting what kinds of queries will be made. Then, internally the implementation would decide what data should be cached. Perhaps it can do some analysis of the data and the queries being run to come up with a fair compromise. Materialized Views are a simpler concept, but of limited usefulness. Having a bunch of materialized views that are very similar would be less efficient overall than having OLAP configured properly. Questions: (1) Has anyone been doing any work or serious thought on anything like this? (2) Should I be focusing on OLAP rather than materialized views? With materialized views, a few specific queries work really well, but related queries get no benefit. Apparently, with OLAP, a whole class of queries will get a huge boost. I'm not certain that OLAP and materialized views are entirely different. (3) I can't seem to find the paper on the twelve laws of OLAP, but it gets a lot of mentioning. Does anyone know where I can obtain it? Future Thoughts: OLAP relies on knowing how the data fits together and what kinds of queries are being done. It could be possible, just by analyzing the queries being run, to have OLAP automatically kick in without user intervention. -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PITR Phase 2 - Design Planning
On Tue, 2004-04-27 at 23:11, Rod Taylor wrote: > On Tue, 2004-04-27 at 17:36, Simon Riggs wrote: > > On Tue, 2004-04-27 at 21:56, Rod Taylor wrote: > > > > Overall, I'd refer back to the points Bruce raised - you certainly do > > > > need a way of finding out the time to recover to, and as others have > > > > said also, time isn't the only desirable "recovery point". > > > > > > Wouldn't it be sufficient to simply use the transaction ID and ensure > > > that all the parameters the user might want to use to find that ID can > > > be made available in the log files? > > > Yes, of course, all methods of locating a particular xlog file to stop > > at are effectively equivalent. The discussion is mostly about what is > > convenient for the user in a real recovery situation. > > I see.. The first thing I would need to do is look at /var/log/pgsql. At > that point it really doesn't matter what the identifier is so long as > the identifier is there. > PITR works on the assumption that /var/log/pgsql no longer exists at all. It is suitable for use in bare-metal recovery situations, as well as usage-induced situations. You pick up the pieces, work out what the best identifier is, then plan on using that might not be a pgsql log, it might be: i) literally wallclock - "power went off about 2" ii) other systems logs iii) etc Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Phase 2 - Design Planning
On Fri, 2004-05-28 at 00:02, Peter Galbavy wrote: > Bruno Wolff III wrote: > > For long running transactions where you want to recover as much as possible, > > one might also want to recover up until just before a specific transaction > > committed (as opposed to started). > > If your DB has died and you are recovering it, how do you reestablish a > session so that a transaction can complete ? Doesn't all client > connections assume that a transaction has failed if the connection to > the DB fails ? > Reasonable question... You re-establish connection, but cannot resume the failed transaction. PostgreSQL already has crash recovery...this is for restore from backup scenarios. Best Regards, Simon Riggs ---(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] [pgsql-advocacy] What can we learn from MySQL?
On Tue, 2004-04-27 at 21:56, scott.marlowe wrote: > On Mon, 26 Apr 2004, Andrew Payne wrote: > > > > > Bruce asked an excellent question: > > > > > My question is, "What can we learn from MySQL?" I don't know there is > > > anything, but I think it makes sense to ask the question. > > Ignore the opposition and focus. Look outward, not inward. Best Regards, Simon ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR Phase 2 - Design Planning
On Tue, 2004-04-27 at 17:36, Simon Riggs wrote: > On Tue, 2004-04-27 at 21:56, Rod Taylor wrote: > > > Overall, I'd refer back to the points Bruce raised - you certainly do > > > need a way of finding out the time to recover to, and as others have > > > said also, time isn't the only desirable "recovery point". > > > > Wouldn't it be sufficient to simply use the transaction ID and ensure > > that all the parameters the user might want to use to find that ID can > > be made available in the log files? > Yes, of course, all methods of locating a particular xlog file to stop > at are effectively equivalent. The discussion is mostly about what is > convenient for the user in a real recovery situation. I see.. The first thing I would need to do is look at /var/log/pgsql. At that point it really doesn't matter what the identifier is so long as the identifier is there. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PITR Phase 2 - Design Planning
Bruno Wolff III wrote: For long running transactions where you want to recover as much as possible, one might also want to recover up until just before a specific transaction committed (as opposed to started). If your DB has died and you are recovering it, how do you reestablish a session so that a transaction can complete ? Doesn't all client connections assume that a transaction has failed if the connection to the DB fails ? Or am I just being naive, as usual ? Peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 2 - Design Planning
On Tue, 2004-04-27 at 21:56, Rod Taylor wrote: > > Overall, I'd refer back to the points Bruce raised - you certainly do > > need a way of finding out the time to recover to, and as others have > > said also, time isn't the only desirable "recovery point". > > Wouldn't it be sufficient to simply use the transaction ID and ensure > that all the parameters the user might want to use to find that ID can > be made available in the log files? > Yes, of course, all methods of locating a particular xlog file to stop at are effectively equivalent. The discussion is mostly about what is convenient for the user in a real recovery situation. >From all that has been said so far, I would implement: 1. Recovery to a specific txnid, which is fairly straightforward 2. Recovery to a specific date/time a) either by implementing a log inspection tool that shows the txnid for a PIT b) implementing recovery to a PIT directly 3. Recovery to a named checkpoint Best Regards, Simon Riggs ---(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] [pgsql-advocacy] What can we learn from MySQL?
On Mon, 26 Apr 2004, Andrew Payne wrote: > For those that look to Apache: Apache never had a well-established > incumbent (Oracle), an a well-funded upstart competitor (MySQL). Rob > McCool's NCSA httpd (and later, Apache) were good enough and developed > rapidly enough that they prevented any other HTTP server projects from > getting critical mass. This is a followup to my previous message where I mentioned apache, but did not really followup on it. While Apache is and has been wildly popular for bulk hosing and domain parking, for serious commercial use, Netscape's enterprise server, now Sun One, has long been a leader in commercial web sites. That has now changed too. While Netscape's server was pretty good, it is simply harder to configure, not as versatile as apache, and not as reliable or as fast nowadays. This was not always the case. There was a time when its performance was considered to be much better than apache (I'm thinking about apache 1.3.4 or so) and apache configuration was a black art few understood. with modern gui tools for configuring apache, and the incredible performance gains the late model 1.3 versions and now 2.0.x versions have, it is quickly displacing the more expensive netscape. Apache did not start in first place when it comes to "enterprise" class web servers, no matter how many small personal web sites ran on it. Most commercial companies didn't use it at first. It too had to "earn its stripes" over time and by proving it was better. Now I know people who think Open Source is just so much pie in the sky hand waving philosophical candy who think apache and jboss are the bomb. they'll come around on PostgreSQL too, once someone with some foresight points out the advantages it has. and one of its advantages is that it doesn't have a large monolithic organization driving development. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
Jim C. Nasby wrote: Maybe also a more generic section about how PGSQL is different from other databases. Maybe I'm just dense, but it took me a long time to figure out the whole lack of stored procedures thing (yes, PGSQL obviously has the functionality, but many experienced DBAs won't associate functions with stored procs). Pointing out the documentation on MVCC and how it changes how you want to use the database would be good, as would links to documentation on what postgresql.conf settings you want to change out of the box. I think this is a good idea. And you seem to be suggesting that it includes information on differences in nomenclature as well. On the other topics... I think the biggest service PGSQL could provide to the open source community is a resource that teaches people with no database experience the fundamentals of databases. If people had an understanding of what a RDBMS should be capable of and how it should be used, they wouldn't pick MySQL. I think that this is incredibly important. Many many developers choose MySQL because MySQL really does make the effort in this regard. This strategy has helped both MySQL and Red Hat become the commercial successes they are today. Having a windows port is critical for 'student mindshare'. If PGSQL can't play on windows, professors can't use it. Likewise, installation on OS X should be made as easy as possible. PostgreSQL *can* play on Windows (via Cygwin) and I am not sure that this is so important to student mindshare. Howener, it is important for another reason: a windows port (even one labled "for development use only") would go a LONG way towards recruiting new faces into our community, as it would lower the barrier to entry for using the database (yes, the Cygwin installer because of the ipc stuff is a reasonable barrier to entry). Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] TPC H data
On Wed, 21 Apr 2004, Shalu Gupta wrote: > Hello, > > We are trying to import the TPC-H data into postgresql using the COPY > command and for the larger files we get an error due to insufficient > memory space. > > We are using a linux system with Postgresql-7.3.4 > > Is it that Postgresql cannot handle such large files or is there some > other possible reason. what method(s) are you using to load the data? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
On Wed, 21 Apr 2004 [EMAIL PROTECTED] wrote: > Hi > I am working on a project in postgres..in which i designed customized data type > and operations on it.it requires a look up table.. > I have three options regarding this table... > 1. Every time a query is executed it creates table assigns values and after > execution destroys it...which is overhead.. > > 2. store table on disk in database and access it whenever required but it > degrades the performance > > 3. whenever psql starts it can load the table in memory from database which is > efficient way to do PostgreSQL has no facility to put tables into memory. Assuming this lookup table will be hit quite often, it WILL be in memory for selects. updates / deletes / inserts will have to get flushed out to disk of course. the Linux and BSD kernels are both quite good at keeping commonly used data in memory. I think you are mistaken in assuming that an "on disk" table will be significantly slower than if it was fixed in memory due to the very efficient cachine of the most common unix kernels. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
On Mon, 26 Apr 2004, Andrew Payne wrote: > > Bruce asked an excellent question: > > > My question is, "What can we learn from MySQL?" I don't know there is > > anything, but I think it makes sense to ask the question. > > After watching the traffic on this, the biggest MySQL lesson has gone > largely unmentioned: that a well-funded, well-marketed, focused commercial > entity clearly associated with the project can do wonders to overcome > feature and technical shortcomings. > > At some point (probably there now), I think the lack of a "Postgres, Inc." > is going to hinder adoption. Companies want to 'buy' from vendors that look > like real, viable companies, and provide them products with support, > training, features, and direction. With MySQL, you get one stop shopping. > With Postgres, you've got to find and assemble the parts yourself. Most > CIOs stop there, and start waiting for MySQL to get better before switching > from Oracle. I'm gonna disagree here. I think that not having a postgresql inc to go to means that by the time postgresql becomes ubiquitous, it will be like apache. no company behind it, every company using it. I.e. we'll earn our stripes one at a time by proving we're the better database for 95% of all purposes, and anyone not using postgresql will be behind the power curve and doing themselves no favor. like CIO's who call Open Source "Shareware" and believe that .net provides for a more efficient programming environment, people who poo poo postgresql will find themselves behind the 8 ball in the long run. No need for a postgresql inc to do that, just time, good code, and knowledgable DBAs choosing it more and more often. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Phase 2 - Design Planning
> Overall, I'd refer back to the points Bruce raised - you certainly do > need a way of finding out the time to recover to, and as others have > said also, time isn't the only desirable "recovery point". Wouldn't it be sufficient to simply use the transaction ID and ensure that all the parameters the user might want to use to find that ID can be made available in the log files? ---(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] btbulkdelete
On Mon, 2004-04-26 at 17:24, Manfred Koizar wrote: > On Mon, 26 Apr 2004 14:29:58 +0100, Simon Riggs <[EMAIL PROTECTED]> > wrote: > >> Now that FSM > >> covers free btree index pages this access pattern might be highly > >> nonsequential. > > > >I had considered implementing a mode where the index doesn't keep trying > >to reuse space that was freed by earlier deletes. > > Or maybe an FSM function a la "Give me a free page near this one"? > I think you're statement of the requirement is better, but I suspect more complex to implement. Overall, my feeling about the index code is: - its based upon the earlier Lehman-Yao coding and we know better than that now...various literature - the b-tree code is written with the assumption that the inserts/deletes are more or less randomly distributed and balanced, as is the case with TPC-B - I would prefer a mode where the case of large table inserts - the HISTORY table in TPC-B, or many of the tables in TPC-H was optimised for - so inserts on the leading edge of the index go faster, bulk deletes go faster, but we take the chance that space is not reclaimed effectively by random deletes. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 1 - Code Overview (1)
On Tue, 2004-04-27 at 18:10, Peter Eisentraut wrote: > Simon Riggs wrote: > > New utility aimed at being located in src/bin/pg_arch > > Why isn't the archiver process integrated into the server? > Number of reasons Overall, I initially favoured the archiver as another special backend, like checkpoint. That is exactly the same architecture as Oracle uses, so is a good starting place for thought. We discussed the design in detail on the list and the suggestion was made to implement PITR using an API to send notification to an archiver. In Oracle7, it was considered OK to just dump the files in some directory and call them archived. Later, most DBMSs have gone to some trouble to integrate with generic or at least market leading backup and recovery (BAR) software products. Informix and DB2 provide open interfaces to BARs; Oracle does not, but then it figures it already (had) market share, so we'll just do it our way. The XLogArchive design allows ANY external archiver to work with PostgreSQL. The pg_arch program supplied is really to show how that might be implemented. This leaves the door open for any BAR product to interface through to PostgreSQL, whether this be your favourite open source BAR or the leading proprietary vendors. Wide adoption is an important design feature and the design presented offers this. The other reason is to do with how and when archival takes place. An asynchronous communication mechanism is required between PostgreSQL and the archiver, to allow for such situations as tape mounts or simple failure of the archiver. The method chosen for implementing this asynchronous comms mechanism lends itself to being an external API - there were other designs but these were limited to internal use only. You ask a reasonable question however. If pg_autovacuum exists, why should pg_autoarch not work also? My own thinking about external connectivity may have overshadowed my thinking there. It would not require too much additional work to add another GUC which gives the name of the external archiver to confirm execution of, or start/restart if it fails. At this point, such a feature is a nice to have in comparison with the goal of being able to recover to a PIT, so I will defer this issue to Phase 3 Best regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
> -Original Message- > From: Andrew Dunstan [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 27, 2004 12:55 PM > To: [EMAIL PROTECTED] > Subject: Re: [HACKERS] Usability, MySQL, Postgresql.org, > gborg, contrib, > > > Jim C. Nasby wrote: > > > > >I would still argue that if any language should be installed > by default > >it should be plpgsql and not java. As I mentioned, everyone using a > >database already knows SQL; not nearly as many know java. > > > > > > I know both. :-). > > Seriously - I'd like to raise my voice in favor of installing > plpgsql in > template1 by default. I haven't heard any good reason not to > (nor even a > bad reason). I offered the same opinion a while back, and was told that plpgsql is a security hole. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
I know both. :-). Seriously - I'd like to raise my voice in favor of installing plpgsql in template1 by default. I haven't heard any good reason not to (nor even a bad reason). If we install plPGSQL by default, we should install any other pl language that was configured at runtime by default as well. This includes plPerl, plTCL, and plPython. Of course only if they were compiled in, but sense they are a part of the core distribution we shouldn't favor one over the other. Personally, plpgSQL is only useful to those who are coming from Oracle. People are more likely to be comfortable with plPython or plPerl than plpgSQL. Sincerely, Joshua D. Drake cheers andrew ---(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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR Phase 2 - Design Planning
On Tue, 2004-04-27 at 18:43, Bruno Wolff III wrote: > On Tue, Apr 27, 2004 at 10:38:45 +0100, > Richard Huxton <[EMAIL PROTECTED]> wrote: > > > > Speaking as a DBA, what I usually want to do is restore to "immediately before > > I started the payroll calculation". An actual wall-clock time is mostly > > irrelevant to me. > > For long running transactions where you want to recover as much as possible, > one might also want to recover up until just before a specific transaction > committed (as opposed to started). Sounds like the difference between > and >=, so should be possible... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PITR Phase 2 - Design Planning
On Tue, 2004-04-27 at 08:56, Hans-Jürgen Schönig wrote: > Simon Riggs wrote: > > Since Phase1 is functioning and should hopefully soon complete, we can > > now start thinking about Phase 2: full recovery to a point-in-time. > > > > Previous thinking was that a command line switch would be used to > > specify recover to a given point in time, rather than the default, which > > will be recover all the way to end of (available) xlogs. > > > > Recovering to a specific point in time forces us to consider what the > > granularity is of time. > > We could recover: > > 1.to end of a full transaction log file > > 2.to end of a full transaction > > > > Transaction log files currently have timestamps, so that is > > straightforward, but probably not the best we can do. We would > > rollforward until the xlog file time > desired point in time. > > > > To make (2) work we would have to have a timestamp associated with each > > transaction. ... > > So, I propose: > > > > - appending 8 byte date/time data into xlog file header record > > - appending 4 bytes of time offset onto each xlog record > > - altering the recovery logic to compare the calculated time of each > > xlog record (file header + offset) against the desired point-in-time, > > delivered to it by GUC. > > > > Input is sought from anybody with detailed NTP knowledge, since the > > working of NTP drift correction may have some subtle interplay with this > > proposal. > > > > > I have one question which might be important: If we use timestamps > inside the WAL system to find out where to stop. What happens if > somebody changes the time of the system? (e.g. correcting the system > clock by calling ntpdate). Wouldn't it confuse the PITR system? How do > you plan to handle that? Unfortunately time is nothing which can be used > as a key (at least not from my point of view). > Interesting question. The timestamps written within WAL would be "just data" once written. The recovery process would refer only to that timestamp data, so would not refer to any external notion of time. This is required to ensure that the same recovery will happen identically, no matter how many times you choose to re-run it (or are forced to by external circumstances). If you change the time of the system backwards, this might invalidate the transaction log historythis would effectively create two (or more) xlog records with the same timestamp on them and it would be logically indeterminate which one should limit recovery. In practical terms, I would implement this as "apply all records <= PIT". This would mean that recovery would stop only when the time became larger than PIT, which would only occur at the second (or last) record that had a timestamp equal to PIT. I guess I could put a WARNING in to say "time just went backwards...spoohw" Overall, I'd refer back to the points Bruce raised - you certainly do need a way of finding out the time to recover to, and as others have said also, time isn't the only desirable "recovery point". Best regards, Simon Riggs ---(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] PITR Phase 2 - Design Planning
On Tue, 2004-04-27 at 10:38, Richard Huxton wrote: > On Tuesday 27 April 2004 00:32, Bruce Momjian wrote: > > Simon Riggs wrote: > > > On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote: > > > > On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote: > > > > > I was thinking --- how would someone know the time to use for > > > > > restore? > > > > > > > > I think there should be a way to get a TransactionId and restore up to > > > > that point. It'd be cool, but not required, if the system showed what > > > > valid TransactionIds there are, and roughly what they did (the xlog > > > > code already has "describers" everywhere AFAICS). > > > > > > You're right, I think we should start by implementing the rollforward to > > > a txnid before we consider the rollforward to a specified point-in-time. > > > All the hooks for that are already there... > > > > Yep, sounds like a plan. > > Speaking as a DBA, what I usually want to do is restore to "immediately before > I started the payroll calculation". An actual wall-clock time is mostly > irrelevant to me. > > Suggestion: How about a pg_trans_note table (crap name, I know) that only > permits inserts - records (backend-pid, timestamp, notes). My app inserts > "starting payroll calc" and "ending payroll calc" entries because those are > the points I might wish to sync to. If I want to sync for each individual > transaction in my calculations, my app can do that too. > >From a usability point of view you might want to automatically insert rows on > client connection/table creation etc. You could also delete any rows more > than a week old when archiving WAL files. Not sure I like you're implementation, but the overall idea is great. I'd suggest extending the CHECKPOINT command so you can say: CHECKPOINT e.g. CHECKPOINT 'starting payroll Feb04'; (I'm sure some other DBMS does this...head spinning can;t recall...) the text could just appear in the xlog record data packet... That could then be used as the target recovery point. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Jim C. Nasby wrote: I would still argue that if any language should be installed by default it should be plpgsql and not java. As I mentioned, everyone using a database already knows SQL; not nearly as many know java. I know both. :-). Seriously - I'd like to raise my voice in favor of installing plpgsql in template1 by default. I haven't heard any good reason not to (nor even a bad reason). cheers andrew ---(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] Usability, MySQL, Postgresql.org, gborg, contrib,
On Mon, Apr 26, 2004 at 02:31:27PM -0400, [EMAIL PROTECTED] wrote: > > On Sun, Apr 25, 2004 at 05:15:19PM -0400, [EMAIL PROTECTED] wrote: > >> (5) Programming languages. We need to make a programming language > >> standard > >> in PostgreSQL. plpgsql is good, but isn't someone working on a Java > >> language. That would be pretty slick. > > > > If there's going to be a single standard language, I strongly believe it > > should be plpgsql. Any other language means that you have to find > > something that someone else knows or is willing to learn, whereas anyone > > using a database already knows SQL. plpgsql is simply an extension of > > SQL, and is trivial for anyone who's worked with any other database > > procedural languages to pickup. Asking a DBA to learn java or perl or > > PHP is asking a lot. > > > > If anything I'd like to see more features brought into plpgsql, like > > packages (ala Oracle). > > Sorry, by standard, I meant installed by default, not to the exclusion of > all else. I would still argue that if any language should be installed by default it should be plpgsql and not java. As I mentioned, everyone using a database already knows SQL; not nearly as many know java. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_clog question.
Ahh, perfect, thank you.. On Tue, 2004-04-27 at 16:18, Alvaro Herrera wrote: > On Tue, Apr 27, 2004 at 09:26:38AM -0300, Chris Bowlby wrote: > > > I've been noticing that files in the pg_clog directory test to stay > > around forever, I know they are used to determine the state of > > transaction id's, but according to the docs: > > > > "(However, the urgency of this concern has decreased greatly with the > > adoption of a segmented storage method for pg_clog: it is no longer > > necessary to keep old pg_clog entries around forever.)" > > > > How does one determine which files are no longer neccessary? > > You don't. They are deleted on VACUUM FULL when they are no longer > needed. > > > Is the system configured to clear out stale ones during > > startup/shutdown? > > No need to take the server down. -- Chris Bowlby <[EMAIL PROTECTED]> PostgreSQL Inc. ---(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: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Tue, Apr 27, 2004 at 12:57:46PM -0400, Tim Conrad wrote: > Seriously, though. I was looking through the list yesterday trying > to figure out something, and it was kind of hard to do.But, more to > my point, this stuff is in the MySQL manual, making it easy to find. > (Yes. I know what MySQL includes kind of blows, but, it's better > than nothing) 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." My first patch here was rejected, not because it didn't do anything useful (it did), but because "it didn't solve the complete problem." I had to do a lot more work to get it accepted. Similarly, people here don't want to showcase a list of things that will be on the next release, because we _don't know_ what will be on the next release. There are guesses, but guesses are not good enough. (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.) In PostgreSQL there are no guesses. There are certainties. And I think this it how it should be for a database server ;-) -- Alvaro Herrera () "No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra"(Malucha Pinto) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
I'm a great fan of Java. Still, I firmly believe that pgsql should be the language of choice as the one included by default. I think many users consider the ability to write functions and triggers using SQL intermixed with the DDL statements (create function etc.) as the only natural way of doing it. All other languages implies either mixing languages in the same file or splitting declaration and implementation into separate files. Java is excellent when you want to extend the language beyond what's possible using SQL. Examples might include soundex or image recognition algorithms. XML is another area where server side Java might play a significant role. But that's all beyond core server functionality. If anything, I'd rather see the JDBC and ODBC drivers reinstated in the release. More than 56% of the PostgreSQL users (according to the poll) uses JDBC today. ODBC is merely 18% but that might change significantly when the native Win32 port is released. I might have missed something altogether here, my apologies if that's the case, but looking at the coming 7.5 release, 75% of the users will be forced to download stuff from more than one location just to get their basic stuff running. Kind regards, Thomas Hallgren ""scott.marlowe"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Tue, 27 Apr 2004, Jochem van Dieten wrote: > > > [EMAIL PROTECTED] wrote: > > > (5) Programming languages. We need to make a programming language standard > > > in PostgreSQL. plpgsql is good, but isn't someone working on a Java > > > language. That would be pretty slick. > > > > IMHO SQL/PSM would be the obvious choice for the standard > > procedural language. Not only because it is part of the SQL > > standard (ISO/IEC 9075-4:2003), but also because it is reasonably > > intuitive and it is the same as what is implemented in DB2 (and > > in MySQL 5 due sometime not so soon). > > The only problem is that there is no PostgreSQL implementation. > > While I can see heading in that direction, the lack of an implementation > makes this suggestion impractical. It will take time not just to > implement it, but to test it and debug it, and for it to reach "maturity." > > PL/pgsql is mature and tested, has a great deal of code already written > for it, and has reached maturity. I'd say including it by default > represents little or no security risk, and increases the value, out of the > box, of postgresql for most folks while costing very little in terms of > wasted disk space etc... > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-advocacy] What can we learn from MySQL?
On Mon, Apr 26, 2004 at 21:31:33 -0400, Andrew Payne <[EMAIL PROTECTED]> wrote: > > At some point (probably there now), I think the lack of a "Postgres, Inc." > is going to hinder adoption. Companies want to 'buy' from vendors that look > like real, viable companies, and provide them products with support, > training, features, and direction. With MySQL, you get one stop shopping. > With Postgres, you've got to find and assemble the parts yourself. Most > CIOs stop there, and start waiting for MySQL to get better before switching > from Oracle. I would expect that technical people (which would be DBAs and application developers) should be doing this research and reporting the results to the CIO. > The other issue is marketing: in mature software markets, the best > marketing (not the best technology) often wins. Without a sizeable > marketing budget earmarked for Postgres, MySQL could be 60% as good and > still win, unfortunately. It is not clear that Postgres needs to "win". It needs to have enough people interested in it in order to continue to significant development. It doesn't need to have a majority of the market share in order to do this. I suspect that get a larger market share amoungst some categories of users will hurt development by requiring more support than they contribute back to the project. > For those that look to Apache: Apache never had a well-established > incumbent (Oracle), an a well-funded upstart competitor (MySQL). Rob > McCool's NCSA httpd (and later, Apache) were good enough and developed > rapidly enough that they prevented any other HTTP server projects from > getting critical mass. Perhaps for a while. There are open source web servers now. A derivative of AOLserver is used by openACS. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
scott.marlowe wrote: I think the answer to all of this would require a lot of code being touched to either make it case fold, costing performance, or the replacement of the default lower cased catalog with upper cased catalog. I'm not the one to decide, but it seems to me that this is not a good time to enter such a major change, and may never be. How terrible will it be if the system is per server (i.e. - one given to initdb?). A more comprehensive solution, one which allowed switching from upper folding to lower folding to no folding, to case insensitive, or some subset of those possibilities results in a: slower backend performance, due to folding case for system catalogs b: touching a helluva lot of backend code to make it possible to fold up or down. Well, if it's only the shared catalogs that are affected, this may not be such a big problem (very hopeful tone?) I'm not 100% sure on this all, but that seems to be the point Tom and I came to in our discussion, and neither of the two solutions seemed very good at the time. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(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] PITR Phase 1 - Code Overview (1)
Peter Eisentraut wrote: > Simon Riggs wrote: > > New utility aimed at being located in src/bin/pg_arch > > Why isn't the archiver process integrated into the server? I think it is because the archiver process has to be started/stopped independently of the server. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
On Tue, 27 Apr 2004, Jochem van Dieten wrote: > [EMAIL PROTECTED] wrote: > > (5) Programming languages. We need to make a programming language standard > > in PostgreSQL. plpgsql is good, but isn't someone working on a Java > > language. That would be pretty slick. > > IMHO SQL/PSM would be the obvious choice for the standard > procedural language. Not only because it is part of the SQL > standard (ISO/IEC 9075-4:2003), but also because it is reasonably > intuitive and it is the same as what is implemented in DB2 (and > in MySQL 5 due sometime not so soon). > The only problem is that there is no PostgreSQL implementation. While I can see heading in that direction, the lack of an implementation makes this suggestion impractical. It will take time not just to implement it, but to test it and debug it, and for it to reach "maturity." PL/pgsql is mature and tested, has a great deal of code already written for it, and has reached maturity. I'd say including it by default represents little or no security risk, and increases the value, out of the box, of postgresql for most folks while costing very little in terms of wasted disk space etc... ---(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] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
On Mon, 26 Apr 2004, Josh Berkus wrote: > > I think that a talented manager could make the case for certain features. > > So? So could any community member with a good grasp of database engineering > and an ability to write persuasive e-mails. I'd like to inject here that I was the one who started the whole argument over date style enforcement about a year ago. It was a bit of a slog at first, but I felt my input was good, and the cause was just :-). Because of that discussion, PostgreSQL accpeting dates like 2004-31-03 and turning them into 2004-03-31 on the fly stopped. as did postgresql accepting a date like 03/31/2004 and turning it into 31/03/2004 when it was set to accept Euro style dates. I am not a PostgreSQL hacker, just a user. My input made the difference. No title needed. What PostgreSQL needs more of is caring users who can spot bugs / misbehaviours, make a decent argument about it, and maybe even a patch or two. We're way more short on indians than chiefs in my opinion. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bitwise and/or aggregate functions?
Fabien COELHO wrote: > > Dear hackers, > > still in the spirit of "it may be useful to others, as it was to me, and > it does cost very little", and before submitting a small patch and being > exploded because it is obviously very stupid: > > Would it be appropriate to contribute BIT_AND and BIT_OR aggregates > for integer types, with some documentation and minimal validation? > There has been a discussion recently on pgsql-general about that. > > 1) mysql has them... it seems to be an argument here around sometimes;-) >it is in their proud list-of-features that it has and that postgresql >does not have. > > 2) each declaration is a 4-line "CREATE AGGREGATE", the underlying >functions being already available for & and | operators. > > 3) I know that one can add them if they are needed, but what >would be the point of NOT providing such simple features, and >having the basic user to have to learn about creating aggregate >functions and browse a long time in the documentation for that? > > I needed them for some application: I'm happy I know how to add them now, > but I would have been even happier if I had found them just available > without having to learn about these intesting details about postgresql > extensions. I am confused why you would use bit on integers when there is a bit type with an AND operator: pg_catalog | &| bit| bit| bit | bitwise and -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
On Mon, 26 Apr 2004, Josh Berkus wrote: > Shachar, > > > I think the concensus was that the runtime part was aprox. four lines > > where the case folding currently takes place. Obviously, you would have > > to get a var, and propogate that var to that place, but not actually > > change program flow. > > That's only if you ignore the system catalogs entirely, which maybe you're > prepared to do. If you want to change case folding for the system catalogs, > though, you'll need to update code in thousands of places, becuase the > back-end code is expecting lower-case identifiers As someone who has discussed this with Tom in the past, I seem to remember that there were major issues with handling the system catalogs, because internally, the backends treat the identifiers as if they have already been quoted. I think the answer to all of this would require a lot of code being touched to either make it case fold, costing performance, or the replacement of the default lower cased catalog with upper cased catalog. i.e. no simple switch setting, but an initdb option that would be set like locale currently is, for the life of the cluster. A more comprehensive solution, one which allowed switching from upper folding to lower folding to no folding, to case insensitive, or some subset of those possibilities results in a: slower backend performance, due to folding case for system catalogs b: touching a helluva lot of backend code to make it possible to fold up or down. I'm not 100% sure on this all, but that seems to be the point Tom and I came to in our discussion, and neither of the two solutions seemed very good at the time. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR Phase 2 - Design Planning
On Tue, Apr 27, 2004 at 10:38:45 +0100, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Speaking as a DBA, what I usually want to do is restore to "immediately before > I started the payroll calculation". An actual wall-clock time is mostly > irrelevant to me. For long running transactions where you want to recover as much as possible, one might also want to recover up until just before a specific transaction committed (as opposed to started). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 1 - Code Overview (1)
Simon Riggs wrote: > New utility aimed at being located in src/bin/pg_arch Why isn't the archiver process integrated into the server? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
> > Not entirely true. I've read enough on the lists to see Bruce or > > others saying 'x feature isn't expected until version y.z'. Heck, to > > me, something that says 'we're hoping for feature x in version y.z', > > but it's not an exact science. See the MySQL releases as an example > > :) > > Ah, then in that case, look at the TODO list, pull out all items that have > a name beside them, and for those, they aren't expected until the next > version .. :) But the list is lnng...and my brain is weeekkk. :) Seriously, though. I was looking through the list yesterday trying to figure out something, and it was kind of hard to do.But, more to my point, this stuff is in the MySQL manual, making it easy to find. (Yes. I know what MySQL includes kind of blows, but, it's better than nothing) Tim ---(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, 27 Apr 2004, Tim Conrad wrote: > On Tue, Apr 27, 2004 at 12:58:59PM -0300, Marc G. Fournier wrote: > > On Tue, 27 Apr 2004, Tim Conrad wrote: > > > > > 2) There doesn't seem to be a clear roadmap on Postgres features. > > >When certian things are expected. There's the TODO list that > > >Bruce maintains, but it only outlines 'near' fixes. MySQL has a > > >nice listing of what to expect in certian future versions. > > > > Not possible for us, since we have no "upper management" that dictates > > what features get added, for when ... > > Not entirely true. I've read enough on the lists to see Bruce or > others saying 'x feature isn't expected until version y.z'. Heck, to > me, something that says 'we're hoping for feature x in version y.z', > but it's not an exact science. See the MySQL releases as an example > :) Ah, then in that case, look at the TODO list, pull out all items that have a name beside them, and for those, they aren't expected until the next version .. :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] bitwise and/or aggregate functions?
Dear hackers, still in the spirit of "it may be useful to others, as it was to me, and it does cost very little", and before submitting a small patch and being exploded because it is obviously very stupid: Would it be appropriate to contribute BIT_AND and BIT_OR aggregates for integer types, with some documentation and minimal validation? There has been a discussion recently on pgsql-general about that. 1) mysql has them... it seems to be an argument here around sometimes;-) it is in their proud list-of-features that it has and that postgresql does not have. 2) each declaration is a 4-line "CREATE AGGREGATE", the underlying functions being already available for & and | operators. 3) I know that one can add them if they are needed, but what would be the point of NOT providing such simple features, and having the basic user to have to learn about creating aggregate functions and browse a long time in the documentation for that? I needed them for some application: I'm happy I know how to add them now, but I would have been even happier if I had found them just available without having to learn about these intesting details about postgresql extensions. Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Tim Conrad wrote: > > Of course, some gullible people actually believe this and compare [2] > > the existing and working implementations with vaporware (MySQL 5.1, > > anyone?). > > > > > On those same lines, there doesn't seem to be anything about the > > > improvements in the minor versions. It seems that in every > > > release (i.e. 7.2,7.3,7.4) there are pretty significant changes, > > > but finding a place that outlines these changes is somewhat > > > difficult. > > > > Have you tried looking in the release notes [3]? > > > > > > [1] http://www.geocrawler.com/archives/3/194/1998/8/0/1061364/ > > [2] http://www.devx.com/dbzone/Article/20743/1763?supportItem=1 > > [3] http://www.postgresql.org/docs/7.4/interactive/release.html > > I guess I'm an ignorant fool and I don't comprehend many of the > items under the release note. I'm also looking for something I can > hand my boss and say ' this is why we should use postgres instead of > oracle'. I think the summary of each release at the top would be OK for that. Actually, your biggest problem is that we don't have a big motivation to _sell_ PostgreSQL to anyone. We are more driven toward solving problems and designing superior software. If it looks like we don't have a polished sales image, that's because we don't stive for that. However, we have had a large number of volunteers over the past few months focus in this area and I hope there will be visible results shortly. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Tue, Apr 27, 2004 at 12:58:59PM -0300, Marc G. Fournier wrote: > On Tue, 27 Apr 2004, Tim Conrad wrote: > > > 2) There doesn't seem to be a clear roadmap on Postgres features. > >When certian things are expected. There's the TODO list that > >Bruce maintains, but it only outlines 'near' fixes. MySQL has a > >nice listing of what to expect in certian future versions. > > Not possible for us, since we have no "upper management" that dictates > what features get added, for when ... Not entirely true. I've read enough on the lists to see Bruce or others saying 'x feature isn't expected until version y.z'. Heck, to me, something that says 'we're hoping for feature x in version y.z', but it's not an exact science. See the MySQL releases as an example :) > > >I know > >it's not a perfect list, but it'd be nice to know when full blown > >replication will be included in PostgreSQL as an example. > > Never, since there is no such thing as a 'full blown replication', since > there is no *one* way to do replication ... It was puretly there for example purposes... > > > 3) There's the issues of 'advanced database features' in general. > > Many MySQL applications perform much of their logic in the > > application level, instead of the database level. They do this > > because there aren't things like triggers or stored procedures > > in MySQL. As the saying goes, 'if mohammad won't go to the > > mountain, bring the mountian to mohammad'. Why not do some > > simple explainations as to why these things are good, and what > > they do, and how to use them in real context? > > Just a matter of someone writing and submitting it ... how are your > writing skills? :) > > > 4) As other peole have noted, there's no windows build readily > > available for Postgres. There may be, but it's difficult to > > find. If someone's used to running, say, Oracle, and all they > > have is a windows machine to test something out on, MySQL has > > compiled binaries ready to go. > > there is no native windows currently available, but its being worked on > for 7.5 ... after which, a pre-compiled binary becomes automatic ... > > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Tue, Apr 27, 2004 at 07:55:08PM +0400, Alexey Borzov wrote: > Hi! > > Tim Conrad wrote: > >I was researching an article I wrote about a comparison between > >Postgres and MySQL recently (If you want, you can read the article > >at http://www.devx.com/dbzone/Article/20743/). I noticed some clear > >differences between the mysql.com website and the Postgres website. > > Sorry, couldn't resist: may I suggest doing the research *before* > writing an article, not *after*? > > My favourite part of it is: > > MySQL uses traditional row-level locking. PostgreSQL uses something > called Multi Version Concurrency Control (MVCC) by default. MVCC is a > little different from row-level locking in that transactions on the > database are performed on a snapshot of the data and then serialized. > New versions of PostgreSQL support standard row-level locking as an > option, but MVCC is the preferred method. > Nice that you point out that incorrectly stated something. Even nicer that you don't tell me what the correct answer would be. Unfortunanatly, that's the best I could come up with with doing research with the documentation I could find on the subject. MVCC does a lot more than can be easily contained in a sentance. > > >2) There doesn't seem to be a clear roadmap on Postgres features. > > When certian things are expected. There's the TODO list that > > Bruce maintains, but it only outlines 'near' fixes. MySQL has a > > nice listing of what to expect in certian future versions. I know > > it's not a perfect list, but it'd be nice to know when full blown > > replication will be included in PostgreSQL as an example. > > MySQL's roadmap is complete bullshit. Subselects were first promised in > 4.0, which was "not that far away" [1] back in 1998! Well, they are in > 4.1, which is still alpha in 2004. I realize this. I also realize that having a nicely defined roadmap would give Postgres a hands up in this category. > > Of course, some gullible people actually believe this and compare [2] > the existing and working implementations with vaporware (MySQL 5.1, > anyone?). > > > On those same lines, there doesn't seem to be anything about the > > improvements in the minor versions. It seems that in every > > release (i.e. 7.2,7.3,7.4) there are pretty significant changes, > > but finding a place that outlines these changes is somewhat > > difficult. > > Have you tried looking in the release notes [3]? > > > [1] http://www.geocrawler.com/archives/3/194/1998/8/0/1061364/ > [2] http://www.devx.com/dbzone/Article/20743/1763?supportItem=1 > [3] http://www.postgresql.org/docs/7.4/interactive/release.html I guess I'm an ignorant fool and I don't comprehend many of the items under the release note. I'm also looking for something I can hand my boss and say ' this is why we should use postgres instead of oracle'. Tim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
On Tue, 27 Apr 2004, Tim Conrad wrote: > 2) There doesn't seem to be a clear roadmap on Postgres features. >When certian things are expected. There's the TODO list that >Bruce maintains, but it only outlines 'near' fixes. MySQL has a >nice listing of what to expect in certian future versions. Not possible for us, since we have no "upper management" that dictates what features get added, for when ... >I know >it's not a perfect list, but it'd be nice to know when full blown >replication will be included in PostgreSQL as an example. Never, since there is no such thing as a 'full blown replication', since there is no *one* way to do replication ... > 3) There's the issues of 'advanced database features' in general. > Many MySQL applications perform much of their logic in the > application level, instead of the database level. They do this > because there aren't things like triggers or stored procedures > in MySQL. As the saying goes, 'if mohammad won't go to the > mountain, bring the mountian to mohammad'. Why not do some > simple explainations as to why these things are good, and what > they do, and how to use them in real context? Just a matter of someone writing and submitting it ... how are your writing skills? :) > 4) As other peole have noted, there's no windows build readily > available for Postgres. There may be, but it's difficult to > find. If someone's used to running, say, Oracle, and all they > have is a windows machine to test something out on, MySQL has > compiled binaries ready to go. there is no native windows currently available, but its being worked on for 7.5 ... after which, a pre-compiled binary becomes automatic ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] 7.5 features
On Tue, 2004-04-27 at 09:27, Bruce Momjian wrote: > Here are features that are being worked on, hopefully for 7.5: > > o tablespaces (Gavin) > o nested transactions (Alvaro) > o two-phase commit (Heikki Linnakangas) > o integrated pg_autovacuum (O'Connor) > o PITR (Riggs) > o Win32 (Claudio, Magnus) > > If we get the majority of them, and I think we will, this will be a > great release. Dare I even mention calling it 8.0? (ducks and runs for cover...) ---(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] 7.5 features
Martin Marques wrote: > El Tuesday 27 April 2004 10:27, Bruce Momjian escribi?: > > Here are features that are being worked on, hopefully for 7.5: > > > > o tablespaces (Gavin) > > o nested transactions (Alvaro) > > o two-phase commit (Heikki Linnakangas) > > o integrated pg_autovacuum (O'Connor) > > o PITR (Riggs) > > o Win32 (Claudio, Magnus) > > > > If we get the majority of them, and I think we will, this will be a > > great release. > > How's Jans' Slowny-I doing? Any chance of getting it at least in the contribs > (depending on how stable it gets)? Yep, I forgot that, though it is independent of the 7.5 release. I think Jan will make a final release in a few months. I also should mention server-side java, again also an independent project. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.5 features
> > How's Jans' Slowny-I doing? Any chance of getting it at least in the contribs > > (depending on how stable it gets)? > > Zero chance ... Slony-I is *a* replication solution, not *the* > replication solution ... unless someone ever comes up with an 'end all and Not to mention Jan doesn't want it to be in contrib as that would imply it was version specific. One of the more popular uses of Slony will be version upgrades with minimal downtime. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
I've been sort-of reading this thread off and on, so this may contain duplicate suggestions. I was researching an article I wrote about a comparison between Postgres and MySQL recently (If you want, you can read the article at http://www.devx.com/dbzone/Article/20743/). I noticed some clear differences between the mysql.com website and the Postgres website. 1) Since MySQL AB supports and trains for MySQL, there's loads of training information available on their website. On the other hand, I had a hard time finding training information for Postgres in general. Same goes for support. It's easier to find, but it's still somewhat convoluted, IMO. 2) There doesn't seem to be a clear roadmap on Postgres features. When certian things are expected. There's the TODO list that Bruce maintains, but it only outlines 'near' fixes. MySQL has a nice listing of what to expect in certian future versions. I know it's not a perfect list, but it'd be nice to know when full blown replication will be included in PostgreSQL as an example. On those same lines, there doesn't seem to be anything about the improvements in the minor versions. It seems that in every release (i.e. 7.2,7.3,7.4) there are pretty significant changes, but finding a place that outlines these changes is somewhat difficult. While being somewhat nit-picky on this, it'd also be helpful if someone wasn't completely database literate could understand some of the changes. Who needs transactions, anyways? :) 3) There's the issues of 'advanced database features' in general. Many MySQL applications perform much of their logic in the application level, instead of the database level. They do this because there aren't things like triggers or stored procedures in MySQL. As the saying goes, 'if mohammad won't go to the mountain, bring the mountian to mohammad'. Why not do some simple explainations as to why these things are good, and what they do, and how to use them in real context? 4) As other peole have noted, there's no windows build readily available for Postgres. There may be, but it's difficult to find. If someone's used to running, say, Oracle, and all they have is a windows machine to test something out on, MySQL has compiled binaries ready to go. 5) I believe that this was noted as well somewhere along the line - the other tools, like pgadmin III aren't readily available either. They're excellent tools, and they should be quick to find on the postgres website. 6) Bug tracking. I haven't really looked into how MySQL handles this, but when learning about Postgres, I discovered that the whole development model seemed kind of 'closed', and people on the mailing lists would find bugs repeatedly. Something like Bugzilla would be very helpful in this respect. I've been kind of out of the loop for the past 6 months in this area, so it may have changed since then. 7) The two Postgres books are available online for anyone to read and download. They're there, but, to me, you have to notice them on the sidebar to go to them. They're extremely helpful, and they should be pointed out more. Most of these suggestions aren't really anything to do with the database itself. It's simply a re-organization of some of the information that's already available. As others have mentioned, 'it's about the PR'. Just my $.02 worth. Tim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.5 features
On Tuesday 27 April 2004 15:22, Martin Marques wrote: > > How's Jans' Slowny-I doing? Any chance of getting it at least in the > contribs (depending on how stable it gets)? There was a post from Jan the other day (on General iirc) - I think he's looking for testers at the moment before he goes to feature-freeze. -- Richard Huxton Archonet Ltd ---(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] 7.5 features
On Tue, 27 Apr 2004, Martin Marques wrote: > El Tuesday 27 April 2004 10:27, Bruce Momjian escribió: > > Here are features that are being worked on, hopefully for 7.5: > > > > o tablespaces (Gavin) > > o nested transactions (Alvaro) > > o two-phase commit (Heikki Linnakangas) > > o integrated pg_autovacuum (O'Connor) > > o PITR (Riggs) > > o Win32 (Claudio, Magnus) > > > > If we get the majority of them, and I think we will, this will be a > > great release. > > How's Jans' Slowny-I doing? Any chance of getting it at least in the contribs > (depending on how stable it gets)? Zero chance ... Slony-I is *a* replication solution, not *the* replication solution ... unless someone ever comes up with an 'end all and be all' replication solution (which, personally, I don't think will ever happen), we should not be officially endorsing any one replication solution ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: How to Welcome Windows Users (was Re: [HACKERS] 7.5 features)
On Tue, 27 Apr 2004, Richard Huxton wrote: > Dave Page has bravely stepped into the breach to maintain the ODBC > driver, but the niggles in it will generate a flood of support messages > as Windows users test it out. Basically, I'm asking what would need to > be done technically for the ODBC driver, and is there anything a > non-hacker can do to help? First question ... what niggles? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pg_clog question.
Hi All, I've been noticing that files in the pg_clog directory test to stay around forever, I know they are used to determine the state of transaction id's, but according to the docs: "(However, the urgency of this concern has decreased greatly with the adoption of a segmented storage method for pg_clog: it is no longer necessary to keep old pg_clog entries around forever.)" How does one determine which files are no longer neccessary? Is the system configured to clear out stale ones during startup/shutdown? -- Chris Bowlby <[EMAIL PROTECTED]> PostgreSQL Inc. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.5 features
El Tuesday 27 April 2004 10:27, Bruce Momjian escribió: > Here are features that are being worked on, hopefully for 7.5: > > o tablespaces (Gavin) > o nested transactions (Alvaro) > o two-phase commit (Heikki Linnakangas) > o integrated pg_autovacuum (O'Connor) > o PITR (Riggs) > o Win32 (Claudio, Magnus) > > If we get the majority of them, and I think we will, this will be a > great release. How's Jans' Slowny-I doing? Any chance of getting it at least in the contribs (depending on how stable it gets)? -- 11:21:02 up 49 days, 15:45, 4 users, load average: 0.53, 0.68, 0.71 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
How to Welcome Windows Users (was Re: [HACKERS] 7.5 features)
On Tuesday 27 April 2004 14:27, Bruce Momjian wrote: > Here are features that are being worked on, hopefully for 7.5: > > o tablespaces (Gavin) > o nested transactions (Alvaro) > o two-phase commit (Heikki Linnakangas) > o integrated pg_autovacuum (O'Connor) > o PITR (Riggs) > o Win32 (Claudio, Magnus) > > If we get the majority of them, and I think we will, this will be a > great release. Sounds like the biggest release since 7.0 to me, and all good stuff. I do have a nagging concern with the Windows support though. I'm guessing most people running Windows servers will either be running php on top, or have windows clients. AFAIK this means .NET or ODBC, and for older Access-based systems upgrading definitely ODBC. Dave Page has bravely stepped into the breach to maintain the ODBC driver, but the niggles in it will generate a flood of support messages as Windows users test it out. Basically, I'm asking what would need to be done technically for the ODBC driver, and is there anything a non-hacker can do to help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.5 features
I think great would be an understatement. Great work all! |-+--> | | Bruce Momjian | | | <[EMAIL PROTECTED]| | | > | | | Sent by: | | | [EMAIL PROTECTED]| | | tgresql.org| | | | | | | | | 04/27/2004 08:27 AM| | | | |-+--> >--| | | | To: PostgreSQL-development <[EMAIL PROTECTED]> | | cc: | | Subject: [HACKERS] 7.5 features | >--| Here are features that are being worked on, hopefully for 7.5: o tablespaces (Gavin) o nested transactions (Alvaro) o two-phase commit (Heikki Linnakangas) o integrated pg_autovacuum (O'Connor) o PITR (Riggs) o Win32 (Claudio, Magnus) If we get the majority of them, and I think we will, this will be a great release. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html * PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. * ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.5 features
Here are features that are being worked on, hopefully for 7.5: o tablespaces (Gavin) o nested transactions (Alvaro) o two-phase commit (Heikki Linnakangas) o integrated pg_autovacuum (O'Connor) o PITR (Riggs) o Win32 (Claudio, Magnus) If we get the majority of them, and I think we will, this will be a great release. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Broken Catalog? -- 7.4.2
On Mon, 2004-04-26 at 22:04, Alvaro Herrera wrote: > On Mon, Apr 26, 2004 at 09:36:26PM -0400, Rod Taylor wrote: > > The function format_type() fails only for interval when used on the > > interval type > > > > template1=# select format_type(oid, typlen) from pg_type; > > select format_type(oid, typtypmod) from pg_type; > -- works > > In fact, I believe this is the correct answer from timestamp types, for > example. Yes, your right. The only differences are with the timestamp types and interval. No matter -- I still have a bug in DBD::Pg ;) ---(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] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
[EMAIL PROTECTED] wrote: (5) Programming languages. We need to make a programming language standard in PostgreSQL. plpgsql is good, but isn't someone working on a Java language. That would be pretty slick. IMHO SQL/PSM would be the obvious choice for the standard procedural language. Not only because it is part of the SQL standard (ISO/IEC 9075-4:2003), but also because it is reasonably intuitive and it is the same as what is implemented in DB2 (and in MySQL 5 due sometime not so soon). The only problem is that there is no PostgreSQL implementation. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje ---(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] PITR Phase 2 - Design Planning
On Tuesday 27 April 2004 00:32, Bruce Momjian wrote: > Simon Riggs wrote: > > On Mon, 2004-04-26 at 23:01, Alvaro Herrera wrote: > > > On Mon, Apr 26, 2004 at 05:05:41PM -0400, Bruce Momjian wrote: > > > > I was thinking --- how would someone know the time to use for > > > > restore? > > > > > > I think there should be a way to get a TransactionId and restore up to > > > that point. It'd be cool, but not required, if the system showed what > > > valid TransactionIds there are, and roughly what they did (the xlog > > > code already has "describers" everywhere AFAICS). > > > > You're right, I think we should start by implementing the rollforward to > > a txnid before we consider the rollforward to a specified point-in-time. > > All the hooks for that are already there... > > Yep, sounds like a plan. Speaking as a DBA, what I usually want to do is restore to "immediately before I started the payroll calculation". An actual wall-clock time is mostly irrelevant to me. Suggestion: How about a pg_trans_note table (crap name, I know) that only permits inserts - records (backend-pid, timestamp, notes). My app inserts "starting payroll calc" and "ending payroll calc" entries because those are the points I might wish to sync to. If I want to sync for each individual transaction in my calculations, my app can do that too. >From a usability point of view you might want to automatically insert rows on client connection/table creation etc. You could also delete any rows more than a week old when archiving WAL files. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Pl/Java and GCJ
"Bruce Momjian" <[EMAIL PROTECTED]> wrote in message > It seems this would be handled just like we handle C functions today, > that is you create a shared object file, it sits in the file system, and > you LOAD the object into your backend, or you record it via CREATE > FUNCTION and specify the pathname. > I'm not sure that just handling it as C functions will get me what I want. Consider the following use-case: 1. A user starts a psql client to a server. The user has no access to the file system on the server. 2. Using the psql, a jar file is installed in some schema using the sqlj.install_jar function (provided by pl/java). The jar contains a deployment descriptor (i.e. SQL commands that executes the necessary 'CREATE FUNCTION', etc.). 3. The user uses some installed function. This works today using interpreted code and I don't want the user interaction changed when the code is compiled. I.e. I'd like for my gcj version of Pl/Java to automatically compile the .jar file into a shared object (on demand in step 3), store it so that it doesn't conflict with other shared objects, but still be able to handle permissions (GRANT/REVOKE) etc. correctly on the corresponding jar entries in the table. In essence, the server will perform the compilation "on the fly" and needs a place in the filesystem where it can store the result. Perhaps a directory in the current database root would be sufficient and then name the objects using the OID of the entry in the jar table? Associated triggers would remove the file when the corresponding row is deleted. I want Pl/Java to be housebroken, so if there's any conventions that could be applied here, please let me know. - thomas ---(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] What can we learn from MySQL?
On Mon, Apr 26, 2004 at 04:41:35PM -0400, Bruce Momjian wrote: > Jean-Michel POURE wrote: > [ PGP not available, raw data follows ] > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > > My question is, "What can we learn from MySQL?" I don't know there is > > > anything, but I think it makes sense to ask the question. > > > > Dear Bruce, > > > > Taking the example of pgAdmin III, which reached nearly one million hits in > > December (http://www.pgadmin.org/stats/webalizer), nothing seems impossible > > for PostgreSQL. > > > > Why not create an all-in-one bundle offering PostgreSQL, Apache, Php and > > PhpPgAdmin for Win32 and ... mass-release it. > > > > There is no need to create a complete installer. There could be a single > > installer executing other installers (like it is sometimes the case in the > > Win32 world). So that installers remain different. > > > > A single web page like "http://win.postgresql.org"; in 40 languages is enough > > to mass-release PostgreSQL. > > > > With an installer and a single web page, PostgreSQL Win32 could quickly reach > > one million downloads every month. > > > > There is no need to look for complicated strategies. Every month, there can be > > 10% more downloads. In the end, people will even forget the name of MySQL. > > That seems like a good idea. Agree. The page should be describe basic PostgreSQL features and step-by-step introduction from download to a first user's "SELECT ... FROM". Do you expect translate PostgreSQL-win installer to foreign languages? Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib, etc.
Just a reflection from someone who has not been following PostgreSQL that long... I think you provide excellent leadership and keep a firm grip on the core PostgreSQL server. Moving stuff out to Gborg and the discussion regarding contrib tells me that you want to keep it that way. As I'm a firm beliver in separation of concern and modularization, I really like that move. The server group must concentrate on the server. PosgreSQL is much more than just a server though. Something can be done that would bring order to the current chaos and make the fragementation a positive thing. What I think is needed is an architecture that goes beyond the server. An architecture endorsed by the PostgreSQL. It would of course include the semantics needed to create and link a plugin but it wouldn't stop there. A very important feature would be packaging and deployment (installers etc.). Another would be administration. The architecture I have in mind *must* be endorsed and controlled by PostgreSQL. It cannot be delivered by a freestanding group. Still, it's not core server stuff. This is the means by which PostgreSQL would (re)gain leadership of the whole thing. You don't need managers, you need a broader component architecure. My perception is that you need to somehow fork your efforts and look beyond the core server horizon. A don't think it's too late. I'm convinced that everyone that creates products that integrates with PostgreSQL would be more than happy to comply (and make additions to) a common architecture if it would help PostgreSQL to get a wider acceptance. In short, You have a great leverage. It's just a matter of using it the right way. Kind regards, Thomas Hallgren "Peter Eisentraut" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > [EMAIL PROTECTED] wrote: > > If we want to make PostgreSQL a wildly popular product, there will be > > some pain. There should be a "Product Management" group. The > > leader(s) of this group should be chosen carefully, as he (they) must > > be free to define what PostgreSQL is. They must have a good feel for > > product development and understanding of the underlying technology, > > but not be so techie that we don't address the issues intended. They > > must be able to rally the troops and direct development efforts. > > Lastly, he (they) must have the confidence of the core hackers, as it > > is likely that there will be disagreements with the direction of > > PostgreSQL, and it wouldn't work if "Product Management" couldn't > > actually manage what the product was because nobody listened. > > I agree with this, more or less. The lack of leadership that > coordinates all activities actively is really missing. Unfortunately, > I believe we are already in a state of fragmentation where setting up > something like this is no longer possible. What the end user sees as a > PostgreSQL system is brought to them by nearly a dozen different groups > nowadays. And the server group can no longer count on having a > stronger position to pull them all together. The only option to > achieve what you want soon is to market your own product. > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Phase 2 - Design Planning
Simon Riggs wrote: Since Phase1 is functioning and should hopefully soon complete, we can now start thinking about Phase 2: full recovery to a point-in-time. Previous thinking was that a command line switch would be used to specify recover to a given point in time, rather than the default, which will be recover all the way to end of (available) xlogs. Recovering to a specific point in time forces us to consider what the granularity is of time. We could recover: 1.to end of a full transaction log file 2.to end of a full transaction Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time > desired point in time. To make (2) work we would have to have a timestamp associated with each transaction. This could be in one of two places: 1. the transaction record in the clog 2. the log record in the xlog We would then recover the xlog record by record, until we found a record that had a timestamp > desired point-in-time. Currently, neither of these places have a timestamp. H. We can't use pg_control because we are assuming that it needs recovery... I can't see any general way of adding a timestamp in any less than 2 bytes. We don't need a timezone. The timestamp could refer to a number of seconds since last checkpoint; since this is limited already by a GUC to force checkpoints every so often. Although code avoids a checkpoint if no updates have taken place, we wouldn't be too remiss to use a forced checkpoint every 32,000 seconds (9 hours). Assuming that accuracy of the point-in-time was of the order of seconds?? If we went to 0.1 second accuracy, we could checkpoint (force) every 40 minutes or so. All of that seems too restrictive. If we went to milliseconds, then we could use a 4 byte value and use a checkpoint (force) every 284 hours or 1.5 weeks. Thoughts? Clog uses 2 bits per transaction, so even 2 bytes extra per transaction will make the clog 9 times larger than originally intended. This could well cause it to segment quicker, but I'm sure no one would be happy with that. So, lets not add anything to the clog. The alternative is to make the last part of the XlogHeader record a timestamp value, increasing each xlog write. It might be possible to make this part of the header optional depending upon whether or not PITR was required, but then my preference is against such dynamic coding. So, I propose: - appending 8 byte date/time data into xlog file header record - appending 4 bytes of time offset onto each xlog record - altering the recovery logic to compare the calculated time of each xlog record (file header + offset) against the desired point-in-time, delivered to it by GUC. Input is sought from anybody with detailed NTP knowledge, since the working of NTP drift correction may have some subtle interplay with this proposal. Also, while that code is being altered, some additional log records need to be added when recovery of each new xlog starts, with timing, to allow DBAs watching a recovery to calculate expected completion times for the recovery, which is essential for long recovery situations. I am also considering any changes that may be required to prepare the way for a future implementation of parallel redo recovery. Best regards, Simon Riggs, 2ndQuadrant http://www.2ndquadrant.com ---(end of broadcast)--- TIP 8: explain analyze is your friend Simon, I have one question which might be important: If we use timestamps inside the WAL system to find out where to stop. What happens if somebody changes the time of the system? (e.g. correcting the system clock by calling ntpdate). Wouldn't it confuse the PITR system? How do you plan to handle that? Unfortunately time is nothing which can be used as a key (at least not from my point of view). Just some lousy ideas early in the morning ... Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
