Re: Sun acquires MySQL

I love Postgresql to death, it's one of the shining stars of the Open Source
movement IMHO. It's rock solid, crashes less frequently than Oracle in my
experience, and does almost everything I could ask of it (granted - I don't
ask much often, just simple things like consistent behaviour, which seems to
elude many other products). My one biggest bone to pick with Postgresql is
that stored procedures are not compiled. It makes writing anything but the
most trivial things in plpgsql stupid because it will slow the crap out of
your queries. For example: I wrote a simple function to return the distance
between two lat longs in plpgsql. Not only did it choke on values that were
part of a valid domain when calling acos() (I have a list of them someplace
that I keep meaning to post as it seems like a really bad bug), it was
slow. I re-implemented in C and it was 8-12 times faster, and didn't error
out on acos for the same values. Expecting DBAs to be able to write
functions in C IMHO is a bit unrealistic. I am far from a typical DBA, I've
met precious few Oracle DBAs who could write functions in C. Trying to
implement good database code that is atomic and makes good use of functions
in Postgresql is an uphill battle because they slow the database down so
much.

On Jan 20, 2008 12:04 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> 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?).

Alex