Re: 2-to-3 Question about adapter using AsIs - Mailing list psycopg

Hello,

On Mon, 20 Nov 2023 at 19:58, Ams Fwd <ams.fwd@gmail.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