Re: Executing on the connection? - Mailing list psycopg

On Wed, 2 Dec 2020 at 12:48, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:

> The only issue I see with this sort of approach is the problem of having
> to set the search path, which I believe needs to be done for each
> cursor where custom search paths are used.

No, being the search path a property of the session, it is a property
of the connection, and all the cursors on the same connection will
share the same.

The use case suggested is only for the barebone use of connection +
cursor + execute + fetch. I wouldn't advise it as the most generic
usage, only as a shortcut.


> > ...actually there will be no callproc at all in
> > psycopg3...

> Ah. We presently use callproc a lot, together with NamedTupleConnection,
> which works brilliantly for us.

The problem with callproc is that it has a confusing semantic in
itself: from the DBAPI
<https://www.python.org/dev/peps/pep-0249/#callproc>:

    """
    The result of the call is returned as modified copy of the input sequence.
    Input parameters are left untouched, output and input/output
parameters replaced
    with possibly new values.
    """

which 1) is silly unless you are writing C, 2) in postgres you don't
know if the params are I/O unless you do extra query to query the
catalog. Maybe it's modelled after databases using this precise same
interface, but postgres doesn't.

On top of that, Postgres also provides named parameters, which are
supported in psycopg2, and were in psycopg3. You can see how sprawling
it was to convert named and positional parameters from this commit,
where I finally pulled out the plug

<https://github.com/psycopg/psycopg3/commit/9369a3b3717c12d4a398fe0934994ad19cbc564a#diff-9e33206c8c2b3b18daec4b778457f6fc54cf03754747cd476833048fc374b9e6L364>.

On top of on the top of that, recent PostgreSQL added a new concept of
procedure, different from what is called stored procedure, and which
require CALL instead of SELECT:
<https://www.postgresql.org/docs/13/sql-call.html> Again, without
knowing the catalog, you cannot tell by name which is which, but
people would expect us to know:
<https://github.com/psycopg/psycopg2/issues/1155>.

If you know which parameters are positional and which are named, if
you know whether a stored procedure is a function or a new procedure,
users can just use cursor.execute(): it will be faster than composing
SQL dynamically. Using the newly available fetch*() chaining you can
get results directly from the call, which in psycopg2 needs two lines
of code:

    result = cur.execute("SELECT my_function(%s, 42, foo=%s)", ["bar",
baz]).fetchone()

This is pretty much the most efficient thing that can be done (bar
making it a prepared statement), because the libpq/postgres don't
offer any fast-path for function calls. There is this
<https://www.postgresql.org/docs/current/libpq-fastpath.html>, but
it's deprecatish, non-feature-complete, and requires a pg_catalog
lookup to find the function oid.

Hope this explains the rationale behind dropping callproc (which is an
optional method of the DBAPI so it doesn't make us less
standard-compliant by not implementing it). Feedback is welcome anyway
:)

Cheers,

-- Daniele