Re: [HACKERS] Last beta ... we hope?
Hi, 7.4beta4 is still mentioned in INSTALL. Tommi Am Mittwoch, 22. Oktober 2003 16:21 schrieb Marc G. Fournier: 'K, I packaged it up last night so that the ftp mirrors could get up to date on it ... I'm going to put out an announce to -general and -announce on this later this evening, but if someone wants to take a quick scan of the tar ball to make sure that it all looks okay to them, that would be great ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] pg_ctl reports succes when start fails
Hi, I installed 7.4beta5, created a data-dir and tried to start postgresql with pg_ctl without initdb. As expected, this will fail. But pg_ctl tells me postmaster successfully started, after a fatal error, which looks very confusing. When I use -l for specifying a logfile, I don't even see the error, but only the success-message. Tommi -- Dr. Eckhardt + Partner GmbH http://www.epgmbh.de ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] So, are we going to bump catversion for beta5, or not?
On Wednesday 22 October 2003 20:12, scott.marlowe wrote: On Wed, 22 Oct 2003, Tom Lane wrote: It would probably be a good idea to allow the function's search path to be explicitly specified as a clause of CREATE FUNCTION (otherwise it will be a headache for pg_dump). So we could allow both viewpoints, if there is a way to explicitly say don't force any search path. Perhaps specifying an empty path could mean that. But I think the default should be to adopt the current search path (at the time of CREATE FUNCTION) as the function's permanent path. It might be nice to have an alter function capability that could change the search path at a later date should one add schema etc... later on. If it's part of CREATE FUNCTION then presumably CREATE OR REPLACE FUNCTION would let you do that (it's not changing the signature of the function, so I can't think why not). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Automatic compat checking? (was 7.4 compatibility question)
On Wednesday 22 October 2003 07:37, Neil Conway wrote: The second audience is the people who are really interested in exactly what has changed between the new release of PostgreSQL and the previous release series. It is important that we make it easy for an admin planning a PostgreSQL upgrade at a fairly large site to be able to see what changes in PostgreSQL have been made, and what changes will be necessary in their own applications. Something I was pondering the other day was whether a pg_compat_chk utility would be practical/desirable. You run it against your existing database / schema dump and it prints a set of warnings: Old version = 7.2.1 New version = 7.4.0 Warning: schema support introduced (v7.3) all objects will be placed in the default schema Failure: DEFAULT 'now' not supported (v7.4) table1.column2 table2.column3 Notice: timestamp now holds milliseconds by default (v7.3) tableX.whatever My main concern would be that a 90% solution might be worse than nothing at all. Incidentally, this is not idle speculation, but something I might well have time to stick in gborg during the 7.5 devt cycle. -- Richard Huxton Archonet Ltd A pg_compat_chk utility sounds great. No idea, if this is practical, but it's desirable - at least to me. Regards, Christoph PS I'm surprised no one else replied. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Automatic compat checking? (was 7.4 compatibility question)
On Friday 24 October 2003 00:01, Christoph Haller wrote: A pg_compat_chk utility sounds great. No idea, if this is practical, but it's desirable - at least to me. Well, I'm confident the first 90% is practical just by running some regexps against a pg_dumped schema. It doesn't need to guarantee there's a problem, just say here's something you want to check. And there are things you could probably never check convincingly (e.g. the queries in an application). It's that final 10% that makes me uncertain. Maybe it'd be enough to just list tests I couldn't perform, at first anyway. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] multi-backend psql
Rod Taylor wrote: On Tue, 2003-10-21 at 21:24, Christopher Kings-Lynne wrote: There is always the biggest evil of all... Putting SHOW / DESCRIBE / HELP commands into the backend itself. I'm sure the pgAdmin group likes that idea (they're probably tired of maintaining 4 different versions of queries for getting a list of tables). Any solution to make psql backward or forward compatible should go an additional step to assist other frontends as well. All that means for phpPgAdmin and pgAdmin is that we'll have to support 5 different queries :P Yes, but I would hope it stops at 5, and over the next 3 years you don't have 10 different query forms. We could use information_schema... Nay... I would expect a PostgreSQL specific information_schema to get just as much mucking around as the system tables, which means you are still maintaining a set of queries per release. The problem about information_schema is that it's restricted to show objects of the owner only. This is by spec, but will prevent us from seeing all we need. This might get better if we get rules. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] is GiST still alive?
On Wed, 22 Oct 2003, Christopher Browne wrote: No, tables wouldn't be the right way to do it. But it's going to be troubled, in any case, because of the every-popular mixtures of: a) Often weird declarations of what character sets are in use; I gotta admit that I haven't spend too much attention on that specific part. But couln't you just store it in the character set that was originally used to populate the document? b) Pointers to other parts of a document; do you mean to the parent element and the child elements? This is specifially what my custom format is designed for. c) What's a database going to consist of? One XML document? Or many? many, each of which can be up to about 1TB And if many, then then how do you have a centralized reference point to navigate from to find the document that you want? This one could be a table, or another xml document. And navigate was a carefully chosen word; what you then have is essentially a network database system, and have to then start making up ways of describing queries. XQuery may be better than CODASYL of yesteryear, but you're still left writing a lot of recursive code. (Thus making those that understand the Lambda Nature more powerful...) I don't get your point? XQuery works on one document, IIRC. At the end, do you have a database? Or just a set of documents? It's hard to tell, a priori. OK, know waht you mean. I'd say it's a database, because the information is stored not plain - but in pages and in an optimized format for insertion, deletion and querying. And do you think this is likely to be useful because: a) You have some clear notion as to why this ought to be useful? yes. Modyfing and querying plain xml files sucks performancewise once your documents get a little larger (100 MB+) b) XML is a big buzzword, and people have been able to succesfully attract research funds or vulture capital on the basis of having that acronym in a proposal? That time's over anyways, isn't it? -- Gregor Zeitlinger [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 compatibility question
-Original Message- From: Bruce Momjian ... The big question is whether the current release notes hit he right balanced. Do they for you? The last time I read the notes was when we upgraded to 7.3.4. I'll pick up couple entries from Release Notes and the HISTORY file (which we always read) for examples. PostgreSQL now supports the ALTER TABLE ... DROP COLUMN functionality. = this is entirely sufficient. Detailed info can be found in the docs. Optimizer improvements = this tells me nothing. I suppose this could be a minor internal code tweak, which does not affect me. On the other hand this could be a major breakthrough, so now I can run some stupid query which would take a week to complete in the previous release. How do I know? Fix to_ascii() buffer overruns = I don't think I need any more details here Work around buggy strxfrm() present in some Solaris releases = if we did not suffer from this (big thanks for fixing!) I would've never guessed how it may manifest itself and affect the database, even though this alone could be a strong reason for upgrade. If you think this would take too much space and bloat the document, then maybe the best solution is to have a reference number: Bug# 123 : Work around ... Then I could go to some http://postgres../bugtrack enter this number and learn more. Mike. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] is GiST still alive?
On Thu, 23 Oct 2003, Christopher Kings-Lynne wrote: 4. Extend the contrib/ltree gist-based tree indexing scheme to work on xml and hence the operations in no.3 above are really fast... but then, the plain xml data is still stored in a database colum, if I understand correctly? -- Gregor Zeitlinger [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] is GiST still alive?
On Wed, 22 Oct 2003, Josh Berkus wrote: Reinvent the wheel? Well, yes. The first thing ... the VERY first thing, abosolutely ... that you need to do is invent a theory of XML databases. Well, I have. It doen't cover all parts in detail yet, because I've started with a simple IO layer (simple page locking, no concurrent transactions) and worked on the page layout and parsing algorithms from there on. Querying on that format will follow thereafter. And concurrency issuses will be dealt with even later. I am considering hierachical locking (any part of the tree). I was just wondering wheater I could take some of that fuctionlay from an existing database. Without these things, you're just another idiot floundering around a morass of acronyms and half-baked ideas. I know With them, you will have something that no current XML database project/product has, and can give XML databases a fighting chance to survive beyond the current fad. If there was a promising project for an xml database, I would have joined it. Of course,it's possible in the course of theorizing that you may prove that XML databases are impossible. But that's how the cookie crumbles only an implementation is a real proof. -- Gregor Zeitlinger [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] is GiST still alive?
Storing the XML text has problems - you have to parse it every time you want something - that has to cause a huge performance hit. I use XML a lot for all sorts of purposes, but it is appropriate for data transfer rather than data storage, IMNSHO. cheers andrew Christopher Kings-Lynne wrote: Do this: 1. Create a new type called 'xml', based on text. 2. The xmlin function for that type will validate what you are enterering is XML 3. Create new functions to implement XPath, SAX, etc. on the xml type. 4. Extend the contrib/ltree gist-based tree indexing scheme to work on xml and hence the operations in no.3 above are really fast... Chris Andrew Dunstan wrote: Christopher Browne wrote: But I think back to the XML generator I wrote for GnuCash; it has the notion of building up a hierarchy of entities and attributes, each of which is visible as an identifyable object of some sort. Mapping that onto a set of PostgreSQL relations wouldn't work terribly well. *nod* I have tried this several times - it just doesn't work well, because the maps are too different. You could do something like this: . a table for each element type, fields being the attributes, plus the node id. . a table to tie everything together (parent_id, child_id, child_order, child_type). In theory you could even generate the DB schema from an XML schema and evaluate it with XPath-like expressions. But why put yourself to such bother? I have never found a good reason to do this sort of thing. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] is GiST still alive?
On Thu, 23 Oct 2003, Andrew Dunstan wrote: Storing the XML text has problems - you have to parse it every time you want something - that has to cause a huge performance hit. You couldn't have said better what I meant. I store the xml already parsed. You can navigate right along. To the parent, the previous, the next elemnt or the first or last child. I use XML a lot for all sorts of purposes, but it is appropriate for data transfer rather than data storage, IMNSHO. Right now, you're quite right. But I want to change that. -- Gregor Zeitlinger [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] multi-backend psql
Nay... I would expect a PostgreSQL specific information_schema to get just as much mucking around as the system tables, which means you are still maintaining a set of queries per release. The problem about information_schema is that it's restricted to show objects of the owner only. This is by spec, but will prevent us from seeing all we need. This might get better if we get rules. Well... That, and it only describes about 1/2 of the features in PostgreSQL. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] is GiST still alive?
4. Extend the contrib/ltree gist-based tree indexing scheme to work on xml and hence the operations in no.3 above are really fast... but then, the plain xml data is still stored in a database colum, if I understand correctly? Yep - which to me seems to be the most useful way to store it :) Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] is GiST still alive?
*nod* I have tried this several times - it just doesn't work well, because the maps are too different. You could do something like this: . a table for each element type, fields being the attributes, plus the node id. . a table to tie everything together (parent_id, child_id, child_order, child_type). In theory you could even generate the DB schema from an XML schema and evaluate it with XPath-like expressions. Although bits separated from a topic, I am developing XML database enviroment based on PostgreSQL, XpSQL. http://gborg.postgresql.org/project/xpsql/ The enviroment decompose XML documents into fragments and uses PostgreSQL to store them in a set of relations. Users do not have to know that they are stored as relations. It provides a variety of means to access XML Documents; (a) DOM functions to build and traverse XML Documents (b) XPath engine to extract infomation from documents (c) update functions to modify documents Although the development is on going, the performance is better than Apache Xindice. #if you require, I'll send you the result after. - points - * node labeling is based on dewey decimal classification, so updating performance is well. * The index method is general (postgres) B-tree. this is a programing image. http://gborg.postgresql.org/project/xpsql/cvs/co.php/XpSQL/doc/USAGE?r=1.4 +---+ Makoto Yui [EMAIL PROTECTED] Key fingerprint = 6462 E285 97D8 1323 40C4 F9E5 EB0F 9DE6 1713 219E +---+ ---(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] pg_ctl reports succes when start fails
Tommi Maekitalo [EMAIL PROTECTED] writes: I installed 7.4beta5, created a data-dir and tried to start postgresql with pg_ctl without initdb. As expected, this will fail. But pg_ctl tells me postmaster successfully started, after a fatal error, which looks very confusing. When I use -l for specifying a logfile, I don't even see the error, but only the success-message. If you don't use -w, then pg_ctl doesn't wait around to see whether the postmaster started or not. It'd probably be a good idea for it to issue a less positive message in this case, maybe only postmaster launched. I also wonder why -w isn't the default. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] is GiST still alive?
On Thu, 23 Oct 2003, Christopher Kings-Lynne wrote: You couldn't have said better what I meant. I store the xml already parsed. You can navigate right along. To the parent, the previous, the next elemnt or the first or last child. Which is the whole point of indexing it... not quite. Indexing can solve some of the problems, not all. 1) You have to update the index every time you modify the data. My custom format serves as an index for some queries. 2) The page format is designed in such a way that modifications (insertion, deletion) are as fast as the original parsing. I'm not sure how that stacks up to modifying data in a column. I guess it depens on the strategy to store very large strings in columns. I use XML a lot for all sorts of purposes, but it is appropriate for data transfer rather than data storage, IMNSHO. Right now, you're quite right. But I want to change that. No point, it's a data exchange format, it's not usefull for data storage. Well, neither one is a data exchange format only or a data storage format only. Rather, the difference is that relations are designed to store structued data while xml is desinged to store semi-structued (not so regular) data. Which is better suited for data exchange is a matter of convention (where xml seems to be good), while efficiency and other features of an implementation determine, which one is suited for data storage. If your point is that currently xml is not suited for storage, because there are more efficent RDBMS than xml databases, I agree. Otherwise, I don't see your point. -- Gregor Zeitlinger [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] is GiST still alive?
Gregor, Well, I have. It doen't cover all parts in detail yet, because I've started with a simple IO layer (simple page locking, no concurrent transactions) and worked on the page layout and parsing algorithms from there on. Querying on that format will follow thereafter. And concurrency issuses will be dealt with even later. Um, I/O and Page layout are not theory. They are implementation issues. Theory would answer things like What are the mathematical operations I can use to define compliance or non-compliance with the DTD for a heirarchy and for data elements? Or, Is an XML database multiple documents or a single large document? Or, How may new items be added to a DTD for an existing database, and what operations must then be performed on that database to enforce compliance? etc. only an implementation is a real proof. Implementation is proof of a theory. But you've got to have the theory first or you don't know what you're proving. Anyway, I don't think you an borrow code from any existing relational database,since an XML database would be radically different structurally. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_ctl reports succes when start fails
If you don't use -w, then pg_ctl doesn't wait around to see whether the postmaster started or not. It'd probably be a good idea for it to issue a less positive message in this case, maybe only postmaster launched. I also wonder why -w isn't the default. I've also noticed that on our production 7.3.4 server logging to syslog, that if I change the postgresql.conf to enable log_statement, and then do pg_ctl reload, it works, but then when I disable it again, pg_ctl reload does not cause postgres to pick up the changes. I haven't done too much investigation here in to the exact problem, but there is something odd going on... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_ctl reports succes when start fails
Tom Lane wrote: Tommi Maekitalo [EMAIL PROTECTED] writes: I installed 7.4beta5, created a data-dir and tried to start postgresql with pg_ctl without initdb. As expected, this will fail. But pg_ctl tells me postmaster successfully started, after a fatal error, which looks very confusing. When I use -l for specifying a logfile, I don't even see the error, but only the success-message. If you don't use -w, then pg_ctl doesn't wait around to see whether the postmaster started or not. It'd probably be a good idea for it to issue a less positive message in this case, maybe only postmaster launched. I also wonder why -w isn't the default. It is for stop but not for start/restart, which does seem a bit odd. On a slightly related note, I see that this is still a shell script, as are initlocation, ipcclean and pg_config. I assume these will have to be rewritten in C for the Win32 port? 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] pg_ctl reports succes when start fails
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I've also noticed that on our production 7.3.4 server logging to syslog, that if I change the postgresql.conf to enable log_statement, and then do pg_ctl reload, it works, but then when I disable it again, pg_ctl reload does not cause postgres to pick up the changes. By disable do you mean turn off, or comment out again? The latter is not going to affect the state of the postmaster ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] is GiST still alive?
On Thu, 23 Oct 2003, Josh Berkus wrote: Um, I/O and Page layout are not theory. They are implementation issues. yes or no, depending on your point of view. Theory would answer things like What are the mathematical operations I can use to define compliance or non-compliance with the DTD for a heirarchy and for data elements? snip Yes, that's also tought of. Most of it was done by a collegue at university, who came up with the idea, but didn't do any implementation. only an implementation is a real proof. Implementation is proof of a theory. But you've got to have the theory first or you don't know what you're proving. agreed. Anyway, I don't think you an borrow code from any existing relational database,since an XML database would be radically different structurally. I'm getting this impression, too. -- Gregor Zeitlinger [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_ctl reports succes when start fails
By disable do you mean turn off, or comment out again? The latter is not going to affect the state of the postmaster ... The latter...why won't it affect the postmaster state? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_ctl reports succes when start fails
Christopher Kings-Lynne [EMAIL PROTECTED] writes: By disable do you mean turn off, or comment out again? The latter is not going to affect the state of the postmaster ... The latter...why won't it affect the postmaster state? Because it's a *comment*. 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] pg_ctl reports succes when start fails
The latter...why won't it affect the postmaster state? Because it's a *comment*. Shouldn't it revert to the default value? 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] 7.4 compatibility question
Michael Brusser wrote: -Original Message- From: Bruce Momjian ... The big question is whether the current release notes hit he right balanced. Do they for you? The last time I read the notes was when we upgraded to 7.3.4. I'll pick up couple entries from Release Notes and the HISTORY file (which we always read) for examples. PostgreSQL now supports the ALTER TABLE ... DROP COLUMN functionality. = this is entirely sufficient. Detailed info can be found in the docs. Good. Optimizer improvements = this tells me nothing. I suppose this could be a minor internal code tweak, which does not affect me. On the other hand this could be a major breakthrough, so now I can run some stupid query which would take a week to complete in the previous release. How do I know? Yes, this is always very hard to explain. The optimizer itself is complex, and uses complex terms like merge join and key pruning. It is hard to explain what queries will be affected, though the basic issue is that the optimizer will choose a better plan more frequently. Fix to_ascii() buffer overruns = I don't think I need any more details here Work around buggy strxfrm() present in some Solaris releases = if we did not suffer from this (big thanks for fixing!) I would've never guessed how it may manifest itself and affect the database, even though this alone could be a strong reason for upgrade. We don't actually explain enough in the release notes for people to determine if they should do _minor_ upgrades --- bottom line is that minor upgrades only require a stop/install/restart postmaster, so we assume everyone will do that, and in this case, if you are running Solaris, that is enough of a reason alone --- whether that particular bug affects you or not. If you think this would take too much space and bloat the document, then maybe the best solution is to have a reference number: Bug# 123 : Work around ... Then I could go to some http://postgres../bugtrack enter this number and learn more. Yes, that would be nice. So, it sounds like we are already pretty close to ideal for you. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Last beta ... we hope?
Tommi Maekitalo wrote: Hi, 7.4beta4 is still mentioned in INSTALL. Oh, I didn't realize the beta number was in the install. I just updated it, and we will have the right numbers in the for final. -- 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 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_ctl reports succes when start fails
Andrew Dunstan wrote: Tom Lane wrote: Tommi Maekitalo [EMAIL PROTECTED] writes: I installed 7.4beta5, created a data-dir and tried to start postgresql with pg_ctl without initdb. As expected, this will fail. But pg_ctl tells me postmaster successfully started, after a fatal error, which looks very confusing. When I use -l for specifying a logfile, I don't even see the error, but only the success-message. If you don't use -w, then pg_ctl doesn't wait around to see whether the postmaster started or not. It'd probably be a good idea for it to issue a less positive message in this case, maybe only postmaster launched. I also wonder why -w isn't the default. It is for stop but not for start/restart, which does seem a bit odd. On a slightly related note, I see that this is still a shell script, as are initlocation, ipcclean and pg_config. I assume these will have to be rewritten in C for the Win32 port? OK, I updated the Win32 web page to mention we need a C version of pg_ctl. I don't think we will need pg_config once we have initdb in C, and I don't think it is worth doing initlocation because we need tablespaces. -- 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] pg_ctl reports succes when start fails
Tom Lane wrote: Tommi Maekitalo [EMAIL PROTECTED] writes: I installed 7.4beta5, created a data-dir and tried to start postgresql with pg_ctl without initdb. As expected, this will fail. But pg_ctl tells me postmaster successfully started, after a fatal error, which looks very confusing. When I use -l for specifying a logfile, I don't even see the error, but only the success-message. If you don't use -w, then pg_ctl doesn't wait around to see whether the postmaster started or not. It'd probably be a good idea for it to issue a less positive message in this case, maybe only postmaster launched. I also wonder why -w isn't the default. The following patch changes the message from started to starting for non--w starts. I will keep it for 7.5. -- 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 Index: src/bin/pg_ctl/pg_ctl.sh === RCS file: /cvsroot/pgsql-server/src/bin/pg_ctl/pg_ctl.sh,v retrieving revision 1.36 diff -c -c -r1.36 pg_ctl.sh *** src/bin/pg_ctl/pg_ctl.sh14 Aug 2003 18:56:41 - 1.36 --- src/bin/pg_ctl/pg_ctl.sh23 Oct 2003 16:49:56 - *** *** 399,406 fi done $silence_echo echo done fi - $silence_echo echo postmaster successfully started fi # start or restart exit 0 --- 399,408 fi done $silence_echo echo done + $silence_echo echo postmaster successfully started + else + $silence_echo echo postmaster starting fi fi # start or restart exit 0 ---(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] pg_ctl reports succes when start fails
Bruce Momjian wrote: OK, I updated the Win32 web page to mention we need a C version of pg_ctl. I don't think we will need pg_config once we have initdb in C, and I don't think it is worth doing initlocation because we need tablespaces. I will put it on my todo list (should be simpler than initdb ;-) ). I'll start with a Unix version since I haven't seen the shape of the signalling we are using on Win32 yet. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dreaming About Redesigning SQL
Bob Badour [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] All physical structures will bias performance for some operations and against others. This strikes me as a succinct statement of the value of data independence. One has the option (but not the requirement) to adjust the physical structures the DBMS uses while keeping the logical model (and therefor all application code and queries, etc.) unchanged. Unless one has data independence, one does not have this option; one will be locked into a particular performance model. This is why I found the MV guy's obvious pleasure at being able to precisely describe the performance model for his DB as odd: I thought it a deficit to be able to say what it was; he thought it an asset. Marshall PS. This is nothing you don't know, Bob; just a comment for the group. ---(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] Dreaming About Redesigning SQL
Lauri Pietarinen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Anthony W. Youngman wrote: In article [EMAIL PROTECTED], Lauri Pietarinen [EMAIL PROTECTED] writes Anthony W. Youngman wrote: Fine. But MV *doesn't* *need* much of a cache. Let's assume both SQL and MV have the same amount of RAM to cache in - i.e. *not* *much*. I did say the spec said extract maximum performance from the hardware available. So what's wrong with gettng a machine with lots of memory? How much does 2G of memory for an Intel-box cost now a days? Is this some kind of new ultimate sport, trying to get along with as little memory as possible? I presume you didn't read the bit below ... what if you have SEVERAL tables, and EACH of them is a gigabyte or two in size? OK, I get your point. Well, if it is normalised, how easy is it for you to change the customer_id of an order? Anyway, Incredibly easy. Just update the customer_id field of the invoice record. A single change to a single row And I presume the system will automatically move all related stuff (order details etc.) into the same block as the new customer? How long will that take? What if there is no room for it there? if we stick to your example and even if we don't normalise using e.g. clustering features of Oracle, as Bob pointed out, we are getting at most the same number of I/O's. So, answer to your question: our formula is at least as good as yours. Except I think Bob said we could optimise to favour *certain* transactions. Exactly. This is as true for Pick as it is for any other file processor. I think actually ANY transaction benefits. Wol thinks a lot of things that are just plain wrong. That's inherent to his ignorance and his stupidity. You're relying on stuff that's outwith your theory, we're relying on stuff that's inherent to our model. I am relying on reality, and Wol relies on fantasy. In his mind, he is right and nothing will ever change his mind. That certainly is not true. The theory says NOTHING about how data should be arranged on disk. You are talking about how modern SQL-databases behave. No, he isn't. Wol doesn't even know how modern SQL-databases really behave. He is talking about nothing but his own imagined prejudices. The DBMS is at liberty to do whatever it pleases with the data, even save it in a PICK database. Hey, wadda you think? Would that be a good idea? We get to keep our SQL but with the speed of PICK ;-) Now, that was a *conservative* estimate, and we assumed that we did not have any rows lying around in the (global!) cache. As the size of the cache grows in proportion to the size of the total database we can assume less and less disk I/O. You're relying on the hardware to bale you out :-) We can do the same! Well why don't you? We let the hardware help us out if it can. There's a big difference. If you can't get the hardware, you're stuffed. We don't need it, so while we may have a hard time of it it's nowhere near as bad for us. And again, relational separates the physical from the logical. You're being hypocritical if you call upon the physical representation to help out with the (speed of the) logical presentation. My goodness, no I'm not! Its the same as claiming that if you have a drawing for a house, you have to make that house out of paper?!? Don't you see? Wol is an ignorant moron. You will save a lot of bandwidth if you simply ignore the idiot. I want a list with all products with corresponding total sales, read from order detail e.g. Hammer 1$ Nail 5000$ Screw 1200$ How many disk reads (or head movements)? Actually, probably the same as you here. If we're indexed on order detail. If Hammer appears in N invoices, then T = (1+N) * ST * 1.05 for hammers, and the same for all the other products. Theory favours us, in that if a product appears X times in one invoice, that's one read for us and X for you No, theory does not favour Wol's product at all. Making ignorant and stupid assertions about how many reads are required for a relational dbms does not affect the actual number of reads required. Wol is an ignorant moron. No matter how many times you explain these points to him, he will remain convinced of Pick's mythical performance advantage. , but hardware will probably help you more than us (that is, assuming thrashing cuts in) in that you stand a marginally higher chance of getting multiple instances of a product in any given read. So for each product you get T = (1+N) * ST * 1.05. Now, for our SQL-DBMS, presuming that we build indexes for detail and product: order_detail(product_id, qty, unit_price) = 20 bytes/row product(product_id, product_name) = 50 bytes/row With 2 disk reads I would get 8K/20 = 400 order detail rows and 8K/50 = 160 product rows Since all rows are in product_id order, no need for random disk
Re: [HACKERS] Dreaming About Redesigning SQL
Anthony W. Youngman [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] As soon as a requirement for a database specifies extraction of the maximum power from the box, I don't for a second believe that this is your only requirement, or that this is even an actual requirement. If it really is an actual requirement, then I assume you're writing all of your code in hand-tuned assembly language, and that the document you consult most regularly when writing code is the CPU's instruction timing table. Another commodity box costs $1000, which is about the same as the cost to a company of a day's programmer time. What *really* matters is getting software delivered in a timely fashion, that is as correct as possible, and that will operate reliably over time and not cause data corruption. Marshall ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dreaming About Redesigning SQL
Anthony W. Youngman [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Just like the academics were brainwashed into thinking that microkernels were the be-all and end-all - until Linus showed them by practical example that they were all idiots The academics (presumably you mean Tannenbaum et al) never claimed that monolithic kernels could not obtain market acceptance; they never said anything about market acceptance. Instead, they had identified a number of weaknesses of monolithic kernels and pointed out that a microkernel architecture didn't suffer from these problems. Certainly the monolithic kernel is easier to implement. Linus set out to build a unix kernel workalike, and he chose the easiest path, copying architecture from the 1970s, along with all the weaknesses that those idiot academics had identified years earlier. Since then, his monolithic kernel has gotten a lot of marketshare, due to a number of different reasons, none of them being architectural superiority. Marshall ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Dreaming About Redesigning SQL
Anthony W. Youngman wrote: In article [EMAIL PROTECTED], Lauri Pietarinen [EMAIL PROTECTED] writes Anthony W. Youngman wrote: Fine. But MV *doesn't* *need* much of a cache. Let's assume both SQL and MV have the same amount of RAM to cache in - i.e. *not* *much*. I did say the spec said extract maximum performance from the hardware available. So what's wrong with gettng a machine with lots of memory? How much does 2G of memory for an Intel-box cost now a days? Is this some kind of new ultimate sport, trying to get along with as little memory as possible? I presume you didn't read the bit below ... what if you have SEVERAL tables, and EACH of them is a gigabyte or two in size? OK, I get your point. Well, if it is normalised, how easy is it for you to change the customer_id of an order? Anyway, Incredibly easy. Just update the customer_id field of the invoice record. A single change to a single row And I presume the system will automatically move all related stuff (order details etc.) into the same block as the new customer? How long will that take? What if there is no room for it there? if we stick to your example and even if we don't normalise using e.g. clustering features of Oracle, as Bob pointed out, we are getting at most the same number of I/O's. So, answer to your question: our formula is at least as good as yours. Except I think Bob said we could optimise to favour *certain* transactions. I think actually ANY transaction benefits. You're relying on stuff that's outwith your theory, we're relying on stuff that's inherent to our model. That certainly is not true. The theory says NOTHING about how data should be arranged on disk. You are talking about how modern SQL-databases behave. The DBMS is at liberty to do whatever it pleases with the data, even save it in a PICK database. Hey, wadda you think? Would that be a good idea? We get to keep our SQL but with the speed of PICK ;-) Now, that was a *conservative* estimate, and we assumed that we did not have any rows lying around in the (global!) cache. As the size of the cache grows in proportion to the size of the total database we can assume less and less disk I/O. You're relying on the hardware to bale you out :-) We can do the same! Well why don't you? We let the hardware help us out if it can. There's a big difference. If you can't get the hardware, you're stuffed. We don't need it, so while we may have a hard time of it it's nowhere near as bad for us. And again, relational separates the physical from the logical. You're being hypocritical if you call upon the physical representation to help out with the (speed of the) logical presentation. My goodness, no I'm not! Its the same as claiming that if you have a drawing for a house, you have to make that house out of paper?!? I want a list with all products with corresponding total sales, read from order detail e.g. Hammer 1$ Nail 5000$ Screw 1200$ How many disk reads (or head movements)? Actually, probably the same as you here. If we're indexed on order detail. If Hammer appears in N invoices, then T = (1+N) * ST * 1.05 for hammers, and the same for all the other products. Theory favours us, in that if a product appears X times in one invoice, that's one read for us and X for you, but hardware will probably help you more than us (that is, assuming thrashing cuts in) in that you stand a marginally higher chance of getting multiple instances of a product in any given read. So for each product you get T = (1+N) * ST * 1.05. Now, for our SQL-DBMS, presuming that we build indexes for detail and product: order_detail(product_id, qty, unit_price) = 20 bytes/row product(product_id, product_name) = 50 bytes/row With 2 disk reads I would get 8K/20 = 400 order detail rows and 8K/50 = 160 product rows Since all rows are in product_id order, no need for random disk reads so T = 1 + N/400 + P/160 (N=number of details, P=number of products) for ALL products and details. And, because of sequential prefetch, we probably would not have to wait for I/O's at all. Really, however you calculate it, it is an order of magnitude less than your alternative. And please don't tell me that using indexes is not fair or not in the spirit of the relational model ;-) And: what if I was just reading customer-data. Would the same formula apply (= (2+N)*ST*1.05)? Nope. If I understand you correctly, you want attributes that belong to the entity customer, not the entity invoice. T = ST * 1.05. (By the way, billing and/or invoice address (for example) are invoice attributes, not company attributes.) No, I want you to give me a list of all your customers. How many disk reads? T = N * 1.05 where N is the number of customers. What do you want to know about those customers? Address? Phone number*s*? Anything else? That's *all* at no extra cost. Well, no thanks. I just
Re: [HACKERS] Dreaming About Redesigning SQL
Marshall Spight [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Bob Badour [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] All physical structures will bias performance for some operations and against others. This strikes me as a succinct statement of the value of data independence. One has the option (but not the requirement) to adjust the physical structures the DBMS uses while keeping the logical model (and therefor all application code and queries, etc.) unchanged. Unless one has data independence, one does not have this option; one will be locked into a particular performance model. This is why I found the MV guy's obvious pleasure at being able to precisely describe the performance model for his DB as odd: I thought it a deficit to be able to say what it was; he thought it an asset. It becomes an obvious deficit as soon as he needs to improve upon the performance for some operation and he has no way to do it. Thus, he lacks the option to gain the factor of eight improvement for the first query offered by clustering. Marshall PS. This is nothing you don't know, Bob; just a comment for the group. Of course. Likewise. ---(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] Dreaming About Redesigning SQL
In article [EMAIL PROTECTED], Lauri Pietarinen [EMAIL PROTECTED] writes Anthony W. Youngman wrote: Fine. But MV *doesn't* *need* much of a cache. Let's assume both SQL and MV have the same amount of RAM to cache in - i.e. *not* *much*. I did say the spec said extract maximum performance from the hardware available. So what's wrong with gettng a machine with lots of memory? How much does 2G of memory for an Intel-box cost now a days? Is this some kind of new ultimate sport, trying to get along with as little memory as possible? I presume you didn't read the bit below ... what if you have SEVERAL tables, and EACH of them is a gigabyte or two in size? If an engineer has a problem, throwing brute force at it is rarely the solution. Let's be topical (near enough) and look at the Titanic (seeing as there was this film recently). If they'd forseen the problem, they could have thrown brute force at it and doubled the thickness of the steel plate. Except she would have then sunk when they launched her, before she even had a chance to hit the iceberg. Or look at aviation - especially in the early years. They had gliders that could fly, and they had engines that could easily provide the power to get a glider airborne. The problem was, every time they increased the power of the engine they got *further* *away* from the possibility of powered flight, because the increased power came at the price of increased weight. You're welcome to live in your mathematical world where power can be gained for no cost, but that doesn't work in the real world. And the cost isn't necessarily dollars. Like in the aircraft example, the cost could be a case of sorry, technology ain't that advanced yet mate! You're assuming that you can throw hardware at the problem - fine, but that's not always possible. You might have already maxed out the ram, you might have a huge database, you might be sharing your db server with other programs (BIND really likes to chew up every available drop of ram, doesn't it :-). I'm not saying that you shouldn't throw hardware at it, but what if you can't? Except my example was an *average* case, and yours is a *best* case. Oh, and my data is still normalised - I haven't had to denormalise it! AND I haven't run an optimiser over it :-) Are you hiding your optimiser behind the curtain? ;-) Well, if you include getting optimisation for free because that's the way things work, maybe I am ;-) Well, if it is normalised, how easy is it for you to change the customer_id of an order? Anyway, Incredibly easy. Just update the customer_id field of the invoice record. A single change to a single row if we stick to your example and even if we don't normalise using e.g. clustering features of Oracle, as Bob pointed out, we are getting at most the same number of I/O's. So, answer to your question: our formula is at least as good as yours. Except I think Bob said we could optimise to favour *certain* transactions. I think actually ANY transaction benefits. You're relying on stuff that's outwith your theory, we're relying on stuff that's inherent to our model. Now, that was a *conservative* estimate, and we assumed that we did not have any rows lying around in the (global!) cache. As the size of the cache grows in proportion to the size of the total database we can assume less and less disk I/O. You're relying on the hardware to bale you out :-) We can do the same! Well why don't you? We let the hardware help us out if it can. There's a big difference. If you can't get the hardware, you're stuffed. We don't need it, so while we may have a hard time of it it's nowhere near as bad for us. And again, relational separates the physical from the logical. You're being hypocritical if you call upon the physical representation to help out with the (speed of the) logical presentation. Note also that the cache can be configured many ways, you can put different tables (or indexes) in different caches, and even change the size of the cache on the fly (you might want a bigger cache during evening and night when your batch programs are running) so you can rig your system to favour certain types of queries. I havn't even gone into the topic of using thick indexes so table access can be totally avoided (=we are reading into memory only interesting columns). Now, in your example, what if the product department comes along and wants to make a report with sales / product? What would be your formula in that case? I'm not quite sure what you're trying to do. I'll assume you want a report of all invoices which refer to a given product. Assuming I've got the relevant indices defined, I can simply read a list of invoices from the product code index, a second list of invoices from the month index, and do an intersect of the two lists. I want a list with all products with corresponding total sales, read from order detail e.g. Hammer 1$ Nail 5000$ Screw 1200$ How many disk reads
[HACKERS] Failed to create temporary file
Hi! I'm doing a select (from an OACS page or from psql) and I get: ERROR: Failed to create temporary file pgsql_tmp/pgsql_tmp27212.775 The same select work ok a different database (which is on a different machine) the select is : select a.attribute_id, a.pretty_name, a.ancestor_type, t.pretty_name as ancestor_pretty_name from acs_object_type_attributes a, (select t2.object_type, t2.pretty_name, tree_level(t2.tree_sortkey) - tree_level(t1.tree_sortkey) + 1 as type_level from acs_object_types t1, acs_object_types t2 where t1.object_type = 'group' and t2.tree_sortkey between t1.tree_sortkey and tree_right(t1.tree_sortkey)) t where a.object_type = 'group' and t.object_type = a.ancestor_type order by type_level ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Failed to create temporary file
Sounds like your drives are full. On Thu, 23 Oct 2003, Yuval Lieberman wrote: Hi! I'm doing a select (from an OACS page or from psql) and I get: ERROR: Failed to create temporary file pgsql_tmp/pgsql_tmp27212.775 The same select work ok a different database (which is on a different machine) the select is : select a.attribute_id, a.pretty_name, a.ancestor_type, t.pretty_name as ancestor_pretty_name from acs_object_type_attributes a, (select t2.object_type, t2.pretty_name, tree_level(t2.tree_sortkey) - tree_level(t1.tree_sortkey) + 1 as type_level from acs_object_types t1, acs_object_types t2 where t1.object_type = 'group' and t2.tree_sortkey between t1.tree_sortkey and tree_right(t1.tree_sortkey)) t where a.object_type = 'group' and t.object_type = a.ancestor_type order by type_level ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dreaming About Redesigning SQL
Marshall Spight kirjutas N, 23.10.2003 kell 11:01: Anthony W. Youngman [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Just like the academics were brainwashed into thinking that microkernels were the be-all and end-all - until Linus showed them by practical example that they were all idiots ... Linus set out to build a unix kernel workalike, and he chose the easiest path, copying architecture from the 1970s, along with all the weaknesses that those idiot academics had identified years earlier. Since then, his monolithic kernel has gotten a lot of marketshare, due to a number of different reasons, none of them being architectural superiority. Unless you count as architectural superiority the fact that it can be actually written and debugged in a reasonable time. Being able to mathematically define something as not having certain weaknesses does not quarantee that the thing can be actually implemented and/or is usable. -- Hannu ---(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
[HACKERS] duration time on beta5
I'm seeing on the log file: LOG: duration: 0.024814 select now(); Am I wrong or we agree to have: LOG: duration: 24.81 ms select now(); Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] duration time on beta5
Gaetano Mendola wrote: I'm seeing on the log file: LOG: duration: 0.024814 select now(); Am I wrong or we agree to have: LOG: duration: 24.81 ms select now(); I think you are pointing to the wrong version --- I see: LOG: duration: 0.998 ms statement: select 1; -- 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] duration time on beta5
Bruce Momjian wrote: I think you are pointing to the wrong version --- I see: LOG: duration: 0.998 ms statement: select 1; I'm a dummy, I forgot to do gmake install after have compiled the last beta :-( yes you're right. Regards Gaetano Mendola ---(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
[HACKERS] how to optimize for ia64
Hello This is my first time to post. I have several questions. Currently, there is Postgresql for ia64. I was wondering, previous version was optimized for ia64 or just converted. Now I am looking for some way to optimize postgresql especially for ia64 machine. (thinking about modifying cache part of postgresql) Do you have any idea about this? And what kind of methods are possible to improve performance for ia64? How can I get the specific information about this? Thanks.
Re: [HACKERS] 2-phase commit
Satoshi, can you get this ready for inclusion in 7.5? We need a formal proposal of how it will work from the user's perspective (new commands?), and how it will internally work. It seem Heikki Linnakangas has also started working on this and perhaps he can help. Ideally, we should have this proposal when we start 7.5 development in a few weeks. I know some people have concerns about 2-phase commit, from a performance perspective and from a network failure perspective, but I think there are enough people who want it that we should see how this can be implemented with the proper safeguards. --- Satoshi Nagayasu wrote: Andrew Sullivan [EMAIL PROTECTED] wrote: On Fri, Oct 10, 2003 at 09:46:35AM +0900, Tatsuo Ishii wrote: Satoshi, the only guy who made a trial implementation of 2PC for PostgreSQL, has already showed that 2PC is not that slow. If someone has a fast implementation, so much the better. I'm not opposed to fast implementations! The pgbench results of my experimental 2PC implementation and plain postgresql are available. PostgreSQL 7.3 http://snaga.org/pgsql/pgbench/pgbench-REL7_3.log Experimental 2PC in PostgreSQL 7.3 http://snaga.org/pgsql/pgbench/pgbench-TPC0_0_2.log I can't see a grave overhead from this comparison. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- 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] 2-phase commit
Bruce, Ok, I will write my proposal. BTW, my 2PC work is now suspended because of my master thesis. My master thesis will (must) be finished in next few months. To finish 2PC work, I feel 2 or 3 months are needed after that. Bruce Momjian wrote: Satoshi, can you get this ready for inclusion in 7.5? We need a formal proposal of how it will work from the user's perspective (new commands?), and how it will internally work. It seem Heikki Linnakangas has also started working on this and perhaps he can help. Ideally, we should have this proposal when we start 7.5 development in a few weeks. I know some people have concerns about 2-phase commit, from a performance perspective and from a network failure perspective, but I think there are enough people who want it that we should see how this can be implemented with the proper safeguards. --- Satoshi Nagayasu wrote: Andrew Sullivan [EMAIL PROTECTED] wrote: On Fri, Oct 10, 2003 at 09:46:35AM +0900, Tatsuo Ishii wrote: Satoshi, the only guy who made a trial implementation of 2PC for PostgreSQL, has already showed that 2PC is not that slow. If someone has a fast implementation, so much the better. I'm not opposed to fast implementations! The pgbench results of my experimental 2PC implementation and plain postgresql are available. PostgreSQL 7.3 http://snaga.org/pgsql/pgbench/pgbench-REL7_3.log Experimental 2PC in PostgreSQL 7.3 http://snaga.org/pgsql/pgbench/pgbench-TPC0_0_2.log I can't see a grave overhead from this comparison. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend -- NAGAYASU Satoshi [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- NAGAYASU Satoshi [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: [HACKERS] 7.4 compatibility question
Bruce Momjian [EMAIL PROTECTED] writes: Michael Brusser wrote: Optimizer improvements = this tells me nothing. I suppose this could be a minor internal code tweak, which does not affect me. On the other hand this could be a major breakthrough, so now I can run some stupid query which would take a week to complete in the previous release. How do I know? Yes, this is always very hard to explain. The optimizer itself is complex, and uses complex terms like merge join and key pruning. It is hard to explain what queries will be affected, though the basic issue is that the optimizer will choose a better plan more frequently. One thing that might be worth mentioning is that WHERE foo IN (subquery) type queries are much improved. That's a one of the more common complaints about 7.3 and previous and it's one that fairly easy to recognize. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Broken Constraint Checking in Functions
I am not sure we should add something to the SET CONSTRAINT page on this. Our current behavior is clearly a bug, and for that reason belongs more on the TODO list, where it already is: * Have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function The big question is whether this entry is clear enough for people to understand it could bite them. --- Curt Sampson wrote: So it seems I got bitten today by this to-do list item: Have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function Under normal circumstances, delaying this stuff until the end of the function doesn't bother me; in fact I've even used it to get around the fact that SET CONSTRAINTS won't let you delay non-referential constraint checks. However, it seems that cascading deletes are also delayed, which leads to a pretty serious problem. The following code: INSERT INTO master (master_id) VALUES (400); INSERT INTO dependent (master_id) VALUES (400); DELETE FROM master WHERE master_id = 400; works just fine outside a function, but inside a function it fails with ERROR: $1 referential integrity violation - key referenced from dependent not found in master It seems that the integrity check for the dependent is happening before the cascaded delete, but the check is operating not on the data at the time of the statement, but the data as it stands after the statement following the one that triggered the check. Ouch! Having spent the better part of a day tracking down this problem (because of course, as always, it only decides to appear in one's own code after it's gotten quite complex), I think for a start it would be a really, really good idea to put something about this in the documentation for the 7.4 release. Probably the SET CONSTRAINTS page would be a good place to have it, or at least a pointer to it. In the long run, of course, I'd like to see a fix, but preferably after we fix the system to allow delay of non-referential constraints as well, since I am use this bug now in production code to delay constraint checking for non-referential constraints. (You might even document that workaround in the SET CONSTRAINTS manual page, with an appropriate warning, if one seems necessary.) I've attached a short shell script that will demonstrate the problem. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- 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] 2-phase commit
Satoshi Nagayasu wrote: Bruce, Ok, I will write my proposal. BTW, my 2PC work is now suspended because of my master thesis. My master thesis will (must) be finished in next few months. To finish 2PC work, I feel 2 or 3 months are needed after that. Oh, OK, that is helpful. Perhaps Heikki Linnakangas could help too. -- 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] 7.4 compatibility question
Greg Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: Michael Brusser wrote: Optimizer improvements = this tells me nothing. I suppose this could be a minor internal code tweak, which does not affect me. On the other hand this could be a major breakthrough, so now I can run some stupid query which would take a week to complete in the previous release. How do I know? Yes, this is always very hard to explain. The optimizer itself is complex, and uses complex terms like merge join and key pruning. It is hard to explain what queries will be affected, though the basic issue is that the optimizer will choose a better plan more frequently. One thing that might be worth mentioning is that WHERE foo IN (subquery) type queries are much improved. That's a one of the more common complaints about 7.3 and previous and it's one that fairly easy to recognize. That is right at the top of the release notes: Performance IN/NOT IN subqueries are now much more efficient -- 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] Foreign Key bug -- 7.4b4
I can confirm this bug in CVS. --- Rod Taylor wrote: -- Start of PGP signed section. May have posted this earlier... It would seem that caching the plans for foreign keys has some unwanted side effects. test=# select version(); version PostgreSQL 7.4beta4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4 (1 row) test=# test=# create table a (col integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a CREATE TABLE test=# test=# create table b (col integer primary key references a on update cascade on delete cascade); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b CREATE TABLE test=# test=# test=# insert into a values (1); INSERT 687978 1 test=# insert into b values (1); INSERT 687979 1 test=# test=# insert into a values (2); INSERT 687980 1 test=# insert into b values (2); INSERT 687981 1 test=# test=# delete from a where col = 1; DELETE 1 test=# test=# alter table b drop constraint b_pkey; ALTER TABLE test=# test=# delete from a where col = 2; ERROR: could not open relation with OID 687972 -- End of PGP section, PGP failed! -- 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] Broken Constraint Checking in Functions
On Fri, 24 Oct 2003, Bruce Momjian wrote: I am not sure we should add something to the SET CONSTRAINT page on this. Our current behavior is clearly a bug, and for that reason belongs more on the TODO list, where it already is: Had it been on the SET CONSTRAINT page, it would have saved me several hours of debugging. I found the entry only after tracking down the problem and creating a simple test case to demonstrate it. We document other bugs on this page, e.g.: Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively initially immediate not deferrable. So why not document this one as well? * Have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function The big question is whether this entry is clear enough for people to understand it could bite them. My big question is, should we expect that anybody reading the documentation also has to go through the TODO list to see if there are bugs on the list not mentioned in the manual? cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] rte in set_plan_rel_pathlist()
hi:in allpath.c ,the
set_plan_rel_pathlist() function has a parameter of RangeTblEntry type.what
does we use this varaible to do ?i doesn't see any code in this function
using it .In optimizer module, it look like that we can get
mostinformation from Query . Is it right ?so , what time use Query?
what time use RangeTblEntry? Are there any rule?Kao
136 /*137 *
set_plain_rel_pathlist138 *Build access
paths for a plain relation (no subquery, no inheritance)139 */140
static void141 set_plain_rel_pathlist(Query *root, RelOptInfo *rel,
RangeTblEntry *rte)142 {143 /* Mark rel with
estimated output rows, width, etc */144
set_baserel_size_estimates(root, rel);145 146
/*147 * Generate paths and add them
to the rel's pathlist.148 *149
* Note: add_path() will discard any paths that are
dominated by another150 * available path,
keeping only those paths that are superior along at151
* least one dimension of cost or sortedness.152
*/153 154 /* Consider
sequential scan */155 add_path(rel,
create_seqscan_path(root, rel));156 157 /*
Consider TID scans */158
create_tidscan_paths(root, rel);159 160 /*
Consider index paths for both simple and OR index clauses */161
create_index_paths(root, rel);162 163
/* create_index_paths must be done before create_or_index_paths
*/164 create_or_index_paths(root, rel);165
166 /* Now find the cheapest of the paths for
this rel */167 set_cheapest(rel);168
}169 --
Re: [HACKERS] Broken Constraint Checking in Functions
Curt Sampson wrote: On Fri, 24 Oct 2003, Bruce Momjian wrote: I am not sure we should add something to the SET CONSTRAINT page on this. Our current behavior is clearly a bug, and for that reason belongs more on the TODO list, where it already is: Had it been on the SET CONSTRAINT page, it would have saved me several hours of debugging. I found the entry only after tracking down the problem and creating a simple test case to demonstrate it. We document other bugs on this page, e.g.: Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively initially immediate not deferrable. So why not document this one as well? Does it belong on the SET CONSTRAINT page, the CREATE TABLE page, or the CREATE FUNCTION page? * Have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function The big question is whether this entry is clear enough for people to understand it could bite them. My big question is, should we expect that anybody reading the documentation also has to go through the TODO list to see if there are bugs on the list not mentioned in the manual? This is more of a this is a clear bug rather than an judgement call or something. I can add it, but we do expect most people to read the TODO list because it shows our shortcomings. Can I get anyone else to recommend adding it to the CREATE CONSTRAINT manual page? -- 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 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
