Re: Truncate if exists - Mailing list pgsql-hackers

On Mon, Oct 15, 2012 at 11:57 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> 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.

The places where *I* care about this are places where performance is
almost entirely irrelevant to the question.

When I'm writing 'scripts' that are doing this kind of thing, I'm
doing schema 'surgery', and, within reason, it's not particularly
performance sensitive.  I'm much more worried about DDL scripts being
repeatable and manageable than I am about them being fast.

So I'm going to elide the performance bits.

Robert, when you first tossed out the notion of:

do $$   begin       if (select 1 from pg_class where relname = 'foo' and
pg_table_is_visible(oid)) then           truncate table foo;       end if;   end
$$;

my first reaction was "Ick!   Why am I switching languages (e.g. -
from plain SQL to pl/pgsql), and running functions to do this?!?"

In retrospect, your later comments make it pretty clear that you're
not proposing that as the end state, just that that's the
functionality that needs to be run.

That would would be equivalent to my would-be-strawman syntax of:
   TRUNCATE TABLE public.foo IF EXISTS TABLE public.foo;

I'm comfortable that Dimitri didn't particularly love the idea of
stowing the conditional at the end; it was just a strawman proposal,
and what was particularly important to me was to make sure that it was
recognizable that other systems (e.g. - Perl, Ruby, probably SNOBOL)
have done the very same thing.  I'd be perfectly happy if someone came
up with something better.  The number of "+1"'s thus far is pretty
gratifying, mind you.

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

When I'm "managing schema", I have exactly *zero* interest in
switching over to Perl or Python.  Those aren't languages for managing
database schemas, and, if I wind up using them, my code is going to be
rife with context switches as I'm switching between  "oh, am I writing Perl code?"   and  "Am I attached to the right
Perldatabase connection object, with
 
the proper transaction context?"   and  "Oh, here is the SQL DDL for managing the schema."

Two of these three varieties of contexts are distracting sidelines to
me.  Guess which are the two?  :-)
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"