Thread: Good Optimization
Hi, Bruce, I've only just picked up the thread on optimizations (I get the digest for the pgsql-sql list). I really feel that a lot of effort could be saved with some good benefits if a stored proc mechanism is put into place. Once that has been done, it can be used to store temporary plans (procedures) for ad-hoc queries which are released on the termination of the connection. However, I think that a lot of users will develop stored procs to replace a lot of their existing common SQL, in order to (partially) optimize their systems. Perhaps in the PREPARE statement we could add a facility to allow the user to specify the TTL of the cached proc for an ad-hoc query. When I talk about stored procs, I don't mean functions. We already have those. I mean procedures that are able to return a rowset. Just to make sure nobody gets the wrong idea. And it's another feature on the marketing list. I know that marketing is definitely not what drives the developers, but there are some of us who are really pushing for PostgreSQL to be used in production systems, and when comparisons are done, stored procs is a biggy, even though I'm sure that most of the people who ask the question are not even really sure what a stored proc is. They just like to use it in order to perform feature comparison. However, in a system that uses stored procs, of course, conversion to PG is out, because we just don't have them. Remember that from a design position, there is an argument that all data should be accessed through stored procs (except very ad-hoc stuff), in order to abstract the implementation a further level. Whether or not this works, or whether it's desirable in a design is another question which I'm not attempting to answer. However, there are a lot of systems out there that have them. Thoughts... Also, has anything happened about the idea to get PG to cluster (somebody mentioned Beowulf)? -------------------------------------------------------------------- Science is the game we play with God to find out what his rules are. -------------------------------------------------------------------- [(LI)U]NIX IS user friendly; it's just picky about who its friends are.
Hi, I'm developing on PostgreSQL. The lack of stored procedures which return a rowset is a major disadvantage for PostgreSQL. Completely aside from the performance issue are application security and the architecture of the information system. I'll give an example so I'm what I'm talking about. :-) Based on the database user's identity I chose which rowset to return. We can't allow someone to peek at someone else's data. (They may be a few more steps before I decide which rowset to return.) The alternatives are not pretty. 1. Allow the client to chose which rowset is returned. This is means no security so its not really an option. 2. Create a database function which given the primary key of a table returns true or false for whether the current usermay return this row. Use this function in a view. Massively inefficient. 3. Adopt a 3-tier architecture for all parts of the system. Right down to the most trivial data entry and reporting. Sonow we have an unnecessarily complex system. I.e. it will take longer, cost more and have more bugs. Of course theywon't be my bugs. ;-) I have a small question where do I get started hacking PostgreSQL? I'll have plenty of time to get started round about September. In the meantime what homework do you suggest? Clustering!? Can we have a transaction log first? Regards John Ridout. P.S. Send me your tips for programming PostgreSQL. So far I have received hints and tips for Query optimization. Passing more than 8 arguments to a function.
> I have a small question where do I get started hacking PostgreSQL? > I'll have plenty of time to get started round about September. See the FAQ. Read developers FAQ, flowchard, and developers manual. > In the meantime what homework do you suggest? > Clustering!? Can we have a transaction log first? > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
[Charset iso-8859-1 unsupported, filtering to ASCII...] > Hi, Bruce, > > I've only just picked up the thread on optimizations (I get the digest for > the pgsql-sql list). I really feel that a lot of effort could be saved with > some good benefits if a stored proc mechanism is put into place. Once that > has been done, it can be used to store temporary plans (procedures) for > ad-hoc queries which are released on the termination of the connection. > However, I think that a lot of users will develop stored procs to replace a > lot of their existing common SQL, in order to (partially) optimize their > systems. Perhaps in the PREPARE statement we could add a facility to allow > the user to specify the TTL of the cached proc for an ad-hoc query. > > When I talk about stored procs, I don't mean functions. We already have > those. I mean procedures that are able to return a rowset. Just to make > sure nobody gets the wrong idea. Not sure why our functions can't return tuples. > Also, has anything happened about the idea to get PG to cluster (somebody > mentioned Beowulf)? No one has mentioned this. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026