Re: A PGsendQuery API design for your review

On Tue, 02 Feb 2016 19:58:16 +0100
Federico Di Gregorii <fog@dndg.it> wrote:

> I can say with some confidence that we'll never implement such an
> API. Even the
> ux, i.e., accepting multi-queries from the user is wrong, imho. Let
> the user
> write single queries, organize them, exrcute them using bound
> variables and NOT
> mogrify() and then return the result. Anything less is lazy.

I'm don't think you've a complete picture of my users, what
they're doing, and why it is useful to submit multiple queries
at once.

I support these people:
http://amboselibaboons.nd.edu/
http://gombechimpanzees.org/

Who use interfaces like:
http://papio.biology.duke.edu/babasewiki/MyStartingPage
https://gombemi.ccas.gwu.edu/gombemiwiki/Main_Page

My users are scientists, from undergraduates to senior
researchers and American Academy of Science members, with a wide
variety of SQL expertise.  They have direct access to their
databases, writing SQL to explore their data.  They want a
simple, comprehensive interface.  (One that not only provides
support for SQL but also allows the unsophisticated to create
their own tables, etc.  Which is neither here nor there.)

They repeatedly try different queries and compare results,
iterating until they tease the data apart.  They might write a
query, run it, modify it, and then run both the modified and the
original version.  They want to see the results in a single
window so they can compare.  They want to be able to edit and
tweak either or both and hit a "resubmit" button and again review
the results.

Some are sophisticated SQL users; regardless most don't want to
think about SQL.  It's easier for them to cut and paste the same
query 3 times and tweak the SQL to get 3 results (adult males,
adult females, juveniles) than to try to write a single query
that does a GROUP BY.  But they do want to be able to see and
compare the results of the 3 queries.

They use SQL post-validate their data and check for sanity after
a bulk data upload.  They have a file containing from a few to
10s of SQL queries that they cut and paste into a "SQL window"
and run.  Usually many of the queries produce a single number as
output.  Then they examine the output and see if the numbers add
up.

They use sets of SELECT INTO queries to copy, or pre-join, or
summarize data in the master schema, moving it into their own
schema for further analysis.  They again use cut-and-paste to
replay these operations when there's new data from a fresh bulk
data upload.  (They likewise have full permissions, within their
own schemas, to create whatever tables they need to support their
analysis.)

Etc.

Of course everything _could_ be done by submitting a single query
at a time.  (Or an interface that requires such parsing by the
end-user, and then runs them in bulk.  Or some sort of arbitrary
file format that contains easily parse-able statement delimiters.
Etc.)  But an interface that allows submission of multiple
statements in a single go, and review of all the results at once,
greatly simplifies their work flow.  (And it's nice that their
interface has point-and-click table creation, deletion, etc.)

I'm surprised (Shocked I say!  Shocked! :-) that you'd want to
place arbitrary restrictions on the user interfaces that can be
created with psycopg2.  And Sir!  I must take offense at the
implication that my users are lazy.  They work hard to press the
semicolon key to end their statements!  ;-)

Seriously, the real world is complex.  If PG allows me to create
a user interface where the SQL parsing into separate statements
is done by PG then psycopg2 should let me do the same.  There's
a reason you can pipe multiple SQL statements to psql without
having to pre-parse them into separate statements.

If you really want to reject the idea of accepting multiple
queries from the user let me know and I'll not pursue the
matter.

Regards,


Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein