On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark <st...@mit.edu> wrote: > I'm a bit lost. I would think pl/pgsql is precisely the same as > Oracle's pl/sql and MS's T-SQL. I see the complaint you have as a > purely implementation detail. I don't think pl/pgsql is the best > implemented part of Postgres but I don't see how integrating it into > the core is going to automatically make it all wonderful either.
It isn't. But (1) there would be a significant usability benefit in not having to surround procedural logic with DO $$ BEGIN ... END $$ and (2) PL/pgsql's performance issues seem to revolve around the fact that you don't get one big ol' plan thingy that can be passed to the executor and run; instead, you interpret each statement separately and pass them off to the executor one piece at a time. It wouldn't technically be necessary to integrate the code fully into core into realize these benefits; you could maintain some abstraction layer in between and provide an API to push information back and forth. But to take a trivial example, consider a FOR loop that executes an enclosed SQL statement a large number of times. Right now, we build a plan tree for the SQL statement and then start up and shut down the executor N times. If we could instead push an "iterate" not on top of the plan tree to handle the iteration, and then start up the executor, run the plan, and shut down the executor, my guess is that it would be way faster than our current implementation. Everything I've seen leads me to believe that the executor is quite zippy when it gets going, but bouncing in and out of it repeatedly seems to be a source of real pain. > Fwiw my experience has consistently been that life got better whenever > I moved anything I had implemented as PL/SQL or PL/pgsql into client > code in Perl or Python. Hmm... I've had the opposite experience, which I guess is why I've got strong feelings about this. I've found that checking for uniqueness violations without relying on the database doesn't really work due to concurrency issues, and once I've got to catch that error from the database side and expose it to the user as a nicely-formatted complaint (the name you have chosen is already in use; please choose another) I have found that it seems to make sense to push everything other than the initial, relatively trivial syntax checking into PostgreSQL. Anyway, I think there's probably more than one sensible design decision there and may come down to personal preference and toolchain selection more than anything. Whatever either of us think, though, the complaint at the top of this thread indicates that people are NOT happy doing this on the client side and DO isn't convenient enough either. What do we do about that? I'm not extraordinarily attached to any specific proposal but I think we should be looking for ways to make this better. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
