Re: 2-to-3 Question about adapter using AsIs

Hello,

On Mon, 20 Nov 2023 at 19:58, Ams Fwd <ams(dot)fwd(at)gmail(dot)com> wrote:

> As far as I can tell from the documentation the `sql.DEFAULT` should
> be the appropriate thing to put in the dumper so that generated query
> uses `'DEFAULT'` in the correct place during query generation.
>
> However when I do use this I run into
>
> > ???
> E TypeError: bytes or buffer expected, got <class 'psycopg.sql.SQL'>
>
> psycopg_binary/pq/pqbuffer.pyx:111: TypeError
>
> which in some ways makes sense as `AsIs` previously did something
> special and it's not the same? Looking at the code, it should merely
> be doing `PyUnicode_AsUTF8String` but I am assuming that `sql.DEFAULT`
> is not generating the appropriate quoted string?

Psycopg 3 uses server-side binding
(https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding)
as a consequence, the DEFAULT value cannot be passed as a query
argument, because it is an SQL construct, not a value.

You can force client-side binding (like psycopg2 does) by using a
ClientCursor instead of the ordinary Cursor class
(https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#client-side-binding-cursors)
The ClientCursor will call the `quote()` method on the dumper
(https://www.psycopg.org/psycopg3/docs/api/abc.html#psycopg.abc.Dumper.quote)
which should return the value's literal (so, a normal SQL literal
would need to be single-quoted; the DEFAULT singleton doesn't want
quotes as it's not a value).

So, your attempt was always right: your dumper should look like:

class PostgresDefaultValueTypeDumper(Dumper):
def dump(self, obj):
raise NotImplementedError("can't represent DEFAULT as
server-side value")
def quote(self, obj):
return b"DEFAULT"

but you can only use `PostgresDefaultValueType` with a ClientCursor
(or in a `sql.Literal()`); you can visualize the query that a client
cursor would execute using the classic `mogrify()`:

>>> cur = psycopg.ClientCursor(conn)
>>> cur.mogrify("INSERT INTO tbl (f1, f2) VALUES (%s, %s)",
[PostgresDefaultValueType(), "hell'o"])
"INSERT INTO tbl (f1, f2) VALUES (DEFAULT, 'hell''o')"

However you need to negotiate with Django the creation of a
client-side cursor. I don't know if Django normally uses, or allows to
use them. It's easy to create one one-off, as you see from the
example, but integration with the Django ORM is... left as exercise
(you may ask Django people about it, they would be more informed than
I am).

The `sql.DEFAULT` object is not designed to be passed as value for a
query. It can only participate in query composition using the
psycopg.sql objects
(https://www.psycopg.org/psycopg3/docs/api/sql.html) but using it
would require coding a query execution in a way much more different
than the normal query, for instance using `{}` placeholders, for
instance:

cur.execute(sql.SQL("INSERT INTO tbl (f1, f2) VALUES ({},
{})").format(sql.DEFAULT, "hell'o"))

I would rather go the ClientCursor way in this case.

Cheers

-- Daniele