Обсуждение: 2-to-3 Question about adapter using AsIs

Hi,

I am working on migrating our django codebase from ver 2 to ver 3+ and
have run into a code conversion issue that I have not quite been able
to figure out.

Previously for a generated column in our schema (dealing with accented
names) we used to have:

    from django.db import models
    from psycopg2.extensions import AsIs, register_adapter

    class PostgresDefaultValueType:
        pass

    register_adapter(PostgresDefaultValueType, lambda _: AsIs("DEFAULT"))

    class PostgresHandledTextField(models.TextField):
        def get_prep_value(self, value):
            return PostgresDefaultValueType()

This was based off of a comment on a ticket
(https://code.djangoproject.com/ticket/21454#comment:28) to be able to
handle DEFAULT columns in postgres when using Django.

Trying to convert this to psycopg3's updated adapter framework, what I
have tried so far is this:

    from django.db import models
    from psycopg import adapters, sql
    from psycopg.adapt import Dumper

    class PostgresDefaultValueType:
        pass

    class PostgresDefaultValueTypeDumper(Dumper):
        def dump(self, obj):
            return sql.DEFAULT

    adapters.register_dumper(PostgresDefaultValueType,
PostgresDefaultValueTypeDumper)

    class PostgresHandledTextField(models.TextField):
        def get_prep_value(self, value):
            return PostgresDefaultValueType()

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?

Any help in debugging this would be greatly appreciated

TIA.
AM

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

Hi.

On Mon, Nov 20, 2023 at 2:46 PM Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:

So it turns out that the Django default, at least for 4.2 is ClientCursors :)

https://docs.djangoproject.com/en/4.2/ref/databases/#server-side-parameters-binding

Which means I can get the `quote` based solution work ootb.

Thanks a ton for the clarification.
AM

> 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')"
>