[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
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
Re: [HACKERS] bitwise and/or aggregate functions?
Would it be appropriate to contribute BIT_AND and BIT_OR aggregates I am confused why you would use bit on integers Well, (I think) I need them to manipulate pg_catalog's aclitem bitfields. I plea not guilty for the design of pg_catalog;-) Moreover, I added aclitem accessors which return INT4 in a recent patch that you kindly applied. when there is a bit type with an AND operator: pg_catalog | | bit| bit| bit | bitwise and Sure. is also available for all integer types. BTW, I'm arguing about AGGREGATE functions, and there is no aggregate functions at the time, neither for int* nor for bit. SELECT BIT_OR(aclitem_privs(...)) AS effective_privs FROM ... WHERE aclitem_grantee(...)=... AND ... ; Or maybe I cannot understand why you're confused? -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] bitwise and/or aggregate functions?
SELECT BIT_OR(aclitem_privs(...)) AS effective_privs FROM ... WHERE aclitem_grantee(...)=... AND ... ; Is there anything in SQL2003 about such operators? If there is, we should make sure we use the correct aggregate names. Chris ---(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
[HACKERS] Weirdness with OIDs and JOIN ON?
Why doesn't this work: test=# select oid, relname, indisclustered from pg_index join pg_class on indexrelid=oid where indexrelid 17205; ERROR: column oid does not exist I'm _joining_ on the oid column. If I qualify it, it works: test=# select pg_class.oid, relname, indisclustered from pg_index join pg_class on indexrelid=oid where indexrelid 17205; oid | relname| indisclustered ---+--+ 17214 | child_b_key | t 17210 | parent_a_key | t (2 rows) I can't see that I've made an error in the first example - is it a Postgres bug? 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] 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: I'm about to submit my SET WITHOUT CLUSTER patch, so I'll fix this bug in that. Chris ---(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] bitwise and/or aggregate functions?
SELECT BIT_OR(aclitem_privs(...)) AS effective_privs FROM ... WHERE aclitem_grantee(...)=... AND ... ; Is there anything in SQL2003 about such operators? If there is, we should make sure we use the correct aggregate names. That's a point! I thought of BIT_* because it is short and also used by mysql. Ingres has BIT_AND and BIT_OR functions, but they are not aggregates. I don't know where these standards are available online... It seems they are not available:-( -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-advocacy] [HACKERS] What can we learn from MySQL?
Dear Tim, These are execellent proposals. My only remark would be to build a step-by-step approach. In a first stage, we could set-up a minimal web page for the Win32 port: - PostgreSQL Win32 installer (possibly translated), - translation of the web page in 40 languages, - step-by-step installation under Win32 (screenshots), - links (NLS project, documentation), ... advertise (example: http://www.pgadmin.org/pgadmin3/advocacy.php) and start monitoring downloads. With PostgreSQL Win32 version and looking at pgAdmin III statistics, reaching one million downloads every month seems a reasonable target. PostgreSQL is such a wonderful community project that there is no need to build complex marketing strategies to reach impressive goals. In a second stage, we can start building a rich web site (as you proposed) and make it live on the long run. Best regards, Jean-Michel 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PITR Phase 1 Code Review
Please could the committers review my code with a view to committing it onto the main dev branch? There are additional cosmetic changes likely to be required, such as removing some dead comments and streamlining log output. Let me know whether these should occur before or after first commit. I'll be around daily to answer queries and fix reported bugs. Thanks, Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] bitwise and/or aggregate functions?
Is there anything in SQL2003 about such operators? If there is, we should make sure we use the correct aggregate names. That's a point! I thought of BIT_* because it is short and also used by mysql. Ingres has BIT_AND and BIT_OR functions, but they are not aggregates. I don't know where these standards are available online... It seems they are not available:-( Neil - can you check your SQL2003 copy to see if it mentions standard aggregates on bit types? Thanks, Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Bringing PostgreSQL torwards the standard regarding
scott.marlowe kirjutas T, 27.04.2004 kell 20:43: 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. why not leave it at that and mandate their *external* doublequoted use ? not only for system catalogs but also for system columns. I already have had negative experience with a proprietary tool not being able to use postgres (over ODBC) because it has a system column called tmin (or was it tid). If things were case-folded to upper automatically, there would not have been such problem. -- Hannu ---(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
Bruno Wolff III wrote: 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. Ah! followed by Eek! Now I see the light. It's very bright and painful. What I can see is that expressing this accurately and unambiguously is going to be _difficult_. How do you know accurately the point just before a transaction was completed. There must be a good subset of candidates that can be labelled. Is there anyway to label/name a transaction that can be kept somewhere ? Like begin transaction 'bigtrasacation26'; - is there any allowance in the SQL standards for naming trasactions ? PS I have fixed my system clock - apologies to my earlier reply being a month ahead. rgds, -- Peter ---(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 Wed, 28 Apr 2004 00:08:48 -0400, Tom Lane [EMAIL PROTECTED] wrote: Is there a special reason for scanning the leaf pages in *logical* order, i.e. by following the opaque-btpo_next links? Yes. [..] interlocking between indexscans and deletions. Thanks for refreshing my memory. This has been discussed two years ago, and I even participated in that discussion :-( Servus Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] bitwise and/or aggregate functions?
I thought of BIT_* because it is short and also used by mysql. Ingres has BIT_AND and BIT_OR functions, but they are not aggregates. I don't know where these standards are available online... It seems they are not available:-( Neil - can you check your SQL2003 copy to see if it mentions standard aggregates on bit types? I haven't seen such a function in the draft standard I have found. It seems it is not yet available from INCITS. There are also EVERY (= aggregate AND) and SOME/ANY (= aggregate OR) for booleans, that are interesting. I'll add them with the patch. However I think that I may need to add two small functions for that. -- Fabien Coelho - [EMAIL PROTECTED] ---(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
[HACKERS] tcl error
dear all i tried using tcl with ./configure --with-tcl. errors appear when make: make[4]: Entering directory `/home/sm/pgsql7.4/src/pl/plpgsql/src' bison -y -d gram.y NONE:0: /usr/bin/m4: ERROR: EOF in string sed -e 's/yy/plpgsql_yy/g' -e 's/YY/PLPGSQL_YY/g' y.tab.c ./pl_gram.c /bin/sh: line 1: y.tab.c: No such file or directory make[4]: *** [pl.tab.h] Error 1 make[4]: Leaving directory `/home/sm/pgsql7.4/src/pl/plpgsql/src' make[3]: *** [all] Error 2 make[3]: Leaving directory `/home/sm/pgsql7.4/src/pl/plpgsql' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/sm/pgsql7.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/sm/pgsql7.4/src' make: *** [all] Error 2 what's wrong with it? [EMAIL PROTECTED] 2004-04-28 ---(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] Weirdness with OIDs and JOIN ON?
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Why doesn't this work: test=# select oid, relname, indisclustered from pg_index join pg_class on indexrelid=oid where indexrelid 17205; ERROR: column oid does not exist The JOIN is a bit like a view --- it doesn't have any system columns. For example consider select alias.* from (a join b on ...) as alias; This should certainly not show any system columns --- both because SQL92 says so, and because there'd be no way to choose which input table's system columns to use. In scenarios where only one input table has an OID column, we could maybe kluge things to allow it to be accessed without qualification, but I don't really see the value of adding a wart for that ... 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] tcl error
=?gb2312?B?y6fDzQ==?= [EMAIL PROTECTED] writes: make[4]: Entering directory `/home/sm/pgsql7.4/src/pl/plpgsql/src' bison -y -d gram.y NONE:0: /usr/bin/m4: ERROR: EOF in string I'd say you have a broken bison installation; or possibly bison is okay but m4 isn't (recent bisons depend on m4, and I think require it to be reasonably late-model gnu m4). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
On Wed, 28 Apr 2004, Christopher Kings-Lynne wrote: 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. This wouldn't be a problem, though, would it? The dumps don't run inside a transaction so after the failure to create PL/PgSQL because it already exists, the import would continue as before. Jon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nasty security bug with clustering
Christopher Kings-Lynne [EMAIL PROTECTED] writes: No check is performed for being a superuser, the table owner or that it is a system table when marking an index for clustering: I'm about to submit my SET WITHOUT CLUSTER patch, so I'll fix this bug in that. I'm in the middle of reviewing (read whacking around) Rod Taylor's patch for multiple operations in ALTER TABLE, so I'm afraid that no patch in the same area is likely to apply cleanly after the dust settles :-( I had noted the lack of permissions checks in CLUSTER ON (it's fairly glaring in the reorganized code) and planned to fix it along with what I was doing. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg ANY/SOME ambiguity wrt sql standard?
I'm looking into adding sql standard aggregates EVERY/ANY/SOME. It seems to me that there is a syntax ambiguity with ANY and SOME: CREATE TABLE bla(b BOOL); SELECT TRUE = ANY(b) FROM bla; Is parsed as an array-operator and there is a semantical error because no array is provided. Now ANY could be an aggregate function call, and it should be fine. However I really cannot see (my usual lack of imagination) how to handle this from the parser. Thus I'm afraid that I'll have to rewrite the A_Expr structure into a FuncCall to 'any' or 'some' somewhere. Comments? Any better idea? -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OLAP versus Materialized Views?
On Wed, Apr 28, 2004 at 12:14:06AM +0100, Simon Riggs wrote: Yes. There is Relational OLAP (ROLAP) and Multidimensional OLAP (MOLAP). PostgreSQL can be used as a ROLAP server. For multidimentional on PostgreSQL there is Mondrian: http://sourceforge.net/projects/mondrian/ --Tim Larson ---(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] pg ANY/SOME ambiguity wrt sql standard?
It seems to me that there is a syntax ambiguity with ANY and SOME: CREATE TABLE bla(b BOOL); SELECT TRUE = ANY(b) FROM bla; Is parsed as an array-operator and there is a semantical error because no array is provided. Now ANY could be an aggregate function call, and it should be fine. Well, it is not that fine, because allowing ANY and SOME as function name generates a lot of conflicts, obviously. The reverse (let us recognize an array expression in an function call wouldn't work either, as the parser need the special handling of ANY/SOME in order to look for subselects. Thus I'm afraid that I'll have to rewrite the A_Expr structure into a FuncCall to 'any' or 'some' somewhere. Comments? Any better idea? So my question is Any idea? instead of Any better idea? :-( -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain wrote: 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 There's your problem. Creating database connections is an expensive operation. They are not intended to be opened and closed often or quickly. Open your database connection at the beginning of your program, and close it at the end. You could also throw an index on the column you're using in your order by clause, but that won't make a difference until your table get a little bigger. Please take further questions of this nature to the pgsql-novice list. 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 ---(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,
Jon Jensen wrote: On Wed, 28 Apr 2004, Christopher Kings-Lynne wrote: 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. This wouldn't be a problem, though, would it? The dumps don't run inside a transaction so after the failure to create PL/PgSQL because it already exists, the import would continue as before. We could make it somewhat cleaner by having a flag on pg_dump that allowed/suppressed the language creation for plpgsql. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg ANY/SOME ambiguity wrt sql standard?
Fabien COELHO [EMAIL PROTECTED] writes: I'm looking into adding sql standard aggregates EVERY/ANY/SOME. It seems to me that there is a syntax ambiguity with ANY and SOME: CREATE TABLE bla(b BOOL); SELECT TRUE = ANY(b) FROM bla; AFAICS this ambiguity is built into the SQL standard, and in fact it's possible to generate cases that are legally parseable either way: SELECT foo.x = ANY((SELECT bar.y FROM bar)) FROM foo; The parenthesized sub-select could be a plain value expression, in which case ANY must be an aggregate function call, or we could regard it as a table subquery, in which case we've got a quantified comparison predicate. These interpretations could both work, if the sub-select yields only one row, but they won't necessarily give the same answer. So I think that the SQL committee shot themselves in the foot when they decided it was a good idea to call the boolean-OR aggregate ANY, and our addition of an array option isn't the fundamental problem. Anyone know if SQL2003 fixed this silliness? regards, tom lane ---(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] Advice regarding configuration parameters
Patch posted on the patches list :-) Let me know what you think. - thomas Joe Conway [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Thomas Hallgren wrote: Some very good suggestions where made here. What happens next? Will this end up in a TODO list where someone can claim the task (I'm trying to learn how the process works) ? If someone doesn't jump right on it and make a diff -c proposal, it probably belongs on the TODO list. If your need is sufficiently high, and you have the time to take it on, then go for it ;-). If not, I might someday, but no promises for 7.5. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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 1 - Code Overview (1)
Am Tuesday 27 April 2004 22:21 schrieb Simon Riggs: Why isn't the archiver process integrated into the server? You ask a reasonable question however. If pg_autovacuum exists, why should pg_autoarch not work also? pg_autovacuum is going away to be integrated as a backend process. ---(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 1 - Code Overview (1)
Am Tuesday 27 April 2004 19:59 schrieb Bruce Momjian: 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. When the server is not running there is nothing to archive, so I don't follow this argument. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR Phase 1 - Code Overview (1)
Am Monday 26 April 2004 23:11 schrieb Simon Riggs: ii) encourage (i.e. force) people using XLogArchive API to run as the PostgreSQL owning-user (postgres). I think this is perfectly reasonable. ---(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?
Andrew Payne wrote: Bruce wrote: 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. Uh, no. Linux HAD a commercial entity providing marketing, support, and direction. Red Hat went a long, long way to making Linux real for businesses. They were (are) a well-funded entity, focused on Linux adoption. Their early success, in turn, validated the business (a) so competitors got funded and (b) so established companies (e.g. IBM) started to pay attention. (This is not meant to give all credit to Red Hat: if it wasn't them, it would have been some other similar group). So, 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? If not, where's the Red Hat for Postgres? My point was that once a single company showed it as profitable, other companies came alone and no one company controls Linux development. We have that now with SRA, Red Hat, Fujitsu, and many smaller companies funding development of PostgreSQL. (In fact, there were several Linux companies before Red Hat.) Now, if you are asking about marketing, yea, we don't have much in that area right now, and we need it. I think your point was that we need a single controlling company to provide marketing because if there are many, there is little incentive to market PostgreSQL because all the other companies are taking advantage of it. That is mostly true. However, I would argue that Red Hat providing support was more important than Red Hat marketing, and we do have that with a number of companies now, and SRA is going to be announcing world-wide support soon (not just Japan), and we have other venture capital guys looking a forming companies. My concern about a single company, as all of us are, is that we kill the community that created the software, which then burdens the single company to steer development, leading to disaster. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PITR Phase 1 - Code Overview (1)
On Wed, 2004-04-28 at 16:14, Peter Eisentraut wrote: Am Tuesday 27 April 2004 19:59 schrieb Bruce Momjian: 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. When the server is not running there is nothing to archive, so I don't follow this argument. The running server creates xlogs, which are still available for archive even when the server is not running... Overall, your point is taken, with many additional comments in my other posts in reply to you. I accept that this may be desirable in the future, for some simple implementations. The pg_autovacuum evolution path is a good model - if it works and the code is stable, bring it under the postmaster at a later time. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR Phase 2 - Design Planning
On Wed, 2004-04-28 at 05:00, Bruce Momjian wrote: 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. Good thinking. I'll have a look at this and come back to you. 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: How to Welcome Windows Users (was Re: [HACKERS] 7.5 features)
On Tue, 27 Apr 2004, Richard Huxton wrote: 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? I would say the OLE-DB driver would be nice to have ready to go. There are apparently a few projects on source forge to make one that are making good progress, and it would be nice to have a fairly workable solution about the time 7.5 rolls out. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR Phase 2 - Design Planning
Simon Riggs wrote: On Wed, 2004-04-28 at 05:00, Bruce Momjian wrote: 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. Good thinking. I'll have a look at this and come back to you. log_statement triggered logging happens very early in the process - if you are logging them all it happens before the statements are even parsed. Would we have an xid to log sensibly at that stage? Perhaps with log_min_duration_statment = 0 we would (i.e. log statements when finished rather than when started). 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] [pgsql-advocacy] What can we learn from MySQL?
On Tue, 27 Apr 2004, Andrew Payne wrote: Scott Marlowe wrote: 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. Netscrape/SunONE may have been a leader in some sub-market, but this misses the point. Not A submarket, THE submarket, enterprise class application server, i.e. web commerce and such. Just because apache hosts hundreds of thousands of personal web sites with all static content does not make it a market leader. When it came to commercial usage, apache still had to fight its way to the top. Apache + NCSA never had less than 50% market share, overall. http://news.netcraft.com/archives/web_server_survey.html Again, if 98% of those sites are personal web sites with static content, (they certainly were until a few years ago) and you remove those from the counting, then you find out that in enterprise class web servers, apache had sound competition it is only now starting to consume. Postgres is in a completely different situation: 95+?% of the world's databases don't run on Postgres, and it's been this way for a long time. and some large percentage of the worlds app servers were running on something other than apache for quite some time too. If postgresql was ubiquitous as the database of choice for simple access type applications, it would still have to earn its stripes in the enterprise one at a time. My point: Apache was successful in a situation that may not apply here. I agree that the situations aren't the exact same, but they're more similar than most people realize. Apache was never a market leader in the enterprise realm until fairly late in the 1.3.x series releases. 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? gcc? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] signal 11 on AIX: 7.4.2
On Mon, Apr 26, 2004 at 03:19:21PM -0400, Bruce Momjian wrote: Has this been resolved? Not as far as I know. Unfortunately, the problem happened in an environment I Can't Play With, and I haven't been able to reproduce it elsewhere. I've been trying some alternative approaches to causing it today, and so far no luck. Jan is, AFAIK, similarly mystified about what happened. A -- Andrew Sullivan | [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?
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? gcc? Nope most big houses will use Intel/Borland/Vc++ or whatever comes with Solaris. In fact, I can not think of a single project that has displaced a commercial one, without market force behind it. Linux won't do it without RedHat/Novell. I would even dare say that Novell will be that driving force, not RedHat. Even Apache has an entity... It actually became much more popular once that entity came to existence (even though it was a 501). Another look at Linux shows that it's popularity amongst the washed masses didn't really soar until Big Blue (IBM) starting pushing it. PHP might be an interesting thought, but ASP is used more widely as is Java for commercial stuff. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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 Wed, 2004-04-28 at 18:35, Andrew Dunstan wrote: Simon Riggs wrote: On Wed, 2004-04-28 at 05:00, Bruce Momjian wrote: 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. Good thinking. I'll have a look at this and come back to you. log_statement triggered logging happens very early in the process - if you are logging them all it happens before the statements are even parsed. Would we have an xid to log sensibly at that stage? Perhaps with log_min_duration_statment = 0 we would (i.e. log statements when finished rather than when started). Let's call this XLogSpy. For starters, we only need to look at write transactions. Many read-only transactions would not need to be examined, just to know they were read only. Remembering that we're using xlogs for recovery, we perhaps should not assume that we have anything other than that which has been archived. Currently, that is just the xlogs. So really we need to work off what is within them and right now that isn't much at all. We might optionally include the parsed statement data into the xlog, since this might be smaller than the exact text itself and would also allow us to filter the xlogs based upon any aspect of the lists. Not really happy with any of these ideas yet. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nasty security bug with clustering
I'm in the middle of reviewing (read whacking around) Rod Taylor's patch for multiple operations in ALTER TABLE, so I'm afraid that no patch in the same area is likely to apply cleanly after the dust settles :-( OK, Bruce - just ignore the patch I sent in. I'll refactor it after Tom commits. 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] PITR Phase 2 - Design Planning
Simon Riggs said: On Wed, 2004-04-28 at 18:35, Andrew Dunstan wrote: Simon Riggs wrote: On Wed, 2004-04-28 at 05:00, Bruce Momjian wrote: 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. Good thinking. I'll have a look at this and come back to you. log_statement triggered logging happens very early in the process - if you are logging them all it happens before the statements are even parsed. Would we have an xid to log sensibly at that stage? Perhaps with log_min_duration_statment = 0 we would (i.e. log statements when finished rather than when started). Let's call this XLogSpy. For starters, we only need to look at write transactions. Many read-only transactions would not need to be examined, just to know they were read only. Remembering that we're using xlogs for recovery, we perhaps should not assume that we have anything other than that which has been archived. Currently, that is just the xlogs. So really we need to work off what is within them and right now that isn't much at all. We might optionally include the parsed statement data into the xlog, since this might be smaller than the exact text itself and would also allow us to filter the xlogs based upon any aspect of the lists. Not really happy with any of these ideas yet. I don't see how this relates to the log_line_prefix idea, or are you abandoning that? cheers andrew ---(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] linked list rewrite
On 28-Apr-04, at 12:12 AM, Bruce Momjian wrote: 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. I'm not sure what you mean: list_head() returns the head of the list, which is the first cell in the list. That cell contains a data value and a pointer to the next cell in the list. Makes sense to me... -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] linked list rewrite
On 27-Apr-04, at 10:07 PM, Tom Lane wrote: [ ... on the topic of list_union(), list_union_int() and friends ] I guess the real question in my mind is whether there is any true gain in symmetry or readability by doing it this way. I think there's a small gain: everything else being equal, an API with fewer functions is easier to use and easier to understand. If we can provide a single function that takes the place of three functions without losing anything, we ought to do so. -Neil ---(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
[HACKERS] PITR logging control program
Simon Riggs wrote: When the server is not running there is nothing to archive, so I don't follow this argument. The running server creates xlogs, which are still available for archive even when the server is not running... Overall, your point is taken, with many additional comments in my other posts in reply to you. I accept that this may be desirable in the future, for some simple implementations. The pg_autovacuum evolution path is a good model - if it works and the code is stable, bring it under the postmaster at a later time. [ This email isn't focused because I haven't resolved all my ideas yet.] OK, I looked over the code. Basically it appears pg_arch is a client-side program that copies files from pg_xlog to a specified directory, and marks completion in a new pg_rlog directory. The driving part of the program seems to be: while ( (n = read( xlogfd, buf, BLCKSZ)) 0) if ( write( archfd, buf, n) != n) return false; The program basically sleeps and when it awakes checks to see if new WAL files have been created. There is some additional GUC variable to prevent WAL from being recycled until it has been archived, but the posted patch only had pg_arch.c, its Makefile, and a patch to update bin/Makefile. Simon (the submitter) specified he was providing an API to archive, but it is really just a set of C routines to call that do copies. It is not a wire protocol or anything like that. The program has a mode where it archives all available wal files and exits, but by default it has to remain running to continue archiving. I am wondering if this is the way to approach the situation. I apologize for not considering this earlier. Archives of PITR postings of interest are at: http://momjian.postgresql.org/cgi-bin/pgtodo?pitr It seems the backend is the one who knows right away when a new WAL file has been created and needs to be archived. Also, are folks happy with archiving only full WAL files? This will not restore all transactions up to the point of failure, but might lose perhaps 2-5 minutes of transactions before the failure. Also, a client application is a separate process that must remain running. With Informix, there is a separate utility to do PITR logging. It is a pain to have to make sure a separate process is always running. Here is an idea. What if we add two GUC settings: pitr = true/false; pitr_path = 'filename or |program'; In this way, you would basically specify your path to dump all WAL logs into (just keep appending 16MB chunks) or call a program that you pipe all the WAL logs into. You can't change pitr_path while pitr is on. Each backend opens the filename in append mode before writing. One problem is that this slows down the backend because it has to do the write, and it might be slow. We also need the ability to write to a tape drive, and you can't open/close those like a file. Different backends will be doing the WAL file additions, there isn't a central process to keep a tape drive file descriptor open. Seems pg_arch should at least use libpq to connect to a database and do a LISTEN and have the backend NOTIFY when they create a new WAL file or something. Polling for new WAL files seems non-optimal, but maybe a database connection is overkill. Then, you start the backend, specify the path, turn on pitr, do the tar, and you are on your way. Also, pg_arch should only be run the the install user. No need to allow other users to run this. Another idea is to have a client program like pg_ctl that controls PITR logging (start, stop, location), but does its job and exits, rather than remains running. I apologies for not bringing up these issues earlier. I didn't realize the direction it was going. I wasn't focused on it. Sorry. -- 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] linked list rewrite
Neil Conway wrote: On 28-Apr-04, at 12:12 AM, Bruce Momjian wrote: 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. I'm not sure what you mean: list_head() returns the head of the list, which is the first cell in the list. That cell contains a data value and a pointer to the next cell in the list. Makes sense to me... Oh, I thought cell returned the first data value, not the first-data-and-pointer-to-next-one. Yes, list_head and list_tail seems fine. Why mix cell in there? I don't know what a cell is. -- 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] 7.5 features
Tom Lane wrote: 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. Gavin says he has patches for tablespaces, Alvaro has submitted patches already, server-side java is heading into beta, and Heikki has two-phase commit patches because he already worried about drift. PITR patches have been submitted too. I wouldn't call them done, but I wouldn't call them handwaving either. What we really need is for these folks to start finalizing their patches and get them submitted. -- 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
Bruce Momjian [EMAIL PROTECTED] writes: What we really need is for these folks to start finalizing their patches and get them submitted. Eggzackle ... my point is that I see the win32 train leaving the station pretty soon, and I don't see anyone else ready to get on board. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] TPC H data
What scale factor TPC H are you importing? additionally - might be worth giving the specs of the machine you are doing this on. (I seem to recall trying this with Pg 7.2 a while ago without this issue, mind you - think I had ~1G of Ram and used the scale fact 1 dataset, i.e 1G) regards Mark 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. Thanks Shalu Gupta NC State University. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] error about tcl/tk
dear all i compiled postgresql7.4 ,but when type in make ,error appeared as following: bison -y -d gram.y NONE:0: /usr/bin/m4: ERROR: EOF in string sed -e 's/yy/plpgsql_yy/g' -e 's/YY/PLPGSQL_YY/g' y.tab.c ./pl_gram.c /bin/sh: line 1: y.tab.c: No such file or directory make[4]: *** [pl.tab.h] Error 1 make[4]: Leaving directory `/home/sm/pgsql7.4/src/pl/plpgsql/src' make[3]: *** [all] Error 2 before this ,i configure like this : ./configure --enable-multibyte --with-tcl --with-tclconfig= /usr/local/lib --with-tkconfig=/usr/local/lib (see at http://member.melbpc.org.au/~bradmac/postgreSQL.html#LtohTOCentry-2) i have already intalled tck8.4.5 and tk8.4.5 would you please tell what's the matter with my postgres? thanks :) [EMAIL PROTECTED] 2004-04-29 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
