Re: pgbench \for or similar loop

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.
Maybe that's useful enough to make it worth doing: I'm not sure.

Now pgbench is a bit of a different case, because presumably in that
case it matters somewhat whether the work happens on the client side
or the server side, though I think in your particular case not really.
Actually in that case it seems like you could do the whole thing in
one SQL statement even without DO, using INSERT INTO foo SELECT ...
FROM generate_series(1,10000) g.

If we are going to add more scripting capability, it would be nice to
have a bit of consistency between pgbench and psql in terms of these
backslash commands, and maybe some kind of sketch of what the overall
design looks like.  For example, if \for is defined as a loop over
integers, whatdya do if you want to loop over query results or arrays?
See recent discussions of these issues in relation to plpgsql.  I
don't really see a reason to oppose adding functionality like this
categorically, but I do think it should be carefully thought out and
well-designed so we don't box ourselves into a corner; and we should
know what use cases we are shooting at.

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