Create a `ClientBindingCursor`?

[dvarrazzo]

Client-side statement can be merged using sql.SQL(). However we could create a cursor doing that automatically.

It would be handy in porting psycopg2 code more easily maybe, even if less efficient.

It could be made a mixin to add to ClientCursor and AsyncClientCursor, replacing the PostgresQuery with a PostgresClientQuery doing a different transformation.

[sureshdsk]

@dvarrazzo looks like something I can do. but I don't understand the use case clearly since I'm not familiar with sql.SQL. can you give me some examples of how the existing merging works with manual steps and how the expected automatic way?

[dvarrazzo]

Hi @sureshdsk,

Psycopg 2 composes the queries client-side: if you run execute("insert into table values (%s)", ["hello"]) it will create a string on the client, which is valid sql (e.g. insert into table values ('hello')) and send it to the server.

Psycopg 3 uses a different mechanism and passes the query and argument separated, using a different protocol: it transform the query to postgres parameters (insert into table values ($1)) and leaves the server do the binding.

This has advantages in terms of performance, memory usage, flexibility. However it only works with certain statements (SELECT, data manipulation statements) but not with others (data-definition statements and others). So you cannot run anymore cur.execute("SET TimeZone to %s") or alter table foo set default %s.

The main workaround is to use SQL composition with the objects in the psycopg.sql module. This however requires the strings to be translated because it uses a third way of specifying placeholders (e.g. alter table foo set default {}).

The issue is explained in the docs: https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding

So, the idea would be to have a cursor which, given a cur.execute("alter table foo set default %s", [42]), internally would transform the statement and call sql.SQL("alter talbe foo set default {}").format(sql.Literal(42)) and then send the query already merged to the server (using PGconn.exec_() instead of PGconn.exec_params()).

However:

I opened this ticket to keep a note of this, because it's doable, but nobody has asked for it yet. My idea is that if people start pressuring that composing queries client-side explicitly is difficult or is a chore, we could introduce it. But at the moment no such pressure has materialised yet: it might be because the library is not very used yet, or it might be that people just understand the issue and use the workaround proposed.

So, right now, I wouldn't work on this task. It is a bit of a solution in search of a problem, and adds a new way of doing something that is already possible. If we start gathering use cases which would see this object useful we might have more material to design it.

[dvarrazzo]

A comment from #163: how to retain types information? (@zzzeek)

The transformation suggested above discards the parameter types so things may break badly. Normally we pass types:

>>> cur.execute("select %s + %s", (date(2020, 1, 1), 30)).fetchone()[0]
datetime.date(2020, 1, 31)

But if we convert arguments trivially the date won't be recognised, even if correctly quoted, as it will have lost the type:

>>> cur.execute(sql.SQL("select {} + {}").format(date(2020, 1, 1), 30)).fetchone()[0]
...
InvalidTextRepresentation: invalid input syntax for type integer: "2020-01-01"
LINE 1: select '2020-01-01' + 30
               ^

we need some casts here. We can get them from the same place server-side binding takes them, the cursor transformer:

>>> cur._tx.get_dumper(date(2020, 1, 1), 't')
<psycopg_c._psycopg.DateDumper at 0x7f0310041780>

>>> cur._tx.get_dumper(30, 't')
<psycopg_c._psycopg.Int2Dumper at 0x7f030bfcb3c0>

the dumpers have an oid property that can be used to look up to the TypeInfo object in the cursor (or the transformer) types map

>>> cur.adapters.types[cur._tx.get_dumper(date(2020, 1, 1), 't').oid]
<TypeInfo: date (oid: 1082, array oid: 1182)>

Its name can be used to create a cast.

All together it may look like:

>>> parts = []
>>> for arg in args:
...     # must special case arg is None
...     dumper = cur._tx.get_dumper(arg, 't')
...     # must special-case dumper.oid == 0
...     type_name = cur.adapters.types[dumper.oid].name
...     # must special-case arrays
...     parts.append(sql.SQL("{}::{}").format(sql.Literal(arg), sql.Identifier(type_name)))

# The result is safe and "strongly typed" via cast.
>>> print(sql.SQL(", ").join(parts).as_string(cnn))
'2020-01-01'::"date", 30::"int2"

These bits work as expected in a client-bound query

>>> cur.execute(sql.SQL("select {} + {}").format(*parts)).fetchone()[0]
datetime.date(2020, 1, 31)

P S. months later: this is happening in #205

[Andersson007]

Hello, I can give you an example why this thing is really needed. There's my draft for the issue I've been going to create there (i can create a separate issue if needed):

Draft

Hello and thank you for working on the adapter of version 3.

@hunleyd and I are maintainers of the community.postgresql Ansible collection which has been a part of Ansible package for many years and currently uses psycopg2.

We are trying to add psycopg3 support to the collection.

Question 1: We haven't found EOL date of psycopg2, does it exists? If not yet, are there any related plans?

Most of the statements are DDL in our modules.

Question 2: Are there any plans to introduce the parameterized DDL statement execution in the future or there are technical restrictions for that?

Context: In most cases DDL statements in the collection are combined dinamically,
so the result passed to execution can be different in the same function, for example
(I'll replace the statement variable and parameters with their possible values):

• cursor.execute("ALTER USER ... PASSWORD %(password)s", {"password": "lsdjfs", "expires": "2022-02-01"})
• cursor.execute("ALTER USER ... PASSWORD %(password)s VALID UNTIL %(expires)s", {"password": "lsdjfs", "expires": "2022-02-01"})
• cursor.execute("ALTER USER ... VALID UNTIL %(expires)s", {"password": "lsdjfs", "expires": "2022-02-01"})

As we can see it uses, as i call it, the named args format, for example, %(password)s and the dictionary containing parameters, and in some runs all of the dict elements can be used and in some runs only a part.

As said in https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding , parameterized DDL statements
are not supported any more.

The doc suggests using psycopg.sql.SQL() and format() BUT it doesn't seem to support the %(key)s format, but only {key} and others similar to string.format().

Question 3: Is that correct that format() does not understand things like %(key)s, only {key}? If yes, this is a huge issue for us (see the above examples) as it will require to write an alternative implementation for almost every function to support psycopg3 (tens of thousands lines of code.. + same for unit tests, etc., which is tough for 2 folks)

Question 4: If the things mentioned in questions 2 and 3 are impossible to change because of Server-side binding or whatever, is there a way (or plans to introduce it) for users to specify which binding (server- or client-side) the driver will use?

Looking forward to any feedback, thank you!

[dvarrazzo]

Hello @Andersson007 , thank you for the very detailed use case.

1. There is no eol for psycopg2, it's still immensely used. I am personally not planning to develop new features, but we can expect bugfix and porting to future Python and Postgres versions that are not total rewrites.

2. The fact that parametrised DDL is not supported is a PostgreSQL limit. I don't know if they have plans to extend the types of statements accepting parameters. I assume it's a much harder work for the server than for the client. Furthermore there are parts of SQL statents which you can consider semantically parameters but which postgres only accepts as identifiers. An user might want to execute CREATE USER %s PASSWORD %s; the server wants the first parameter unquoted, or in double quotes, the second in single quotes. Supporting it in the server would require changes to every statement at the parser level, solving it on the client side is much easier.

3. Yes, format() only understands {} and {name} because passing it %s and %(name)s would have required escaping every placeholder you might have wanted to compose and passing something like %%%% to LIKE. It was that way in the first iteration of the feature and it was harder to use.

4. I would like to provide this feature with a cursor subclass; using that subclass would be the way to switch, hence this ticket.

If we have to decide an interface for the feature, the original design would be to use the cursor_factory attribute:

conn = psycopg.connect(..., cursor_factory=ClientBindingCursor)
cur = conn.cursor()
cur.execute("CREATE USER %s WITH PASSWORD %s", [SQL.Identifier(username), password])

But that makes hard to use the same connection in two ways. Something like conn.cursor(server_side=True) would be much more friendly.

The plan is all in this ticket. The thing we need the most is... a proper name for his class 😄 Although that's less important if we use client_side=True.

There is an important piece of preparatory work, which is being implemented in #205. It's almost finished and, after that's merged, finishing this would be a breeze.

[Andersson007]

@dvarrazzo thanks for the detailed explanation!

I opened this ticket to keep a note of this, because it's doable, but nobody has asked for it yet. My idea is that if people start pressuring that composing queries client-side explicitly is difficult or is a chore, we could introduce it. But at the moment no such pressure has materialised yet

So please consider the pressure has materialized:)

The thing we need the most is... a proper name for his class

yeah, naming is hard

There is an important piece of preparatory work, which is being implemented in #205. It's almost finished and, after that's merged, finishing this would be a breeze.

That's cool, thanks for working on this! I'll pause the move, will be waiting for this feature then. The move would be doable after that.

[dvarrazzo]

@Andersson007 I have implemented a client-side cursor, using which Psycopg 3 behaviour should be similar to Psycopg 2.

Feedback is welcome, thank you very much :)

[Andersson007]

@dvarrazzo great!
I'll try to make time to continue its introduction ASAP and get back if there are any issues.
Thank you much!