Re: Truncate if exists - Mailing list pgsql-hackers

On Mon, Oct 15, 2012 at 10:34 AM, Greg Stark <stark@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
proposalbut I think
 
we should be looking for ways to make this better.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company