Re: pgbench \for or similar loop

On Tue, Apr 19, 2011 at 10:22 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Apr 18, 2011 at 5:37 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
>> Excerpts from Merlin Moncure's message of lun abr 18 18:26:54 -0300 2011:
>>> On Mon, Apr 18, 2011 at 4:02 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>>
>>> > begin;
>>> > \for iterator 1 10000
>>> >  \setrandom foo 1 :iterator
>>> >  insert into foo values (:foo);
>>> > \end
>>> > commit;
>>> >
>>> > Would something like this be acceptable?
>>>
>>> *) what does this do that isn't already possible with 'DO' (not being
>>> snarky, genuinely curious)?
>>
>> Uhm, not sure.  I'm not really used to having DO available so I didn't
>> think about it.  I'll look at it a bit more.
>>
>>> *) should psql get some of these features?  simple logic and looping
>>> would be a nice addition?
>>
>> I dunno.  They have been proposed and shot down in the past.  Apparently
>> people don't want psql to become too powerful.  ("But that would make
>> psql turing complete! Soon you're going to want to have \while on it!").
>> I think pgbench is supposed to be designed to handle data in bulk which
>> is why I started using it for this in the first place.
>
> [ woops, just realized that i sent this response off-list the first time ]
>
> I do think that DO covers a lot of the same territory that could
> usefully be addressed by a more powerful backslash-command language in
> psql.  It's in some ways quite a bit more powerful, because it's
> available from any client, and it knows about data types, which psql
> doesn't, so things like \while are going to be awkward (what
> comparison semantics will it use?).  The only advantage I can really
> see to doing that stuff on the client side is that you could do things
> like \connect and \prompt that wouldn't make sense on the server side.

Well, you missed one big advantage: with DO you are 'one transaction'
limited -- this makes it unsuitable for certain classes of maintenance
tasks (no vacuum etc), creating a lot of tables, long running (even
infinite) scripts etc.  It would remain a boutique feature more or
less, but would add a lot of value to psql scripting which is
particularly suffering from adequate error handling.

Ultimately if you have stored procedures then you have the best of
both worlds especially if you had a method of sending the procedure
body as you can with functions and DO.   A psql meta language would do
in a pinch though, and it would be a lot easier to implement -- don't
like the bifurcated implementation (psql and pgbench) though.

merlin