Re: [HACKERS] Constraint exclusion is not general enough
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: But you don't have any cost numbers until after you've done the plan. Couldn't this work similar to geqo_effort? The planner could try planning the query using only cheap algorithmns, and if the cost exceeds a certain value, it'd restart, and use more sophisticated methods. AFAICS this would be a net loss on average. Most of the time, the constraint exclusion code doesn't win, and so throwing away all your planning work to try it is going to be a loser most of the time. On the other hand, if the consider-replanning threshold is high enough, than that additional time really doesn't matter - If a query runs for minutes, or even hours, a few wasted cycles during planning don't hurt. greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL performance enhancement when query
Constantin, What binding are you using ? We here use Java+JDBC, and we were able to get stable query plans by forcing server side prepared statements (using PGStatement#setPrepareThreshold with 1 as the threshold), where the query is prepared without knowing the parameter values. This can backfire too, but for our purposes it was the right thing (probably sacrificing some performance, but getting a stable system). The plans in this case are made to work with guessed mean values for the estimates, and that's usually resulting in a stable plan, so once you got it right it will stay like that. Cheers, Csaba. On Mon, 2006-08-07 at 22:02, Constantin Teodorescu wrote: Hello all, hope you are remembering me, some years ago I've designed the PgAccess , the Tcl/Tk visual interface to PostgreSQL. Thought you haven't received any news from me, I continued working with PostgreSQL, being involved in very big projects in Romania. Right now, the national identification of the cows, sheep, goats and pigs in Romania runs on PostgreSQL on a very big database. Once again , I had to thank you all for keeping up maintaining and improving PostgreSQL. My message to all of you is related to this big project (a government sustained project) and some performance issues. Very few words about the database: approx. 60 tables, 30 of them containing 10 millions to 50 millions records , the whole database is approx 40 Gb size ! In order to get a good performance, the database is operated on a dual XEON with 6 Gb RAM IBM x235 server, the database with tables and indexes carefully distributed on 6 different SCSI disks, in different tablespaces in such a manner to allow parallelizing reads and HDD head movements on different devices when joining those big tables. We have tuned every possible parameter in config file, we have reorganized queries, analyzing explains in order to get the best results for all big queries and we succeeded most of the time. But we have encountered some problems. Due to constant updates and inserts into the database, it's size is growing continuously. Of course we are doing DAILY the needed maintaince, vacuums, analyzes and backups. Due to permanent changes in database size and statistics there are queries that sometimes change their execution plan, badly choosing another plan and executing those queries in 2,3 minutes instead of 10 seconds, the usual execution time since the query plan is switched. We have done any effort in changing subselects and the query sentence in order to force using some indexes, continuously watching the explain results. We have faced yesterday with such a problem with a query that switched the query plan to a very bad one, almost putting the whole system down. The only way that we have succeeded to make it work again was by using the SET ENABLE_MERGE_JOIN to OFF. For the moment it works but in our opinion this is NOT the best approach to guide the planner to a better query-plan variant. Our suggestion would be : extending the EXPLAIN and SELECT commands like that: EXPLAIN VARIANTS SELECT .. (and so on) that will display the different query plans analyzed by the planner and their estimated time values , not just the best guess . assuming that the EXPLAIN VARIANTS will show 3 or 4 different query plans, the database manager will be able to experiment, to test, and to decide by himself what is THE BEST PLAN FOR ME, instead of letting postgresql planner to to that. Doing this, we would be able to clearly specify then in the SELECT statement the version of the query-plan that would be used in execution like in the following example: SELECT (very big and complex query) ... USING PLAN 3; Specifying the desired plan could be of course, different. I realise that it would be probably better that the query-plan will guess the right and optimal plan. I agree that this can be done be tweaking parameters and costs BUT THIS CAN TAKE A LOT OF TIME, much more than a couple of tests on the real database. An experimented database admin can detect much easier the appropriate plan and force the executor to select that one that he desires. In our opinion, this would be the simplest and the most non-intrusive method of manual choosing another query plan rather than indirectly setting ON or OFFS various parameters that could affect badly other queries. First of all, it's assumed that the query planner HAS ALREADY evaluated different variants and it decides to use one based upon the statistics informations of the involved tables and costs for various types of access. Unfortunately, due to a very difficult adjustment of those costs and timings of the HDD performance, IO transfer speeds, PostgreSQL is choosing sometimes a wrong plan. If we would have the power of choosing and experimenting different plans with SELECT USING PLAN that-one we can select than the
Re: [HACKERS] PostgreSQL performance enhancement when query
On Tue, 2006-08-08 at 12:36, Constantin Teodorescu wrote: We have tried PGStatement#setPrepareThreshold with 1 as the threshold but it's not a good solution. Actually is worst. Considering that you have 5 different query plans, you are selecting approx. random one of them, not taking into account the statistics. Wrong, you'll select _the same_ plan, that's what matters. If it's not the plan you wanted, you have to rewrite the query, and try again, but once you got the plan you wanted, it's pretty much you'll get always the same plan. So you only need to test as long as you get the right query to trigger the right plan... but of course this requires that your queries are so constructed to always be OK with that plan, regardless the parameter values. Usually this means a suboptimal plan, but stable execution times. If you need to give hints to the DB based on the parameter values and choose different plans for different parameter values, then you basically do the job of the planner in your application, and I guess sooner or later you'll make wrong choices too. Some hinting mechanism would be good for cases where the developer really know better how the data is laid out (e.g. forcing the use of a specific access method for one table in a complex join), but that forcing a complete plan is probably not good. Even the hinting is only a workaround for the planner fixes which will cannot make it to the stable version... On the daydreaming part, how about a 2 phase planner ? Modus operandi: Phase 1: compile and cache plan decision tree: - collect all reasonable plans without taking into account the parameter values; - check the parameter bounds where each plan is the fastest; - compile a decision tree which based on the parameter values chooses one plan or the other; - cache this plan decision tree; - there's no need to cache plans which will always loose to some other plan no matter what parameter values you give (to limit the size of the decision tree); Phase 2: run the decision tree to chose the best cached plan for the parameter values; You could use variables coming from the statistics system in the decision tree so it doesn't have to be recalculated too often on statistics changes. With a system like this, you could at system startup make the decision tree for all your frequently used queries and have fast planning at runtime which is optimized for the parameter values (takes the decision tree from the cache, runs it with the current parameters). Or just store the whole thing in a system table... or tweak the decision tree manually... This is actually not addressing the plan stability issue, but if manual tweaking would be allowed, it would... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] standard interfaces for replication providers
Markus Schiltknecht wrote: Hi, José Orlando Pereira wrote: I would argue that people haven't been able to build production-grade multi-master replication, in part, due to the barrier of not having a standard database-agnostic API. :-) In fact, the problem is not the lack of a standard API but the lack of an API at all. Having to learn the intrincacies of a database server (or build a full fledged server wrapper) to experiment with simple prototypes is a serious hurdle for RD in database replication. This has been the case for replication based on group communication. I disagree. There have been a couple of approaches and each has had a different interface to the database. But for most of them, coding the database interface was _not_ the hardest part. And a good understanding of the database system internals is simply required to write a good replication system. I agree with you. But, I would add that the same understanding is required to design and implement this interface on any database system. Please don't confuse two proposals included in the distributed package: (1) A PostgreSQL specific patch, which implements a minimal set of required features with a PostgreSQL-specific interface (e.g. triggers, new statements, configuration variables). This is by no means a standard interface. The included technical report discusses why these are required for a variety of replication scenarios. Where do I find the included technical report? GordaInterfaces/javasrc/docs/gapi.pdf I've read the READMEs in PostgreSQL/G toplevel and csrc directory and did not find convincing reasons why exactly these triggers need to be added as replication hooks. In fact, Postgres-R (8) would already need different hooks. Such triggers are not necessary to develop any kind of replication protocol... However, indeed any replication protocol requires a set of hooks that might be enabled by different means, e.g. call back functions, triggers, etc, etc... We developed our hooks by means of triggers as their provide a standard interface (triggers) that might be easily exploited by other projects besides replication, e.g. materialized views. From studying the patch, I understand that these hooks are quite close to what's needed for a Postgres-R or Slony-II like sync, multi-master replication system (i.e. hooks for writeset extraction, the addition of a 'serialization error' for remote transactions). I can see use for such an API as soon as we have a production-grade replication system, which performs well enough in most applications (i.e. when we know exactly where to place the hooks). But up until then, people will try different algorithms and different hooks. Concerning my work on Postgres-R I can tell: I'm not going to use these triggers (hooks) because they are limiting. Could you tell me why they are limiting ? I know enough about the database system internals and I _want_ to fiddle with the database system. Why should I use such an API? The idea is not to impose our API but what we really want is to show that behind the requirements for replication systems there is place for a variety of systems that could be leverage by means of a standard api. Jose Orlando, any comments on that ? ---(end of broadcast)--- TIP 1: 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] An Idea for planner hints
Hi Since the discussion about how to force a specific plan has come up, I though I'd post an idea I had for this a while ago. It's not reall well though out yet, but anyway. When the topic of optimizer hints comes up, people often suggest that there should be a way to force postgres to use a certain index, or do joins in a certain order. AFAIK, this mimics what oracle does - you can put comments into your query that specify what index to use. This approach has two major drawbacks .) Plans that seem good now might not seem that good a few months later - your data might have changed, and other execution plans might fit better now .) You have to change all your queries to make use of features in new postgres versions, like bitmap scans. My experience with the postgres optimizer is that it usually performs great - and if it doesn't, that always boiled down to two problems (at least for me) .) The query is autogenerated, and includes complex, and highly inter- dependent where (or join) conditions. This leads to wrong estimates of where selectivity, and thus to bad plans. .) There are correlations between columns and/or tables that postgres doesn't know about (and has no chance of knowing about). Again, this leads to vastly wrong estimates of row counts, and to bad plans. I think that those bad estimates of the selectivity of where-clauses (or on-clauses for joins) is where postgres could use hints. Image a query like select ... from t1 join t2 on t1.t2_id = t2.id and expr. Lets say that expr is true for only 1% of the rows in t2 - but those are exactly the rows that have matching rows in t1. Postgres would probably guess that this join will produce about 1/100 of the rows that t1 has - but I _know_ that it will produce 100 (!) times more rows. Now, I'd like to hand that information to postgres. I wouldn't want to force any particular access method or join order, but rather I'd just tell it hey, this expression has selectivity 1 in this context, not 0.01 as you might think. Could that work? greetings, Florian Pflug ---(end of broadcast)--- TIP 1: 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] ecpg test suite
We changed some things that should remove most of the differences you had. Two other diffs looked like you had an older version of ecpglib running. Could that be? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] proposal for PL packages for 8.3.
Pavel Stehule [EMAIL PROTECTED] writes: I unlike concept of nested schemats or packages nested in schema. I don't see reason for it. About implementation.. package is more special kind of function for me. But relation between package and function I can create via dot notation in function's name. It's different from nested syntax from PL/SQL or ADA. I can easy separate SQL part and non SQL part. Apparently you're not aware that that syntax is not free for the taking. The reason people are complaining about this proposal is that currently foo.bar(...) means function bar in schema foo, and you seem to be intending to break it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] proposal for PL packages for 8.3.
Pavel Stehule [EMAIL PROTECTED] writes: I unlike concept of nested schemats or packages nested in schema. I don't see reason for it. About implementation.. package is more special kind of function for me. But relation between package and function I can create via dot notation in function's name. It's different from nested syntax from PL/SQL or ADA. I can easy separate SQL part and non SQL part. Apparently you're not aware that that syntax is not free for the taking. The reason people are complaining about this proposal is that currently foo.bar(...) means function bar in schema foo, and you seem to be intending to break it. I understand it. But I don't know better solution. Certainly foo.bar(..) is ambigous and it can mean both. ANSI SQL don't use packages and Oracle's package are unsolveable because we have separated parsers. Do you have any idea, what is good model for it? Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ecpg test suite
Michael Meskes [EMAIL PROTECTED] writes: We changed some things that should remove most of the differences you had. Two other diffs looked like you had an older version of ecpglib running. Could that be? Bingo --- I had been doing make clean, make all, make check. It seems this is managing to invoke the installed version of ecpglib not the just-built version, probably because of the rpath switches we use on HPUX. With make install before make check, I get a clean pass with this morning's CVS tip (using gcc ... will try HP's cc in a bit). You really oughta support make installcheck anyway; aside from being less vulnerable to this issue, it's a lot less overhead to run. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2 features status
Bruce Momjian wrote: I know about the same as the community members who pay attention to postings. What I do is to act on that information by contacting developers and asking them to complete their work for feature freeze. Many of my conversations are not appropriate for the public, which is why it is done privately. In fact, the feedback I have gotten from some community members that have heard a little of the discussions I have had with developers is that I am too forceful. I know that doesn't match my often non-critical or even lax handling of things, but I take my community responsibility seriously, and if someone has stated they are working on an item, I expect them to take that pledge seriously as well. Is that a response from other developers, or from those you have pressed a bit? Perhaps the fact that the process is so very informal has led people to false expectations anyway. Maybe if we were quite up front about it people would not get upset. If you say you will work on feature X, expect an occasional ping from someone asking about progress. As far as people always asking for better tracking, they used to always ask for a roadmap, and when we stated we couldn't because we have no control over developers, they pointed to Mozilla, which had a roadmap at the time (but we know what happened to them.) This seems to me to be a case of the well known fallacy post hoc ergo propter hoc. The fact that mozilla had some less than good results does not mean that everything they did was wrong. In the case of recursive queries, I did more than might have even been polite to try to get the developer to complete it. I don't see how changing our system is going to improve it. If you want to change the system, find a system that would have actually done better than what we have in place. Or try a new system, and I will keep doing what I do, and we can see which system works best. Excellent idea. We don't have to have a one size fits all set of procedures anyway - in fact I think it might be a mistake. Maybe we should select a few major features that people will work on for 8.3 and try a different model. We could then assess things around this time next cycle. cheers andrew ---(end of broadcast)--- TIP 1: 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] ecpg test suite
I wrote: With make install before make check, I get a clean pass with this morning's CVS tip (using gcc ... will try HP's cc in a bit). Further results: * The vulnerability to using a previously installed ecpglib exists in our default Linux configuration as well as HPUX. * Still fails with HP's cc on HPUX: *** expected/sql-desc.stdoutThu Aug 3 09:24:58 2006 --- results//sql-desc.stdoutTue Aug 8 09:03:35 2006 *** *** 1,4 output = 1 ! val1=1 (ind1: 0) val2='one' (ind2: 0) val1=2 val2=null val1=2 val2=null --- 1,4 output = 1 ! val1=654311425 (ind1: 0) val2='one' (ind2: 0) val1=2 val2=null val1=2 val2=null * Still fails with gcc on x86_64: *** expected/pgtypeslib-num_test2.stdoutMon Aug 7 09:17:02 2006 --- results//pgtypeslib-num_test2.stdoutTue Aug 8 08:51:06 2006 *** *** 53,59 (no errno set) - num[4,3]: 592490.0 (no errno set) - num[4,4]: 592490.00 (no errno set) - num[4,5]: 0.00 ! (errno == PGTYPES_NUM_OVERFLOW) - num[4,6]: 0 (r: -1) (errno == PGTYPES_NUM_OVERFLOW) - num[4,8]: 0 (r: -1) (errno == PGTYPES_NUM_OVERFLOW) - num[4,10]: 592490.000 (r: 0) (no errno set) - num[4,11]: 592490.00 (cmp: 0) --- 53,60 (no errno set) - num[4,3]: 592490.0 (no errno set) - num[4,4]: 592490.00 (no errno set) - num[4,5]: 0.00 ! (no errno set) - num[4,6]: 592490 (r: 0) ! (no errno set) - num[4,7]: 592490.00 (cmp: 0) (errno == PGTYPES_NUM_OVERFLOW) - num[4,8]: 0 (r: -1) (errno == PGTYPES_NUM_OVERFLOW) - num[4,10]: 592490.000 (r: 0) (no errno set) - num[4,11]: 592490.00 (cmp: 0) *** expected/sql-dynalloc.stderrTue Aug 8 08:43:33 2006 --- results//sql-dynalloc.stderrTue Aug 8 08:51:06 2006 *** *** 34,75 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 3 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 43: allocating 21 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 43: RESULT: varchar offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 43: RESULT: offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 4 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 44: allocating 16 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 44: RESULT: v offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 44: RESULT: v offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 5 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 45: allocating 22 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 45: RESULT: coffset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 45: RESULT: coffset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 6 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 46: allocating 70 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 46: RESULT: Mon Mar 03 11:33:07 2003 PST offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 46: RESULT: Mon Mar 03 11:33:07 2003 PST offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 7 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 47: allocating 16 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 47: RESULT: t offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 47: RESULT: f offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 9 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 50: allocating 46 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 50: RESULT: 2001:4f8:3:ba:2e0:81ff:fe22:d1f1 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 50: RESULT: offset: -1 array: Yes --- 34,75 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc: reading items for tuple 3 [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGstore_result: line 43: allocating 33 bytes for 2 tuples (char**=0)[NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 43: RESULT: varchar offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 43: RESULT: offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_desc:
Re: [HACKERS] 8.2 features status
Andrew Dunstan wrote: Bruce Momjian wrote: I know about the same as the community members who pay attention to postings. What I do is to act on that information by contacting developers and asking them to complete their work for feature freeze. Many of my conversations are not appropriate for the public, which is why it is done privately. In fact, the feedback I have gotten from some community members that have heard a little of the discussions I have had with developers is that I am too forceful. I know that doesn't match my often non-critical or even lax handling of things, but I take my community responsibility seriously, and if someone has stated they are working on an item, I expect them to take that pledge seriously as well. Is that a response from other developers, or from those you have pressed a bit? Perhaps the fact that the process is so very informal has led From other developers, not those I have pressed. people to false expectations anyway. Maybe if we were quite up front about it people would not get upset. If you say you will work on feature X, expect an occasional ping from someone asking about progress. As far as people always asking for better tracking, they used to always ask for a roadmap, and when we stated we couldn't because we have no control over developers, they pointed to Mozilla, which had a roadmap at the time (but we know what happened to them.) This seems to me to be a case of the well known fallacy post hoc ergo propter hoc. The fact that mozilla had some less than good results does not mean that everything they did was wrong. My point is that we knew the idea was useless for us at the time, even though people asked for it over and over again. In the case of recursive queries, I did more than might have even been polite to try to get the developer to complete it. I don't see how changing our system is going to improve it. If you want to change the system, find a system that would have actually done better than what we have in place. Or try a new system, and I will keep doing what I do, and we can see which system works best. Excellent idea. We don't have to have a one size fits all set of procedures anyway - in fact I think it might be a mistake. Maybe we should select a few major features that people will work on for 8.3 and try a different model. We could then assess things around this time next cycle. My big point is that we should choose a system that would have had a better chance of completing features than what we have used in the past, and no one has suggested one. It is just like the bug tracker issue. Many think we need a bugtracker, but when I ask to see a project that has one that is better than what we have now, no one responds. Again, the same criteria should be applied to this issue. If people want to do something different with no objective hope it will be better, feel free to go ahead and do it, but I can't get excited about spending time on it. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 features status
Bruce Momjian wrote: Or try a new system, and I will keep doing what I do, and we can see which system works best. Excellent idea. We don't have to have a one size fits all set of procedures anyway - in fact I think it might be a mistake. Maybe we should select a few major features that people will work on for 8.3 and try a different model. We could then assess things around this time next cycle. My big point is that we should choose a system that would have had a better chance of completing features than what we have used in the past, and no one has suggested one. It is just like the bug tracker issue. Many think we need a bugtracker, but when I ask to see a project that has one that is better than what we have now, no one responds. Again, the same criteria should be applied to this issue. If people want to do something different with no objective hope it will be better, feel free to go ahead and do it, but I can't get excited about spending time on it. I give up. You say try something else and we'll see what works best. I respond great idea.. Then you say but it won't work anyway. Is it any wonder people get frustrated? Why give the illusion of an open mind when you have already made up your mind? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] standard interfaces for replication providers
Hi, Jose Orlando Pereira wrote: Sorry, stuff was put twice in the zip file making it somewhat confusing. It is in postgresql-g-0.1/javasrc/GordaInterfaces/docs/gapi.pdf or directly on the web site at http://gorda.di.uminho.pt/download/reports/gapi.pdf. Thank you. I've just had a quick glance at it. Can you point out why is it [limiting], given that it is admittedly quite close? An API is always limiting. And if you have to change the API a lot, to fit your needs, what's the point in using it at all? Good APIs don't change a lot. Even if it's quite close, I estimate the effort to port Postgres-R to use your API to be quite large. I.e. the first missing thing that came to my mind was the ordering of processes when waking them up after waiting for a lock. Postgres-R needs the processes to be woken up in the order of writeset arrival. Now, I didn't see anything related in the patch, but the gapi.pdf has 'Predictable Deadlock Handling' in it. I need to take another look... We'd rather discuss specific issues instead of the general topic of whether to build APIs around them. We certainly are not married to the proposed interfaces, although the functionality they capture does reflect our experience with several algorithms. I still feel that I would need ways too many hooks. Especially when you consider advanced replication features such as data partitioning and remote query execution. What also worries me is the use of triggers. ISTM that using triggers is not deep enough in the database. In the above example, do I really want to fire a trigger every time the database needs to wake up a process? In PostgreSQL a trigger normally runs within a transaction. How do you work around that? I'm operating a level deeper with Postgres-R and really enjoy the freedom I have with C. Having to write a hook or trigger for every change in the database systems seems a lot of work, which I tend to postpone until such a thing is really needed. Regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
Andrew Dunstan wrote: My big point is that we should choose a system that would have had a better chance of completing features than what we have used in the past, and no one has suggested one. It is just like the bug tracker issue. Many think we need a bugtracker, but when I ask to see a project that has one that is better than what we have now, no one responds. Again, the same criteria should be applied to this issue. If people want to do something different with no objective hope it will be better, feel free to go ahead and do it, but I can't get excited about spending time on it. I give up. You say try something else and we'll see what works best. I respond great idea.. Then you say but it won't work anyway. Is it any wonder people get frustrated? Why give the illusion of an open mind when you have already made up your mind? I am saying other people can try a new system, but I don't have time to try something different when no evidence has been given that it is better (just different). Or try a new system, and I will keep doing what I do, and we can see which system works best. I realized when I said, we can try that I was being inconsistent, but I was just saying that if others want to try something, go ahead. I personally don't see how it will improve things, but if others want to spend time on it, they are welcome to do that. What I am not willing to do is to abandon a system that works for one that doesn't have evidence it is an improvement, and I don't want to spend time on a new system just for the sake of trying to do two systems at once. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
Bruce Momjian wrote: I am saying other people can try a new system, but I don't have time to try something different when no evidence has been given that it is better (just different). Ok, not sure if I am in a position to call shots like I am about to, but here it goes: Could everybody who is willing to invest time setting up an alternative contact me so that we can maybe get together in IRC to talk things through and come up with a solid game plan? Maybe with such a plan we can also get Bruce to atleast give us infrequent, even very raw, brain dumps so that we do not face developers with all too much redundant information seeking. regards, Lukas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Intermittent make check failures on hyena
Josh Berkus napsal(a): Zdenek, However what happened? I think that following scenarios occurred. Postmaster listen only in one process and there are many clients run really parallel. T2000 server has 32 threads ( 8 core and each has 4 threads). These clients generate more TCP/IP request at one time, than postmaster is able accepted. I don't quite follow this ... are you saying that the regression test generate more than 128 connections?And that Solaris ships by default only allowing 128 connections? My idea is completely wrong. The problem is not related to TCP/IP stack. It related only to UNIX_AF sockets. I have tried analyze problem with dtrace, but I cannot reproduce this on my machine. Josh could I have access to this server (for D-Trace I need root access as well). Zdenek ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] proposal for 8.3: Simultaneous assignment for PL/pgSQL
Tom Lane wrote:
Pavel Stehule [EMAIL PROTECTED] writes:
a,b,c := out3fce(1); -- Simultaneous assignment
I thought we rejected that idea once already, on the grounds that it
would make it too hard to tell the difference between intended code
and typos.
In any case, I had some questions:
. is it compatible with PLSQL?
. can the effect be achieved by assigning to a composite?
I looked into SQL2003, and SQL2003 knows it (SQL/PSM):
assignment statement ::=
singleton variable assignment
| multiple variable assignment
multiple variable assignment ::=
SET assignment target list equals operator assigned row
assignment target list ::=
left paren assignment target [ { comma assignment target }... ]
right paren
singleton variable assignment ::=
SET assignment target equals operator assignment source
Regards
Pavel Stehule
_
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] An Idea for planner hints
If this feature I'm proposing already exists, sorry for the waste of bandwidth, and could someone please point me to it? :) What if there were a mode that told postgres to do an exhaustive search (or if not exhaustive, then much more extensive search) of all plans (or many plans), trying each plan, reporting the performance of each, and discarding the query results, much like explain analyze does. Postgres could then dump the best plan in machine readable (and semi-human readable) form which the planner could parse and use at some later date in lieu of a SQL query. This would allow people with reasonably static table statistics (where the best plan is not likely to change) to spend upfront cycles investigating the best plan and then embed that plan in their business logic. Since the stored plan is both written-by and read-by postgres, it can get quite complicated without putting a burden on humans to read and write such complicated things. It would also remove the risk that the planner will occasionally (due to its nondeterministic workings) choose a really bad plan and stall a production system. mark Florian G. Pflug wrote: Hi Since the discussion about how to force a specific plan has come up, I though I'd post an idea I had for this a while ago. It's not reall well though out yet, but anyway. When the topic of optimizer hints comes up, people often suggest that there should be a way to force postgres to use a certain index, or do joins in a certain order. AFAIK, this mimics what oracle does - you can put comments into your query that specify what index to use. This approach has two major drawbacks .) Plans that seem good now might not seem that good a few months later - your data might have changed, and other execution plans might fit better now .) You have to change all your queries to make use of features in new postgres versions, like bitmap scans. My experience with the postgres optimizer is that it usually performs great - and if it doesn't, that always boiled down to two problems (at least for me) .) The query is autogenerated, and includes complex, and highly inter- dependent where (or join) conditions. This leads to wrong estimates of where selectivity, and thus to bad plans. .) There are correlations between columns and/or tables that postgres doesn't know about (and has no chance of knowing about). Again, this leads to vastly wrong estimates of row counts, and to bad plans. I think that those bad estimates of the selectivity of where-clauses (or on-clauses for joins) is where postgres could use hints. Image a query like select ... from t1 join t2 on t1.t2_id = t2.id and expr. Lets say that expr is true for only 1% of the rows in t2 - but those are exactly the rows that have matching rows in t1. Postgres would probably guess that this join will produce about 1/100 of the rows that t1 has - but I _know_ that it will produce 100 (!) times more rows. Now, I'd like to hand that information to postgres. I wouldn't want to force any particular access method or join order, but rather I'd just tell it hey, this expression has selectivity 1 in this context, not 0.01 as you might think. Could that work? greetings, Florian Pflug ---(end of broadcast)--- TIP 1: 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 5: don't forget to increase your free space map settings
Re: [HACKERS] proposal for PL packages for 8.3.
From: Tom Lane [EMAIL PROTECTED] To: Pavel Stehule [EMAIL PROTECTED] CC: [EMAIL PROTECTED], dev@archonet.com, pgsql-hackers@postgresql.org Subject: Re: [HACKERS] proposal for PL packages for 8.3. Date: Tue, 08 Aug 2006 08:18:42 -0400 Pavel Stehule [EMAIL PROTECTED] writes: I unlike concept of nested schemats or packages nested in schema. I don't see reason for it. About implementation.. package is more special kind of function for me. But relation between package and function I can create via dot notation in function's name. It's different from nested syntax from PL/SQL or ADA. I can easy separate SQL part and non SQL part. Apparently you're not aware that that syntax is not free for the taking. The reason people are complaining about this proposal is that currently foo.bar(...) means function bar in schema foo, and you seem to be intending to break it. regards, tom lane I found some doc about it, but I confused. Oracle has two similar kind of objects: packages and modules. Ansi SQL defines MODULES. http://64.233.183.104/search?q=cache:jkXyiDKg-sgJ:www.oracle.com/technology/products/rdb/pdf/createmodule_external_routines.pdf+%22CREATE+MODULE%22+sqlhl=csct=clnkcd=4 Has anybody more documentation about it? Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] proposal for 8.3: Simultaneous assignment for PL/pgSQL
Pavel Stehule wrote:
Tom Lane wrote:
Pavel Stehule [EMAIL PROTECTED] writes:
a,b,c := out3fce(1); -- Simultaneous assignment
I thought we rejected that idea once already, on the grounds that it
would make it too hard to tell the difference between intended code
and typos.
In any case, I had some questions:
. is it compatible with PLSQL?
. can the effect be achieved by assigning to a composite?
I looked into SQL2003, and SQL2003 knows it (SQL/PSM):
assignment statement ::=
singleton variable assignment
| multiple variable assignment
multiple variable assignment ::=
SET assignment target list equals operator assigned row
assignment target list ::=
left paren assignment target [ { comma assignment target
}... ] right paren
singleton variable assignment ::=
SET assignment target equals operator assignment source
The parentheses are apparently required for multiple variables, so in
our case it might look like this:
(a,b,c) := foo(bar);
That might overcome the objection Tom referred to, I guess?
cheers
andrew
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] proposal for 8.3: Simultaneous assignment for PL/pgSQL
Andrew Dunstan wrote: Pavel Stehule wrote: I looked into SQL2003, and SQL2003 knows it (SQL/PSM): [grammar productions] The parentheses are apparently required for multiple variables, so in our case it might look like this: (a,b,c) := foo(bar); That might overcome the objection Tom referred to, I guess? Are we intending to support SQL/PSM with PL/pgSQL? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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] proposal for 8.3: Simultaneous assignment for PL/pgSQL
On 8/8/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Are we intending to support SQL/PSM with PL/pgSQL? I hope not. While PL/pgSQL and SQL/PSM share some similarities, they should be totally separate. IIRC, EnterpriseDB had tried to sponsor someone to write SQL/PSM support for PostgreSQL a little over a year ago and no one wanted to do it. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ecpg test suite
On Tue, Aug 08, 2006 at 09:09:17AM -0400, Tom Lane wrote: * The vulnerability to using a previously installed ecpglib exists in our default Linux configuration as well as HPUX. On my linux box the libs get built with -rpath as well and I think that there's no portable way to remove it once it is in. Doesn't the backend regression test (using psql) suffer from the same problem with libpq? Joachim -- Joachim Wieland [EMAIL PROTECTED] GPG key available ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] An Idea for planner hints
On Tue, Aug 08, 2006 at 01:55:35PM +0200, Florian G. Pflug wrote: Hi Since the discussion about how to force a specific plan has come up, I though I'd post an idea I had for this a while ago. It's not reall well though out yet, but anyway. snip Image a query like select ... from t1 join t2 on t1.t2_id = t2.id and expr. Lets say that expr is true for only 1% of the rows in t2 - but those are exactly the rows that have matching rows in t1. Postgres would probably guess that this join will produce about 1/100 of the rows that t1 has - but I _know_ that it will produce 100 (!) times more rows. ISTM theat the easiest way would be to introduce a sort of predicate like so: SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1); If you teach the optimiser that pg_selectivity always has the selectivity of the second argument, you're done. Other than that you just need to define pg_selectivity as a no-op. One thing though: when people think of selectivity, they think number of rows in foo that have a match in bar whereas selectivity for postgres means chance this expression will be true. They are related but not the same thing. Converting from one to the other will have it's own pitfalls... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] 8.2 features status
For example: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 07/20/06 We could do that, but once an item is done I don't see the point in having the date and person's name. You are right that is clearly a different purpose from the TODO list, and if someone wants to track that, it might help things. The idea of the above is not to track when it is done. THe confirmed is to track that development is taking place and that we have confirmed from the developer that they think it will be done for 8.2. It is something that in theory would update throughout the cycle 3 or 4 times. You could even have: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 04/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Trouble encountered | 06/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Asks for help | 08/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | Alvaro | Confirmed | 09/20/06 Notice the sequence of events. I am not saying the specific statuses are the way to go but it would give a simple way to keep tabs on things without having to create a whole new ball of yarn. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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] ecpg test suite
On 8/2/06, Michael Meskes [EMAIL PROTECTED] wrote: I'm in the process of committing the first version of the ecpg regression test suite to CVS. This is not exactly finished work, but it shows OK on all test on my machine and on Joachim's machine. The tests need to be tweaked some before it's finished, but I'd like to hear about what others are seeing soon enough to be able to fix bugs before 8.2. Just run make check in src/interfaces/ecpg and tell us if there is some test that fails. just fyi: did a cvs update around 12pm est today and am getting a make error: make[4]: Entering directory `/usr/src/pgsql/src/interfaces/ecpg/test' sed -e 's,@bindir@,/usr/local/pgsql/bin,g' \ -e 's,@libdir@,/usr/local/pgsql/lib,g' \ -e 's,@pkglibdir@,/usr/local/pgsql/lib,g' \ -e 's,@datadir@,/usr/local/pgsql/share,g' \ -e 's/@VERSION@/8.2devel/g' \ -e 's/@host_tuple@/i686-pc-linux-gnu/g' \ -e 's,@GMAKE@,make,g' \ -e 's/@enable_shared@/yes/g' \ -e 's/@GCC@/yes/g' \ pg_regress.inc.sh.in pg_regress.inc.sh make -C connect all make: *** connect: No such file or directory. Stop. make: Entering an unknown directorymake: Leaving an unknown directorymake[4]: *** [all] Error 2 make[4]: Leaving directory `/usr/src/pgsql/src/interfaces/ecpg/test' make[3]: *** [all] Error 2 make[3]: Leaving directory `/usr/src/pgsql/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/src/pgsql/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/src/pgsql/src' make: *** [all] Error 2 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] An Idea for planner hints
Martijn van Oosterhout wrote: On Tue, Aug 08, 2006 at 01:55:35PM +0200, Florian G. Pflug wrote: Hi Since the discussion about how to force a specific plan has come up, I though I'd post an idea I had for this a while ago. It's not reall well though out yet, but anyway. snip Image a query like select ... from t1 join t2 on t1.t2_id = t2.id and expr. Lets say that expr is true for only 1% of the rows in t2 - but those are exactly the rows that have matching rows in t1. Postgres would probably guess that this join will produce about 1/100 of the rows that t1 has - but I _know_ that it will produce 100 (!) times more rows. ISTM theat the easiest way would be to introduce a sort of predicate like so: SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1); Ideally, though it needs to be defined upon the table(s) in question, possibly with a WHERE clause as with indexes: CREATE STATISTIC ...defn here... ON invoices (cli_id), clients (id) WHERE invoices.paid = false WITH PRIORITY 100; (I'm thinking the priority so you can delete any rules with a low priority while keeping ones you think are vital) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ecpg test suite
Merlin, On Tue, Aug 08, 2006 at 12:32:05PM -0400, Merlin Moncure wrote: just fyi: did a cvs update around 12pm est today and am getting a make error: make -C connect all make: *** connect: No such file or directory. Stop. make: Entering an unknown directorymake: Leaving an unknown directorymake[4]: *** [all] Error 2 You don't have ecpg/test/connect/ ? http://developer.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/test/connect/ Joachim -- Joachim Wieland [EMAIL PROTECTED] GPG key available ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] standard interfaces for replication providers
In the last exciting episode, [EMAIL PROTECTED] (Markus Schiltknecht) wrote: I'm operating a level deeper with Postgres-R and really enjoy the freedom I have with C. Having to write a hook or trigger for every change in the database systems seems a lot of work, which I tend to postpone until such a thing is really needed. The fact that GORDA is operating as a Java application seems to me to throw a big layer of fuzziness in the way, too. Most databases that are interesting to replicate are implemented in C or C++, thereby implying that a suitably deep API needs to be implemented in C. In the case of PostgresQL, at least, operating in Java means that you need to operate at arms length from the database, which means the replication system is by no means tightly integrated. -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://linuxdatabases.info/info/postgresql.html Know the list of large, chronic problems. If there is any problem with the window system, blame it on the activity system. Any lack of user functionality should be attributed to the lack of a command processor. A suprisingly large number of people will believe that you have thought in depth about the issue to which you are alluding when you do. -- from the Symbolics Guidelines for Sending Mail ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ecpg test suite
On 8/8/06, Joachim Wieland [EMAIL PROTECTED] wrote: Merlin, On Tue, Aug 08, 2006 at 12:32:05PM -0400, Merlin Moncure wrote: just fyi: did a cvs update around 12pm est today and am getting a make error: make -C connect all make: *** connect: No such file or directory. Stop. make: Entering an unknown directorymake: Leaving an unknown directorymake[4]: *** [all] Error 2 You don't have ecpg/test/connect/ ? my fault...needed to to cvs update -d regards, merlin ---(end of broadcast)--- TIP 1: 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] [PATCHES] PL instrumentation plugin support (i.e. PL/pgSQL debugger infrastructure)
[EMAIL PROTECTED] [EMAIL PROTECTED] writes: The attached patch adds support for loadable instrumentation plugins for procedural languages (as discussed at the anniversary summit). It also adds plugin support to the PL/pgSQL language handler. In view of the other patch submitted to support init/fini functions for shared libraries, I'm inclined to change this one to depend on that; in particular it seems like we could eliminate the necessity for users to specify the correct setup-function names. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] PL instrumentation plugin support (i.e. PL/pgSQL
In view of the other patch submitted to support init/fini functions for shared libraries, I'm inclined to change this one to depend on that; in particular it seems like we could eliminate the necessity for users to specify the correct setup-function names. Thoughts? I think that would be great. Can you point me to the patch you're referring to? I can convert my patch if you prefer. -- Korry -- Korry Douglas [EMAIL PROTECTED] EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] 8.2 features status
Joshua D. Drake wrote: For example: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 07/20/06 We could do that, but once an item is done I don't see the point in having the date and person's name. You are right that is clearly a different purpose from the TODO list, and if someone wants to track that, it might help things. The idea of the above is not to track when it is done. THe confirmed is to track that development is taking place and that we have confirmed from the developer that they think it will be done for 8.2. Oh, confirmed confused me. Maybe anticipated or planned for 8.2. It is something that in theory would update throughout the cycle 3 or 4 times. You could even have: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 04/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Trouble encountered | 06/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Asks for help | 08/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | Alvaro | Confirmed | 09/20/06 Notice the sequence of events. I am not saying the specific statuses are the way to go but it would give a simple way to keep tabs on things without having to create a whole new ball of yarn. Interesting idea. If people willing to state they will complete items for the next release, I can add this to the TODO list, and just remove it once the item is in CVS. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] standard interfaces for replication providers
Hello Christopher,
Christopher Browne wrote:
Most databases that are interesting to replicate are implemented in C
or C++, thereby implying that a suitably deep API needs to be
implemented in C.
I generally agree with you. Although it's probably worth mentioning that
the API they propose adds hooks to PostgreSQL in the form of triggers.
This API comes as a patch is against the PostgreSQL source, thus in C.
The nature of triggers in PostgreSQL would then allow to write
replication systems in whatever language you prefer, as long as there is
a PL/{$LANG} for $LANG = your favorite.
I'm questioning if a replication system can be written by only using
triggers as hooks. AFAIK Slony-I uses triggers, so you can probably
better comment on problems or limitations using triggers. For me a
shared library with some hooks as C function calls seems a more
plausible approach.
Regards
Markus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] standard interfaces for replication providers
Markus Schiltknecht wrote: Hi, Jose Orlando Pereira wrote: Sorry, stuff was put twice in the zip file making it somewhat confusing. It is in postgresql-g-0.1/javasrc/GordaInterfaces/docs/gapi.pdf or directly on the web site at http://gorda.di.uminho.pt/download/reports/gapi.pdf. Thank you. I've just had a quick glance at it. Can you point out why is it [limiting], given that it is admittedly quite close? An API is always limiting. And if you have to change the API a lot, to fit your needs, what's the point in using it at all? Good APIs don't change a lot. Even if it's quite close, I estimate the effort to port Postgres-R to use your API to be quite large. I.e. the first missing thing that came to my mind was the ordering of processes when waking them up after waiting for a lock. Postgres-R needs the processes to be woken up in the order of writeset arrival. Now, I didn't see anything related in the patch, but the gapi.pdf has 'Predictable Deadlock Handling' in it. I need to take another look... If I correctly understood your idea, a priority mechanism would be enough to do so and different applications might exploit it. Most likely, we need this to apply remote transactions. However, note that a priority mechanism is not only of interesting in the field of replication systems but it might be used to improve performance for instance. Take a look at the ideas presented in http://www.cs.cmu.edu/~bianca/icde04.pdf Unfortunately, our current prototype only provides two levels: high priority or normal priority. Definitely, it should be improved and we are aware of that. We'd rather discuss specific issues instead of the general topic of whether to build APIs around them. We certainly are not married to the proposed interfaces, although the functionality they capture does reflect our experience with several algorithms. I still feel that I would need ways too many hooks. Especially when you consider advanced replication features such as data partitioning and remote query execution. What also worries me is the use of triggers. ISTM that using triggers is not deep enough in the database. In the above example, do I really want to fire a trigger every time the database needs to wake up a process? In PostgreSQL a trigger normally runs within a transaction. How do you work around that? I think we are talking about different levels as I said a high priority mechanism would be enough. In this case, the API should provide only an interface to set the priority of a transaction In our case, still unfinished and quite simple: set transaction master but it could be easily transformed into set transaction priority n. Best regards, Alfranio Junior. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] standard interfaces for replication providers
Markus Schiltknecht wrote: I'm questioning if a replication system can be written by only using triggers as hooks. AFAIK Slony-I uses triggers, so you can probably better comment on problems or limitations using triggers. For me a shared library with some hooks as C function calls seems a more plausible approach. Of course not... It is impossible to build a replication system entirely by only using triggers... But definitely, there is a set of common requirements among a variety of replication systems. Moreover, such requirements are also useful to other systems as well. Roughly, the GAPI reflects any event inside a database system and allows any application to intercept and modify them. For instance, an event might be: 1 - database shutdown, startup 2 - connection shutdown, startup 3 - statement reception 4 - parsing 5 - execution plan generation 6 - tuples written Our current prototype covers some of them and some of them partially (2,6). Besides, we also need the priority mechanism. Best regards, Alfranio Junior. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 features status
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Bruce Momjian) wrote: Joshua D. Drake wrote: For example: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 07/20/06 We could do that, but once an item is done I don't see the point in having the date and person's name. You are right that is clearly a different purpose from the TODO list, and if someone wants to track that, it might help things. The idea of the above is not to track when it is done. THe confirmed is to track that development is taking place and that we have confirmed from the developer that they think it will be done for 8.2. Oh, confirmed confused me. Maybe anticipated or planned for 8.2. It is something that in theory would update throughout the cycle 3 or 4 times. You could even have: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Confirmed for 8.2 | 04/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Trouble encountered | 06/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | PeterE | Asks for help | 08/20/06 Make postmater and postgres options distinct so the postmaster -o option is no longer needed | Alvaro | Confirmed | 09/20/06 Notice the sequence of events. I am not saying the specific statuses are the way to go but it would give a simple way to keep tabs on things without having to create a whole new ball of yarn. Interesting idea. If people willing to state they will complete items for the next release, I can add this to the TODO list, and just remove it once the item is in CVS. Is it forcibly necessary to have that commitment in order for this to be of some use? It seems to me that this would be a reasonably useful way of tracking the progress of TODO items irrespective of any particular commitment to completion in sync with a version. -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://linuxdatabases.info/info/languages.html When aiming for the common denominator, be prepared for the occasional division by zero. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
Christopher Browne wrote: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | Alvaro | Confirmed | 09/20/06 Notice the sequence of events. I am not saying the specific statuses are the way to go but it would give a simple way to keep tabs on things without having to create a whole new ball of yarn. Interesting idea. If people willing to state they will complete items for the next release, I can add this to the TODO list, and just remove it once the item is in CVS. Is it forcibly necessary to have that commitment in order for this to be of some use? It seems to me that this would be a reasonably useful way of tracking the progress of TODO items irrespective of any particular commitment to completion in sync with a version. The problem comes with someone starting to work on something, then giving up, but if you record it, people think they are still working on it. What happens now is that someone says they want to work on X, and the community tells them that Y might be working on it, and Y gives us a status. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] proposal for PL packages for 8.3.
Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: Are you saying that the package would effectively *be* a schema from the outside. That is, if I have package foo then I can't also have a schema foo? Yes, because I don't need duplicity in function's names. What if the package needs some tables associated with it? I think you need to think harder about the relationship of packages and schemas. I don't necessarily object to merging the concepts like this, but the implications look a bit messy at first sight. I like the idea of a package being a schema. I imagine that a package would put its own schema name first in the 'search_path' before referencing an object. I think anything more complex is going to be too hard to use. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] buildfarm - make check failures for leveret on 8.0
Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: Plus if it is backported, I can enable 8.x builds on mongoose (my x86 icc buildfarm box). Please do --- I've applied the changes in 8.1 and 8.0 branches. and leveret went green on both 8.0 and 8.1 ... Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgsql: Add detail on packages: A package would be a
Bruce Momjian wrote: A package would be a schema with its own variables, private functions, and initialization functions What are the intended scope and lifetime of package private variables? This could be very cool and useful, but it could also be a major can of worms. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Patch to allow C extension modules to initialize/finish
Ralf S. Engelschall [EMAIL PROTECTED] writes: Hence I propose the patch below (applies to PostgreSQL 8.1.4) which mimics the dlopen(3) and dlclose(3) behaviour of some Unix platforms and resolves and calls _PG_init and _PG_fini functions of an extension module right after/before the pg_dlopen/pg_dlclose calls in the FMGR. Patch applied, with consequent changes to simplify preload_libraries feature in favor of using _PG_init(). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] buildfarm - make check failures for leveret on 8.0
Stefan Kaltenbrunner wrote: Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: Plus if it is backported, I can enable 8.x builds on mongoose (my x86 icc buildfarm box). Please do --- I've applied the changes in 8.1 and 8.0 branches. and leveret went green on both 8.0 and 8.1 ... Good. Now we need to clean up the huge number of warnings, such as: /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE/proto.h(95): warning #1292: attribute warn_unused_result ignored __attribute__warn_unused_result__; ^ and pg_restore.c(332): warning #188: enumerated type mixed with another type AH = OpenArchive(inputFileSpec, opts-format); ^ cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] buildfarm - make check failures for leveret on 8.0
Andrew Dunstan wrote: Stefan Kaltenbrunner wrote: Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: Plus if it is backported, I can enable 8.x builds on mongoose (my x86 icc buildfarm box). Please do --- I've applied the changes in 8.1 and 8.0 branches. and leveret went green on both 8.0 and 8.1 ... Good. Now we need to clean up the huge number of warnings, such as: /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE/proto.h(95): warning #1292: attribute warn_unused_result ignored __attribute__warn_unused_result__; ^ and pg_restore.c(332): warning #188: enumerated type mixed with another type AH = OpenArchive(inputFileSpec, opts-format); well a large number of those look a bit bogus(annoying) - and icc has ways to disable individual warnings (indicated by the number following the #) like: -wdL1[,L2,...LN] Disable diagnostics L1 through LN. maybe we should use that(ftp://download.intel.com/support/performancetools/c/linux/v9/icc.txt has the full manpage)? Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
[EMAIL PROTECTED] (Bruce Momjian) writes: Christopher Browne wrote: Make postmater and postgres options distinct so the postmaster -o option is no longer needed | Alvaro | Confirmed | 09/20/06 Notice the sequence of events. I am not saying the specific statuses are the way to go but it would give a simple way to keep tabs on things without having to create a whole new ball of yarn. Interesting idea. If people willing to state they will complete items for the next release, I can add this to the TODO list, and just remove it once the item is in CVS. Is it forcibly necessary to have that commitment in order for this to be of some use? It seems to me that this would be a reasonably useful way of tracking the progress of TODO items irrespective of any particular commitment to completion in sync with a version. The problem comes with someone starting to work on something, then giving up, but if you record it, people think they are still working on it. If there is some form of last updated on date, that seems to me to be quite sufficient for the purpose. If the person last working on it hasn't reported any new news on the item in some substantial period of time, that's a good implicit indication that something is stalled. What happens now is that someone says they want to work on X, and the community tells them that Y might be working on it, and Y gives us a status. If what we see in the todo is... Implement hierarchical queries using ANSI WITH/recursive query system | Someone | Under way | [some date six months ago] ... then those that are interested in seeing this go in can probably guess that the effort has stalled in that nothing has been worth commenting on in six months. This sort of thing is suggestive of having some sort of systematic way to store structured information. Perhaps one could implement some sort of database for it... :-) -- output = (cbbrowne @ acm.org) http://cbbrowne.com/info/sgml.html Rules of the Evil Overlord #21. I will hire a talented fashion designer to create original uniforms for my Legions of Terror, as opposed to some cheap knock-offs that make them look like Nazi stormtroopers, Roman footsoldiers, or savage Mongol hordes. All were eventually defeated and I want my troops to have a more positive mind-set. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] proposal for 8.3: Simultaneous assignment for PL/pgSQL
Andrew Dunstan [EMAIL PROTECTED] writes:
Pavel Stehule wrote:
I looked into SQL2003, and SQL2003 knows it (SQL/PSM):
assignment statement ::=
singleton variable assignment
| multiple variable assignment
multiple variable assignment ::=
SET assignment target list equals operator assigned row
assignment target list ::=
left paren assignment target [ { comma assignment target
}... ] right paren
singleton variable assignment ::=
SET assignment target equals operator assignment source
The parentheses are apparently required for multiple variables, so in
our case it might look like this:
(a,b,c) := foo(bar);
More to the point, a SET keyword is required too by that standard.
I concur with the other comment that plpgql is intended to mimic
Oracle PL/SQL, not SQL/PSM. If we try to follow two different leads
we are likely to find ourselves with a mess.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] An Idea for planner hints
Martijn van Oosterhout kleptog@svana.org writes: ISTM theat the easiest way would be to introduce a sort of predicate like so: SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1); The one saving grace of Florian's proposal was that you could go hack the statistics *without* changing your queries. This throws that away again. The thing I object to about the I want to decorate my queries with planner hints mindset is that it's coming at it from the wrong direction. You should never be thinking in terms of fix this one query, because that just leads back into the same dead end that your fix doesn't work tomorrow. What you *should* be thinking about is why did the planner get this wrong, and how do I fix the generic problem?. If you attack it that way then your fix is much more likely to work on the next slightly-different query. So some kind of override for statistical guesses doesn't seem completely silly to me. But it needs to be declarative information that's stored somewhere out of view of the actual SQL queries. IMHO anyway. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] An Idea for planner hints
On Tue, Aug 08, 2006 at 04:14:45PM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: ISTM theat the easiest way would be to introduce a sort of predicate like so: SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1); The one saving grace of Florian's proposal was that you could go hack the statistics *without* changing your queries. This throws that away again. Well, that true. I was thinking of the easy way. To run with something suggested in this thread, do you think it would be more reasonable to be able to provide statistics information for joins, which currently we have no grip on at all. Something like: CREATE STATISTIC foo ON table1 a, table2 b WHERE a.x = b.x AS SELECTIVITY 0.1; The idea being that if the planner see those tables being joined on those fields, that it will do its guess on the number of rows, but caps the selectivity to less than 0.1. My main problem is that selectivity is the wrong measurement. What users really want to be able to communicate is: 1. If you join tables a and b on x, the number of resulting rows will be the number of roows selected from b (since b.x id a foreign key referencing a.x). 2. That on average there is a N:1 ratio of results between a.x and b.x. So if you take a value of a.x and look it up in b, on average you'll get N results. This can be a valid measurement for any two columns, not just ones related by a foreign key. For either of those, selectivity is the wrong variable, but I'll be damned if I can think of a better way of expressing it... The interesting case would be joins across a number of tables and be able to tell the planner information about that, but that's an even harder problem. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] 8.2 features status
If what we see in the todo is... Implement hierarchical queries using ANSI WITH/recursive query system | Someone | Under way | [some date six months ago] ... then those that are interested in seeing this go in can probably guess that the effort has stalled in that nothing has been worth commenting on in six months. This sort of thing is suggestive of having some sort of systematic way to store structured information. Perhaps one could implement some sort of database for it... :-) Mysql should be able to handle something like that nicely. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
Bruce, What happens now is that someone says they want to work on X, and the community tells them that Y might be working on it, and Y gives us a status. What happens now is: A starts working on X. 3 months pass B comes to hackers, spends hours reading the archives, doesn't find X (because they know it by a different name), comes to -hackers and asks Is anyone working on X? B waits for 2 weeks without an answer and repeats the question. Hackers E, F and G reply yes, someone is but I don't remember who, search the archives for keyword X B searches again, finds original post. B e-mails A and gets no response. B finally offers to take over X Hackers M, L, and N say sure, but read the archives for spec info B reads more archives for several hours. There's a LOT of unnecessary overhead in that process: having a simple web app that lists who claimed what todo and when, any status updates if they've voluntarily provided them, and links to archive discussions, we could reduce the above to a 3-step process making it vastly easier for new hackers to get started. To be clear: I'm not trying to solve a problem for existing hackers, for whom the existing system works fine. I'm trying to solve a problem for two groups: new hackers, and users who want to check the plans for new features without combing through the archives. I'll also point out that having an annotated TODO with regular updates would lessen the pressure we get from some parties for a roadmap. --Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] standard interfaces for replication providers
On Tuesday 08 August 2006 17:44, Christopher Browne wrote: Most databases that are interesting to replicate are implemented in C or C++, thereby implying that a suitably deep API needs to be implemented in C. In the case of PostgresQL, at least, operating in Java means that you need to operate at arms length from the database, which means the replication system is by no means tightly integrated. Yes, PostgreSQL is the tough one here. Having a single-process multithreaded PostgreSQL in which PL/Java would run in one global JVM would sure feel like Christmas! ;) Note however that the problem is not Java, but any package that does not expect the current PostgreSQL concurrency model. AFAIK Postgres-R has same arms length architecture with an external process, probably for compatibility with Spread. We have however tried to minimize the inconvenience, even for PostgreSQL, by doing the following: 1. Implementation was done in two layers, in which the PostgreSQL-specific one is 100% Java-free and feature complete. 2. High level functionality (i.e. transaction priorities instead of directly handling individual lock operations) reduce the number of round-trips to the replication process. 3. Event listeners in Java can be registered as non-blocking, thus putting the external JVM out of the critical path except in a few critical operations. Finally, although I concede that most databases that are interesting to replicate are written in C or C++, these days, most are also getting tightly coupled JVMs fairly high in their feature lists. -- Jose Orlando Pereira ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] standard interfaces for replication providers
On Tuesday 08 August 2006 15:24, Markus Schiltknecht wrote: An API is always limiting. Which is a good thing when you are not the one using it but the one committing to support it. :-) I still feel that I would need ways too many hooks. Especially when you consider advanced replication features such as data partitioning and remote query execution. Indeed. We have not prototyped those features. Nonetheless, look at the silly query cache example in the distribution (search for QueryCache.java). It shows how the proposed hooks might be used to intercept a query and fake a result set, while at the same time executing some stuff locally. (Warning: this runs on Apache Derby only, as in PostgreSQL we'd need something like PL/J for server side JDBC.). What also worries me is the use of triggers. ISTM that using triggers is not deep enough in the database. In the above example, do I really want to fire a trigger every time the database needs to wake up a process? In PostgreSQL a trigger normally runs within a transaction. How do you work around that? As Alfranio has pointed out in another message in this thread, these triggers are high level. We never consider some thing trigger on lock acquire (also because it also would hardly be portable). They certainly are more coarse grained than the standard on update stuff. Furthermore, having on commit triggers running within transactional boundaries is very useful. Think about recording global commit order or global timestamps in the originating site after propagation. -- Jose Orlando Pereira ---(end of broadcast)--- TIP 1: 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] proposal for 8.3: Simultaneous assignment for PL/pgSQL
Tom, I concur with the other comment that plpgql is intended to mimic Oracle PL/SQL, not SQL/PSM. If we try to follow two different leads we are likely to find ourselves with a mess. Well, the proposed functionality would be extremely useful in making PL/pgSQL a more robust language. So can we find a syntax that is unambiguously assignment? To be honest, I'm unclear on what's wrong with Pavel's suggested syntax. --Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
On Aug 8, 2006, at 17:47 , Josh Berkus wrote: What happens now is: A starts working on X. 3 months pass B comes to hackers, spends hours reading the archives, doesn't find X (because they know it by a different name), comes to -hackers and asks Is anyone working on X? B waits for 2 weeks without an answer and repeats the question. Hackers E, F and G reply yes, someone is but I don't remember who, search the archives for keyword X B searches again, finds original post. B e-mails A and gets no response. B finally offers to take over X Hackers M, L, and N say sure, but read the archives for spec info B reads more archives for several hours. There's a LOT of unnecessary overhead in that process: having a simple web app that lists who claimed what todo and when, any status updates if they've voluntarily provided them, and links to archive discussions, we could reduce the above to a 3-step process making it vastly easier for new hackers to get started. A developers' wiki with links into the list archives would be great. -M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
Josh Berkus wrote: Bruce, What happens now is that someone says they want to work on X, and the community tells them that Y might be working on it, and Y gives us a status. What happens now is: A starts working on X. 3 months pass B comes to hackers, spends hours reading the archives, doesn't find X (because they know it by a different name), comes to -hackers and asks Is anyone working on X? B waits for 2 weeks without an answer and repeats the question. Hackers E, F and G reply yes, someone is but I don't remember who, search the archives for keyword X I would bet, right about here we loose a whole lot of would be contributors. Just the the questions I had about two todos this year was enough basically give up on doing any work on them. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] proposal for PL packages for 8.3.
Tom, I'm confused. I thought the consensus was that we'd get package functionality via SQL99 TYPEs, rather than by implementing oracle-copycat syntax. --Josh ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] proposal for PL packages for 8.3.
Bruce, I like the idea of a package being a schema. I imagine that a package would put its own schema name first in the 'search_path' before referencing an object. I think anything more complex is going to be too hard to use. Or we could just add local variables to schema and dispense with PACKAGES entirely. --Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
OK, seems this should be a separate application, not done in the TODO list, and I am not willing to take on that additional workload. --- Josh Berkus wrote: Bruce, What happens now is that someone says they want to work on X, and the community tells them that Y might be working on it, and Y gives us a status. What happens now is: A starts working on X. 3 months pass B comes to hackers, spends hours reading the archives, doesn't find X (because they know it by a different name), comes to -hackers and asks Is anyone working on X? B waits for 2 weeks without an answer and repeats the question. Hackers E, F and G reply yes, someone is but I don't remember who, search the archives for keyword X B searches again, finds original post. B e-mails A and gets no response. B finally offers to take over X Hackers M, L, and N say sure, but read the archives for spec info B reads more archives for several hours. There's a LOT of unnecessary overhead in that process: having a simple web app that lists who claimed what todo and when, any status updates if they've voluntarily provided them, and links to archive discussions, we could reduce the above to a 3-step process making it vastly easier for new hackers to get started. To be clear: I'm not trying to solve a problem for existing hackers, for whom the existing system works fine. I'm trying to solve a problem for two groups: new hackers, and users who want to check the plans for new features without combing through the archives. I'll also point out that having an annotated TODO with regular updates would lessen the pressure we get from some parties for a roadmap. --Josh -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
Bruce, OK, seems this should be a separate application, not done in the TODO list, and I am not willing to take on that additional workload. That's my feeling. But I think that we have enough people who are interested to maintain it. If we don't, there was no point anyway. --Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.2 features status
bruce wrote: OK, seems this should be a separate application, not done in the TODO list, and I am not willing to take on that additional workload. Let me add that anyone who has CVS commit access or wants to submit TODO patches can keep the TODO updated in this way. --- --- Josh Berkus wrote: Bruce, What happens now is that someone says they want to work on X, and the community tells them that Y might be working on it, and Y gives us a status. What happens now is: A starts working on X. 3 months pass B comes to hackers, spends hours reading the archives, doesn't find X (because they know it by a different name), comes to -hackers and asks Is anyone working on X? B waits for 2 weeks without an answer and repeats the question. Hackers E, F and G reply yes, someone is but I don't remember who, search the archives for keyword X B searches again, finds original post. B e-mails A and gets no response. B finally offers to take over X Hackers M, L, and N say sure, but read the archives for spec info B reads more archives for several hours. There's a LOT of unnecessary overhead in that process: having a simple web app that lists who claimed what todo and when, any status updates if they've voluntarily provided them, and links to archive discussions, we could reduce the above to a 3-step process making it vastly easier for new hackers to get started. To be clear: I'm not trying to solve a problem for existing hackers, for whom the existing system works fine. I'm trying to solve a problem for two groups: new hackers, and users who want to check the plans for new features without combing through the archives. I'll also point out that having an annotated TODO with regular updates would lessen the pressure we get from some parties for a roadmap. --Josh -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] proposal for PL packages for 8.3.
Josh Berkus wrote: Bruce, I like the idea of a package being a schema. I imagine that a package would put its own schema name first in the 'search_path' before referencing an object. I think anything more complex is going to be too hard to use. Or we could just add local variables to schema and dispense with PACKAGES entirely. Sure, makes more sense to me. I don't think people want Oracle syntax as much as Oracle packages capabilities. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
Josh Berkus wrote: Bruce, OK, seems this should be a separate application, not done in the TODO list, and I am not willing to take on that additional workload. That's my feeling. But I think that we have enough people who are interested to maintain it. If we don't, there was no point anyway. /me raises his hand .. I already have a wiki I use to help maintain the php.net semi official release todo list: http://oss.backendmedia.com/PHPTODO/ But its running on MySQL .. However since it was easy for me to add a subwiki [1] I just did that and gave the world read/write access. I am sure someone else will soon step up and provide something nicer running on PostgreSQL :) regards, Lukas [1] http://oss.backendmedia.com/PGSQLTODO/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 features status
Lukas Smith wrote: Josh Berkus wrote: OK, seems this should be a separate application, not done in the TODO list, and I am not willing to take on that additional workload. That's my feeling. But I think that we have enough people who are interested to maintain it. If we don't, there was no point anyway. /me raises his hand .. I'd vote for a Trac site. I've found it to be a rather useful tool in general, though a bit too simple-minded; integrated Wiki, a simple bugtracker, and roadmap-style reports for people who cares about such stuff. I don't think we'd use the SCM module though. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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] proposal for PL packages for 8.3.
- Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Josh Berkus josh@agliodbs.com Cc: Tom Lane [EMAIL PROTECTED]; Pavel Stehule [EMAIL PROTECTED]; dev@archonet.com; pgsql-hackers@postgresql.org Sent: Wednesday, August 09, 2006 1:49 AM Subject: Re: [HACKERS] proposal for PL packages for 8.3. Or we could just add local variables to schema and dispense with PACKAGES entirely. Sure, makes more sense to me. I don't think people want Oracle syntax as much as Oracle packages capabilities. Is it would be nice , if packages have been ; 1. Package level variables (Public variables) 2. Package member level variables (Private variable) 3. Public and private package members 4. Syntax must be as closer as plpgsql (declaration, assingment etc) rather than any syntax that we have to learn :-) Best regards Adnan DURSUN ASRIN Bilisim Ltd. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
I'd vote for a Trac site. I've found it to be a rather useful tool in general, though a bit too simple-minded; integrated Wiki, a simple bugtracker, and roadmap-style reports for people who cares about such stuff. I don't think we'd use the SCM module though. Oddly enough if anything we could use the SCM module for viewing/changest etc... I already have it regenerating itself over at http://projects.commandprompt.com/public/pgsql Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: 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] 8.2 features status
Can you guys conceive of the thousands of hours of chat you guys are racking upinstead of real hacking because you have an inadequate working structure? This is by far the chattiest and least worthwhile listserv in the bsd world. Bar none. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.7/411 - Release Date: 8/7/2006 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2 features status
Joshua D. Drake wrote: I don't think we'd use the SCM module though. Oddly enough if anything we could use the SCM module for viewing/changest etc... I already have it regenerating itself over at http://projects.commandprompt.com/public/pgsql I've found that repository view to be broken at certain spots. I'm not sure if the problem is in cvs2svn or Trac itself. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Patch to allow C extension modules to initialize/finish
On Tue, Aug 08, 2006, Tom Lane wrote: Ralf S. Engelschall [EMAIL PROTECTED] writes: Hence I propose the patch below (applies to PostgreSQL 8.1.4) which mimics the dlopen(3) and dlclose(3) behaviour of some Unix platforms and resolves and calls _PG_init and _PG_fini functions of an extension module right after/before the pg_dlopen/pg_dlclose calls in the FMGR. Patch applied, with consequent changes to simplify preload_libraries feature in favor of using _PG_init(). Thanks. Ralf S. Engelschall [EMAIL PROTECTED] www.engelschall.com ---(end of broadcast)--- TIP 1: 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] PostgreSQL performance enhancement when query planner
Csaba Nagy wrote: Constantin, What binding are you using ? We here use Java+JDBC, and we were able to get stable query plans by forcing server side prepared statements (using PGStatement#setPrepareThreshold with 1 as the threshold), where the query is prepared without knowing the parameter values. This can backfire too, but for our purposes it was the right thing (probably sacrificing some performance, but getting a stable system). The plans in this case are made to work with guessed mean values for the estimates, and that's usually resulting in a stable plan, so once you got it right it will stay like that. We have tried PGStatement#setPrepareThreshold with 1 as the threshold but it's not a good solution. Actually is worst. Considering that you have 5 different query plans, you are selecting approx. random one of them, not taking into account the statistics. The situation is simpler than it's at the first view. Guessing what is the best plan, based on statistics and costs, IS NOT A EASY THING TO DO. Tweaking costs and statistics CAN TAKE A VERY LONG TIME and need strong knowledge about database architecture, hardware performances and many other things. Not every average user of PostgreSQL can do that! Experimenting the first 3 or 4 query plans in the descending order of their estimated cost, IS SIMPLER and it can take less than an hour and can be done by less experimented people. Choosing the proved better query plan IS SIMPLER and that means PERFORMANCE EVEN FOR THE AVERAGE USER. We are talking about open-source, free-source and the freedom of choice, isn't it? So, why not give the user the freedom of choosing a different query plan that will give a better performances. Maybe I'm not interested in developing WHY the query planner is choosing wrong. Of course , the developers will enhance it but until then, let's give the user the power of manually selecting the right query plan. The final result may be something like that : I heard that PostgreSQL has a very handy tool that gives you a better performance in queries. It gives you the ability to make fine adjustments. Sound good, isn't it ? :-) Teo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Casts
It seems odd to me that implicit casts are checked for when you call a function but not when you're implicitly calling a function via a cast. As a result there are a *lot* of redundant casts in our catalog, essentially n! casts for a domain with n types in it. So for example there are 138 casts between the various numeric data types including every possible pairing of char, int2, int4, int8, float4, float8, and numeric. Now I don't think it actually costs us anything to have so many casts but it sure makes adding new fully functional user defined types a pain. Adding a single new numeric data type requires creating 12 new casts and a second one would require 14, etc. What's strange is that you do not have to go to such lengths to get a fully functional data type in other respects. One implicit cast to numeric and you can use +, -, log(), exp(), floor(), ceil(), etc. You may want to implement some of those for performance reasons but for most relying on the implicit cast is perfectly reasonable. It seems like what ought to happen is that every data domain should have a single blessed data type that is the root data type for that domain. Every data type in that domain should have a single implicit cast to that root data type. That effectively is how all the data types are set up in fact. They have all these dozens of assignment casts and a single implicit cast to a type chosen in some sort of unspoken consensus. There has been some fear expressed in the past that too many implicit casts create surprising side effects. I think that's a valid fear but only relevant if we have two or more such casts for a single data type or have a cast to an inappropriate type. As long as we have precisely one implicit cast for every type and it's a cast to a datatype with basically the same semantics it seems like we should be safe. So for example if all the numeric data types had an implicit cast to numeric and an assignment cast from numeric it ought to be possible for the planner to find a way to handle an explicit cast between any two arbitrary numeric types using just those. It could use the same logic it uses to find functions by looking first for an exact match, then any assignment cast from a data type to which it can implicitly cast to first. That would let people add a new fully functional numeric type by creating only two casts instead of 12. And a second one by creating two more instead of 14, and so on. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 features status
Alvaro Herrera wrote: Joshua D. Drake wrote: I don't think we'd use the SCM module though. Oddly enough if anything we could use the SCM module for viewing/changest etc... I already have it regenerating itself over at http://projects.commandprompt.com/public/pgsql I've found that repository view to be broken at certain spots. I'm not sure if the problem is in cvs2svn or Trac itself. Likely cvs2svn I would guess it is a large repository. I wouldn't expect it to be used instead of CVS but I could see it being useful for reference from a ticket or todo or something. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] An Idea for planner hints
Tom Lane wrote: The thing I object to about the I want to decorate my queries with planner hints mindset is that it's coming at it from the wrong direction. You should never be thinking in terms of fix this one query, because that just leads back into the same dead end that your fix doesn't work tomorrow. What you *should* be thinking about is why did the planner get this wrong, and how do I fix the generic problem?. If you attack it that way then your fix is much more likely to work on the next slightly-different query. So some kind of override for statistical guesses doesn't seem completely silly to me. But it needs to be declarative information that's stored somewhere out of view of the actual SQL queries. IMHO anyway. regards, tom lane Imagine a join between two tables: select a.x, b.y where a.x = f(b.y) from a, b; I may know that, given the data I've put into the tables, only one value in b will ever match one value in a. Or perhaps I know that no more than ten rows in b will match a given value in a. But how can the statistics from ANALYZE ever see through arbitrary math functions to know this sort of thing? The current analyze functionality, as I understand it, can store information about a given table, but not about the relationships between the data in several tables, which is the information the planner would need to choose the right plan. Do all the requests from postgres users for giving hints to the planner involve this type of situation, where the hints are not about a single table, but rather about the relationship between two or more tables and specific joins between them? Do I understand correctly? Is this a reasonable analysis? mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Casts
stark [EMAIL PROTECTED] writes: It seems odd to me that implicit casts are checked for when you call a function but not when you're implicitly calling a function via a cast. As a result there are a *lot* of redundant casts in our catalog, essentially n! casts for a domain with n types in it. So for example there are 138 casts between the various numeric data types including every possible pairing of char, int2, int4, int8, float4, float8, and numeric. This is intentional. If you explicitly cast type foo to type bar there should not be any question about what function will be invoked. The cost is a few more rows in pg_cast ... so what? Adding rows to pg_cast is not the most painful part of making a new datatype. As for the parser ought to be able to find two-step cast pathways, no thanks. The increase in search time and the decrease in predictability are both undesirable. There has been some fear expressed in the past that too many implicit casts create surprising side effects. Not some fear ... we have seen people badly burned, time and time again, by the ill-considered implicit casts that are already in there. IMHO we need fewer implicit casts, not more. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] An Idea for planner hints
Martijn van Oosterhout kleptog@svana.org writes: My main problem is that selectivity is the wrong measurement. What users really want to be able to communicate is: 1. If you join tables a and b on x, the number of resulting rows will be the number of roows selected from b (since b.x id a foreign key referencing a.x). FWIW, I believe the planner already gets that case right, because a.x will be unique and it should know that. (Maybe not if the FK is across a multi-column key, but in principle it should get it right.) I agree though that meta-knowledge like this is important, and that standard SQL frequently doesn't provide any adequate way to declare it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
bruce wrote: bruce wrote: OK, seems this should be a separate application, not done in the TODO list, and I am not willing to take on that additional workload. Let me add that anyone who has CVS commit access or wants to submit TODO patches can keep the TODO updated in this way. I can also give someone ssh access to my server with the ability to modify only the TODO list. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] proposal for PL packages for 8.3.
Is it would be nice , if packages have been ; 1. Package level variables (Public variables) is very hard for imlementation, and it's actually impossible. Needs large changes in code 2. Package member level variables (Private variable) I plan it, in every PL language 3. Public and private package members ?? I see sence only for functions. I don't wont supply schemas. 4. Syntax must be as closer as plpgsql (declaration, assingment etc) rather than any syntax that we have to learn :-) PostgreSQL support other languages than PL/pgSQL. We need universal syntax for plperl and others too Pavel _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
