PostgresSQL: Reflecting tables fails if column's domain datatype is BIT or UUID

[Former user]

Hello,

I'm using "Python 2.5", "SQLAlchemy 0.5.2" & "PostgreSQL 8.3.5" and I've found that if the columns in a table will use domains and the datatype of one domain is BIT or UUID, the table reflection will fail giving out the following error:

"File "C:\Python25\lib\site-packages\sqlalchemy-0.5.2-py2.5.egg\sqlalchemy\databases\postgres.py", line 546, in reflecttable if coltype: UnboundLocalError: local variable 'coltype' referenced before assignment"

Please see below Python and Postgres scripts I'm using.

Thank you

#Python script (modify user/password below in both places)

import sqlalchemy.engine as engine
import sqlalchemy.pool as pool
import sqlalchemy.schema as schema
import psycopg2

class Test(object):

    def __init__(self):

        p = pool.QueuePool(self.__getconn, max_overflow=10, pool_size=20)
        e = engine.create_engine('postgres://postgres:postgres@localhost:5432/test')

        meta = schema.MetaData()
        meta.reflect(bind = e, schema = 'public')

    def __getconn(self):
        try:
            DSN = "dbname='test' user='postgres' host='localhost' password='postgres'"
            c = psycopg2.connect(DSN)
            # execute an initialization function on the connection before returning
            c.cursor.execute('select version()')
            return c
        except Exception, err:
            print "__getconn: ", err


if __name__ == "__main__":
    test = Test()







-- Postgres script:

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

CREATE DOMAIN domain_bit AS bit(1) NOT NULL;
ALTER DOMAIN public.domain_bit OWNER TO postgres;
COMMENT ON DOMAIN domain_bit IS 'Bit 1';

CREATE DOMAIN domain_bit128 AS bit(128) NOT NULL;
ALTER DOMAIN public.domain_bit128 OWNER TO postgres;

CREATE DOMAIN domain_uuid AS uuid NOT NULL;
ALTER DOMAIN public.domain_uuid OWNER TO postgres;

SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE test_domains (
    col1 domain_uuid NOT NULL,
    col2 domain_bit NOT NULL,
    col3 domain_bit128 NOT NULL
);

ALTER TABLE public.test_domains OWNER TO postgres;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

[Former user]

I'm the one who posted the initial issue and I've found a workaround (possible solution). In module sqlalchemy.databases.postgres I created two new classes:

class PGBit(sqltypes.Binary):
    def get_col_spec(self):
        return "BIT"

class PGUUID(sqltypes.String):
    def get_col_spec(self):
        return "UUID"

and added two new entries to ischema_names dictionary

    'bit' : PGBit,
    'uuid' : PGUUID,

This seems to be enough as psycopg doesn't have special datatypes for BIT and UUID

[Mike Bayer]

we should add the UUID and bit types as you stated above. But also reflection should degrade gracefully for unknown types - it breaks here because of the usage of domains. This patch should resolve that issue, which would need some tests:

Index: lib/sqlalchemy/databases/postgres.py
===================================================================
--- lib/sqlalchemy/databases/postgres.py    (revision 5812)
+++ lib/sqlalchemy/databases/postgres.py    (working copy)
@@ -527,6 +527,7 @@
             elif attype == 'timestamp without time zone':
                 kwargs['timezone']('timezone') = False

+            coltype = None
             if attype in ischema_names:
                 coltype = ischema_names[attype](attype)
             else:
@@ -540,8 +541,6 @@
                             # It can, however, override the default value, but can't set it to null.
                             default = domain['default']('default')
                         coltype = ischema_names[domain['attype'](domain['attype')]
-                else:
-                    coltype = None

             if coltype:
                 coltype = coltype(*args, **kwargs)

[Former user]

I applied the patch and indeed I have now a SAWarning instead of an error:

C:\Python25\lib\site-packages\sqlalchemy-0.5.2-py2.5.egg\sqlalchemy\engine\base.py:1265: SAWarning: Did not recognize type 'domain_uuid' of column 'col1'
  self.dialect.reflecttable(conn, table, include_columns)

I will keep for now the classes I mentioned above until they will be integrated in the main development. I was thinking if it's right to use sqltypes.String to represent an UUID, but I see no problem since we only work here with a DDL definition and not an actual data validation. The simple UUID label should be used afterward to check the actual string to be compliant with UUDI rules.

Thanks for looking into this.

[Mike Bayer]

both changes are applied in 77d6d31542ab2365f218d7305309d6c3468a36dc.

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)