Re: [HACKERS] Libpq.dll Souce Code
* Achmad Amin [EMAIL PROTECTED] [2002-09-05 22:54 -0700]: Dear all, I want to make library for visual basic to connect to PostgreSQL, but I have problem to get libpq.dll source code. Can somebody help me ? Download a PostgreSQL source distribution. The libpq sources are in src/interfaces/libpq. The PostgreSQL documentation explains how to compile it on Windows using Vi$ual C++. If you don't have it, you can find Makefiles for building libpq with gcc (either mingw or Cygwin flavour) at my homepage: http://www.cs.fhm.edu/~ifw00065/ In the future, please ask support questions on pgsql-general, not here. The correct list for discussion of libpq is pgsql-interfaces. -- Gerhard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] abou the cost estimation
Hello, All I have read the source code /cvsroot/pgsql/src/backend/optimizer/path/costsize.c and there is a function cost_sort(...). I think the code in 464 to 465 lines must be changed to: startup_cost += npageaccesses * (1.0 + cost_nonsequential_access(1)) * 0.5; The original code is: startup_cost += npageaccesses * (1.0 + cost_nonsequential_access(npages)) * 0.5; Can any one discuss about this issue with me ? Thanks for your response very much! -- Guo long jiang. 2002-9-6 __ === ÐÂÀËÃâ·Ñµç×ÓÓÊÏä (http://mail.sina.com.cn) ÐÂÀË·ÖÀàÐÅÏ¢£º¶þÊÖÊг¡×ßÒ»×ߣ¬¸Ã³öÊÖʱ¾Í³öÊÖ£¡ (http://classad.sina.com.cn/2shou/) ÊýÍòÕÅÊÖ»úͼƬÊýÍòÊ׶ÌÐÅÁåÉùÈÎÄãÌôÑ¡£¬Ã¿Ì춼ÓиüР(http://sms.sina.com.cn/cgi-bin/sms/smspic.cgi) ---(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] [PATCHES] Big number of unused pages as reported by VACUUM
Hi Yury, This question should not be posted to -patches, changed accordingly. What happens if you go 'VACUUM VERBOSE FULL goods;'? Your on-disk files won't shrink or have unused tuples removed unless you VACUUM FULL. The problem with doing VACUUM FULL is that it totally locks the whole table while it's running, meaning no-one can use the table. This is bad in production environments, so it's not the default. Bear in mind that postgres will re-use the unused portion of the table as you add new tuples... Chris Some time ago I've got troubles with performance of my PG. After investigation I had found that the most probable reason was the big number of unused pages. Below follows what VACUUM reported: === vacuum verbose goods; NOTICE: --Relation goods-- NOTICE: Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep 0, UnUsed 465938. === select count(*) from goods; count --- 16157 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] Big number of unused pages as reported by
Hello! On Fri, 6 Sep 2002, Christopher Kings-Lynne wrote: This question should not be posted to -patches, changed accordingly. What happens if you go 'VACUUM VERBOSE FULL goods;'? Oh, big thanx! But 'VACUUM VERBOSE FULL goods;' didn't work, only 'VACUUM FULL VERBOSE goods;' did.:) I make a guess I've got this due to parallel running of a program making bulk INSERTs/UPDATEs into that table. Mmm...I need a way to avoid the big number of unused pages in such a case. LOCK TABLE? Your on-disk files won't shrink or have unused tuples removed unless you VACUUM FULL. The problem with doing VACUUM FULL is that it totally locks the whole table while it's running, meaning no-one can use the table. This This can't scare people whom had dealt with 6.x.;) Only if We scare because we care...=) is bad in production environments, so it's not the default. Bear in mind that postgres will re-use the unused portion of the table as you add new tuples... Yes, as an ole MUMPSter I did catch this very well some times ago.=) Chris Some time ago I've got troubles with performance of my PG. After investigation I had found that the most probable reason was the big number of unused pages. Below follows what VACUUM reported: === vacuum verbose goods; NOTICE: --Relation goods-- NOTICE: Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep 0, UnUsed 465938. === select count(*) from goods; count --- 16157 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html Yep! Suggest to add this as well as that typical mistake with LANGUAGE/HANDLER (plpgsql.so I mean).:-) -- WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group. Phone: +7 (3832) 106228, ext.140, E-mail: [EMAIL PROTECTED] Unix is like a wigwam -- no Gates, no Windows, and an Apache inside. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Inheritance
On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: On 5 Sep 2002, Hannu Krosing wrote: Suppose you have a table CITIZEN with table-level constraint IS_GOOD which is defined as kills_not_others(CITIZEN). and there is table CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD This I very much disagree with. In most object-oriented languages (Eiffel being a notable exception, IIRC), you can't specify constraints on objects. But in a relational database, you can specify constraints on tables, and it should *never* *ever* be possible to violate those constraints, or the constraints are pointless. That's not how real world (which data is supposed to model) operates ;) As Greg already pointed out, there are two kinds of constraints - database integrity constraints (foreign key, unique, not null, check), which should never be overridden and business-rule constraints which should be overridable in child tables. one can argue that the latter are not constraints at all, but they sure look like constraints to me ;) To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. So if I have a constraint that says, no rows appearing in this table will ever violate constraint X, and then you go and create a way of inserting rows into that table that violate that constraint, I think you've just made the database into a non-relational database. SQL standard constraints should be non-overridable. I still think that Constraint triggers should be overridable/dynamic. Or maybe it is better to just make the check function should be dynamically dispatched, so the constraint will always hold, it just can mean different things for different types. I really don't want to break postgres' relational side for some inheritance features of dubious utility. Constraints should be explicitly removed from tables if they are no longer needed, not implicitly removed through the creation of another table. I think we should settle this point before going any further. It seems that the dynamic dispatch of trigger function should be enough for business-rule constraints. And it is also simpler and cleaner (both conceptually and to implement) if constraints themselves are not overridable. So in my CAMPAIGN_GOODS example you just have different valid_prices(GOODS) and valid_prices(CAMPAIGN_GOODS), but one constraint on GOODS which states that price must be valid . Doing it this way ensures that you are not able to have a record in GOODS for which valid_price(ROW) does not hold. If you don't want inherited tables to be able to override valid_price() use it in CHECK constraint in GOODS, which should use the valid_prices(cast(ROW as GOODS)) for any inherited type. - Hannu ---(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] [PATCHES] Big number of unused pages as reported by VACUUM
I make a guess I've got this due to parallel running of a program making
bulk INSERTs/UPDATEs into that table. Mmm...I need a way to avoid the big
number of unused pages in such a case. LOCK TABLE?
Well, I suggest doing a normal vacuum analyze ('VACUUM ANALYZE goods') after
every bulk insert/update. This will go through the table and mark all new
outdated tuples as re-usable. That way, when you do your next bulk
insert/update it will be able to reuse the unused tuples. Give that a
try...
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [PATCHES] Big number of unused pages as reported by
I make a guess I've got this due to parallel running of a program making bulk INSERTs/UPDATEs into that table. Mmm...I need a way to avoid the big number of unused pages in such a case. LOCK TABLE? Only UPDATEs and DELETEs (and rolled back INSERTs) cause unused pages. The trick for other people was to run very frequent 'VACUUM goods;' (like every 15 seconds) on tables when relatively few rows (in small tables) where constantly beeing updated (e.g. counters/balances). It might be sufficient in your case though to do the 'VACUUM goods;' after every bulk UPDATE, like Christopher suggested. A concurrent vacuum won't help if each bulk update is done in one single transaction. Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Inheritance
On 6 Sep 2002, Hannu Krosing wrote: In most object-oriented languages (Eiffel being a notable exception, IIRC), you can't specify constraints on objects. But in a relational database, you can specify constraints on tables, and it should *never* *ever* be possible to violate those constraints, or the constraints are pointless. That's not how real world (which data is supposed to model) operates ;) Sure it is. Please don't blame the language for being wrong when you incorrectly model things for your purposes. To chose a much simpler and more obvious example: if you stored birthdate as a date only, and someone complained that you're not born all day, but at a particular time on that day, you don't blame the language for having the date type not store the time of day. You fix your problem to use both a date and a time to store that value. If the language specifies that contstraints on tables are not to be violated, then don't use those constraints when you don't want them. To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. This looks like a classic case of incorrect modelling to me. Does the good itself change when it becomes a campaign_good? No. The price changes, but that's obviously not an integral part of the good itself. So separate your price information from your good information, and then you can do things like have campaign prices, multiple prices per good (since you probably want to keep the original price information as well), and so on. I'm really getting the feeling a lot of these applications that want table inheritance want it just to be different, not because it provides anything useful. I am completely committed to object-oriented programming, and use inheritance heavily, so it's not that I don't understand or like the concepts. But just because a concept works well in one type of use does not mean it will do any good, or even not do harm, when brought into a completely different world. SQL standard constraints should be non-overridable. I still think that Constraint triggers should be overridable/dynamic. I still don't like it. Eiffel had good reasons for making the constraints non-overridable. Other OO languages don't have constraints, or they would probably do the same. That said, I could live with dynamic dispatch, if the default were to make it non-dynamic, and you had to add a special flag to make it dynamic. That way it would be obvious to the casual user or a DBA familiar with other databases but not postgres that something unusual is going on. 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] contrib/tsearch
On Fri, 6 Sep 2002, Christopher Kings-Lynne wrote: There also seems to be a more complete list of english stopwords here: http://www.dcs.gla.ac.uk/idom/ir_resources/linguistic_utils/ Chris, I think we have to separate stop word list from tsearch package and supply just some defaults. The reason for this is to let user decide what is a stop word - various domains should have different stop words. This is how OpenFTS works. Also, we probably need to let user decide when to check for stop word - after or before stemming. I'm waiting for Martin's fix for english stemmerr and probably we'll switch to use snowball one, which are more qualified. Damn, we wanted to do these and much more a bit later because we're under big pressure of our work. We'll see if we could manage our plans. We certainly need developers to help us in full text searching, ltree ( it has a chance to support XML ). Also we need to work on adding concurrency support to GiST. so, I couldn't promise we'll work on tsearch right now, but we provide makedict.pl so you could build dictionary with custom list of stop words. Did you try it ? However this list again does not include contractions. I can take this list, check it and submit it to you Oleg, but do you want me to add contractions? eg. wasn't, isn't, it's, etc.? Hmm, our parser isn't smart to handle them as a single word, so it'll not helps: 13:30:03[megera@amon]~/app/fts/test-suite./testdict.pl -p wasn't lexeme:wasn:1:Latin word lexeme:':12:Space symbols lexeme:t:1:Latin word But, you always could add 'wasn', 'isn' ... and 't','s' to list of your stop words and be happy. Hmm, probably we could enhance our parser to handle such words too. Anyway, most problems just a question of time we don't have :-( Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher Kings-Lynne Sent: Friday, 6 September 2002 12:20 PM To: Christopher Kings-Lynne; Oleg Bartunov Cc: Hackers; [EMAIL PROTECTED] Subject: Re: [HACKERS] contrib/tsearch Looking at the list of stopwords you sent me, Oleg, there are only about 1 out of the list of 120 stopwords that need to have all word forms added. I also don't think it'll be a maintenance problem. The reason I think this is because stopwords in general don't have different word forms. Actually, it just occurred to me that stuff like: will won't it it's where where's Will all have to be in the list, right? 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 Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib/tsearch
On Fri, 6 Sep 2002, Christopher Kings-Lynne wrote: Looking at the list of stopwords you sent me, Oleg, there are only about 1 out of the list of 120 stopwords that need to have all word forms added. I also don't think it'll be a maintenance problem. The reason I think this is because stopwords in general don't have different word forms. Actually, it just occurred to me that stuff like: will won't it it's where where's Will all have to be in the list, right? right, see my previous message. Teodor is our main developer, he should be back from vacation very soon. But he already has many assignments regarding our main project. Are there one smart programmer ? Chris Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] contrib/tsearch
On Fri, 6 Sep 2002, Christopher Kings-Lynne wrote: Should we check for stop words before stemming or after ? I think you should. In the first case we have to collect all forms of stop-words which is doable but difficult to maintain, in latter - we'll have current problem. Looking at the list of stopwords you sent me, Oleg, there are only about 1 out of the list of 120 stopwords that need to have all word forms added. I also don't think it'll be a maintenance problem. The reason I think this is because stopwords in general don't have different word forms. eg. her, his, i, and, etc. They don't have different forms. In fact, the _only_ word in the stopword list that needs a different form is yourself and yourselves. Actually, according to dictionary.com 'ourself' is also a word. 'themself' isn't tho. Some others I don't know about are: 'veri' - I assume this is stemmed 'very', so why not just use 'very'? That's because we currently check for stop word after stemming and I think porters algorithm converts 'very' to 'veri' :-) So, why don't you change tsearch to check for stop words _before_ stemming? I can give you a list of revised stopwords that haven't been stemmed, with all forms of the words. I agree that english list is, probably, easy to maintain, but what about other languages ? We don't have any volunteers - you're the first one. It's time for beta1 and I'm not sure if we could work on this issue right now, but I feel a big pressure from tsearch users :-) If people want to help us why not to work on stop words list including all forms ? In any case, we are not native english, so don't expect we'll create more or less decent list. Programming changes are trivial, probably we'll end for the moment just using compile time option. As always, your patches are welcome ! I'm happy to work on the list of stopwords for you, Oleg. I agree this might be 7.4 thing though... We always could keep updates separately on our page and in CVS. Chris Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Inheritance
On Fri, 2002-09-06 at 09:53, Curt Sampson wrote: If the language specifies that contstraints on tables are not to be violated, then don't use those constraints when you don't want them. But what _should_ i use then if i want the same business rule on most top-level types, but a changed one on some down the hierarchy ? To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. This looks like a classic case of incorrect modelling to me. Does the good itself change when it becomes a campaign_good? No. The price changes, but that's obviously not an integral part of the good itself. Perhaps we mean different things by good. I meant a GOOD to be a THING bought with the purpose of reselling. Price (actually prices: selling_price and buying_price) is what makes it a GOOD and thus it is an integral part of it. So separate your price information from your good information, and then you can do things like have campaign prices, multiple prices per good (since you probably want to keep the original price information as well), and so on. It does not solve the problem described above - the price at which the good is soled is still constrained differently for orninary and campaign goods. in standard relational model you would make the distinction inside the constraint (CHECK (selling_price buying_price) OR is_campaign_good) but this localises the check in wrong place - in OO model I'd expect it to be possible to define the constraint near the child type, not change the parent constraint each time I derive new child types. I'm really getting the feeling a lot of these applications that want table inheritance want it just to be different, not because it provides anything useful. As with any other inheritance, it is just a way to organize stuff. In case of being able to override constraints for child tables it can also be a significant performance boost - if you have 10 000 000 goods in a table you don't want to change a constraint on GOODS to allow campaign goods to be sold cheaper than bought as it would have to check all goods for validity according to new constraint - putting the constraint on just CAMPAIGN_GOODS will enable the DB engine to check just tuples in CAMPAIGN_GOODS. I am completely committed to object-oriented programming, and use inheritance heavily, so it's not that I don't understand or like the concepts. But just because a concept works well in one type of use does not mean it will do any good, or even not do harm, when brought into a completely different world. Surely great caution is needed when defining the desired behaviour. SQL standard constraints should be non-overridable. I still think that Constraint triggers should be overridable/dynamic. I still don't like it. Eiffel had good reasons for making the constraints non-overridable. Other OO languages don't have constraints, or they would probably do the same. That said, I could live with dynamic dispatch, if the default were to make it non-dynamic, and you had to add a special flag to make it dynamic. That way it would be obvious to the casual user or a DBA familiar with other databases but not postgres that something unusual is going on. That seems about the right compromise between constraining and developer freedom. - Hannu ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3 Beta 1 Build Error on Cygwin
Seems to build cleanly here now. Perhaps anoncvs just hadn't sync'd up when you tried Jason? Regards, Dave. -Original Message- From: Jason Tishler [mailto:[EMAIL PROTECTED]] Sent: 05 September 2002 20:38 To: Peter Eisentraut Cc: Bruce Momjian; Dave Page; pgsql-hackers; pgsql-cygwin Subject: Re: [HACKERS] 7.3 Beta 1 Build Error on Cygwin Peter, On Thu, Sep 05, 2002 at 02:51:31PM -0400, Bruce Momjian wrote: Jason Tishler wrote: On Thu, Sep 05, 2002 at 08:33:20PM +0200, Peter Eisentraut wrote: Should all be fixed now. Huh? I don't see any recent CVS commits to indicate this. I see as a commit: [snip] I assume it was in there. Sorry for the noise, but at the time: cvs status include/miscadmin.h makefiles/Makefile.win did *not* indicate any recent commits. Maybe you sent the above email before you committed your changes? Anyway, I just tried a: make distclean rm include/miscadmin.h makefiles/Makefile.win # remove my patch cvs update make and got the following error: [snip] make[3]: Leaving directory `/home/jt/src/pgsql/src/backend/utils' dlltool --dllname postgres.exe --output-exp postgres.exp --def postgres.def gcc -L/usr/local/lib -o postgres.exe -Wl,--base-file,postgres.base postgres.exp access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o libpq/SUBSYS.o(.text+0x1c84):crypt.c: undefined reference to `crypt' port/SUBSYS.o(.text+0x262):pg_sema.c: undefined reference to `semget' [snip] I can get postgres.exe to successfully link by manually appending -lcrypt -lcygipc to the end of the above gcc command line. Since you are already working on this, would you be willing to fix this problem? Thanks, Jason ---(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] v7.3beta1 Packaged and Released ...
Well, I swear, this is the first release we've actually kept on scheduale with, as far as going into beta is concerned ... We've just packaged up and released v7.3beta1 for broader testing ... and this is a big one as far as changes are concerned. Major changes in this release: Schemas Schemas allow users to create objects in their own namespace so two people or applications can have tables with the same name. There is also a public schema for shared tables. Table/index creation can be restricted by removing permissions on the public schema. Drop Column PostgreSQL now supports ALTER TABLE ... DROP COLUMN functionality. Table Functions Functions returning multiple rows and/or multiple columns are now much easier to use than before. You can call such a table function in the SELECT FROM clause, treating its output like a table. Also, plpgsql functions can now return sets. Prepared Queries For performance, PostgreSQL now supports prepared queries. Dependency Tracking PostgreSQL now records object dependencies, which allows improvements in many areas. Privileges Functions and procedural languages now have privileges, and people running them can take on the privileges of their creators. Multibyte/Locale Both multibyte and locale are now always enabled. Logging A variety of logging options have been enhanced. Interfaces A large number of interfaces have been moved to http://gborg.postgresql.org where they can be developed and released independently. Functions/Identifiers By default, functions can now take up to 32 parameters, and identifiers can be up to 63 bytes long. And these are only the Major Changes ... the minor changes are extensive as well, and are documented in the HISTORY file. This release can be found on the main site, as well as the mirrors in: ftp://ftp.postgresql.org/pub/beta Note that this is a *beta* release ... we have only *just* stop'd development of features, so there are instabilities in the system expected. Anyone, and everyone, is encouraged to download and test this on their various platforms, but do not use it in a production environment as of yet. The more people that can test this release, the faster bugs will get reported and fixed in a much shorter time. Any bugs/problems, please report them to [EMAIL PROTECTED] ... If we are lucky, we can keep this to a reasonably short beta period ... Marc G. Fournier ---(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] abou the cost estimation
ljguo_1234 [EMAIL PROTECTED] writes: I have read the source code /cvsroot/pgsql/src/backend/optimizer/path/costsize.c and there is a function cost_sort(...). I think the code in 464 to 465 lines must be changed to: startup_cost += npageaccesses * (1.0 + cost_nonsequential_access(1)) * 0.5; That would be wrong. Note the definition of cost_nonsequential_access: *Estimate the cost of accessing one page at random from a relation *(or sort temp file) of the given size in pages. 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] Inheritance
On Thu, 2002-09-05 at 15:51, Hannu Krosing wrote: On Fri, 2002-09-06 at 03:19, Greg Copeland wrote: What about the concept of columns being public or private? That is, certain columns may not be inherited by a child? Any thought to such a concept? Perhaps different types of table inheritance can be considered in our model...has-a, is-a, etc... I can't fit this in my mental model of table inheritance for two reasons 1) all parent table columns must be present in child Okay, I must admit, I'm not really sure why. If we look at it in a physical versus logical manner, even if it's physically there, why must it be logically exposed? Can you help me understand why it would even need to physically be there. After all, if a child can't update it, they don't need to see it. 2) granting some right to parent should automatically allow selecting from children Unless the parent deemed it inappropriate access (private)? If a column were deemed private, that would have a couple of stipulations on it. That is, it would have to ensure that NOT NULL where not one of the constraints, or, if it did, ensure that a default value were also provided. both are required for select/insert/update/delete to work on table and its children (i.e. without ONLY) But maybe i just need to think more about it ;) Well, I guess I'm lagging behind you on this manner. Perhaps holding my hand and explaining it a bit will allow you to work through it some more and help bring me in line with what you're thinking. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 7.3 Beta 1 Build Error on Cygwin
Peter, On Fri, Sep 06, 2002 at 12:54:13PM +0100, Dave Page wrote: Seems to build cleanly here now. And here (and now) too. Perhaps anoncvs just hadn't sync'd up when you tried Jason? I guess so -- very strange... Anyway, sorry (again) for the noise and thanks for fixing the Cygwin build. Jason ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Inheritance
On Fri, 2002-09-06 at 07:53, Hannu Krosing wrote: On Fri, 2002-09-06 at 09:53, Curt Sampson wrote: This looks like a classic case of incorrect modelling to me. Does the good itself change when it becomes a campaign_good? No. The price changes, but that's obviously not an integral part of the good itself. Perhaps we mean different things by good. I meant a GOOD to be a THING bought with the purpose of reselling. Price (actually prices: selling_price and buying_price) is what makes it a GOOD and thus it is an integral part of it. No matter now you look at the example, invalidating it does not address the issue raised as it still exists. Either way, Hannu and I seem to agree that some class of constraints need to be able to be overridden. In case of being able to override constraints for child tables it can also be a significant performance boost - if you have 10 000 000 goods in a table you don't want to change a constraint on GOODS to allow campaign goods to be sold cheaper than bought as it would have to check all goods for validity according to new constraint - putting the constraint on just CAMPAIGN_GOODS will enable the DB engine to check just tuples in CAMPAIGN_GOODS. I had not considered this before. Does that still hold true if we go with a parent contains all columns implementation? Of are you simply saying that it doesn't matter as when the constraint were applied it would only scan the rows the below to the child? Perhaps this doesn't matter for this portion of the conversation. But hey, I was curious. :) SQL standard constraints should be non-overridable. I still think that Constraint triggers should be overridable/dynamic. I still don't like it. Eiffel had good reasons for making the constraints non-overridable. Other OO languages don't have constraints, or they would probably do the same. Well Curt, as you outlined above (clipped out) about it being a different world...I think also applies here. IMO, we are treading lightly on new and perhaps thin ground so we need to be careful that we apply common parallels and idioms only we are certain that they need apply. What I'm trying to say is, just because it's not allowed in Eiffel does have to mean the same applies here. That said, I could live with dynamic dispatch, if the default were to make it non-dynamic, and you had to add a special flag to make it dynamic. That way it would be obvious to the casual user or a DBA familiar with other databases but not postgres that something unusual is going on. That seems about the right compromise between constraining and developer freedom. I agree. That does appear to be pointing us in a conservatively sane and safe direction. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Foreign keys in pg_dump
On Fri, 2002-09-06 at 01:19, Christopher Kings-Lynne wrote:
OK,
The argument about using ALTER TABLE/ADD FOREIGN KEY in dumps was that it
caused an actual check of the data in the table, right? This was going to
be much slower than using CREATE CONSTRAINT TRIGGER.
So, why can't we do this in the SQL that pg_dump creates (TODO):
CREATE TABLE ...
ALTER TABLE/ADD FOREIGN KEY ...
update catalogs and disable triggers that the ADD FOREIGN KEY just created
...
COPY .. FROM ...
\.
update catalogs and enable triggers
The problem with this is you may enable a trigger that was disabled by
the user. It cannot be done to all triggers. We could figure out which
triggers were created for the foreign key via pg_depend, then re-enable
only those.
If we did most of this in a single transaction it should be fairly safe.
Doesn't this give us the best of both worlds? ie. Keeps dependencies but
does fast COPYing?
Also, I think a new super-user (or owner) only SQL command would be nice
(TODO):
ALTER TABLE foo {DISABLE|ENABLE} TRIGGER { ALL | trigger_name [ ,... ] };
pg_dump shouldn't need to know that a trigger is involved for foreign
keys. A SET CONSTRAINTS DISABLED would be more appropriate in a binary
mode dump -- but I firmly believe that text mode dumps should run full
checks on the data to ensure the user didn't muck with it.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Inheritance
On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: On 5 Sep 2002, Hannu Krosing wrote: Suppose you have a table CITIZEN with table-level constraint IS_GOOD which is defined as kills_not_others(CITIZEN). and there is table CIVIL_SERVANT (..) UNDER CITIZEN. Now you have just one table MILITARY (...) UNDER CIVIL_SERVANT, where you have other criteria for IS_GOOD This I very much disagree with. In most object-oriented languages (Eiffel being a notable exception, IIRC), you can't specify constraints on objects. But in a relational database, you can specify constraints on tables, and it should *never* *ever* be possible to violate those constraints, or the constraints are pointless. That's not how real world (which data is supposed to model) operates ;) As Greg already pointed out, there are two kinds of constraints - database integrity constraints (foreign key, unique, not null, check), which should never be overridden and business-rule constraints which should be overridable in child tables. one can argue that the latter are not constraints at all, but they sure look like constraints to me ;) To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. What that tells me is that the constraint, valid_prices, shouldn't have been on GOODS in the first place. If it is not a legitimate constraint for the children, then it is not a legitimate constraint for the parent. In human inheritance, if you marry someone with funny coloured skin, you don't get to choose that your children won't have funny coloured skin. That's a pretty forcible constraint. :-). For the GOODS situation, the constraint ought not to be on GOODS in the first place. There ought to be a table ORDINARY_GOODS, or some such thing, to which the constraint applies, and from which CAMPAIGN_GOODS will _not_ be inheriting. So if I have a constraint that says, no rows appearing in this table will ever violate constraint X, and then you go and create a way of inserting rows into that table that violate that constraint, I think you've just made the database into a non-relational database. SQL standard constraints should be non-overridable. I still think that Constraint triggers should be overridable/dynamic. Or maybe it is better to just make the check function should be dynamically dispatched, so the constraint will always hold, it just can mean different things for different types. Or maybe if someone is doing an Object Oriented design, and making extensive use of inheritance, they'll need to apply constraints in a manner that allow them to be properly inherited. -- (concatenate 'string aa454 @freenet.carleton.ca) http://cbbrowne.com/info/ If a cow laughed, would milk come out its nose? ---(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] beta1 packaged
Tom Lane writes: Rod Taylor [EMAIL PROTECTED] writes: SunOS control.shared2 5.7 Generic_106541-20 sun4u sparc SUNW,Ultra-5_10 shows an error in ALTER TABLE tests: ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Relation pg_temp_5. does not exist That's pretty bizarre. Is it reproducible? Can you get in there with a debugger and try to figure out what's going wrong? I saw a similar error on a NetBSD-1.5.1/i386 box, but have not been able to reproduce it. Subsequent runs of 'gmake check' have all passed. Until I saw Rod's message I was thinking it was more evidence of hardware flakiness with this particular machine, but perhaps not. *** ./expected/alter_table.out Sat Aug 31 05:23:20 2002 --- ./results/alter_table.out Fri Sep 6 16:54:35 2002 *** *** 332,337 --- 332,338 -- Try (and succeed) ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 matc h full; NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Relation public.^B^UW88F00} does not exist -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constr aint on -- tmp4 is a,b ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) ma tch full; Regards, Giles ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/Perl?
Well I spent half a day and half a night, pgsql compiles ok for me. However I'm still figuring why I have majodormo probs... So I went back for now. Regards On 5 Sep 2002, Larry Rosenman wrote: Date: 05 Sep 2002 15:16:38 -0500 From: Larry Rosenman [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED], [EMAIL PROTECTED], Olivier PRENANT [EMAIL PROTECTED] Subject: Re: [HACKERS] PL/Perl? On Wed, 2002-09-04 at 19:41, Larry Rosenman wrote: On Wed, 2002-09-04 at 17:54, Tom Lane wrote: Larry Rosenman [EMAIL PROTECTED] writes: I upgraded PostgreSQL to 7.2.1 from a 7.2beta (yeah, I know). One of my users requested plperl, so I got it to createlang, but it SIGSEGV's on any simple perl. I was seeing the same with perl 5.6.1 and PG 7.2.* on HPUX 10.20. However, I have just verified that perl 5.8.0 works okay with PG CVS tip (not much testing, but it handles a simple plperl function). Could you see whether 5.8.0 plays any nicer on your setup? Need to check with my user, I'll let ya know. Well, I tried to install 5.8.0 on my 8.0.1 (beta) system, and blew cc up with an internal compiler error. I'll have to wait for Caldera to fix that. Sorry. -- Olivier PRENANT Tel:+33-5-61-50-97-00 (Work) Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Inheritance
On Fri, 2002-09-06 at 08:57, [EMAIL PROTECTED] wrote: On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: On 5 Sep 2002, Hannu Krosing wrote: To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. What that tells me is that the constraint, valid_prices, shouldn't have been on GOODS in the first place. If it is not a legitimate constraint for the children, then it is not a legitimate constraint for the parent. I don't agree with you on that point. This concept is common to many OO-implementations. Unless you can come up with a powerful argument as to why our to-be picture should never do this, I'm less than convinced. In human inheritance, if you marry someone with funny coloured skin, you don't get to choose that your children won't have funny coloured skin. That's a pretty forcible constraint. :-). Fine, but that only works for YOUR specific example. In that example, the color constraint should be non-virtual, meaning, the child should not be able to change it. On the other hand, if I replace human with metal product, hopefully I won't be stuck with gun metal gray for every derived product. Hopefully, somewhere along the lines, I'll be able to override the parent's color constraint. Or maybe it is better to just make the check function should be dynamically dispatched, so the constraint will always hold, it just can mean different things for different types. Or maybe if someone is doing an Object Oriented design, and making extensive use of inheritance, they'll need to apply constraints in a manner that allow them to be properly inherited. The problem with that assumption is that there is normally nothing wrong with having seemingly mutually exclusive sets of *business rules* for a parent and child. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Inheritance
Oops! [EMAIL PROTECTED] (Greg Copeland) was seen spray-painting on a wall: --=-eu74lKXry3SVx8eZ/qBD Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Fri, 2002-09-06 at 08:57, [EMAIL PROTECTED] wrote: On Fri, 2002-09-06 at 07:37, Curt Sampson wrote: On 5 Sep 2002, Hannu Krosing wrote: To elaborate on Gregs example if you have table GOODS and under it a table CAMPAIGN_GOODS then you may place a general overridable constraint valid_prices on GOODS which checks that you dont sell cheaper than you bought, but you still want sell CAMPAIGN_GOODS under aquiring price, so you override the constraint for CAMPAIGN_GOODS. What that tells me is that the constraint, valid_prices, shouldn't have been on GOODS in the first place. If it is not a legitimate constraint for the children, then it is not a legitimate constraint for the parent. I don't agree with you on that point. This concept is common to many OO-implementations. Unless you can come up with a powerful argument as to why our to-be picture should never do this, I'm less than convinced. If the plan is for table CAMPAIGN_GOODS to virtually be a view on GOODS, then I'd say it _is_ necessary. In human inheritance, if you marry someone with funny coloured skin, yo= u=20 don't get to choose that your children won't have funny coloured skin.= =20=20 That's a pretty forcible constraint. :-). =20 Is there something broken with your mailer? It's reformatting quotes rather horribly... Fine, but that only works for YOUR specific example. In that example, the color constraint should be non-virtual, meaning, the child should not be able to change it. On the other hand, if I replace human with metal product, hopefully I won't be stuck with gun metal gray for every derived product. Hopefully, somewhere along the lines, I'll be able to override the parent's color constraint. That happens by _adding_ an additional characteristic, presumably that of what kind of paint the metal is covered with. That doesn't override the fundamental constraint that if it's a metal product, there _will_ be metallic properties. If you decide to add in some non-metallic products, then it would be _silly_ to have them inherit all their characteristics from METAL_PRODUCTS; they should head back up the class hierarchy and inherit their basic characteristics from the _appropriate_ parent. Reality, with the GOODS/CAMPAIGN_GOODS example, is that GOODS isn't the appropriate parent class for CAMPAIGN_GOODS. Both should be inheriting the common characteristics from some common ancestor. If that is done, then there's nothing to override. Or maybe it is better to just make the check function should be dynamically dispatched, so the constraint will always hold, it just can mean different things for different types. =20 Or maybe if someone is doing an Object Oriented design, and making extens= ive=20 use of inheritance, they'll need to apply constraints in a manner that al= low=20 them to be properly inherited. The problem with that assumption is that there is normally nothing wrong with having seemingly mutually exclusive sets of *business rules* for a parent and child. If the rules are totally different, it begs the question of why they _should_ be considered to be related in a parent/child relationship. It may well be that they _aren't_ related as parent/child. They may merely be cousins, sharing some common ancestors. -- (concatenate 'string chris @cbbrowne.com) http://cbbrowne.com/info/spreadsheets.html Note that if I can get you to `su and say' something just by asking, you have a very serious security problem on your system and you should look into it. -- Paul Vixie, vixie-cron 3.0.1 installation notes ---(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] Inheritance
There was a comment earlier that was not really addressed. What can you do with table inheritance that you can not do with a relational implementation? Or what would work *better* as inheritance? (you define better) This is a genuine question, not a snarky comment. I really want to know. This is the reason I can think of to use inheritance: Several tables have a common set of attributes and there is some reason for these tables to be separate AND there is some reason for the common columns to be queried en masse. What kinds of some reasons are there, though? And if my condition for using table inheritance is lacking or misguided, what should be the criteria for using table inheritance? Creating indexes across tables is a project. Is it the most important project? Will it benefit the most users? Will it benefit any users? Theory is great and important, but if no one uses the functionality, who cares? If these changes will enable people to use the functionality that until now had been too much of a PITA then it might be worth it. However, I suspect the majority of people who would use these changes are participating in these discussions. These features were never widely used in Illustra nor Informix although their implementations were a little smoother imho. To weigh in on the constraints issues, it seems problematic that currently some constraints (check) are inherited and others are not (foreign keys). The chcheers,oice of which ones are or aren't is clear to people familiar with the implementation but what about the rest of the world who just want some consistent rule. I also agree with the people who say, if we inherit constrainsts, then we must be able to override them in the subtables. I like the suggested LOCAL keyword, myself. cheers, elein :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: [EMAIL PROTECTED] (510)543-6079 Taking a Trip. Not taking a Trip. --anonymous :~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Rule updates and PQcmdstatus() issue
Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? Thank you very much. - Best regards, Steve Howe mailto:[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] problem with new autocommit config parameter and jdbc
Hello Barry,
JDBC driver must find autocommit (off or on) and set autoCommit field
when open connection.
regards
On Friday 06 September 2002 06:52 pm, Barry Lind wrote:
Haris,
You can't use jdbc (and probably most other postgres clients) with
autocommit in postgresql.conf turned off.
Hackers,
How should client interfaces handle this new autocommit feature? Is it
best to just issue a set at the beginning of the connection to ensure
that it is always on?
thanks,
--Barry
snpe wrote:
Hi Dave,
That is same.Program work with and without quote but row don't deleted.
Postgresql is 7.3 beta (from cvs) and parameter autocommit in
postgresql.conf
is off (no auto commit).
I am tried with db.autocommit(true) after getConnection, but no success
I thin that is bug in JDBC
PGSql 7.3 beta have new features autocommit on/off and JDBC driver
don't work
with autocommit off
Thanks
P.S
I am play ith Oracle JDeveloper 9i and Postgresql and I get error in
prepared
statement like this error :
(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement
preparation. Statement: DELETE FROM org_ban WHERE id=?
and pgsqlerror is :
(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM
org_ban WHERE
id=?] usage : {[? =] call some_function ([? [,?]*]) }
I think that JDeveloper call CallableStatement for insert or delete
(select
and update work fine), but I don't know how.
On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
Remove the quotes around id, and let me know what happens
Dave
On Fri, 2002-09-06 at 10:52, snpe wrote:
Hello Dave,
There isn't any error.Program write 'Rows deleted 1', but row hasn't
been deleted
Thanks
Haris Peco
On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
Harris,
What error do you get?
Also you don't need the quotes around id
Dave
On Fri, 2002-09-06 at 10:06, snpe wrote:
Hello,
I have simple table with column ID and values '4' in this.
I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
postgresql.conf. Next program don't work .
I am tried with compiled postgresql.jar form CVS and with
pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
What is wrong ?
regards
Haris Peco
import java.io.*;
import java.sql.*;
import java.text.*;
public class PrepStatTest
{
Connection db;
String stat=DELETE FROM org_ban WHERE \id\ = ?;
String delid = 4;
public PrepStatTest() throws ClassNotFoundException,
FileNotFoundException, IOException, SQLException
{
Class.forName(org.postgresql.Driver);
db = DriverManager.getConnection(jdbc:postgresql://spnew/snpe,
snpe, snpe);
PreparedStatement st = db.prepareStatement(stat);
st.setString(1, delid);
int rowsDeleted = st.executeUpdate();
System.out.println(Rows deleted + rowsDeleted);
db.commit();
st.close();
db.close();
}
public static void main(String args[])
{
try
{
PrepStatTest test = new PrepStatTest();
}
catch (Exception ex)
{
System.err.println(Exception caught.\n + ex);
ex.printStackTrace();
}
}
}
---(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
---(end of
broadcast)--- TIP 2: you can get off all lists
at once with the unregister command (send unregister
YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Steve Howe wrote: Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? I see on TODO: * Return proper effected tuple count from complex commands [return] and that return link has a discussion of possible fixes. Unfortunately, no fix was agreed upon so there is no fix in 7.3. And, on top of that, I can't even think of a workaround. At best, perhaps someone can write you a patch to fix this. -- 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] Inheritance
On Fri, 2002-09-06 at 11:05, [EMAIL PROTECTED] wrote: Oops! [EMAIL PROTECTED] (Greg Copeland) was seen spray-painting on a wall: That's a pretty forcible constraint. :-). =20 Is there something broken with your mailer? It's reformatting quotes rather horribly... Hmm...not that I know off. Never had complaints before anyways. Looks like an issue with MIME contents...perhaps your mailer doesn't properly parse some MIME and/or mine is hosing it some how. Not really sure. Reality, with the GOODS/CAMPAIGN_GOODS example, is that GOODS isn't the appropriate parent class for CAMPAIGN_GOODS. Both should be inheriting the common characteristics from some common ancestor. If that is done, then there's nothing to override. You can complain about and redefine the model to suit your needs all day long and get no where. It doesn't change the need for it. Fact is, it would be nice to allow. Fact is, OO-implementations tend to allow this. I'm quite happy to let you go to every OO computer language camp and inform them that they've done it all wrong. ;) Citing that a specific example is all wrong hardly invalidates the concept. Since we are pretty much at the conceptual stage, I welcome a conceptual argument on why this is bad and should never be done. Please, be high level and generic. After all, I too can give you a hundred specific reasons why a cat is not dog (i.e. bad model)...but it does nothing to facilitate the topic at hand. The problem with that assumption is that there is normally nothing wrong with having seemingly mutually exclusive sets of *business rules* for a parent and child. If the rules are totally different, it begs the question of why they _should_ be considered to be related in a parent/child relationship. Because this is how the real world works. Often there are exceptions to the rules. When these rules differ, I've not seen a valid high level conceptual reason that should prevent it. Example: animal quadruped (has 4-trunk limbs) dog injuredDog (has 0 or more trunk limbs) Hopefully we can agree that a dog is still a dog even if it only has three legs? Hopefully you'll realize this was given to illustrate an example and to prove a point. Sometimes a model needs to allow for exceptions to the rule. You can argue that a three-legged dog is no longer a quadruped but I prefer to believe that it is a quadruped which just happens to be an exception to the rule. It may well be that they _aren't_ related as parent/child. They may merely be cousins, sharing some common ancestors. Yes, it's true. Sometimes the wrong model is applied but that hardly invalidates the concept or alleviates the need. Regards, Greg Copeland signature.asc Description: This is a digitally signed message part
Re: [HACKERS] beta1 packaged
Marc G. Fournier writes: Actually, I just asked for the split, I think it was peter that actually did it ... :) I recall that you thought of the split in order to save bandwidth for those who didn't need everything. It was expressedly intended that the -base tarball was usable by itself and that you only needed the others if you wanted any of the optional features (--with-* etc.). But now that the optional stuff has mostly either gone away or isn't optional anymore a revised split would come out pretty skewed: -rw-r--r--1 peterusers10824414 Sep 6 23:21 postgresql-7.3b1.tar.gz -rw-r--r--1 peterusers 6675930 Sep 6 23:25 postgresql-base-7.3b1.tar.gz -rw-r--r--1 peterusers 2585621 Sep 6 23:30 postgresql-docs-7.3b1.tar.gz -rw-r--r--1 peterusers 485095 Sep 6 23:30 postgresql-opt-7.3b1.tar.gz -rw-r--r--1 peterusers 1072069 Sep 6 23:30 postgresql-test-7.3b1.tar.gz -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Bruce, Friday, September 6, 2002, 3:22:13 PM, you wrote: BM Steve Howe wrote: Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? BM I see on TODO: BM * Return proper effected tuple count from complex commands [return] Sorry, I missed it because I check the v7.2.2 TODO. BM and that return link has a discussion of possible fixes. BM Unfortunately, no fix was agreed upon so there is no fix in 7.3. So all the databases that uses rules will still be broken ? I don't believe you guys are so unconcerned about this... BM And, on top of that, I can't even think of a workaround. At best, BM perhaps someone can write you a patch to fix this. Let's hope so... and I disagree about the 'write for me' point; it's for *everyone using rules*. They are useless, currently... and it's broken for months and nothing agreed until know... I just can't believe in it. What do you do when you have to update a view ? - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Interesting results using new prepared statements
In testing the new 7.3 prepared statement functionality I have come across some findings that I cannot explain. I was testing using PREPARE for a fairly complex sql statement that gets used frequently in my applicaition. I used the timing information from: show_parser_stats = true show_planner_stats = true show_executor_stats = true The timing information showed that 60% of time was in the parse and planning, and 40% was in the execute for the original statement. This indicated that this statement was a good candidate for using the new PREPARE functionality. Now for the strange part. When looking at the execute timings as shown by 'show_executor_stats' under three different senerios I see: regular execute = 787ms(regular sql execution, not using prepare at all) prepare execute = 737ms(execution of a prepared statement via EXECUTE with no bind variable, all values are hardcoded into the prepared sql statement) prepare/bind execute = 693ms(same as above, but using bind variables) These results where consistent across multiple runs. I don't understand why the timings for prepared statements would be less than for a regular statement, and especially why using bind variables would be better than without. I am concerned that prepared statements may be choosing a different execution plan than non-prepared statements. But I am not sure how to find out what the execution plan is for a prepared statement, since EXPLAIN doesn't work for a prepared statement (i.e. EXPLAIN EXECUTE preparedStatementName, doesn't work). I like the fact that the timings are better in this particular case (upto 12% better), but since I don't understand why that is, I am concerned that under different circumstances they may be worse. Can anyone shed some light on this? thanks, --Barry ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] problem with new autocommit config parameter and jdbc
Haris,
You can't use jdbc (and probably most other postgres clients) with
autocommit in postgresql.conf turned off.
Hackers,
How should client interfaces handle this new autocommit feature? Is it
best to just issue a set at the beginning of the connection to ensure
that it is always on?
thanks,
--Barry
snpe wrote:
Hi Dave,
That is same.Program work with and without quote but row don't deleted.
Postgresql is 7.3 beta (from cvs) and parameter autocommit in
postgresql.conf
is off (no auto commit).
I am tried with db.autocommit(true) after getConnection, but no success
I thin that is bug in JDBC
PGSql 7.3 beta have new features autocommit on/off and JDBC driver
don't work
with autocommit off
Thanks
P.S
I am play ith Oracle JDeveloper 9i and Postgresql and I get error in
prepared
statement like this error :
(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement
preparation. Statement: DELETE FROM org_ban WHERE id=?
and pgsqlerror is :
(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM
org_ban WHERE
id=?] usage : {[? =] call some_function ([? [,?]*]) }
I think that JDeveloper call CallableStatement for insert or delete
(select
and update work fine), but I don't know how.
On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
Remove the quotes around id, and let me know what happens
Dave
On Fri, 2002-09-06 at 10:52, snpe wrote:
Hello Dave,
There isn't any error.Program write 'Rows deleted 1', but row hasn't
been deleted
Thanks
Haris Peco
On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
Harris,
What error do you get?
Also you don't need the quotes around id
Dave
On Fri, 2002-09-06 at 10:06, snpe wrote:
Hello,
I have simple table with column ID and values '4' in this.
I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
postgresql.conf. Next program don't work .
I am tried with compiled postgresql.jar form CVS and with
pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
What is wrong ?
regards
Haris Peco
import java.io.*;
import java.sql.*;
import java.text.*;
public class PrepStatTest
{
Connection db;
String stat=DELETE FROM org_ban WHERE \id\ = ?;
String delid = 4;
public PrepStatTest() throws ClassNotFoundException,
FileNotFoundException, IOException, SQLException
{
Class.forName(org.postgresql.Driver);
db = DriverManager.getConnection(jdbc:postgresql://spnew/snpe,
snpe, snpe);
PreparedStatement st = db.prepareStatement(stat);
st.setString(1, delid);
int rowsDeleted = st.executeUpdate();
System.out.println(Rows deleted + rowsDeleted);
db.commit();
st.close();
db.close();
}
public static void main(String args[])
{
try
{
PrepStatTest test = new PrepStatTest();
}
catch (Exception ex)
{
System.err.println(Exception caught.\n + ex);
ex.printStackTrace();
}
}
}
---(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
---(end of
broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to
[EMAIL PROTECTED])
---(end of
broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Rule updates and PQcmdstatus() issue
I am not any happier about it than you are. Your report is good because it is the first case where returning the wrong value actually breaks software. You may be able to justify adding a fix during beta by saying it is a bug fix. Of course, someone is going to have to generate a patch and champion the cause. This stuff doesn't happen by magic. --- Steve Howe wrote: Hello Bruce, Friday, September 6, 2002, 3:22:13 PM, you wrote: BM Steve Howe wrote: Hello all, PostgreSQL *still* has a bug where PQcmdStatus() won't return the number of rows updated. But that is essential for applications, since without it of course we don't know if the updates/delete/insert commands succeded. Even worst, on interfaces like Delphi/dbExpress the program will return an error message and rollback transaction thinking nothing have been updated. In other words, unusable. This render views useless (I either use view with rules and don't get my program working) and won't allow me to proper use security settings on PostgreSQL... This is a *major* issue in my opinion that appeared on a May thread but I can't see it done on version 7.2.2. Even worst, I can't see nothing on the TODO file. Will this fix finally appear on 7.3 ? Any ways to work around this ? How can I know at least if *something* succeeded, or how many rows (the proper behavior)? BM I see on TODO: BM * Return proper effected tuple count from complex commands [return] Sorry, I missed it because I check the v7.2.2 TODO. BM and that return link has a discussion of possible fixes. BM Unfortunately, no fix was agreed upon so there is no fix in 7.3. So all the databases that uses rules will still be broken ? I don't believe you guys are so unconcerned about this... BM And, on top of that, I can't even think of a workaround. At best, BM perhaps someone can write you a patch to fix this. Let's hope so... and I disagree about the 'write for me' point; it's for *everyone using rules*. They are useless, currently... and it's broken for months and nothing agreed until know... I just can't believe in it. What do you do when you have to update a view ? - Best regards, Steve Howe mailto:[EMAIL PROTECTED] -- 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/users-lounge/docs/faq.html
Re: [HACKERS] problem with new autocommit config parameter and jdbc
Barry Lind wrote: Haris, You can't use jdbc (and probably most other postgres clients) with autocommit in postgresql.conf turned off. Hackers, How should client interfaces handle this new autocommit feature? Is it best to just issue a set at the beginning of the connection to ensure that it is always on? Yes, I thought that was the best fix for apps that can't deal with autocommit being off. -- 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/users-lounge/docs/faq.html
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Bruce, Friday, September 6, 2002, 9:52:18 PM, you wrote: BM I am not any happier about it than you are. Your report is good because BM it is the first case where returning the wrong value actually breaks BM software. You may be able to justify adding a fix during beta by saying BM it is a bug fix. Actually I think it must have happened with someone else, but they must have quit using rules or something... Actually I can't ensure security in the system without rules. BM Of course, someone is going to have to generate a patch and champion the BM cause. This stuff doesn't happen by magic. I understand your point. I just was hoping to see more concern about the issue by the developers... but that's been broken for months. Unhappily I can't do it myself because it would take weeks to get familiar with the inners of PostgreSQL... Let's hope someone realize how serious is this and make a fix. Thanks again... - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Rule updates and PQcmdstatus() issue
Hello Bruce, Friday, September 6, 2002, 10:58:13 PM, you wrote: BM Well, there was a big discussion, and I did bring up the issue in early BM August to see if I could get a resolution to it and was told no BM conclusion could be made. BM I suggest you read the TODO detail on the item and make a proposal on BM how it _should_ work and if you can get agreement from everyone, you may BM be able to nag someone into doing a patch. I think it should return the number of rows modified in the context of the view, and not exactly that of each of the tables affected. And this would not work well with PQcmdStatus() because it returns a single integer entry only. This was working on some previous build, wasn't it ? What was the previous behavior ? Shouldn't the patch follow that way ? - Best regards, Steve Howe mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
