Issues

Issue #2946 resolved

standard_conforming_strings prior to PG 8.2

Anonymous created an issue

SQLAlchemy 9.1 does not support connecting to RedShfit because of the change in dialects/postgresql/base.py:

class PGDialect(default.DefaultDialect):
    ...
    def initialize(self, connection):
        ...
        self._backslash_escapes = connection.scalar(
                                    "show standard_conforming_strings"
                                    ) == 'off'

This crashes because there is no standard_conforming_string defined in redshift. I think this check needs to be added "self.server_version_info >= (8, 2)", as the documentation for postgres starts mentioning this in 8.2. http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html

This was working on older versions of SQLAlchemy but I need to also connect to a postgres 9.3 db in my app, so I needed to upgrade.

For now I've made a simple work around:

Register the dialect in my app:

from sqlalchemy.dialects import registry
registry.register("redshift", "myapp.dialects.redshift", "RedShiftDialect")

Define a new dialect in my app:

from sqlalchemy.dialects.postgresql.base import ENUM
from sqlalchemy.dialects.postgresql.psycopg2 import PGDialect_psycopg2
from sqlalchemy import types as sqltypes


class RedShiftDialect(PGDialect_psycopg2):
    def initialize(self, connection):
        # Don't call super, it will crash, manually copying code that super would call
        #super(PGDialect, self).initialize(connection)
        self.implicit_returning = self.server_version_info > (8, 2) and \
            self.__dict__.get('implicit_returning', True)
        self.supports_native_enum = self.server_version_info >= (8, 3)
        if not self.supports_native_enum:
            self.colspecs = self.colspecs.copy()
            # pop base Enum type
            self.colspecs.pop(sqltypes.Enum, None)
            # psycopg2, others may have placed ENUM here as well
            self.colspecs.pop(ENUM, None)

        # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689
        self.supports_smallserial = self.server_version_info >= (9, 2)

        # This breaks redshift, if a version check was here or exception
        # protection the RedShiftDialect would not be needed
        ###Suggest Adding Check: if self.server_version_info >= (8, 2): ###
        #self._backslash_escapes = connection.scalar(
        #                            "show standard_conforming_strings"
        #                            ) == 'off'

        # Don't call super, it will crash, manually copying code that super would call
        #super(PGDialect_psycopg2, self).initialize(connection)
        self._has_native_hstore = self.use_native_hstore and \
            self._hstore_oids(connection.connection) is not None
        self._has_native_json = self.psycopg2_version >= (2, 5)

Comments (3)

  1. Mike Bayer repo owner

    here's a patch, can't commit it bc bitbucket is down:

    diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
    index 29584d1..43e77d5 100644
    --- a/lib/sqlalchemy/dialects/postgresql/base.py
    +++ b/lib/sqlalchemy/dialects/postgresql/base.py
    @@ -1567,7 +1567,8 @@ class PGDialect(default.DefaultDialect):
             # http://www.postgresql.org/docs/9.3/static/release-9-2.html#AEN116689
             self.supports_smallserial = self.server_version_info >= (9, 2)
    
    -        self._backslash_escapes = connection.scalar(
    +        self._backslash_escapes = self.server_version_info < (8, 2) or \
    +                    connection.scalar(
                                         "show standard_conforming_strings"
                                         ) == 'off'
    

    but also, for redshift can you please use:

    https://pypi.python.org/pypi/redshift-sqlalchemy

    it's listed here in the docs: http://docs.sqlalchemy.org/en/rel_0_9/dialects/index.html#external-dialects

    redshift is not exactly postgresql in any case so the project there is maintaining compatibility.

  2. Anonymous

    Perfect thanks!

    I saw that but the requirements stated SQLAlchemy 0.8, and I need a newer version of SQLAlchemy as 0.8 failed when trying to connect to postgres 9.3 (and I need to be connected to both DBs in my app right now). I also looked and saw that it's code was inheriting off of PGDialect_psycopg2, so it would of had the same problem if it is in fact compatible with 0.9, without modifications.

    Thanks again.

  3. Log in to comment