pg8000 has native uuid support, allow it through (was: Support UUID on pg8000)

[Donald Stufft]

It would be really awesome if the PostgreSQL support for UUIDs worked on pg8000. It appears like this should be relatively simple to do since pg8000 supports UUIDs, the main thing that needs changed is the result processor needs to not pass an already existing UUID object into the Python uuid.UUID class, and instead just return it.

I worked around this locally by simply doing:

from sqlalchemy.dialects.postgresql import UUID as _UUID

class UUID(_UUID):

    def result_processor(self, dialect, coltype):
        if self.as_uuid:
            _process = super().result_processor(dialect, coltype)

            def process(value):
                if isinstance(value, uuid.UUID):
                    return value
                return _process(value)
            return process
        else:
            return None

Which seems to work to me, but I haven't dug into it too deeply to see if there is a reason why it is the way it is.

[Mike Bayer]

OK, this is not (yet) a blanket "won't fix", but let me show you the problems I have with pg8000:

1. it is almost unmaintained. Compare https://github.com/mfenniak/pg8000/graphs/contributors to https://github.com/psycopg/psycopg2/graphs/contributors .

2. It is very slow. I know this because I worked on it quite a bit years ago to make it less than comically slow, but still, it is based on an interesting but very inefficient strategy to apply correct typing and paramstyles to the statement (so that prepared statements can be used, which ironically is for reasons of performance), involving a complex character-by-character scan for every SQL statement.

3. it lacks either fully working support, or in some cases support at all, for basic Postgresql datatypes such as arrays, JSONB, and as is the case here, UUID. psycopg2 supports all kinds of datatypes that pg8000 does not support or does not support completely. So here where we are looking for UUID, that implies I need to also chase after HSTORE, JSONB, RANGE, etc. for pg8000 which seems a little pointless seeing that psycopg2 has already done lots of work to handle all these types for us.

So at this point we get to, why do you need to use pg8000? psycopg2 is vastly preferred. Alternatively, why not request UUID support from pg8000 directly as the driver should be able to handle this datatype natively?

[Donald Stufft]

I don't have anything to say about (1, 2), but wrt to (3) it does support UUID, afaict, the problem is it's returning a UUID as a uuid.UUID from the database and it appears that SQLAlchemy assumes it is going to return it as a string, so it passes the value into uuid.UUID which explodes because it's already a uuid.UUID. My patch to the UUID type is literally just no-oping the reuslt_processor if the DB has already returned a UUID type.

As for why I was using pg8000, psycopg2 itself wouldn't work for me because I was using PyPy (well switching to PyPy from CPython) although I can (and was) using psycopg2cffi instead. However, I was running into a memory leak(ish?) bug with PyPy and one attempt to narrow it down was switching to a pure Python driver to see if there was an issue with PyPy's CFFI + GC support (see https://bitbucket.org/cffi/cffi/issues/320/improve-memory_pressure-management). I was attempting to use pg8000 for that role, and came across this with an apparently simple fix so figured I'd toss open an enhancement request (I assumed that pg8000 backend was relatively unloved and maybe the UUID support in pg8000 was new) to see if there was any interest.

Unfortunately for reasons unrelated to pg8000's UUID support I wasn't able to successfully run my codebase on pg8000 (probably relating to that correct typing thing, it was yelling at me about typing) so I abandoned attempting to use it. Given your response I'm just going to go ahead and close this issue, since it doesn't really affect me anymore for other reasons and you have reasonable reasons why not spending effort on pg8000 is a sane thing to do.

[Donald Stufft]

I think I closed this correctly, if not feel free to fix it.

[Mike Bayer]

if pg8000 is doing native UUID, then we do want to support that, and this is a bug, not a feature request. we need to set the right flags and enable the uuid tests for pg8000.

[Donald Stufft]

Ok cool, well glad my flailing around was useful then! I did not test pg8000 deeply, but it was giving me uuid.UUIDs out of the DB and it's docs claim UUID support so I assume it does.

[Mike Bayer]

Enable uuid for pg8000

Enabled UUID support for the pg8000 driver, which supports native Python uuid round trips for this datatype. Arrays of UUID are still not supported, however.

Change-Id: I44ca323c5d9f2cd87327210233bc36a3556eb050 Fixes: #4016

→ <<cset c86b95038d84>>