On Jan 20, 2008 12:04 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
This seems like a fallacious claim as MySQL only introduced procedures in 5.0 and their implementation was incomplete and has been kind of incremental since 5.0 and still isn't complete, whereas plpgsql in Postgresql is a well advanced implementation that works very well (Other than speed). Given that it is also possible to implement functions in Perl, C, Java and Python, it seems that you can achieve pretty much anything with a function in Postgresql, which is not true in Oracle or MySQL. Oracle makes extensive use of views and sql files, but not so much stored procs in the core distro if I remember rightly, certainly not 99%.
True compilation is necessary for all cases if you care about scalability, which ultimately everybody does as they will continue to run more and more sites/databases on a set of servers until the CPU/IO limit is reached - it's called business - you maximize resources. Plpgsql's lack of compilation dramatically lowers that threshold, which means smaller profits for hosters of Postgresql and serious limits on OLTP scalability when plpgsql functions are utilized. Plus do you really want your hosted people writing functions in C for your Postgresql? Hell, _I_ don't want to write functions in C for Postgresql much, plpgsql is much less error prone and much easier to deal with.
If I had to ask the Postgresql people to put one thing on the wish list for the next major release it would be compiled functions, it's the one thing my apps would benefit the most from as I like keeping data integrity, and functions help me achieve that with elegance (I could tell you some stories about data integrity with data feeds from other companies who clearly didn't actually hire a DBA to design their database implementation, every row in it's own transaction because you never know when it's going to violate foreign key constraints, totally sucktastic). (The other thing would be cache management, I know that my system would benefit hugely from being me being able to direct certain tables to remain in memory regardless of MRU data - It seems like something might be possible with RAM disks but how do you sync it back to physical disk in a reliable way so that when your machine dies your data doesn't buy the farm?).
Alex
Hello
>
> support for compiling and execution of Procedures in Postgres is nonexistent
> 99% of SQL code in either Oracle and MySQL DB's are written in
> Procedures..trying to port that to Postgres is a very long and tedious
> uphill climb
>
This seems like a fallacious claim as MySQL only introduced procedures in 5.0 and their implementation was incomplete and has been kind of incremental since 5.0 and still isn't complete, whereas plpgsql in Postgresql is a well advanced implementation that works very well (Other than speed). Given that it is also possible to implement functions in Perl, C, Java and Python, it seems that you can achieve pretty much anything with a function in Postgresql, which is not true in Oracle or MySQL. Oracle makes extensive use of views and sql files, but not so much stored procs in the core distro if I remember rightly, certainly not 99%.
true compilation is necessary only for some cases (note: MySQL stored
procedures are not compiled too ~ PostgreSQL has similar language
plpgpsm with little bit faster execution
(http://www.pgsql.cz/index.php/SQL/PSM_Manual ). When plpgsql is
potentially slow, you can use perl or write own custom function in C,
what is simpler than with Oracle.
Anyone who is using Perl for something that needs to be fast is seriously misguided. My benchmarking to date shows that Perl is the slowest of the mainstream second/third gen languages. Even python is faster and python can be a dog (Having said that, python 3 looks to be about twice as fast though which is quite an improvement)
True compilation is necessary for all cases if you care about scalability, which ultimately everybody does as they will continue to run more and more sites/databases on a set of servers until the CPU/IO limit is reached - it's called business - you maximize resources. Plpgsql's lack of compilation dramatically lowers that threshold, which means smaller profits for hosters of Postgresql and serious limits on OLTP scalability when plpgsql functions are utilized. Plus do you really want your hosted people writing functions in C for your Postgresql? Hell, _I_ don't want to write functions in C for Postgresql much, plpgsql is much less error prone and much easier to deal with.
If I had to ask the Postgresql people to put one thing on the wish list for the next major release it would be compiled functions, it's the one thing my apps would benefit the most from as I like keeping data integrity, and functions help me achieve that with elegance (I could tell you some stories about data integrity with data feeds from other companies who clearly didn't actually hire a DBA to design their database implementation, every row in it's own transaction because you never know when it's going to violate foreign key constraints, totally sucktastic). (The other thing would be cache management, I know that my system would benefit hugely from being me being able to direct certain tables to remain in memory regardless of MRU data - It seems like something might be possible with RAM disks but how do you sync it back to physical disk in a reliable way so that when your machine dies your data doesn't buy the farm?).
